PLTWEB.git - Gitblit

package com.vci.client.utils.excel;
 
import java.awt.Component;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Locale;
import java.util.Map;
 
import org.apache.poi.hssf.OldExcelFormatException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import com.vci.client.ui.exception.VCIException;
import com.vci.client.ui.swing.VCISwingUtil;
import com.vci.client.ui.swing.components.VCIJOptionPane;
 
/**
 * <p>
 * Title: read infos from excel file or write infos into excel file.
 * </p>
 * <p>
 * Description:
 * </p>
 * <p>
 * Copyright: Copyright (c) 2009
 * </p>
 * <p>
 * Company: VCI
 * </p>
 * 
 * @author eddie
 * @time 2009-6-15
 * @version 1.0
 */
public class ExcelFileOperation {
 
    public ExcelFileOperation() {
 
    }
 
    /**
     * 写入Excel
     * <p>Description: </p>
     * @author: xchao
     * @time 2014-10-24 下午5:51:15
     * @param path 文件路径 
     * @param excelInfoMap 数据 key：sheetName value：String[][] 形式的数据
     * @param settingMap 为每个sheet 设置的样式 key：sheetName value：ExcelCellStyleSettingCallback 形式的数据
     * @return 是否写入成功
     */
    public boolean writeExcelFileInfo(String path, Map<String, String[][]> excelInfoMap, 
            Map<String, ExcelCellStyleSettingCallback> settingMap){
        boolean isSucess = false;
        if(path == null || excelInfoMap == null){
            return isSucess;
        }
        FileOutputStream fos = null; 
        try {
            fos = new FileOutputStream(path);
            Workbook wb = null;
            boolean isEndWithXlsx = path.endsWith(".xlsx");
            if(isEndWithXlsx){
                SXSSFWorkbook swb = new SXSSFWorkbook(100);
                swb = new SXSSFWorkbook(100);
                swb.setCompressTempFiles(true);
                wb = swb;
            } else{
                // 用 HSSFWorkbook 处理2003或以下版本EXCEL文件
                wb = new HSSFWorkbook();
            }
            Iterator<String> its = excelInfoMap.keySet().iterator();
            while(its.hasNext()){
                String sheetName = its.next();
                Sheet sheet = wb.createSheet(sheetName);
                String[][] excelInfos = excelInfoMap.get(sheetName);
                if(excelInfos == null) continue;
                ExcelCellStyleSettingCallback setting = settingMap == null ? null : settingMap.get(sheetName);
                isSucess &= writeExcelInfo(wb, sheet, excelInfos, setting, isEndWithXlsx);
            }
            wb.write(fos);
            fos.close();
            isSucess = true;
        } catch (Exception e) {
            e.printStackTrace();
            isSucess = false;
            throw new RuntimeException(e);
        } finally{
            if(fos != null){
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return isSucess;
    }
    
    private boolean writeExcelInfo(Workbook wb, Sheet sheet, String[][] excelInfos, ExcelCellStyleSettingCallback setting, boolean isEndWithXlsx){
        boolean isSucess = false;
        WorkboolStyleSetting wbStyleSetting = null;
        LinkedHashMap<String, CellStyle> styleMap = null;
        if(setting != null){
            wbStyleSetting = setting.doSetWorkbookStyle(wb);
            styleMap = wbStyleSetting.getStyleMap();
        }
 
        // 文本类型列样式
        DataFormat fmt = wb.createDataFormat();
        CellStyle textStyle = wb.createCellStyle();
        textStyle.setDataFormat(fmt.getFormat("@"));
 
        int row = excelInfos.length;
        int column = -1;
        for (int i = 0; i < row; i++) {
            Row rowData = sheet.createRow(i);
            column = excelInfos[i].length;
            for (int j = 0; j < column; j++) {
                Cell cell = rowData.createCell(j);
                if (excelInfos[i][j] == null) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue("" + excelInfos[i][j]);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                if(styleMap != null && styleMap.containsKey(""+i+"*"+j+"")){
                    CellStyle cs = styleMap.get(""+i+"*"+j+"");
                    cs.setDataFormat(textStyle.getDataFormat());
                    cell.setCellStyle(cs);
                } else{
                    cell.setCellStyle(textStyle);
                }
            }
        }
        
        // add by xchao 2013.10.28 begin
        if(!isEndWithXlsx){
            // 设置列的单元格类型为文本
            for(int i = 0; i < 256; i++){
                sheet.setDefaultColumnStyle(i, textStyle);
            }
        }
        // add by xchao 2013.10.28 end
        isSucess = true;
        return isSucess;
    }
    
    public boolean writeExcelFileInfo(String path, String sheetName, String[][] excelInfos, ExcelCellStyleSettingCallback setting){
        Map<String, String[][]> excelInfoMap = new LinkedHashMap<String, String[][]>();
        excelInfoMap.put(sheetName, excelInfos);
        Map<String, ExcelCellStyleSettingCallback> settingMap = new LinkedHashMap<String, ExcelCellStyleSettingCallback>();
        settingMap.put(sheetName, setting);
        return writeExcelFileInfo(path, excelInfoMap, settingMap);
    }
    
    /**
     * write infos into a excel file. The columns of every row are not equal.
     * 
     * @param path
     *            String, the file's whole path
     * @param sheetName
     *            String, sheet description
     * @param excelInfos
     *            String[][], the infos which will be writed into excel file
     * @return
     */
    public boolean writeExcelFileInfo(String path, String sheetName, String[][] excelInfos) {
        return writeExcelFileInfo(path, sheetName,excelInfos, null);
    }
 
    /**
     * 检查指定路径的excel文件是否是能够支持的EXCEL文件
     * @param parentComponent 父容器对象
     * @param path 文件完整路径 
     * @return
     */
    public boolean isSupportExcelFormat(Component parentComponent, String path){
        boolean res = false;
        boolean isXls = (path.endsWith(".xls") || path.endsWith(".xlsx"));
        // 如果是.xlsx格式，检查格式问题，会有卡钝现象，故简化处理
        if(isXls){
            return true;
        }
        File file = new File(path);
        // 如果文件后缀是excel格式，但文件不存在时，此时返回true
        if(isXls && !file.exists()) {
            return true;
        }
        if(file.length() == 0) return true;
        Workbook wb = null;
        POIFSFileSystem fs = null;
        try{
            // XSSFWorkbook 支持OFFICE EXCEL 2007，
            // 但不支持2003以下的，包含2003
            wb = new XSSFWorkbook(new FileInputStream(path));
            res = true;
        }catch(Exception ex){
            try {
                // 用 HSSFWorkbook 处理2003或以下版本EXCEL文件
                fs = new POIFSFileSystem(new FileInputStream(path));
                wb = new HSSFWorkbook(fs);
                res = true;
            } catch(OldExcelFormatException e){
                e.printStackTrace();
                VCIJOptionPane.showError(parentComponent, "Excel文件版本错误，当前支支持 Excel 97/2000/XP/2003/2007/2010");
                res = false;
            } catch(IOException e){
                e.printStackTrace();
                if(e.getMessage().contains("Invalid header signature")){
                    VCIJOptionPane.showError(parentComponent, "文件类型错误，不是有效的Excel文件！");
                }
                res = false;
            } catch(Exception e){
                e.printStackTrace();
                VCIJOptionPane.showError(parentComponent, "检查文件是否是有效的Excel文件时发生错误！\n" + e.getMessage());
                res = false;
            }
        }
        return res;
    }
    
    /**
     * 返回Excel文件里数据（全部Sheet）
     * @param path Excel 文件完整路径
     * @return
     * @throws VCIException
     */
    public String[][][] readExcelFileInfos(String path) throws VCIException{
        String[][][] res = new String[][][]{}; 
        Workbook wb = getWorkBook(path);
        if(wb == null){
            return res;
        }
        int sheetCount = wb.getNumberOfSheets();
        res = new String[sheetCount][0][0];
        for(int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++){
            res[sheetIndex] = readExcelFileInfo(path, sheetIndex);
        }
        return res;
    }
 
    /**
     * 返回Excel文件里的数据（根据指定的Sheet名称）
     * @param path Excel 文件完整路径
     * @param sheetName Sheet 名称
     * @return
     * @throws VCIException
     */
    public String[][] readExcelFileInfo(String path, String sheetName) throws VCIException {
        String[][] res = new String[][]{}; 
        Workbook wb = getWorkBook(path);
        if(wb == null){
            return res;
        }
        int sheetIndex = wb.getSheetIndex(sheetName);
        if(sheetIndex < 0){
            return res;
        }
        return readExcelFileInfo(path, sheetIndex);
    }
    /**
     * 返回Excel文件里的数据（默认返回第一个Sheet里的数据）
     * @param path Excel 文件完整路径
     * @return
     * @throws VCIException
     */
    public String[][] readExcelFileInfo(String path) throws VCIException {
        return readExcelFileInfo(path, 0);
    }
    
    /**
     * 返回Excel文件里的数据（根据指定的Sheet索引）
     * @param path Excel 文件完整路径
     * @param sheetIndex Sheet索引（0 ~ SheetCount - 1)
     * @return
     * @throws VCIException
     */
    public String[][] readExcelFileInfo(String path, int sheetIndex) throws VCIException {
        String[][] excelInfos = null;
        /**
         * 读取逻辑&&约束
         * 1、标题&抬头行不能出现任何的空数据列
         * 2、标题&抬头行不能出现纯数字列
         */
        // 取Sheet对象
        Sheet sheet = this.getWorkbookSheet(path, sheetIndex);
        if(sheet == null) return new String[0][0];
        LinkedList<String[]> datas = new LinkedList<String[]>();
        if(sheet.getLastRowNum() <= 0){
            throw new VCIException(String.valueOf("99996"), new Object[]{});// 该文件只有标题&抬头行或没有数据（连标题&抬头行都没有）！
        }
 
        StringBuffer sbRowValueString = new StringBuffer();
        // 取标题行有效的列数
        int firstRowColumnNum = this.getFirstRowColumnNum(sheet);
        for(int i = 0; i < sheet.getLastRowNum() + 1; i++){
            Row row = sheet.getRow(i);
            if(row == null) { // 整行都没有数据
                i++;
                continue;
            }
            sbRowValueString = new StringBuffer();    
            LinkedList<String> rowValue = new LinkedList<String>();
            for (int j = 0; j < firstRowColumnNum; j++) {
                Cell cell = row.getCell(j);
                String cellValueString = "";
                if(i == 0){// 标题行
                    // 移除数据列中前可在存在的"'"
                    String val = cell.getStringCellValue();
                    while(val.startsWith("'")){
                        val = val.substring(1);
                    }
                    cellValueString = val;
                    rowValue.add(val);
                    sbRowValueString.append(cellValueString);
                }else{// 数据行
                    // 处理数据行中的空数据
                    if(cell == null){
                        cellValueString = "";
                    }else{
                        cellValueString = getCellValue(cell);
                    }
                    rowValue.add(cellValueString);
                    sbRowValueString.append(cellValueString);
                }
            }
            // add by xchao 如果数据行的数据全部为空，则不添加
            if(sbRowValueString.length() != 0){
                datas.add(rowValue.toArray(new String[]{}));
            }
        }
        excelInfos = datas.toArray(new String[][]{});
        return excelInfos;
    }
    
    public static String getCellValue(Cell cell){
        String cellValueString = "";
        if(cell == null) return cellValueString;
        int cellType = cell.getCellType();
        NumberFormat numFormat = NumberFormat.getInstance(Locale.CHINA);
        SimpleDateFormat sdfDateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        // add by xchao 2012.11.22 处理日期类型和Cell
        if(cellType == Cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)){
            Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
            cellValueString = sdfDateTime.format(date);
        }
        else if(cellType == Cell.CELL_TYPE_BLANK){
            cellValueString = "";
        } else if(cellType == Cell.CELL_TYPE_BOOLEAN){
            cellValueString = cell.getBooleanCellValue() ? "1" : "0";
        } else if(cellType == Cell.CELL_TYPE_ERROR){
            cellValueString = "";
        } else if(cellType == Cell.CELL_TYPE_FORMULA){
            try{
                cellValueString = String.valueOf(cell.getNumericCellValue());
            } catch (Exception e) {
                cellValueString = cell.getRichStringCellValue().getString();
            }
        } else if(cellType == Cell.CELL_TYPE_NUMERIC){
//            String[] dataFormatStrings = cell.getCellStyle().getDataFormatString().replace("_", "").trim().split("\\.");
//            int maxFracDigits = 0;
//            if(dataFormatStrings.length == 2){
//                maxFracDigits = dataFormatStrings[1].length();
//                numFormat.setMaximumFractionDigits(maxFracDigits);
//                cellValueString = numFormat.format(cell.getNumericCellValue()).replace(",", "").trim();
//            } else {
//                cellValueString = cell.toString();
//            }
            //cell.setCellType(Cell.CELL_TYPE_STRING);
            //cellValueString = cell.getStringCellValue();
            DecimalFormat df = new DecimalFormat("#.#########");
            cellValueString = df.format(cell.getNumericCellValue());
        } else if(cellType == Cell.CELL_TYPE_STRING){
            // 移除数据列中前可在存在的"'"
            String val = cell.getStringCellValue();
            while(val.startsWith("'")){
                val = val.substring(1);
            }
            cellValueString = val;
        } 
        return cellValueString;
    }
 
    /**
     * 返回Sheet的标题&抬头行的实际有效的列数
     * @param sheet
     * @return
     * @throws VCIException
     */
    private int getFirstRowColumnNum(Sheet sheet) throws VCIException{
        int i = 0;
        Row row = sheet.getRow(i);
        int rowCellNum = row.getLastCellNum();
        int firstRowColumnNum = 0;
        for (int j = 0; j < rowCellNum; j++) {
            int columnIndex = j;
            int columnIndexToShow = columnIndex + 1;
            Cell cell = row.getCell(j);
            int cellType = 0;
            if(i == 0){// 标题行
                if(cell == null){
                    throw new VCIException(String.valueOf("99995"), new Object[]{columnIndexToShow});// 标题&抬头行中的第{0}列的不能为空！
                }
                cellType = cell.getCellType();
                if(cellType == Cell.CELL_TYPE_BLANK){
                    throw new VCIException(String.valueOf("99995"), new Object[]{columnIndexToShow});// 标题&抬头行中的第{0}列的不能为空！
                }
                if(cellType == Cell.CELL_TYPE_NUMERIC || cellType == Cell.CELL_TYPE_NUMERIC){
                    throw new VCIException(String.valueOf("99994"), new Object[]{columnIndexToShow});// 标题&抬头行中的第{0}列不能是纯数字！
                }
                // 计算标题行有效列数
                firstRowColumnNum += 1;
            }
        }
        return firstRowColumnNum;
    }
    
    /**
     * 批回指定文件的Sheet
     * @param path excel文件完整路径
     * @param sheetIndex Sheet 索引（从0 ~ SheetCount-1）
     * @return
     * @throws VCIException
     */
    private Sheet getWorkbookSheet(String path, int sheetIndex) throws VCIException{
        Workbook wb = getWorkBook(path);
        if(wb == null) return null;
        int sheetCount = wb.getNumberOfSheets();
        if(sheetIndex < 0 || sheetIndex >= sheetCount) return null;
        return wb.getSheetAt(sheetIndex);
    }
    
    private Workbook getWorkBook(String path) throws VCIException{
        Workbook wb = null;
        POIFSFileSystem fs = null; 
        try{
            // XSSFWorkbook 支持OFFICE EXCEL 2007，
            // 但不支持2003以下的，包含2003
            wb = new XSSFWorkbook(new FileInputStream(path));
        }catch(Exception ex){
            try {
                // 用 HSSFWorkbook 处理2003或以下版本EXCEL文件
                fs = new POIFSFileSystem(new FileInputStream(path));
                wb = new HSSFWorkbook(fs);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
                throw new VCIException("99999", new String[]{path});
            } catch (IOException e) {
                e.printStackTrace();
                throw new VCIException("99998", new String[]{path});
            } catch(Exception e){
                e.printStackTrace();
                throw new VCIException("99997", new String[]{path, e.getMessage()});
            }
        }
        return wb;
    }
    
    public static void main(String[] args) throws VCIException{
        String path = "E:/";
        String name = "test.xlsx";
        String[][] datas = new ExcelFileOperation().readExcelFileInfo(path + name);
        for(String[] row : datas){
            for(String val : row){
                System.out.print(val + "\t");
            }
            System.out.println("");
        }
//        writeTest();
//        readTest();
    }
    private static void readTest() throws VCIException{
        String path = VCISwingUtil.getExcelFileURL(null, false, "");
        System.out.println("readTest1:");
        readTest1(path);
        
        System.out.println("readTest2:");
        readTest2(path);
        
        System.out.println("readTest3:");
        readTest3(path);
        
        System.out.println("readTest4:");
        readTest4(path);
    }
    private static void readTest1(String path) throws VCIException{
        out(new ExcelFileOperation().readExcelFileInfo(path));
    }
    private static void readTest2(String path) throws VCIException{
        if(path == null) return;
        out(new ExcelFileOperation().readExcelFileInfo(path, 0));
        out(new ExcelFileOperation().readExcelFileInfo(path, 1));
        out(new ExcelFileOperation().readExcelFileInfo(path, 2));
        out(new ExcelFileOperation().readExcelFileInfo(path, 3));
    }
    private static void readTest3(String path) throws VCIException{
        if(path == null) return;
        out(new ExcelFileOperation().readExcelFileInfo(path, "Sheet1"));
        out(new ExcelFileOperation().readExcelFileInfo(path, "Sheet2"));
        out(new ExcelFileOperation().readExcelFileInfo(path, "Sheet3"));
        out(new ExcelFileOperation().readExcelFileInfo(path, "Sheet4"));
    }
    private static void readTest4(String path) throws VCIException{
        if(path == null) return;
        out(new ExcelFileOperation().readExcelFileInfos(path));
    }
    private static void out(String[][][] datas){
        for(String[][] data : datas){
            out(data);
        }
    }
    private static void out(String[][] datas){
        for(String[] row : datas){
            for(String cell : row){
                System.out.print(cell + "\t");
            }
            System.out.print("\n");
        }
    }
    private static void writeTest(){
        ExcelFileOperation efo = new ExcelFileOperation();
        boolean isRead = false;
        File file = null;
        String filePath = VCISwingUtil.getExcelFileURL(null, !isRead, "");
        if(filePath != null){
            file = new File(filePath);
        }
        if(file == null){
            System.exit(0);
        }
        LinkedList<String[]> list = new LinkedList<String[]>();
        for(int i = 0; i < 100; i++){
            LinkedList<String> row = new LinkedList<String>();
            for(int j = 0; j < 52; j++){
                row.add(String.valueOf(j));
            }
            list.add(row.toArray(new String[]{}));
        }
        String[][] excelInfos = {{"a","b","c"},{"123","456","789"}};
        excelInfos = list.toArray(new String[][]{});
        ExcelCellStyleSettingCallback setting = new ExcelCellStyleSettingCallback() {
            public WorkboolStyleSetting doSetWorkbookStyle(final Workbook workbook) {
                WorkboolStyleSetting setting = new WorkboolStyleSetting() {
                    public LinkedHashMap<String, CellStyle> getStyleMap() {
                        LinkedHashMap<String, CellStyle> styleMap = new LinkedHashMap<String, CellStyle>();
                        org.apache.poi.ss.usermodel.CellStyle style = workbook.createCellStyle();
                        org.apache.poi.ss.usermodel.Font font = (org.apache.poi.ss.usermodel.Font)workbook.createFont();
                        font.setColor(HSSFFont.COLOR_RED);
                        style.setFont(font);
                        styleMap.put("0*0", style);
                        return styleMap;
                    }
                };
                return setting;
            }
        };
        Map<String, String[][]> excelInfoMap = new LinkedHashMap<String, String[][]>();
        excelInfoMap.put("sheet1", excelInfos);
        excelInfoMap.put("sheet2", excelInfos);
        excelInfoMap.put("sheet3", excelInfos);
        
        Map<String, ExcelCellStyleSettingCallback> settingMap = new HashMap<String, ExcelCellStyleSettingCallback>();
        settingMap.put("sheet1", setting);
        efo.writeExcelFileInfo(file.getAbsolutePath(), excelInfoMap, settingMap);
    }
}