Example usage for org.apache.poi.xssf.usermodel XSSFSheet setForceFormulaRecalculation

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet setForceFormulaRecalculation.

Prototype

@Override
public void setForceFormulaRecalculation(boolean value) 

Source Link

Document

Control if Excel should be asked to recalculate all formulas on this sheet when the workbook is opened.

Usage

From source file:nl.detoren.ijsco.io.ExcelExport.java

License:Open Source License

public void exportGroepen(Groepen groepen) {
    String password = "abcd";
    try {/*from  w ww .  java 2  s. com*/
        if (groepen == null)
            return;
        // sheetindx geeft index in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte 
        int[] sheetindx = new int[] { -1, -1, -1, -1, 5, -1, 4, -1, 3, -1, 2, -1, 1, -1, 0, -1, -1, -1 };
        // columnsize geeft lengte in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte
        int[] columnsize = new int[] { -1, -1, -1, -1, 20, -1, 35, -1, 54, -1, 77, -1, 100, -1, 127, -1, -1,
                -1 };
        // pagelngth geeft lengte in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte
        int[] pagelngth = new int[] { -1, -1, -1, -1, 20, -1, 35, -1, 54, -1, 77, -1, 100, -1, 127, -1, -1,
                -1 };
        int sheet2row = 2;
        int sheet3row = 2;
        FileInputStream file = new FileInputStream("Indeling.xlsm");
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFCellStyle style1 = workbook.createCellStyle();
        style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(180, 180, 180)));
        XSSFCellStyle my_style = workbook.createCellStyle();
        XSSFColor my_foreground = new XSSFColor(Color.ORANGE);
        XSSFColor my_background = new XSSFColor(Color.RED);
        my_style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        my_style.setFillForegroundColor(my_foreground);
        my_style.setFillBackgroundColor(my_background);
        XSSFSheet sheet2 = workbook.getSheet("Groepsindeling");
        XSSFSheet sheet3 = workbook.getSheet("Deelnemerslijst");
        updateCell(sheet3, sheet3row, 0, "Naam", style1);
        updateCell(sheet3, sheet3row, 1, "KNSB nr", style1);
        updateCell(sheet3, sheet3row, 2, "rating", style1);
        updateCell(sheet3, sheet3row, 3, "groep", style1);
        sheet3row++;
        for (Groep groep : groepen) {
            logger.log(Level.INFO, "Exporteer groep : " + groep.getNaam());
            XSSFSheet sheet = workbook.cloneSheet(sheetindx[groep.getGrootte()], groep.getNaam());
            updateCell(sheet, 0, 6, groep.getNaam());
            updateCell(sheet2, sheet2row, 1, groep.getNaam());
            sheet2row++;
            updateCell(sheet2, sheet2row, 0, "nr", style1);
            updateCell(sheet2, sheet2row, 1, "Naam", style1);
            updateCell(sheet2, sheet2row, 2, "KNSB nr", style1);
            updateCell(sheet2, sheet2row, 3, "rating", style1);
            sheet2row++;
            for (int i = 0; i < groep.getGrootte(); i++) {
                updateCell(sheet, 3 + i, 2, groep.getSpeler(i).getNaam());
                updateCell(sheet, 3 + i, 3, groep.getSpeler(i).getKnsbnummer());
                updateCell(sheet, 3 + i, 5, groep.getSpeler(i).getRating());
                updateCell(sheet2, sheet2row, 0, i + 1);
                updateCell(sheet2, sheet2row, 1,
                        "'" + sheet.getSheetName() + "'!"
                                + org.apache.poi.ss.util.CellReference.convertNumToColString(2) + (4 + i),
                        true);
                updateCell(sheet2, sheet2row, 2,
                        "'" + sheet.getSheetName() + "'!"
                                + org.apache.poi.ss.util.CellReference.convertNumToColString(3) + (4 + i),
                        true);
                updateCell(sheet2, sheet2row, 3,
                        "'" + sheet.getSheetName() + "'!"
                                + org.apache.poi.ss.util.CellReference.convertNumToColString(5) + (4 + i),
                        true);
                if (groep.getSpeler(i).getNaam() != "Bye") {
                    updateCell(sheet3, sheet3row, 0,
                            "'" + sheet.getSheetName() + "'!"
                                    + org.apache.poi.ss.util.CellReference.convertNumToColString(2) + (4 + i),
                            true);
                    updateCell(sheet3, sheet3row, 1,
                            "'" + sheet.getSheetName() + "'!"
                                    + org.apache.poi.ss.util.CellReference.convertNumToColString(3) + (4 + i),
                            true);
                    updateCell(sheet3, sheet3row, 2,
                            "'" + sheet.getSheetName() + "'!"
                                    + org.apache.poi.ss.util.CellReference.convertNumToColString(5) + (4 + i),
                            true);
                    updateCell(sheet3, sheet3row, 3, groep.getNaam());
                }
                sheet2row++;
                sheet3row++;
            }
            sheet2row++;
            sheet.setForceFormulaRecalculation(true);
            // Set print margins
            XSSFPrintSetup ps = sheet.getPrintSetup();
            ps.setLandscape(true);
            ps.setFitWidth((short) 1);
            sheet.setFitToPage(true);
            sheet.setAutobreaks(false);
            workbook.setPrintArea(workbook.getSheetIndex(sheet), 1, 26, 0, pagelngth[groep.getGrootte()]);
            sheet.setColumnBreak(18);
            sheet.protectSheet(password);
            sheet.enableLocking();
        }
        XSSFSheet sheet4 = workbook.cloneSheet(workbook.getSheetIndex(sheet3), "Deelnemerslijst (naam)");
        sortSheet(sheet4, 1, 3, 62);
        //XSSFSheet sheet5 = workbook.cloneSheet(workbook.getSheetIndex(sheet3), "Deelnemerslijst (rating)");
        //sortSheet(sheet5, 1,4);
        sheet2.protectSheet(password);
        sheet3.protectSheet(password);
        sheet4.protectSheet(password);
        //sheet5.protectSheet(password);
        // Remove template sheets
        for (int i = 0; i < 6; i++) {
            workbook.removeSheetAt(0);
        }

        // Close input file
        file.close();
        // Store Excel to new file
        String filename = "Indeling resultaat.xlsm";
        File outputFile = new File(filename);
        FileOutputStream outFile = new FileOutputStream(outputFile);
        workbook.write(outFile);
        // Close output file
        workbook.close();
        outFile.close();
        // And open it in the system editor
        Desktop.getDesktop().open(outputFile);
    } catch (IOException e) {
        logger.log(Level.SEVERE, "Fout bij maken indeling excel : " + e.getMessage());

    }
}

---