List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet protectSheet
@Override public void protectSheet(String password)
From source file:nl.detoren.ijsco.io.ExcelExport.java
License:Open Source License
public void exportGroepen(Groepen groepen) { String password = "abcd"; try {/*www . ja v a2s .c o m*/ 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()); } }