List of usage examples for org.apache.poi.ss.usermodel Workbook getNumberOfSheets
int getNumberOfSheets();
From source file:sqlitemanager.Excel2Dataset.java
public static List<DataTable> readExcel(String inPath, boolean hasIntColumns, int colsHasInt) { List<DataTable> out = new ArrayList(); try {// ww w . ja va2 s.c om // Create a work book reference Workbook workbook = null; if (inPath.endsWith(".xls")) { workbook = new HSSFWorkbook(new FileInputStream(inPath)); } else if (inPath.endsWith(".xlsx")) { workbook = new XSSFWorkbook(new FileInputStream(inPath)); } else { System.err.println("No XLS or XLSX file found!"); return out; } //Create a count of the sheets in the file short sheetsCount = (short) workbook.getNumberOfSheets(); //create a reference of sheet, cell, first head, last head, head name, //sheet name, row count and row content Sheet sheet; Row row; Cell cell; int firstIndex = Integer.MIN_VALUE; int lastIndex = Integer.MAX_VALUE; String[] headName; fieldType[] fieldTypes; String sheetName; int rowCount; Object cellValue; for (int i = 0; i < sheetsCount; i++) { sheetName = workbook.getSheetName(i); try { sheet = workbook.getSheetAt(i); rowCount = sheet.getLastRowNum() + 1; if (rowCount < 1) { break; } // row = sheet.getRow(0); // for (int j = 0; j < rowCount; j++) { // row = sheet.getRow(j); // if (firstIndex < row.getFirstCellNum()) { // firstIndex = row.getFirstCellNum(); // } // if (lastIndex > row.getLastCellNum()) { // lastIndex = row.getLastCellNum(); // } // } row = sheet.getRow(0); // Head row firstIndex = row.getFirstCellNum(); lastIndex = row.getLastCellNum(); headName = new String[lastIndex]; fieldTypes = new fieldType[lastIndex]; List<String> names = new ArrayList(); for (int index = firstIndex; index < lastIndex; index++) { String name = row.getCell(index).toString(); if (names.contains(name)) { JOptionPane.showMessageDialog(null, String.format("Field \"%s\" duplicated!", name), "Notice", JOptionPane.ERROR_MESSAGE); return null; } else { names.add(name); } headName[index] = name; fieldTypes[index] = fieldType.Double; } // Detect field types for (int k = 1; k < rowCount; k++) { row = sheet.getRow(k); if (row == null) { break; } for (int index = firstIndex; index < lastIndex; index++) { if (fieldTypes[index] != fieldType.String) { if (row.getCell(index) != null) { fieldTypes[index] = fieldType .getType(getCellType(row.getCell(index).getCellType())); } else { fieldTypes[index] = fieldType.String; } } } } DataTable tempTable = new DataTable(sheetName); for (int index = firstIndex; index < lastIndex; index++) { tempTable.addField(headName[index], fieldTypes[index]); } for (int k = 1; k < rowCount; k++) { row = sheet.getRow(k); if (row == null) { break; } tempTable.addRecord(); for (int index = firstIndex; index < lastIndex; index++) { cell = row.getCell(index); if (fieldTypes[index] == fieldType.Double) { try { cellValue = cell.getNumericCellValue(); } catch (Exception e) { System.err.println(String.format("Error reading Sheet: %s, Row: %d, Column: %d", cell.getSheet().getSheetName(), cell.getRowIndex(), cell.getColumnIndex())); cellValue = cell.getStringCellValue().trim(); } } else if (fieldTypes[index] == fieldType.Integer) { try { cellValue = (int) cell.getNumericCellValue(); } catch (Exception e) { System.err.println(String.format("Error reading Sheet: %s, Row: %d, Column: %d", cell.getSheet().getSheetName(), cell.getRowIndex(), cell.getColumnIndex())); cellValue = cell.getStringCellValue().trim(); } } else { if (cell == null) { cellValue = ""; } else { try { try { cellValue = cell.getNumericCellValue(); } catch (Exception e) { cellValue = cell.getStringCellValue().trim(); } } catch (Exception e) { System.err.println( String.format("Error reading Sheet: %s, Row: %d, Column: %d", cell.getSheet().getSheetName(), cell.getRowIndex(), cell.getColumnIndex())); cellValue = cell.getNumericCellValue(); } } } tempTable.getField(index).set(tempTable.getRecordCount() - 1, cellValue); } } if (hasIntColumns) { DataTable table = new DataTable(tempTable.getName()); List<Integer> updateFields = new ArrayList(); if (colsHasInt < 1) { // 0 or negative means check all columns colsHasInt = tempTable.getRecordCount(); } int cols4Check = Math.min(colsHasInt, tempTable.getRecordCount()); for (int j = 0; j < cols4Check; j++) { Field f = tempTable.getField(j); if (f.getType() != fieldType.Double) { continue; } boolean isIntColumn = true; for (int recNum = 0; recNum < tempTable.getRecordCount(); recNum++) { double value = Double.valueOf(f.get(recNum).toString()); double checkValue = Double.valueOf(String.valueOf((int) value)); if (value != checkValue) { isIntColumn = false; break; } } if (isIntColumn) { updateFields.add(j); } } for (int j = 0; j < tempTable.getFieldCount(); j++) { fieldType type = tempTable.getField(j).getType(); if (updateFields.contains(j)) { type = fieldType.Integer; } table.addField(tempTable.getField(j).getName(), type); } for (int recNum = 0; recNum < tempTable.getRecordCount(); recNum++) { table.addRecord(); for (int col = 0; col < tempTable.getFieldCount(); col++) { Object rowItem; if (updateFields.contains(col)) { Double value = (double) tempTable.getRecord(recNum).get(col); rowItem = value.intValue(); } else { rowItem = tempTable.getRecord(recNum).get(col); } table.getField(col).set(table.getRecordCount() - 1, rowItem); } } out.add(table); } else { out.add(tempTable); } } catch (Exception e) { Logger.getLogger(Excel2Dataset.class.getName()).log(Level.SEVERE, null, e); JOptionPane.showMessageDialog(null, String.format("Loading sheet %s error!", sheetName), "Notice", JOptionPane.ERROR_MESSAGE); } } } catch (Exception ex) { Logger.getLogger(Excel2Dataset.class.getName()).log(Level.SEVERE, null, ex); } return out; }
From source file:step.datapool.excel.ExcelDataPoolImpl.java
License:Open Source License
@Override public void init() { super.init(); String bookName = configuration.getFile().get(); String sheetName = configuration.getWorksheet().get(); logger.debug("book: " + bookName + " sheet: " + sheetName); ExcelFileLookup excelFileLookup = new ExcelFileLookup(context); File workBookFile = excelFileLookup.lookup(bookName); forWrite = configuration.getForWrite().get(); workbookSet = new WorkbookSet(workBookFile, ExcelFunctions.getMaxExcelSize(), forWrite, true); Workbook workbook = workbookSet.getMainWorkbook(); if (sheetName == null || sheetName.isEmpty()) { if (workbook.getNumberOfSheets() > 0) { sheet = workbook.getSheetAt(0); } else {/* ww w .j av a 2s . c o m*/ if (forWrite) { sheet = workbook.createSheet(); } else { throw new ValidationException("The workbook " + workBookFile.getName() + " contains no sheet"); } } } else { sheet = workbook.getSheet(sheetName); if (sheet == null) { if (forWrite) { sheet = workbook.createSheet(sheetName); } else { throw new ValidationException( "The sheet " + sheetName + " doesn't exist in the workbook " + workBookFile.getName()); } } } resetCursor(); }
From source file:uk.co.certait.htmlexporter.writer.excel.ExcelExporter.java
License:Apache License
public void exportHtml(String html, OutputStream out) throws IOException { Workbook workbook = new XSSFWorkbook(); StyleMap styleMapper = getStyleMapper(html); Sheet sheet = null;//from www . ja va 2 s . c o m int startRow = 0; for (Element element : getTables(html)) { if (workbook.getNumberOfSheets() == 0) { String sheetName = getSheetName(element); if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } } else if (isNewSheet(element)) { String sheetName = getSheetName(element); if (StringUtils.isNotEmpty(sheetName)) sheet = workbook.createSheet(sheetName); else { sheet = workbook.createSheet(); } startRow = 0; } TableWriter writer = new ExcelTableWriter( new ExcelTableRowWriter(sheet, new ExcelTableCellWriter(sheet, styleMapper))); startRow += writer.writeTable(element, styleMapper, startRow) + 1; sheet.createRow(startRow); } for (int i = 0; i < workbook.getNumberOfSheets(); ++i) { formatSheet(workbook.getSheetAt(i)); } workbook.write(out); out.flush(); out.close(); }
From source file:utilities.ExchangeManager.java
License:Open Source License
public ArrayList<String> getFormsFromXLSX(InputStream inputStream) throws Exception { ArrayList<String> forms = new ArrayList<String>(); Workbook wb = null; try {/* w w w.j a va 2 s .c o m*/ wb = new XSSFWorkbook(inputStream); int sheetCount = wb.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { String name = wb.getSheetName(i); if (name.startsWith("d_")) { // Legacy forms remove prefix added by older results exports 30th January 2018 name = name.substring(2); } forms.add(name); } } finally { try { wb.close(); } catch (Exception e) { } } return forms; }
From source file:writeintoexcel.ReadExcelFileExample.java
private static List getStudentsListFromExcel() { List studentList = new ArrayList(); FileInputStream fis = null;//from w ww . j ava 2 s. co m try { fis = new FileInputStream(FILE_PATH); // Using XSSF for xlsx format, for xls use HSSF Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); //looping over each workbook sheet for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); //iterating over each row while (rowIterator.hasNext()) { Student student = new Student(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); //Iterating over each cell (column wise) in a particular row. while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); //The Cell Containing String will is name. if (Cell.CELL_TYPE_STRING == cell.getCellType()) { student.setName(cell.getStringCellValue()); //The Cell Containing numeric value will contain marks } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { //Cell with index 1 contains marks in Maths if (cell.getColumnIndex() == 1) { student.setMaths(String.valueOf(cell.getNumericCellValue())); } //Cell with index 2 contains marks in Science else if (cell.getColumnIndex() == 2) { student.setScience(String.valueOf(cell.getNumericCellValue())); } //Cell with index 3 contains marks in English else if (cell.getColumnIndex() == 3) { student.setEnglish(String.valueOf(cell.getNumericCellValue())); } } } //end iterating a row, add all the elements of a row in list studentList.add(student); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return studentList; }
From source file:xlsParser.hffsimpl.SSReader.java
@Override public XLSWorkbook read(String file) throws Exception { Workbook workbook = null; if (file.endsWith(".xls")) { workbook = new HSSFWorkbook(new FileInputStream(file)); } else if (file.endsWith(".xlsx")) { workbook = new XSSFWorkbook(new FileInputStream(file)); }/*from w w w. ja v a 2 s .c om*/ if (workbook == null) return null; Sheet[] sheets = new Sheet[workbook.getNumberOfSheets()]; String[] sheetNames = new String[workbook.getNumberOfSheets()]; for (int i = 0; i < sheets.length; ++i) { sheets[i] = workbook.getSheetAt(i); sheetNames[i] = workbook.getSheetName(i); } List<XLSSheet> xlsSheets = new ArrayList(sheets.length); for (int i = 0; i < sheets.length; ++i) { xlsSheets.add(this.createSheet(sheetNames[i], sheets[i])); } return new BaseXLSWorkbook(xlsSheets); }
From source file:XlsUtils.XlsComparator.java
public static boolean comparaExcel(Workbook excel1, Workbook excel2, StringBuilder cache) { boolean res = true; int numSheet1 = excel1.getNumberOfSheets(); int numSheet2 = excel2.getNumberOfSheets(); int maxNumSheets = numSheet1 > numSheet2 ? numSheet1 : numSheet2; try {/*from w w w . ja v a 2s. c om*/ for (int i = 0; i < maxNumSheets; i++) if (!comparaHoja(excel1.getSheetAt(i), excel2.getSheetAt(i), cache)) res = false; } catch (IllegalArgumentException | NullPointerException e) { res = false; } return res; }