List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook removeSheetAt
@Override public void removeSheetAt(int index)
Care must be taken if the removed sheet is the currently active or only selected sheet in the workbook.
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestSuitesFromXLSX(String filePath, String testSuiteName) { boolean hasTrue = false; Iterator<Row> rowIterator; try {//from ww w .j av a 2 s . com FileInputStream myInput = new FileInputStream(filePath); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); XSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (getValue(next.getCell(2)).equalsIgnoreCase(testSuiteName)) { mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { XSSFSheet mySheet1 = myWorkBook.getSheetAt(j); if (mySheet1.getSheetName().equalsIgnoreCase(testSuiteName)) { myWorkBook.removeSheetAt(j); hasTrue = true; break; } } myInput.close(); FileOutputStream outFile = new FileOutputStream(filePath); myWorkBook.write(outFile); outFile.close(); break; } } } catch (Exception e) { } return hasTrue; }
From source file:logic.Export.java
public boolean convertXls() throws IOException, FileNotFoundException, IllegalArgumentException, ParseException { FileInputStream tamplateFile = new FileInputStream(templatePath); XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##")); double hours = 0.0; NumberFormat format = NumberFormat.getInstance(Locale.FRANCE); Number number;//from w w w . j a va 2 s. c om XSSFSheet sheet; XSSFSheet sheet2; Cell cell = null; ConvertData cd = new ConvertData(); for (int i = 0; i < cd.getSheetnames().size(); i++) { sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i)); sheet = workbook.getSheetAt(i + 1); //formate sheets sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); cell = sheet.getRow(0).getCell(1); cell.setCellValue(cd.sheetnames.get(i)); ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i)); //setPrintArea workbook.setPrintArea(i + 1, //sheet index 0, //start column Spalte 6, //end column 0, //start row zeile convert.size() + 8 //end row ); for (int Row = 0; Row < convert.size(); Row++) { for (int Cell = 0; Cell < convert.get(Row).length; Cell++) { cell = sheet.getRow(9 + Row).getCell(Cell); if (Cell == 3) { if ("true".equals(convert.get(Row)[Cell])) { XSSFCellStyle style1 = workbook.createCellStyle(); style1 = (XSSFCellStyle) cell.getCellStyle(); style1 = (XSSFCellStyle) style1.clone(); style1.setFillBackgroundColor(HSSFColor.RED.index); style1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); XSSFColor myColor = new XSSFColor(Color.RED); style1.setFillForegroundColor(myColor); sheet.getRow(9 + Row).getCell(6).setCellStyle(style1); } } else { cell.setCellValue(convert.get(Row)[Cell]); } } } } workbook.removeSheetAt(0); tamplateFile.close(); File exportFile = newPath.getSelectedFile(); if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) { } else { exportFile = new File(exportFile.getParentFile(), FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx"); } FileOutputStream outFile = new FileOutputStream(exportFile); workbook.write(outFile); outFile.close(); tamplateFile.close(); return true; }
From source file:logic.Export.java
public boolean convertXls2() throws IOException, FileNotFoundException, IllegalArgumentException, ParseException { FileInputStream tamplateFile = new FileInputStream(templatePath); XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##")); double hours = 0.0; NumberFormat format = NumberFormat.getInstance(Locale.FRANCE); Number number;// ww w .ja v a2 s.c o m XSSFSheet sheet; XSSFSheet sheet2; Cell cell = null; ConvertData cd = new ConvertData(); for (int i = 0; i < cd.getSheetnames().size(); i++) { sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i)); sheet = workbook.getSheetAt(i + 1); cell = sheet.getRow(0).getCell(1); cell.setCellValue(cd.sheetnames.get(i)); ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i)); for (int Row = 0; Row < convert.size(); Row++) { for (int Cell = 0; Cell < convert.get(Row).length; Cell++) { cell = sheet.getRow(9 + Row).getCell(Cell + 1); String name; switch (Cell) { case 3: name = convert.get(Row)[Cell]; int parseInt = Integer.parseInt(name); cell.setCellValue(parseInt); cell.setCellType(CellType.NUMERIC); break; case 4: number = format.parse(convert.get(Row)[Cell]); cell.setCellValue(number.doubleValue()); // cell.setCellStyle(cellStyle); cell.setCellType(CellType.NUMERIC); break; default: cell.setCellValue(convert.get(Row)[Cell]); break; } } } for (String[] sa : convert) { number = format.parse(sa[4]); hours = hours + number.doubleValue(); } cell = sheet.getRow(6).getCell(5); cell.setCellValue(hours); cell = sheet.getRow(2).getCell(8); XSSFCell cellHourlyRate = sheet.getRow(1).getCell(8); double numericCellValue = cellHourlyRate.getNumericCellValue(); cell.setCellValue(hours * numericCellValue); } workbook.removeSheetAt(0); tamplateFile.close(); File exportFile = newPath.getSelectedFile(); if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) { } else { exportFile = new File(exportFile.getParentFile(), FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx"); } FileOutputStream outFile = new FileOutputStream(exportFile); workbook.write(outFile); outFile.close(); tamplateFile.close(); return true; }
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 a va 2s . c om*/ 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()); } }