List of usage examples for org.apache.poi.xssf.usermodel XSSFPrintSetup setLandscape
public void setLandscape(boolean ls)
From source file:nl.detoren.ijsco.io.ExcelExport.java
License:Open Source License
public void exportGroepen(Groepen groepen) { String password = "abcd"; try {/*from w w w . j av a 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()); } }
From source file:uk.ac.ebi.generic.util.ExcelWorkBook.java
License:Apache License
public ExcelWorkBook(String[] titles, Object[][] tableData, String sheetTitle) throws Exception { this.wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet XSSFSheet sheet = wb.createSheet(sheetTitle); XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true);//from ww w .jav a2 s .c o m sheet.setHorizontallyCenter(true); //header row XSSFRow headerRow = sheet.createRow(0); //headerRow.setHeightInPoints(40); XSSFCell headerCell; for (int j = 0; j < titles.length; j++) { headerCell = headerRow.createCell(j); headerCell.setCellValue(titles[j]); //headerCell.setCellStyle(styles.get("header")); } // data rows // Create a row and put some cells in it. Rows are 0 based. // Then set value for that created cell for (int k = 0; k < tableData.length; k++) { XSSFRow row = sheet.createRow(k + 1); // data starts from row 1 for (int l = 0; l < tableData[k].length; l++) { XSSFCell cell = row.createCell(l); String cellStr = null; try { cellStr = tableData[k][l].toString(); } catch (Exception e) { cellStr = ""; } //System.out.println("cell " + l + ": " + cellStr); // make hyperlink in cell if ((cellStr.startsWith("http://") || cellStr.startsWith("https://")) && !cellStr.contains("|")) { //need to encode URI for this version of ExcelWorkBook cellStr = URIUtil.encodePath(cellStr, "UTF-8"); cellStr = cellStr.replace("%3F", "?"); // so that url link would work //System.out.println("cellStr: " + cellStr); XSSFHyperlink url_link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); url_link.setAddress(cellStr); cell.setCellValue(cellStr); cell.setHyperlink(url_link); } else { cell.setCellValue(cellStr); } //System.out.println((String)tableData[k][l]); } } }