List of usage examples for org.apache.poi.ss.usermodel Workbook getNumberOfSheets
int getNumberOfSheets();
From source file:org.talend.dataprep.transformation.format.XlsWriterTest.java
License:Open Source License
/** * Please have a look at <a href="https://jira.talendforge.org/browse/TDP-1528">TDP-1528</a>. *///from w w w . j a v a 2 s.c o m @Test public void TDP_1528_export_of_backslash() throws Exception { // given SchemaParser.Request request = createSchemaParser("tdp_1528_backslash_not_exported.json"); Workbook workbook = WorkbookFactory.create(request.getContent()); assertThat(workbook).isNotNull(); assertThat(workbook.getNumberOfSheets()).isEqualTo(1); Sheet sheet = workbook.getSheetAt(0); assertThat(sheet).isNotNull().isNotEmpty(); assertThat(sheet.getFirstRowNum()).isEqualTo(0); assertThat(sheet.getLastRowNum()).isEqualTo(2); // assert header content Row row = sheet.getRow(0); /* * [ {"id": "0", "name": "column1", "type": "string"}, {"id": "1", "name": "column2", "type": "string"}, * {"id": "2", "name": "column2", "type": "string"} ] */ assertThat(row.getCell(0).getRichStringCellValue().getString()).isEqualTo("column1"); assertThat(row.getCell(1).getRichStringCellValue().getString()).isEqualTo("column2"); assertThat(row.getCell(2).getRichStringCellValue().getString()).isEqualTo("column3"); // assert first content row = sheet.getRow(1); /* * { "0": "BEAUTIFUL ITEM DESC W\BAG", "1": "Hello", "2": "Yo" } */ assertThat(row.getCell(0).getStringCellValue()).isEqualTo("BEAUTIFUL ITEM DESC W\\BAG"); assertThat(row.getCell(1).getStringCellValue()).isEqualTo("Hello"); assertThat(row.getCell(2).getStringCellValue()).isEqualTo("Yo"); // assert last content row = sheet.getRow(sheet.getLastRowNum()); /* * { "0": "Konishiwa", "1": "Na nga def", "2": "Hola" } */ assertThat(row.getCell(0).getStringCellValue()).isEqualTo("Konishiwa"); assertThat(row.getCell(1).getStringCellValue()).isEqualTo("Na nga def"); assertThat(row.getCell(2).getStringCellValue()).isEqualTo("Hola"); }
From source file:org.talend.dataprep.transformation.format.XlsWriterTest.java
License:Open Source License
/** * Please have a look at <a href="https://jira.talendforge.org/browse/TDP-4571">TDP-4571</a>. *///from ww w . ja va 2 s. c om @Test public void export_bugfix() throws Exception { // given SchemaParser.Request request = createSchemaParser("export_bug_fix_xlsx.json"); Workbook workbook = WorkbookFactory.create(request.getContent()); assertThat(workbook).isNotNull(); assertThat(workbook.getNumberOfSheets()).isEqualTo(1); Sheet sheet = workbook.getSheetAt(0); assertThat(sheet).isNotNull().isNotEmpty(); assertThat(sheet.getFirstRowNum()).isEqualTo(0); assertThat(sheet.getLastRowNum()).isEqualTo(6); // assert header content Row row = sheet.getRow(0); /* * "columns": [ { "id": "id", "type": "string" }, { "id": "firstname", "type": "string" }, { "id": "lastname", * "type": "string" }, { "id": "age", "type": "integer" }, { "id": "date-of-birth", "type": "date" }, { "id": * "alive", "type": "boolean" }, { "id": "city", "type": "string" }, { "id": "7", "type": "float" } ] */ assertThat(row.getCell(0).getRichStringCellValue().getString()).isEqualTo("id"); assertThat(row.getCell(1).getRichStringCellValue().getString()).isEqualTo("firstname"); assertThat(row.getCell(2).getRichStringCellValue().getString()).isEqualTo("lastname"); assertThat(row.getCell(3).getRichStringCellValue().getString()).isEqualTo("age"); assertThat(row.getCell(4).getRichStringCellValue().getString()).isEqualTo("date-of-birth"); assertThat(row.getCell(5).getRichStringCellValue().getString()).isEqualTo("alive"); assertThat(row.getCell(6).getRichStringCellValue().getString()).isEqualTo("city"); assertThat(row.getCell(7).getRichStringCellValue().getString()).isEqualTo("phone-number"); // assert first content row = sheet.getRow(1); /* * { "id" : "1", "firstname" : "Clark", "lastname" : "Kent", "age" : "42", "date-of-birth" : "10/09/1940", * "alive" : "false", "city" : "", "phone-number" : "" } */ assertRowValues(row, 1, "Clark", "Kent", 42, "10/09/1940", // false, "Smallville", ""); // assert second row content row = sheet.getRow(2); /* * { "id" : "2", "firstname" : "Bruce", "lastname" : "Wayne", "age" : "50", "date-of-birth" : "01/01/1947", * "alive" : "true", "city" : "Gotham city", "phone-number" : "null" } */ assertRowValues(row, 2, "Bruce", "Wayne", 50, "01/01/1947", // true, "Gotham city", "null"); // assert third row content row = sheet.getRow(3); /* * { "id" : "3", "firstname" : "Barry", "lastname" : "Allen", "age" : "67", "date-of-birth" : "01/02/1948", * "alive" : "true", "city" : "Central city", "phone-number" : "+33 6 89 46 55 34" } */ assertRowValues(row, 3, "Barry", "Allen", 67, "01/02/1948", // true, "Central city", "+33 6 89 46 55 34"); // assert last content row = sheet.getRow(sheet.getLastRowNum()); /* * { "id" : "6", "firstname" : "Ray", "lastname" : "Palmer", "age" : "93", "date-of-birth" : "01/05/1951", * "alive" : "true", "city" : "Star city" } */ assertRowValues(row, 6, "Ray", "Palmer", 93, "01/05/1951", // true, "Star city", "+33-6-89-46-55-34"); }
From source file:org.teiid.translator.excel.ExcelMetadataProcessor.java
License:Open Source License
public void process(MetadataFactory mf, FileConnection conn) throws TranslatorException { if (this.excelFileName == null) { throw new TranslatorException(ExcelPlugin.Event.TEIID23004, ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23004, "importer.ExcelFileName")); //$NON-NLS-1$ }/*from w w w. ja v a2s . c o m*/ try { File xlsFile = conn.getFile(this.excelFileName); if (xlsFile.isDirectory() || !xlsFile.exists()) { throw new TranslatorException(ExcelPlugin.Event.TEIID23005, ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23005, xlsFile.getName())); } String extension = getFileExtension(xlsFile); FileInputStream xlsFileStream = new FileInputStream(xlsFile); try { Workbook workbook = null; if (extension.equalsIgnoreCase("xls")) { //$NON-NLS-1$ workbook = new HSSFWorkbook(xlsFileStream); } else if (extension.equalsIgnoreCase("xlsx")) { //$NON-NLS-1$ workbook = new XSSFWorkbook(xlsFileStream); } int sheetCount = workbook.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { Sheet sheet = workbook.getSheetAt(i); addTable(mf, sheet, xlsFile.getName()); } } finally { xlsFileStream.close(); } } catch (ResourceException e) { throw new TranslatorException(e); } catch (IOException e) { throw new TranslatorException(e); } }
From source file:org.wso2.carbon.dataservices.sql.driver.processor.reader.ExcelDataReader.java
License:Open Source License
public void populateData() throws SQLException { Workbook workbook = ((TExcelConnection) getConnection()).getWorkbook(); int noOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < noOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); String sheetName = sheet.getSheetName(); ColumnInfo[] headers = this.extractColumnHeaders(sheet); DataTable dataTable = new FixedDataTable(sheetName, headers); Iterator<Row> rowItr = sheet.rowIterator(); while (rowItr.hasNext()) { Row row = rowItr.next();/*from w ww . j a v a 2 s .c o m*/ if (row.getRowNum() != 0) { DataRow dataRow = new DataRow(row.getRowNum() - 1); Iterator<Cell> cellItr = row.cellIterator(); int cellIndex = 0; while (cellItr.hasNext()) { Cell cell = cellItr.next(); DataCell dataCell = new DataCell(cellIndex + 1, cell.getCellType(), extractCellValue(cell)); dataRow.addCell(dataCell.getColumnId(), dataCell); cellIndex++; } dataTable.addRow(dataRow); } } this.getData().put(dataTable.getTableName(), dataTable); } }
From source file:org.wso2.security.tool.adapter.ExcelInputAdapter.java
License:Open Source License
/** * Converts the data in the files with .xlsx extension to the JSON format. * A workbook is created from the the excel file (.xlsx) and while iterating through the sheets in the workbook; * the data is read and set in to a JSONObject. The JSONObject returned by the method contains an array of * row objects corresponding to each row in the workbook. A row object contains values of each cell in a given row, * with key values starting from letter 'A'. * * @param dataFilePath The path where the data file uploaded is saved. * @return returns the JSON object that contains all the data in the .xlsx file. * @throws FeedbackToolException If the .xlsx file is not found in the given path or due to an error in * parsing the data in the data file. *//*from w w w.ja va 2 s .co m*/ @Override public JSONObject convert(String dataFilePath) throws FeedbackToolException { // JSONObject to hold the array of row objects JSONObject dataJSONObject = new JSONObject(); try { Workbook workbook = WorkbookFactory.create(new File(dataFilePath)); logInfo = "Workbook has " + workbook.getNumberOfSheets() + " sheets"; log.info(logInfo); Iterator<Sheet> sheetIterator = workbook.sheetIterator(); // JSONArray to hold all the row objects JSONArray rowsJSONArray = new JSONArray(); while (sheetIterator.hasNext()) { Sheet sheet = sheetIterator.next(); logInfo = "Sheet: " + sheet.getSheetName() + " has " + sheet.getNumMergedRegions() + " merged regions"; log.info(logInfo); DataFormatter dataFormatter = new DataFormatter(); logInfo = "Iterating over Rows and Columns using for-each loop"; log.info(logInfo); for (Row row : sheet) { // JSONObject to hold the data in the cells of a given row JSONObject rowJSONObject = new JSONObject(); char keyLetter = 'A'; for (Cell cell : row) { String cellValue = dataFormatter.formatCellValue(cell); rowJSONObject.put(keyLetter, cellValue); ++keyLetter; } rowsJSONArray.add(rowJSONObject); } } dataJSONObject.put(Constants.JSON_DATA_OBJECT, rowsJSONArray); } catch (InvalidFormatException e) { throw new FeedbackToolException("Error in parsing the data file uploaded", e); } catch (IOException e) { throw new FeedbackToolException("Data file was not found in the specified location", e); } return dataJSONObject; }
From source file:packtest.IterateCells.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(new FileInputStream(args[0])); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i);//from w w w . ja va 2s . c o m System.out.println(wb.getSheetName(i)); for (Row row : sheet) { System.out.println("rownum: " + row.getRowNum()); for (Cell cell : row) { System.out.println(cell.toString()); } } } }
From source file:ro.dabuno.office.integration.Xlsx2Word.java
public static void main(String[] args) throws Exception { log.info("starting app"); // Workbook wb = new XSSFWorkbook(new FileInputStream(args[0])); Workbook wb = new XSSFWorkbook(new FileInputStream("office-files/Input.xlsx")); DataFormatter formatter = new DataFormatter(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i);//from w w w . j a v a2s . c o m System.out.println(wb.getSheetName(i)); int j = 4; for (Row row : sheet) { System.out.println("rownum: " + row.getRowNum()); for (Cell cell : row) { CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); System.out.print(cellRef.formatAsString()); System.out.print(" - "); // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc) String text = formatter.formatCellValue(cell); System.out.println(text); System.out.println("------------"); // Alternatively, get the value and format it yourself switch (cell.getCellTypeEnum()) { case STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.print(cellRef.formatAsString()); System.out.print(" - "); System.out.println((long) cell.getNumericCellValue()); } break; case BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case FORMULA: System.out.println(cell.getCellFormula()); break; case BLANK: System.out.println(); break; default: System.out.println(); } } j--; if (j == 0) { break; } } } XWPFDocument doc = new XWPFDocument(); XWPFParagraph p0 = doc.createParagraph(); XWPFRun r0 = p0.createRun(); r0.setBold(false); r0.setText("Domnule"); XWPFRun r00 = p0.createRun(); r00.setBold(true); r00.setText(" Ionescu Ion"); FileOutputStream out = new FileOutputStream("out/xlsx2word.docx"); doc.write(out); out.close(); }
From source file:se.softhouse.garden.oak.ExcelDecisionTableBuilder.java
License:Open Source License
private void loadFromStream(InputStream is, DecisionEngine engine) throws IOException, InvalidFormatException { if (is == null) { throw new IOException("Unable to find resource."); }/*from w w w. j a v a2 s. co m*/ Workbook wb = WorkbookFactory.create(is); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); DecisionTable table = new DecisionTable(); List<Integer> tableIndexes = locateActionTables(sheet); for (int k = 0; k < tableIndexes.size() - 1; k++) { table.addTable(create(table, sheet, tableIndexes.get(k), tableIndexes.get(k + 1))); } engine.addDecisionTable(sheet.getSheetName(), table); } }
From source file:send.sms.az.util.ReadExcelFileToList.java
public static List<Map<String, Entry>> readExcelData(UploadedFile fileName) { List<Map<String, Entry>> mapList = new ArrayList<>(); // logger.info("--------------- starting workbook"); try {//from w w w . ja v a 2s . c o m //Create the input stream from the xlsx/xls file InputStream fis = fileName.getInputstream(); // InputStream fis = new FileInputStream("D:/test.xls"); //Create Workbook instance for xlsx/xls file input stream Workbook workbook = null; // logger.info("--------------- getting workbook"); if (fileName.getFileName().toLowerCase().endsWith("xlsx")) { // logger.info("--------------- " + fileName.getFileName().toLowerCase()); workbook = new XSSFWorkbook(fis); } else if (fileName.getFileName().toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(fis); } //Get the number of sheets in the xlsx file int numberOfSheets = workbook.getNumberOfSheets(); //loop through each of the sheets for (int i = 0; i < numberOfSheets; i++) { //Get the nth sheet from the workbook Sheet sheet = workbook.getSheetAt(i); //every sheet has rows, iterate over them Iterator<Row> rowIterator = sheet.iterator(); int rowPos = 0; while (rowIterator.hasNext()) { ++rowPos; Map<String, Entry> map = new HashMap<>();//map for full row //Get the row object Row row = rowIterator.next(); //Every row has columns, get the column iterator and iterate over them Iterator<Cell> cellIterator = row.cellIterator(); int cellPos = 0;//setirsayi while (cellIterator.hasNext()) { //Get the Cell object ++cellPos; Cell cell = cellIterator.next(); //check the cell type and process accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String value = cell.getStringCellValue().trim(); // System.out.println("string " + value + " " + cellPos + " " + rowPos); map.put("" + (cellPos - 1), new Entry("" + rowPos, value)); break; case Cell.CELL_TYPE_NUMERIC: double numValue = cell.getNumericCellValue(); // System.out.println("numb " + numValue + " " + cellPos + " " + rowPos); map.put("" + (cellPos - 1), new Entry("" + rowPos, "" + numValue)); break; } } //end of cell iterator // System.out.println("added to list"); mapList.add(map); } //end of rows iterator } //end of sheets for loop //close file input stream fis.close(); } catch (Exception e) { e.printStackTrace(); } return mapList; }
From source file:send.sms.az.util.UploadBlackList.java
public static List<String> readExcelData(UploadedFile fileName) { List<Map<String, Entry>> mapList = new ArrayList<>(); List<String> blList = new ArrayList(); try {/* w ww . j ava 2s .c om*/ InputStream fis = fileName.getInputstream(); Workbook workbook = null; if (fileName.getFileName().toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(fis); } else if (fileName.getFileName().toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(fis); } //Get the number of sheets in the xlsx file int numberOfSheets = workbook.getNumberOfSheets(); //loop through each of the sheets for (int i = 0; i < numberOfSheets; i++) { //Get the nth sheet from the workbook Sheet sheet = workbook.getSheetAt(i); //every sheet has rows, iterate over them Iterator<Row> rowIterator = sheet.iterator(); int rowPos = 0; while (rowIterator.hasNext()) { ++rowPos; Map<String, Entry> map = new HashMap<>();//map for full row //Get the row object Row row = rowIterator.next(); //Every row has columns, get the column iterator and iterate over them Iterator<Cell> cellIterator = row.cellIterator(); int cellPos = 0;//setirsayi while (cellIterator.hasNext()) { //Get the Cell object ++cellPos; Cell cell = cellIterator.next(); //check the cell type and process accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String value = cell.getStringCellValue().trim(); // System.out.println("string " + value + " " + cellPos + " " + rowPos); // map.put("" + (cellPos - 1), new Entry("" + rowPos, value)); blList.add(value); break; case Cell.CELL_TYPE_NUMERIC: double numValue = cell.getNumericCellValue(); // System.out.println("numb " + numValue + " " + cellPos + " " + rowPos); // map.put("" + (cellPos - 1), new Entry("" + rowPos, "" + numValue)); blList.add("" + numValue); break; } break; } //end of cell iterator mapList.add(map); } //end of rows iterator break; } //end of sheets for loop //close file input stream fis.close(); } catch (Exception e) { blList = new ArrayList<>(); e.printStackTrace(); } return blList; }