List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getSheetName
@Override
public String getSheetName()
From source file:nl.architolk.ldt.processors.ExcelConverter.java
License:Open Source License
public void generateData(PipelineContext context, ContentHandler contentHandler) throws SAXException { try {// ww w . j a v a 2 s . c o m // Read binary content of Excel file ByteArrayOutputStream os = new ByteArrayOutputStream(); Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os); readInputAsSAX(context, INPUT_DATA, base64ContentHandler); final byte[] fileContent = os.toByteArray(); final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); // Create workbook XSSFWorkbook workbook = new XSSFWorkbook(bais); DataFormatter formatter = new DataFormatter(); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); contentHandler.startDocument(); contentHandler.startElement("", "workbook", "workbook", new AttributesImpl()); for (int s = 0; s < workbook.getNumberOfSheets(); s++) { XSSFSheet sheet = workbook.getSheetAt(s); AttributesImpl sheetAttr = new AttributesImpl(); sheetAttr.addAttribute("", "name", "name", "CDATA", sheet.getSheetName()); contentHandler.startElement("", "sheet", "sheet", sheetAttr); for (int r = 0; r <= sheet.getLastRowNum(); r++) { XSSFRow row = sheet.getRow(r); if (row != null) { AttributesImpl rowAttr = new AttributesImpl(); rowAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(r)); contentHandler.startElement("", "row", "row", rowAttr); for (int c = 0; c < row.getLastCellNum(); c++) { XSSFCell cell = row.getCell(c); if (cell != null) { try { String cellvalue = formatter.formatCellValue(cell, evaluator); if (cellvalue != "") { AttributesImpl columnAttr = new AttributesImpl(); columnAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(cell.getColumnIndex())); contentHandler.startElement("", "column", "column", columnAttr); contentHandler.characters(cellvalue.toCharArray(), 0, cellvalue.length()); contentHandler.endElement("", "column", "column"); } } catch (Exception e) { } } } contentHandler.endElement("", "row", "row"); } } contentHandler.endElement("", "sheet", "sheet"); } contentHandler.endElement("", "workbook", "workbook"); contentHandler.endDocument(); } catch (IOException e) { throw new OXFException(e); } }
From source file:nl.detoren.ijsco.io.ExcelExport.java
License:Open Source License
public void exportGroepen(Groepen groepen) { String password = "abcd"; try {/*from ww w . j a va 2 s. 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()); } }
From source file:nl.detoren.ijsco.io.ExcelExport.java
License:Open Source License
/** * Sorts (A-Z) rows by String column//from w w w.j av a 2 s. c o m * @param sheet - sheet to sort * @param column - String column to sort by * @param rowStart - sorting from this row down */ private void sortSheet(XSSFSheet sheet, int column, int rowStart, int rowEnd) { try { FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); logger.log(Level.INFO, "sorting sheet: " + sheet.getSheetName()); boolean sorting = true; //int lastRow = sheet.getLastRowNum(); while (sorting == true) { sorting = false; for (Row row : sheet) { // skip if this row is before first to sort if (row.getRowNum() < rowStart) continue; // end if this is last row if (rowEnd == row.getRowNum()) break; Row row2 = sheet.getRow(row.getRowNum() + 1); if (row2 == null) continue; int rownum1 = row.getRowNum(); int rownum2 = row2.getRowNum(); CellValue firstValue; CellValue secondValue; firstValue = evaluator.evaluate(row.getCell(column)); secondValue = evaluator.evaluate(row2.getCell(column)); //compare cell from current row and next row - and switch if secondValue should be before first if (secondValue.toString().compareToIgnoreCase(firstValue.toString()) < 0) { logger.log(Level.INFO, "Shifting rows" + sheet.getSheetName() + rownum1 + " - " + rownum2); sheet.shiftRows(row2.getRowNum(), row2.getRowNum(), -1); logger.log(Level.INFO, "Shifting rows" + sheet.getSheetName() + rownum1 + " - " + rownum2); sheet.shiftRows(row.getRowNum(), row.getRowNum(), 1); sorting = true; } } } } catch (Exception ex) { logger.log(Level.WARNING, "Failing Shifting rows" + sheet.getSheetName() + "Error " + ex.getMessage()); } }
From source file:opn.greenwebs.FXMLDocumentController.java
private List<ItemDB> createListFromTable(File fStock) { List<ItemDB> lstMain = new ArrayList<>(); logger.log(Level.INFO, "into the breach {0}", fStock.getAbsolutePath()); try (FileInputStream fis = new FileInputStream(fStock); XSSFWorkbook book = new XSSFWorkbook(fis)) { XSSFSheet sheet = book.getSheet("Digital Version"); logger.log(Level.INFO, "we have a sheet {0}", sheet.getSheetName()); FormulaEvaluator evaluator2 = book.getCreationHelper().createFormulaEvaluator(); List lstItem;/*from w w w . j av a 2s . co m*/ List<XSSFTable> lstTables = sheet.getTables(); logger.log(Level.SEVERE, "we have a table? {0}", lstTables.size()); if (!lstTables.isEmpty()) { logger.log(Level.INFO, "the table is not empty"); for (int j = lstTables.get(0).getStartCellReference().getRow(); j <= lstTables.get(0) .getEndCellReference().getRow(); j++) { ItemDB itmLine = new ItemDB(); lstItem = new ArrayList(); logger.log(Level.INFO, "we have some rows"); if (j > lstTables.get(0).getStartCellReference().getRow()) { Row row = sheet.getRow(j); if (row != null) { Iterator<Cell> cellIt = row.cellIterator(); logger.log(Level.INFO, "we have some cells {0}", cellIt.hasNext()); while (cellIt.hasNext()) { Cell cell = cellIt.next(); CellValue cellvalue = evaluator2.evaluate(cell); if (cellvalue == null) { lstItem.add(""); } else { switch (cellvalue.getCellType()) { case Cell.CELL_TYPE_STRING: lstItem.add(cellvalue.getStringValue()); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cellvalue.getNumberValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cellvalue.getBooleanValue()); case Cell.CELL_TYPE_BLANK: lstItem.add(""); break; case Cell.CELL_TYPE_FORMULA: lstItem.add("You found a formula"); break; default: } } } logger.log(Level.ALL, "lstItem is {0} and {1}", new Object[] { lstItem.size(), lstItem.toString() }); itmLine.setDblQty(Double.parseDouble(lstItem.get(0).toString())); logger.log(Level.INFO, "setting the quantity to {0}", lstItem.get(0).toString()); itmLine.setStrMfr(lstItem.get(1).toString()); itmLine.setStrSKU(lstItem.get(2).toString()); itmLine.setStrDescrip(lstItem.get(3).toString()); itmLine.setStrSupplier(lstItem.get(4).toString()); itmLine.setStrSupPart(lstItem.get(5).toString()); logger.log(Level.INFO, "setting the suppart to {0}", lstItem.get(5)); if (lstItem.size() > 6) { if (!lstItem.get(6).toString().isEmpty()) { itmLine.setDblSalePrice(Double.parseDouble(lstItem.get(6).toString())); } } //System.out.println("Added item " + itmLine.getStrDescrip()); lstMain.add(itmLine); } } } } } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } logger.log(Level.INFO, "returning {0}", lstMain.size()); return lstMain; }
From source file:org.addition.epanet.network.io.input.ExcelParser.java
License:Open Source License
@Override public Network parse(Network net, File f) throws ENException { FileInputStream stream = null; try {//from ww w . ja v a 2 s . c om stream = new FileInputStream(f); XSSFWorkbook workbook = new XSSFWorkbook(stream); findTimeStyle(workbook); Pattern tagPattern = Pattern.compile("\\[.*\\]"); int errSum = 0; List<XSSFSheet> sheetPC = new ArrayList<XSSFSheet>(); List<XSSFSheet> sheetOthers = new ArrayList<XSSFSheet>(); List<XSSFSheet> sheetNodes = new ArrayList<XSSFSheet>(); List<XSSFSheet> sheetTanks = new ArrayList<XSSFSheet>(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet sh = workbook.getSheetAt(i); if (sh.getSheetName().equalsIgnoreCase("Patterns") || sh.getSheetName().equalsIgnoreCase("Curves")) { sheetPC.add(sh); } else if (sh.getSheetName().equals("Junctions")) sheetNodes.add(sh); else if (sh.getSheetName().equals("Tanks") || sh.getSheetName().equals("Reservoirs")) sheetTanks.add(sh); else sheetOthers.add(sh); } errSum = parseWorksheet(net, sheetPC, tagPattern, errSum); // parse the patterns and curves errSum = parseWorksheet(net, sheetNodes, tagPattern, errSum); // parse the nodes errSum = parseWorksheet(net, sheetTanks, tagPattern, errSum); // parse the nodes errSum = parseWorksheet(net, sheetOthers, tagPattern, errSum); // parse other elements if (errSum != 0) throw new ENException(200); stream.close(); } catch (IOException e) { throw new ENException(302); } adjust(net); net.getFieldsMap().prepare(net.getPropertiesMap().getUnitsflag(), net.getPropertiesMap().getFlowflag(), net.getPropertiesMap().getPressflag(), net.getPropertiesMap().getQualflag(), net.getPropertiesMap().getChemUnits(), net.getPropertiesMap().getSpGrav(), net.getPropertiesMap().getHstep()); convert(net); return net; }
From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java
License:Apache License
public boolean containsDataRows(XSSFSheet sheet) { int rows = sheet.getPhysicalNumberOfRows(); if (rows > headerRowNo + 1) { report.println(//from w ww. j ava 2s . com UtilProperties .getMessage(resource, "PricatTableRows", new Object[] { String.valueOf(headerRowNo + 1), String.valueOf(rows - headerRowNo - 1), sheet.getSheetName() }, locale), InterfaceReport.FORMAT_NOTE); } else { report.println(UtilProperties.getMessage(resource, "PricatNoDataRows", new Object[] { sheet.getSheetName() }, locale), InterfaceReport.FORMAT_ERROR); return false; } return true; }
From source file:org.argrr.extractor.excel.SpreadSheetFile.java
License:Open Source License
public SpreadSheetFile(File f) { FileInputStream file = null;//from w ww . j a va 2 s . c o m tabs = new HashMap<String, SpreadSheetTab>(); try { file = new FileInputStream(f); //Create Workbook instance holding reference to .xlsx file workbook = new XSSFWorkbook(file); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { //int i = 2; XSSFSheet t = workbook.getSheetAt(i); tabs.put(t.getSheetName(), new SpreadSheetTab(t)); } } catch (FileNotFoundException ex) { Logger.getLogger(SpreadSheetFile.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(SpreadSheetFile.class.getName()).log(Level.SEVERE, null, ex); } finally { try { file.close(); } catch (IOException ex) { Logger.getLogger(SpreadSheetFile.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLWorkbook.java
License:Open Source License
@Override public Sheet createNewSheet() { XSSFSheet newSheet = workbook.createSheet(); OOXMLSheet es = new OOXMLSheet(this, newSheet, newSheet.getSheetName(), workbook.getSheetIndex(newSheet)); registerSheet(newSheet.getSheetName(), es); return es;// w w w . j a v a 2 s . c om }
From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLWorkbook.java
License:Open Source License
@Override public Sheet createNewSheet(String name) { XSSFSheet newSheet = workbook.createSheet(name); OOXMLSheet es = new OOXMLSheet(this, newSheet, newSheet.getSheetName(), workbook.getSheetIndex(newSheet)); registerSheet(name, es);/*from ww w.ja v a2s . c o m*/ return es; }
From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLWorkbook.java
License:Open Source License
@Override public Sheet getSheetByName(String name) { if (!containsSheet(name)) { XSSFSheet sheet = workbook.getSheet(name); if (sheet == null) { return null; }//from www.j av a 2 s . co m registerSheet(name, new OOXMLSheet(this, sheet, sheet.getSheetName(), workbook.getSheetIndex(sheet))); } return getSheet(name); }