xlstransformer.java

来自「一个java生成自动生成Excel」· Java 代码 · 共 595 行 · 第 1/2 页

//            Workbook workbook = createWorkbook( hssfWorkbook );
//            exposePOIObjects( workbook,  beanParams );
//            workbookTransformationController = new WorkbookTransformationControllerImpl( workbook );

            SheetTransformer sheetTransformer = new SheetTransformer( fixedSizeCollections, groupedCollections, rowProcessors, cellProcessors, configuration) ;
//            final String templateSheetName = "InternalTemplateSheetName";
            // todo refactoring required
            int sheetNumber = 1;
            for (int sheetNo = 0; sheetNo < hssfWorkbook.getNumberOfSheets(); sheetNo++) {
                final String spreadsheetName = hssfWorkbook.getSheetName(sheetNo);
                if (!isSpreadsheetToRemove(spreadsheetName)) {
                    if (isSpreadsheetToRename(spreadsheetName)) {
                        hssfWorkbook.setSheetName(sheetNo, getSpreadsheetToReName(spreadsheetName));
                    }
                    HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheetNo);
                    if( startSheetNum == sheetNo && objects != null && !objects.isEmpty()){
//                        Object firstBean = objects.get(0);
//                        beanParams.put( beanName + sheetNumber++, firstBean );
//                        hssfWorkbook.setSheetName( sheetNo, (String) newSheetNames.get(0), HSSFWorkbook.ENCODING_UTF_16);
//                        HSSFSheet templateSheet = hssfWorkbook.createSheet(templateSheetName );
//                        Util.copySheets( templateSheet, hssfSheet );
//                        Sheet sheet = workbook.getSheetAt( sheetNo );
//                        sheetTransformer.transformSheet(workbookTransformationController, sheet, beanParams );
                        for (int i = 0; i < objects.size() ; i++) {
                            Object bean = objects.get(i);
                            String beanKey = beanName;
                            HSSFSheet newSheet;
                            if( i != 0 ){
                                beanKey = beanName+i;
                                newSheet = hssfWorkbook.createSheet( (String) newSheetNames.get(i) );
                                Util.copySheets(newSheet, hssfSheet, beanName, beanKey );
                                Util.copyPageSetup(newSheet, hssfSheet);
                                Util.copyPrintSetup(newSheet, hssfSheet);
                            }else{
                                hssfWorkbook.setSheetName( sheetNo, (String) newSheetNames.get(i));
                            }
                            beanParams.put( beanKey, bean );
//                            sheet = new Sheet(hssfWorkbook, newSheet, configuration);
                            // todo: implement update of the FormulaController instance when adding new sheet to workbook
//                            workbook.addSheet( sheet );
//                            workbook.initSheetNames();
//                            sheetTransformer.transformSheet(workbookTransformationController, sheet, beanParams );
                        }
//                        hssfWorkbook.removeSheetAt( hssfWorkbook.getSheetIndex( templateSheetName ) );
//                        beanParams.remove( beanName );
                    }else{
//                          Sheet sheet = workbook.getSheetAt( sheetNo );
//                        sheetTransformer.transformSheet(workbookTransformationController, sheet, beanParams );
                    }
                } else {
                    // let's remove spreadsheet
                    hssfWorkbook.removeSheetAt(sheetNo);
//                    workbook.removeSheetAt( sheetNo );
                    sheetNo--;
                }
            }
//            updateFormulas();
        } catch (IOException e) {
            e.printStackTrace();
        }
        if( hssfWorkbook != null ){
            for(int i = 0;i < hssfWorkbook.getNumberOfSheets();i++)
            {
                Util.setPrintArea(hssfWorkbook,i);
            }
        }
        transformWorkbook( hssfWorkbook, beanParams );
        return hssfWorkbook;
    }

    /**
     * Multiple sheet template multiple transform.
     * It can be used to generate a workbook with N (N=N1+N2+...+Nn) sheets based on :
     * - N1 transformations of the sheet template T1
     * - N2 transformations of the sheet template T2
     * ...
     * - Nn transformations of the sheet template Tn
     * @param is  the {@link InputStream} of the workbook template containing the n template sheets
     * @param templateSheetNameList  the ordered list of the template sheet name used in the transformation.
     * @param sheetNameList  the ordered list of the resulting sheet name after transformation
     * @param beanParamsList  the ordered list of beanParams used in the transformation
     * @return - {@link HSSFWorkbook} representing transformation result
     * @throws ParsePropertyException  in case property parsing failure
     */
    public HSSFWorkbook transformXLS(InputStream is, List
            templateSheetNameList, List sheetNameList, List beanParamsList)
            throws ParsePropertyException {
        HSSFWorkbook hssfWorkbook = null;
        try {
            POIFSFileSystem fs = new POIFSFileSystem(is);
            hssfWorkbook = new HSSFWorkbook(fs);
            int numberOfSheets = hssfWorkbook.getNumberOfSheets();
            for (int templateSheetIndex = 0; templateSheetIndex < templateSheetNameList.size(); templateSheetIndex++) {
                String templateSheetName = (String)templateSheetNameList.get(templateSheetIndex);
                String sheetName = (String)sheetNameList.get(templateSheetIndex);
                for(int workbookSheetIndex = 0; workbookSheetIndex < numberOfSheets; workbookSheetIndex++) {
                    if (templateSheetName.equals(hssfWorkbook.getSheetName(workbookSheetIndex))) {
                        cloneSheet(hssfWorkbook, workbookSheetIndex, sheetName);
                        break;
                    }
                }
            }
            for (int i = 0; i < numberOfSheets; i++) {
                hssfWorkbook.removeSheetAt(0);
            }
            Workbook workbook = createWorkbook(hssfWorkbook);
            workbookTransformationController = new WorkbookTransformationControllerImpl(workbook);
            preprocess(hssfWorkbook);
            SheetTransformer sheetTransformer = new SheetTransformer(fixedSizeCollections, groupedCollections,
                    rowProcessors, cellProcessors, configuration);
            for (int sheetNo = 0; sheetNo < workbook.getNumberOfSheets(); sheetNo++) {
                final String spreadsheetName = hssfWorkbook.getSheetName(sheetNo);
                if (!isSpreadsheetToRemove(spreadsheetName)) {
                    if (isSpreadsheetToRename(spreadsheetName)) {
                        hssfWorkbook.setSheetName(sheetNo, getSpreadsheetToReName(spreadsheetName));
                    }
                    Sheet sheet = workbook.getSheetAt(sheetNo);

                    Map beanParams = (Map) beanParamsList.get(sheetNo);
                    beanParams.put("index", String.valueOf(sheetNo));
                    exposePOIObjects( workbook,  beanParams );
                    sheetTransformer.transformSheet(workbookTransformationController, sheet, beanParams);
                } else {
                    // let's remove spreadsheet
                    workbook.removeSheetAt( sheetNo );
                    sheetNo--;
                }
            }
            updateFormulas();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return hssfWorkbook;
    }

    private void cloneSheet(HSSFWorkbook hssfWorkbook, int index, String name) {
        HSSFSheet hssfSheet = hssfWorkbook.cloneSheet(index);
        for (int i = 0; i < hssfWorkbook.getNumberOfSheets(); i++) {
            if(hssfSheet.equals(hssfWorkbook.getSheetAt(i))) {
                hssfWorkbook.setSheetName(i, name);
                break;
            }
        }
    }

    private Workbook createWorkbook(HSSFWorkbook hssfWorkbook) {
        Workbook workbook = new Workbook(hssfWorkbook);
        for (int sheetNo = 0; sheetNo < hssfWorkbook.getNumberOfSheets(); sheetNo++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheetNo);
            workbook.addSheet( new Sheet(hssfWorkbook, hssfSheet, configuration));
        }
        workbook.initSheetNames();
        workbook.createFormulaController();
        return workbook;
    }

    private void updateFormulas() {
        if( formulaResolver == null ){
            formulaResolver = new CommonFormulaResolver();
        }
        FormulaController formulaController = workbookTransformationController.getWorkbook().getFormulaController();
        formulaController.writeFormulas( formulaResolver );
    }


    private void preprocess(HSSFWorkbook workbook) {
        hideColumns(workbook);
        hideColumnsByPropertyName(workbook);
        for (int sheet_no = 0; sheet_no < workbook.getNumberOfSheets(); sheet_no++) {
            HSSFSheet sheet = workbook.getSheetAt(sheet_no);
            for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
                HSSFRow hssfRow = sheet.getRow(i);
                if (hssfRow != null) {
                    for (short j = hssfRow.getFirstCellNum(); j <= hssfRow.getLastCellNum(); j++) {
                        HSSFCell cell = hssfRow.getCell(j);
                        if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            String value = cell.getStringCellValue();
                            for (int k = 0; k < propertyPreprocessors.size(); k++) {
                                PropertyPreprocessor propertyPreprocessor = (PropertyPreprocessor) propertyPreprocessors.get(k);
                                String newValue = propertyPreprocessor.processProperty(value);
                                if (newValue != null) {
                                    cell.setCellValue(newValue);
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    private void hideColumns(HSSFWorkbook workbook) {
        if (columnsToHide != null) {
            for (int i = 0; i < columnsToHide.length; i++) {
                short column = columnsToHide[i];
                for (int sheet_no = 0; sheet_no < workbook.getNumberOfSheets(); sheet_no++) {
                    HSSFSheet sheet = workbook.getSheetAt(sheet_no);
                    sheet.setColumnWidth(column, (short) 0);
                }
            }
        }
    }

    /**
     * Set column width = 0 for column if any it cell value contains any of {@link this#columnPropertyNamesToHide} string.
     * @param workbook - {@link HSSFWorkbook} to hide columns in
     */
    private void hideColumnsByPropertyName(HSSFWorkbook workbook) {
        if (columnPropertyNamesToHide == null) return;
        for (int sheet_no = 0; sheet_no < workbook.getNumberOfSheets(); sheet_no++) {
            HSSFSheet sheet = workbook.getSheetAt(sheet_no);
            //for all rows
            for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
                HSSFRow hssfRow = sheet.getRow(i);
                if (hssfRow != null) {
                    //for all cells
                    for (short j = hssfRow.getFirstCellNum(); j <= hssfRow.getLastCellNum(); j++) {
                        HSSFCell cell = hssfRow.getCell(j);
                        if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            String value = cell.getStringCellValue();
                            //if any from columnPropertyNamesToHide is substring of cell value, than hide column
                            for (int prptIndx = 0; prptIndx < columnPropertyNamesToHide.length; prptIndx++) {
                                if (value != null && value.indexOf(columnPropertyNamesToHide[prptIndx]) != -1) {
                                    sheet.setColumnWidth(j, (short) 0);
                                    break;
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * @return The column numbers to hide in result XLS
     */
    public short[] getColumnsToHide() {
        return columnsToHide;
    }

    /**
     * Sets the columns to hide in result XLS
     * @param columnsToHide - Column numbers to hide
     */
    public void setColumnsToHide(short[] columnsToHide) {
        this.columnsToHide = columnsToHide;
    }

    /**
     * @return The property names for which all columns containing them should be hidden
     */
    public String[] getColumnPropertyNamesToHide() {
        return columnPropertyNamesToHide;
    }

    /**
     * Set the columns to hide in result XLS
     * @param columnPropertyNamesToHide - The names of bean properties for which all columns
     * containing this properties should be hidden
     */
    public void setColumnPropertyNamesToHide(String[] columnPropertyNamesToHide) {
        this.columnPropertyNamesToHide = columnPropertyNamesToHide;
    }

    /**
     * Set spreadsheets with given names to be hidden
     * @param names - Names of the worksheets to hide
     */
    public void setSpreadsheetsToRemove(String[] names) {
        spreadsheetsToRemove.clear();
        for (int i = 0; i < names.length; i++) {
            spreadsheetsToRemove.add(names[i]);
        }
    }

    public void setSpreadsheetToRename(String name, String newName) {
        spreadsheetsToRename.put(name, newName);
    }

    protected boolean isSpreadsheetToRemove(String name) {
        return spreadsheetsToRemove.contains(name);
    }

    protected boolean isSpreadsheetToRename(String name) {
        return spreadsheetsToRename.containsKey(name);
    }

    protected String getSpreadsheetToReName(String name) {
        final String newName = (String) spreadsheetsToRename.get(name);
        if (newName != null)
            return newName;
        return name;
    }

}