List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetName
@Override public String getSheetName(int sheetIx)
From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java
License:Apache License
/** * ?/*from ww w. j a v a 2s. co 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???/*from w ww.j a v a2 s. c o 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 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(); }//from ww w .j a va2 s .co m } } 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); } } }
From source file:org.olat.search.service.document.file.ExcelOOXMLDocument.java
License:Apache License
private void extractContent(final StringBuilder buffy, final XSSFWorkbook document) { for (int i = 0; i < document.getNumberOfSheets(); i++) { final XSSFSheet sheet = document.getSheetAt(i); buffy.append(document.getSheetName(i)).append(' '); // Header(s), if present extractHeaderFooter(buffy, sheet.getFirstHeader()); extractHeaderFooter(buffy, sheet.getOddHeader()); extractHeaderFooter(buffy, sheet.getEvenHeader()); // Rows and cells for (final Object rawR : sheet) { final Row row = (Row) rawR; for (final Iterator<Cell> ri = row.cellIterator(); ri.hasNext();) { final Cell cell = ri.next(); if (cell.getCellType() == Cell.CELL_TYPE_FORMULA || cell.getCellType() == Cell.CELL_TYPE_STRING) { buffy.append(cell.getRichStringCellValue().getString()).append(' '); } else { final XSSFCell xc = (XSSFCell) cell; final String rawValue = xc.getRawValue(); if (rawValue != null) { buffy.append(rawValue).append(' '); }//ww w . j a va 2 s . c o m } // Output the comment in the same cell as the content final Comment comment = cell.getCellComment(); if (comment != null) { buffy.append(comment.getString().getString()).append(' '); } } } // Finally footer(s), if present extractHeaderFooter(buffy, sheet.getFirstFooter()); extractHeaderFooter(buffy, sheet.getOddFooter()); extractHeaderFooter(buffy, sheet.getEvenFooter()); } }
From source file:org.wandora.application.tools.extractors.excel.ExcelExtractor.java
License:Open Source License
public Collection getSheets(XSSFWorkbook workbook) { ArrayList<String> sheets = new ArrayList(); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets && !forceStop(); i++) { sheets.add(workbook.getSheetName(i)); }//w w w . j a v a2 s . co m return sheets; }
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
private static void startReadXlsxFile(String fileName) { try {//from ww w.ja v a2 s .co m XSSFWorkbook wb = HSSFReadWrite.readxlsxFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { XSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { XSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { XSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:timetable.translate.StudentInfoTranslator.java
public boolean convertToStudentMap(XSSFWorkbook workbook, HashMap<String, String> studentMap, List<CourseStruct> coursesInfo, List<String> courseList) { XSSFSheet[] enrolmentSheets = new XSSFSheet[workbook.getNumberOfSheets()]; // XSSFRow[] row = new XSSFRow[workbook.getNumberOfSheets()]; // XSSFCell[] cell = new XSSFCell[workbook.getNumberOfSheets()]; for (int iSheet = 0; iSheet < workbook.getNumberOfSheets(); iSheet++) { enrolmentSheets[iSheet] = workbook.getSheetAt(iSheet); if (enrolmentSheets[iSheet] != null) { for (Row row : enrolmentSheets[iSheet]) { StudentStruct tempStudent = new StudentStruct(); tempStudent.studentID = row.getCell(0).getStringCellValue(); tempStudent.studentName = row.getCell(1).getStringCellValue(); if ((!tempStudent.studentID.equals("Registration Number")) && (!tempStudent.studentID.equals("Name"))) { coursesInfo.get(courseList.indexOf(workbook.getSheetName(iSheet))).enrolledStudents .add(tempStudent); studentMap.put(tempStudent.studentID, tempStudent.studentName); }/*from www. ja v a2 s . c om*/ // studentMap.remove("Registration Number", "Name"); } } } return true; }