List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:mil.tatrc.physiology.utilities.testing.validation.ValidationMatrix.java
License:Apache License
public static void convert(String from, String to) throws IOException { FileInputStream xlFile = new FileInputStream(new File(from)); // Read workbook into HSSFWorkbook XSSFWorkbook xlWBook = new XSSFWorkbook(xlFile); List<SheetSummary> sheetSummaries = new ArrayList<SheetSummary>();// has to be an ordered list as sheet names can only be so long Map<String, String> refs = new HashMap<String, String>(); List<Sheet> Sheets = new ArrayList<Sheet>(); for (int s = 0; s < xlWBook.getNumberOfSheets(); s++) { XSSFSheet xlSheet = xlWBook.getSheetAt(s); Log.info("Processing Sheet : " + xlSheet.getSheetName()); if (xlSheet.getSheetName().equals("Summary")) { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 1; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; SheetSummary ss = new SheetSummary(); sheetSummaries.add(ss);//w ww. j av a 2 s . co m ss.name = row.getCell(0).getStringCellValue(); ss.description = row.getCell(1).getStringCellValue(); ss.validationType = row.getCell(2).getStringCellValue(); } } else if (xlSheet.getSheetName().equals("References")) { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 1; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; refs.put("\\[" + r + "\\]", "@cite " + row.getCell(1).getStringCellValue()); } } else { int rows = xlSheet.getPhysicalNumberOfRows(); Sheet sheet = new Sheet(); sheet.summary = sheetSummaries.get(s - 2); Sheets.add(sheet); int cells = xlSheet.getRow(0).getPhysicalNumberOfCells(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; String cellValue = null; for (int c = 0; c < cells; c++) { List<Cell> column; if (r == 0) { column = new ArrayList<Cell>(); sheet.table.add(column); } else { column = sheet.table.get(c); } XSSFCell cell = row.getCell(c); if (cell == null) { column.add(new Cell("", Agreement.NA, refs)); continue; } cellValue = null; switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } if (cellValue == null || cellValue.isEmpty()) column.add(new Cell("", Agreement.NA, refs)); else { Agreement a = Agreement.NA; XSSFColor color = cell.getCellStyle().getFillForegroundColorColor(); if (color != null) { byte[] rgb = color.getRGB(); if (rgb[0] < -25 && rgb[1] > -25 && rgb[2] < -25) { a = Agreement.Good; sheet.summary.goodAgreement++; } else if (rgb[0] > -25 && rgb[1] > -25 && rgb[2] < -25) { a = Agreement.Ok; sheet.summary.okAgreement++; } else if (rgb[0] > -25 && rgb[1] < -25 && rgb[2] < -25) { a = Agreement.Bad; sheet.summary.badAgreement++; } } column.add(new Cell(cellValue, a, refs)); } } } } } xlWBook.close(); xlFile.close(); //close xls // Generate our Tables for each Sheet PrintWriter writer = null; try { String name = from.substring(from.lastIndexOf('/') + 1, from.lastIndexOf('.')) + "Scenarios"; writer = new PrintWriter(to + name + "Summary.md", "UTF-8"); writer.println( "|Scenario|Description|Validation Type|Good agreement|General agreement with deviations|Some major disagreements|"); writer.println("|--- |--- |:---: |:---: |:---: |:---: |"); for (Sheet sheet : Sheets) { writer.println("|" + sheet.summary.name + "|" + sheet.summary.description + "|" + sheet.summary.validationType + "|" + success + sheet.summary.goodAgreement + endSpan + "|" + warning + sheet.summary.okAgreement + endSpan + "|" + danger + sheet.summary.badAgreement + endSpan + "|"); } writer.close(); // Create file and start the table writer = new PrintWriter(to + name + ".md", "UTF-8"); writer.println(name + " {#" + name + "}"); writer.println("======="); writer.println(); writer.println(); for (Sheet sheet : Sheets) { Log.info("Writing table : " + sheet.summary.name); writer.println("## " + sheet.summary.name); writer.println(sheet.summary.description); writer.println("We used a " + sheet.summary.validationType + " validation method(s)."); writer.println(""); for (int row = 0; row < sheet.table.get(0).size(); row++) { for (int col = 0; col < sheet.table.size(); col++) { writer.print("|" + sheet.table.get(col).get(row).text); } writer.println("|"); if (row == 0) { for (int col = 0; col < sheet.table.size(); col++) { writer.print("|--- "); } writer.println("|"); } } writer.println(); writer.println(); } writer.close(); } catch (Exception ex) { Log.error("Error writing tables for " + from, ex); writer.close(); } }
From source file:mx.infotec.dads.arq.excel.ImplementExcel.java
public List<XSSFSheet> getSheet() throws ExcelException { InputStream excelFileToRead = null; List<XSSFSheet> lst = new ArrayList<>(); try {/*from w w w . jav a2s. c o m*/ excelFileToRead = new FileInputStream(this.path); XSSFWorkbook wb = new XSSFWorkbook(excelFileToRead); int numberOfSheet = wb.getNumberOfSheets(); for (int i = 0; i < numberOfSheet; i++) { lst.add(wb.getSheetAt(i)); } } catch (IOException ex) { throw new ExcelException("Error a obtener los libros del excel ", ex); } finally { try { excelFileToRead.close(); } catch (IOException ex) { Logger.getLogger(ImplementExcel.class.getName()).log(Level.SEVERE, null, ex); } } return lst; }
From source file:net.clementlevallois.gradingpics.io.Excel.java
public void writeFGradeForOneStudent(String name, String grade) throws FileNotFoundException, IOException { XSSFWorkbook workbook; FileInputStream file = null;// ww w . j av a 2s.c o m if (Files.exists(Paths.get("grades_2018.xlsx"))) { file = new FileInputStream(new File("grades_2018.xlsx")); workbook = new XSSFWorkbook(file); } else { workbook = new XSSFWorkbook(); } while (workbook.getNumberOfSheets() < 4) { workbook.createSheet(String.valueOf(String.valueOf(workbook.getNumberOfSheets() + 1))); } XSSFSheet sheet = workbook.getSheetAt(1); int lastRowNumber = sheet.getLastRowNum(); //Create a new row in current sheet XSSFRow row = sheet.createRow(lastRowNumber + 1); //Create a new cell in current row XSSFCell cellName = row.createCell(0); //Set value to new value cellName.setCellValue(name); XSSFCell cellGrade = row.createCell(1); cellGrade.setCellValue(grade); //close the excel file when done if (file != null) { file.close(); } FileOutputStream fos = new FileOutputStream(new File("grades_2018.xlsx")); workbook.write(fos); fos.close(); }
From source file:nl.architolk.ldt.processors.ExcelConverter.java
License:Open Source License
public void generateData(PipelineContext context, ContentHandler contentHandler) throws SAXException { try {// w w w. j a va 2 s. com // 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: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 . j ava 2 s .co m 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.AbstractPricatParser.java
License:Apache License
public boolean isNumOfSheetsOK(XSSFWorkbook workbook) { report.print(UtilProperties.getMessage(resource, "CheckPricatHasSheet", locale), InterfaceReport.FORMAT_NOTE); int sheets = workbook.getNumberOfSheets(); if (sheets < 1) { report.println(UtilProperties.getMessage(resource, "PricatTableNoSheet", locale), InterfaceReport.FORMAT_ERROR); return false; } else if (sheets >= 1) { report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); report.println(UtilProperties.getMessage(resource, "PricatTableOnlyParse1stSheet", locale), InterfaceReport.FORMAT_WARNING); }// w w w.j a v a 2 s. c om return true; }
From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java
License:Apache License
/** * ?/*from ww w . j a va 2 s .c o m*/ * * @return * @throws FileNotFoundException * @throws ParseException * @throws IOException */ @SuppressWarnings({ "unchecked", "rawtypes" }) public Datasource build() throws FileNotFoundException, ParseException, IOException { ExcelDatasource datasource = new ExcelDatasource(); datasource.name = datasourceName; InputStream stream = null; try { List<Table> tables = new ArrayList<>(); for (File file : excelFiles) { stream = new FileInputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(stream); int cntSheet = workbook.getNumberOfSheets(); for (int i = 0; i < cntSheet; i++) { String sheetName = workbook.getSheetName(i); // sheet name -> table name ExcelTable table = new ExcelTable(); Matcher matcher = PTN_TABLE_NAME.matcher(sheetName); if (matcher.find()) { table.label = matcher.group(3); table.name = matcher.group(1); } else { table.label = sheetName; table.name = sheetName; } XSSFSheet sheet = workbook.getSheetAt(i); // Check row size int cntRow = sheet.getLastRowNum() + 1; if (3 > cntRow) { System.out.println("Skip sheet[" + sheetName + "]. row size < 3"); continue; } // Read Field List<ExcelField> fields = new ArrayList<ExcelField>(); XSSFRow rowLabel = sheet.getRow(0); XSSFRow rowName = sheet.getRow(1); XSSFRow rowType = sheet.getRow(2); for (int col = 0; col < rowLabel.getLastCellNum(); col++) { ExcelField field = readField(col, rowLabel.getCell(col), rowName.getCell(col), rowType.getCell(col)); fields.add(field); } // Read Data List<ExcelRecord> records = new ArrayList<ExcelRecord>(); for (int row = 3; row < cntRow; row++) { XSSFRow xssfrow = sheet.getRow(row); if (!isEmptyRow(xssfrow)) { ExcelRecord record = readData(row, xssfrow, fields); records.add(record); } else { System.out .println("Skip empty row.[table: " + table.getName() + "; row: " + row + ";]"); } } table.fields = (List) fields; table.records = (List) records; tables.add(table); } } datasource.tables = tables; } catch (FileNotFoundException ex) { throw ex; } catch (ParseException ex) { throw ex; } catch (IOException ex) { throw ex; } finally { if (null != stream) { try { stream.close(); } catch (IOException ex) { } finally { stream = null; } } } return datasource; }
From source file:org.azkfw.datasource.excel.ExcelDatasourceFactory.java
License:Apache License
/** * Excel???// w ww . j av a 2s . co m * * @param aName ?? * @param aStream Excel * @return */ @SuppressWarnings({ "unchecked", "rawtypes" }) public static Datasource generate(final String aName, final InputStream aStream) throws FileNotFoundException, ParseException, IOException { ExcelDatasource datasource = new ExcelDatasource(); datasource.name = aName; try { List<Table> tables = new ArrayList<>(); XSSFWorkbook workbook = new XSSFWorkbook(aStream); int cntSheet = workbook.getNumberOfSheets(); for (int i = 0; i < cntSheet; i++) { String sheetName = workbook.getSheetName(i); // sheet name -> table name ExcelTable table = new ExcelTable(); Matcher matcher = PTN_TABLE_NAME.matcher(sheetName); if (matcher.find()) { table.label = matcher.group(3); table.name = matcher.group(1); } else { table.label = sheetName; table.name = sheetName; } XSSFSheet sheet = workbook.getSheetAt(i); // Check row size int cntRow = sheet.getLastRowNum() + 1; if (3 > cntRow) { System.out.println("Skip sheet[" + sheetName + "]. row size < 3"); continue; } // Read Field List<ExcelField> fields = new ArrayList<ExcelField>(); XSSFRow rowLabel = sheet.getRow(0); XSSFRow rowName = sheet.getRow(1); XSSFRow rowType = sheet.getRow(2); for (int col = 0; col < rowLabel.getLastCellNum(); col++) { ExcelField field = readField(col, rowLabel.getCell(col), rowName.getCell(col), rowType.getCell(col)); fields.add(field); } // Read Data List<ExcelRecord> records = new ArrayList<ExcelRecord>(); for (int row = 3; row < cntRow; row++) { XSSFRow xssfrow = sheet.getRow(row); if (!isEmptyRow(xssfrow)) { ExcelRecord record = readData(row, xssfrow, fields); records.add(record); } else { System.out.println("Skip empty row.[table: " + table.getName() + "; row: " + row + ";]"); } } table.fields = (List) fields; table.records = (List) records; tables.add(table); } datasource.tables = tables; } catch (FileNotFoundException ex) { throw ex; } catch (ParseException ex) { throw ex; } catch (IOException ex) { throw ex; } finally { if (null != aStream) { try { aStream.close(); } catch (IOException ex) { } } } return datasource; }
From source file:org.dhatim.fastexcel.Correctness.java
License:Apache License
@Test public void singleWorksheet() throws Exception { String sheetName = "Worksheet 1"; String stringValue = "Sample text with chars to escape : < > & \\ \" ' ~ "; Date dateValue = new Date(); LocalDateTime localDateTimeValue = LocalDateTime.now(); ZoneId timezone = ZoneId.of("Australia/Sydney"); ZonedDateTime zonedDateValue = ZonedDateTime.ofInstant(dateValue.toInstant(), timezone); double doubleValue = 1.234; int intValue = 2_016; long longValue = 2_016_000_000_000L; BigDecimal bigDecimalValue = BigDecimal.TEN; byte[] data = writeWorkbook(wb -> { Worksheet ws = wb.newWorksheet(sheetName); int i = 1; ws.value(i, i++, stringValue);// w w w.j a va2 s.co m ws.value(i, i++, dateValue); ws.value(i, i++, localDateTimeValue); ws.value(i, i++, zonedDateValue); ws.value(i, i++, doubleValue); ws.value(i, i++, intValue); ws.value(i, i++, longValue); ws.value(i, i++, bigDecimalValue); try { ws.finish(); } catch (IOException ex) { throw new RuntimeException(ex); } }); // Check generated workbook with Apache POI XSSFWorkbook xwb = new XSSFWorkbook(new ByteArrayInputStream(data)); assertThat(xwb.getActiveSheetIndex()).isEqualTo(0); assertThat(xwb.getNumberOfSheets()).isEqualTo(1); XSSFSheet xws = xwb.getSheet(sheetName); @SuppressWarnings("unchecked") Comparable<XSSFRow> row = (Comparable) xws.getRow(0); assertThat(row).isNull(); int i = 1; assertThat(xws.getRow(i).getCell(i++).getStringCellValue()).isEqualTo(stringValue); assertThat(xws.getRow(i).getCell(i++).getDateCellValue()).isEqualTo(dateValue); // Check zoned timestamps have the same textual representation as the Dates extracted from the workbook // (Excel date serial numbers do not carry timezone information) assertThat(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(ZonedDateTime .ofInstant(xws.getRow(i).getCell(i++).getDateCellValue().toInstant(), ZoneId.systemDefault()))) .isEqualTo(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(localDateTimeValue)); assertThat(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(ZonedDateTime .ofInstant(xws.getRow(i).getCell(i++).getDateCellValue().toInstant(), ZoneId.systemDefault()))) .isEqualTo(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(zonedDateValue)); assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(doubleValue); assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(intValue); assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(longValue); assertThat(new BigDecimal(xws.getRow(i).getCell(i++).getRawValue())).isEqualTo(bigDecimalValue); }
From source file:org.dhatim.fastexcel.Correctness.java
License:Apache License
@Test public void multipleWorksheets() throws Exception { int numWs = 10; int numRows = 5000; int numCols = 6; byte[] data = writeWorkbook(wb -> { @SuppressWarnings("unchecked") CompletableFuture<Void>[] cfs = new CompletableFuture[numWs]; for (int i = 0; i < cfs.length; ++i) { Worksheet ws = wb.newWorksheet("Sheet " + i); CompletableFuture<Void> cf = CompletableFuture.runAsync(() -> { for (int j = 0; j < numCols; ++j) { ws.value(0, j, "Column " + j); ws.style(0, j).bold().fontSize(12).fillColor(Color.GRAY2).set(); for (int k = 1; k <= numRows; ++k) { switch (j) { case 0: ws.value(k, j, "String value " + k); break; case 1: ws.value(k, j, 2); break; case 2: ws.value(k, j, 3L); break; case 3: ws.value(k, j, 0.123); break; case 4: ws.value(k, j, new Date()); ws.style(k, j).format("yyyy-MM-dd HH:mm:ss").set(); break; case 5: ws.value(k, j, LocalDate.now()); ws.style(k, j).format("yyyy-MM-dd").set(); break; default: throw new IllegalArgumentException(); }// www . jav a 2s.c om } } ws.formula(numRows + 1, 1, "=SUM(" + ws.range(1, 1, numRows, 1).toString() + ")"); ws.formula(numRows + 1, 2, "=SUM(" + ws.range(1, 2, numRows, 2).toString() + ")"); ws.formula(numRows + 1, 3, "=SUM(" + ws.range(1, 3, numRows, 3).toString() + ")"); ws.formula(numRows + 1, 4, "=AVERAGE(" + ws.range(1, 4, numRows, 4).toString() + ")"); ws.style(numRows + 1, 4).format("yyyy-MM-dd HH:mm:ss").set(); ws.formula(numRows + 1, 5, "=AVERAGE(" + ws.range(1, 5, numRows, 5).toString() + ")"); ws.style(numRows + 1, 5).format("yyyy-MM-dd").bold().italic().fontColor(Color.RED) .fontName("Garamond").fontSize(new BigDecimal("14.5")).horizontalAlignment("center") .verticalAlignment("top").wrapText(true).set(); ws.range(1, 0, numRows, numCols - 1).style().borderColor(Color.RED).borderStyle("thick") .shadeAlternateRows(Color.RED).set(); }); cfs[i] = cf; } try { CompletableFuture.allOf(cfs).get(); } catch (InterruptedException | ExecutionException ex) { throw new RuntimeException(ex); } }); // Check generated workbook with Apache POI XSSFWorkbook xwb = new XSSFWorkbook(new ByteArrayInputStream(data)); assertThat(xwb.getActiveSheetIndex()).isEqualTo(0); assertThat(xwb.getNumberOfSheets()).isEqualTo(numWs); for (int i = 0; i < numWs; ++i) { assertThat(xwb.getSheetName(i)).isEqualTo("Sheet " + i); XSSFSheet xws = xwb.getSheetAt(i); assertThat(xws.getLastRowNum()).isEqualTo(numRows + 1); for (int j = 1; j <= numRows; ++j) { assertThat(xws.getRow(j).getCell(0).getStringCellValue()).isEqualTo("String value " + j); } } }