List of usage examples for org.apache.poi.ss.usermodel Cell getSheet
Sheet getSheet();
From source file:se.softhouse.garden.oak.ExcelDecisionTableBuilder.java
License:Open Source License
private Number getNumericValue(int type, Cell cell) { switch (type) { case Cell.CELL_TYPE_NUMERIC: if (cell instanceof XSSFCell) { String raw = ((XSSFCell) cell).getRawValue(); return new BigDecimal(raw); }/* ww w .j a va 2 s .c o m*/ return cell.getNumericCellValue(); case Cell.CELL_TYPE_FORMULA: return getNumericValue(cell.getCachedFormulaResultType(), cell); case Cell.CELL_TYPE_STRING: { String raw = cell.getStringCellValue(); if (raw == null || raw.isEmpty() || !raw.matches("0-9")) { // We throw this instead of the plain NumberFormatException that would've been // thrown otherwise. throw new IllegalArgumentException("Invalid Numeric String Cell value [" + raw + "] in Sheet(" + cell.getSheet().getSheetName() + "). Row:Column[" + cell.getRowIndex() + 1 + ":" + (cell.getColumnIndex() + 1) + "]"); } return new BigDecimal(raw); } } return BigDecimal.ZERO; }
From source file:sqlitemanager.Excel2Dataset.java
public static List<DataTable> readExcel(String inPath, boolean hasIntColumns, int colsHasInt) { List<DataTable> out = new ArrayList(); try {// w w w . j a v a 2s . 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.ExcelFunctions.java
License:Open Source License
private static int evaluateFormulaCell(Cell cell, FormulaEvaluator evaluator) { int typ = -1; try {//from w w w.ja v a 2 s .c o m typ = evaluator.evaluateFormulaCell(cell); } catch (RuntimeException e) { String cellRef = CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1); String errMsg = "Error while evaluating cell " + cellRef + " from sheet " + cell.getSheet().getSheetName() + ": " + e.getMessage(); throw new RuntimeException(errMsg, e); } return typ; }
From source file:testpoi.GenerateDailyExcel.java
License:Open Source License
private static void makeEntry(Department deptt) { //create new row in xlsx to be generated Row newRow = sheetNew.createRow(rowCnt++); //Create a new cell in current row Cell newCell = newRow.createCell(0); //Set value to the department's name newCell.setCellValue(deptt.name);/* w ww. java 2s . c om*/ double random = Math.random(); Row row = null; if (deptt.name.equals("Gynaecology")) { //Pick a row from female sheet randomly (Female sheet should have all reproducible ages) int rowNum = (int) (random * sheetFemale.getPhysicalNumberOfRows()); row = sheetFemale.getRow(rowNum); } else if (deptt.name.equals("Paediatrics")) { //Pick a row from children sheet randomly (Children sheet should have all ages under 13) int rowNum = (int) (random * sheetChildren.getPhysicalNumberOfRows()); row = sheetChildren.getRow(rowNum); } else { //Pick a row from all sheet randomly int rowNum = (int) (random * sheetAll.getPhysicalNumberOfRows()); row = sheetAll.getRow(rowNum); } assert (row != null); //read and write fetched row Iterator<Cell> cellIterator = row.cellIterator(); int newCellCnt = 1; while (cellIterator.hasNext()) { //May we write all cells as strings? Cell cell = cellIterator.next(); String cellValue = null; try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = cell.getNumericCellValue() + ""; else cellValue = cell.getStringCellValue(); newCell = newRow.createCell(newCellCnt++); newCell.setCellValue(cellValue); // System.out.print (cellValue+"("+cell.getColumnIndex()+")\t"); } catch (Exception e) { System.out.println("Could not write from cell (value:" + cellValue + // ", column:"+cell.getSheet().getWorkbook().+ ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:" + cell.getColumnIndex() + ")"); e.printStackTrace(); } } System.out.println(); //delete row read if (row.getSheet() == sheetFemale) sheetFemale.removeRow(row); else if (row.getSheet() == sheetChildren) sheetChildren.removeRow(row); else sheetAll.removeRow(row); }
From source file:testpoi.GenerateDailyExcelPickingRowsSequentially.java
License:Open Source License
private static void makeEntry(Department deptt) { //create new row in xlsx to be generated Row newRow = sheetNew.createRow(rowCnt++); //Create a new cell in current row Cell newCell = newRow.createCell(0); //Set value to the department's name newCell.setCellValue(deptt.name);/*from w ww . j a v a 2 s . co m*/ Row row = null; if (deptt.name.equals("Obs & Gynae") && femaleRowNum < sheetFemale.getPhysicalNumberOfRows()) { // //Pick a row from female sheet randomly (Female sheet should have all reproducible ages) // int rowNum = (int)(random*sheetFemale.getPhysicalNumberOfRows()); row = sheetFemale.getRow(femaleRowNum++); System.out.println("Sheet:Female, row: " + row.getRowNum()); } else if (deptt.name.equals("Paediatrics") && childRowNum < sheetChildren.getPhysicalNumberOfRows()) { row = sheetChildren.getRow(childRowNum++); System.out.println("Sheet:Children, row: " + row.getRowNum()); } else //if (allRowNum<sheetAll.getPhysicalNumberOfRows()) { row = sheetAll.getRow(allRowNum++); System.out.println("Sheet:All, row: " + row.getRowNum()); } assert row != null; //read and write fetched row Iterator<Cell> cellIterator = row.cellIterator(); int newCellCnt = 1; while (cellIterator.hasNext()) { //May we write all cells as strings? Cell cell = cellIterator.next(); String cellValue = null; try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = (int) (cell.getNumericCellValue()) + ""; else cellValue = cell.getStringCellValue(); newCell = newRow.createCell(newCellCnt++); newCell.setCellValue(cellValue); } catch (Exception e) { System.out.println("Could not write from cell (value:" + cellValue + // ", column:"+cell.getSheet().getWorkbook().+ ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:" + cell.getColumnIndex() + ")"); e.printStackTrace(); } } System.out.println(); // //delete row read // if (row.getSheet()==sheetFemale) // sheetFemale.removeRow(row); // else if (row.getSheet()==sheetChildren) // sheetChildren.removeRow(row); // else // sheetAll.removeRow(row); }
From source file:testpoi.Department.java
License:Open Source License
private static void makeEntry(Department deptt) { //create new row in xlsx to be generated Row newRow = sheetNew.createRow(rowCnt++); //Create a new cell in current row Cell newCell = newRow.createCell(0); //Set value to the department's name newCell.setCellValue(deptt.name);/*from w w w.jav a2 s .co m*/ newCell = newRow.createCell(1); newCell.setCellValue(deptt.isNew ? "New" : "Old"); newCell = newRow.createCell(2); newCell.setCellValue(crNo++); Row row = null; if (deptt.name.equals("Obs & Gynae") && femaleRowNum < sheetFemale.getPhysicalNumberOfRows()) { // //Pick a row from female sheet randomly (Female sheet should have all reproducible ages) // int rowNum = (int)(random*sheetFemale.getPhysicalNumberOfRows()); row = sheetFemale.getRow(femaleRowNum++); System.out.println("Sheet:Female, row: " + row.getRowNum()); } else if (deptt.name.equals("Paediatrics") && childRowNum < sheetChildren.getPhysicalNumberOfRows()) { row = sheetChildren.getRow(childRowNum++); System.out.println("Sheet:Children, row: " + row.getRowNum()); } else //if (allRowNum<sheetAll.getPhysicalNumberOfRows()) { row = sheetAll.getRow(allRowNum++); System.out.println("Sheet:All, row: " + row.getRowNum()); } assert row != null; //read and write fetched row Iterator<Cell> cellIterator = row.cellIterator(); int newCellCnt = 3; while (cellIterator.hasNext()) { //May we write all cells as strings? Cell cell = cellIterator.next(); String cellValue = null; try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = (int) (cell.getNumericCellValue()) + ""; else cellValue = cell.getStringCellValue(); newCell = newRow.createCell(newCellCnt++); newCell.setCellValue(cellValue); } catch (Exception e) { System.out.println("Could not write from cell (value:" + cellValue + // ", column:"+cell.getSheet().getWorkbook().+ ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:" + cell.getColumnIndex() + ")"); e.printStackTrace(); } } System.out.println(); // //delete row read // if (row.getSheet()==sheetFemale) // sheetFemale.removeRow(row); // else if (row.getSheet()==sheetChildren) // sheetChildren.removeRow(row); // else // sheetAll.removeRow(row); }
From source file:testpoi.OldDepttSheet.java
License:Open Source License
private static void makeEntry(Department deptt) { //create new row in xlsx to be generated Row newRow = sheetNew.createRow(rowCnt++); //Create a new cell in current row Cell newCell = newRow.createCell(0); //Set value to the department's name newCell.setCellValue(deptt.name);/*from w ww . j ava 2 s . c o m*/ newCell = newRow.createCell(1); newCell.setCellValue(deptt.isNew ? "New" : "Old"); if (deptt.isNew) { newCell = newRow.createCell(2); newCell.setCellValue(crNo++); Row row = null; if (deptt.name.equals("Obs & Gynae")) { // //Pick a row from female sheet randomly (Female sheet should have all reproducible ages) // int rowNum = (int)(random*sheetFemale.getPhysicalNumberOfRows()); if (femaleRowNum < sheetFemale.getPhysicalNumberOfRows()) { row = sheetFemale.getRow(femaleRowNum++); System.out.println("Sheet:Female, row: " + row.getRowNum()); } else { System.err.println("Female entries exhausted!"); } } else if (deptt.name.equals("Paediatrics")) { if (childRowNum < sheetChildren.getPhysicalNumberOfRows()) { row = sheetChildren.getRow(childRowNum++); System.out.println("Sheet:Children, row: " + row.getRowNum()); } else { System.err.println("Child entries exhausted!"); } } else { if (allRowNum < sheetAll.getPhysicalNumberOfRows()) { row = sheetAll.getRow(allRowNum++); System.out.println("Sheet:All, row: " + row.getRowNum()); } else { System.err.println("All(General New) entries exhausted!"); } } if (row == null) { throw new IllegalArgumentException("New input Rows Exhausted"); } assert row != null; //read and write fetched row Iterator<Cell> cellIterator = row.cellIterator(); int newCellCnt = 3; while (cellIterator.hasNext()) { //May we write all cells as strings? Cell cell = cellIterator.next(); String cellValue = null; try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = (int) (cell.getNumericCellValue()) + ""; else cellValue = cell.getStringCellValue(); newCell = newRow.createCell(newCellCnt++); newCell.setCellValue(cellValue); } catch (Exception e) { System.out.println("Could not write from cell (value:" + cellValue + // ", column:"+cell.getSheet().getWorkbook().+ ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:" + cell.getColumnIndex() + ")"); e.printStackTrace(); } } } else //deptt is old { OldDepttSheet oldDepttSheetToUse = depttToOldSheetsMap.get(deptt.name); Row row = oldDepttSheetToUse.sheet.getRow(oldDepttSheetToUse.rowCnt++); if (row == null) { throw new IllegalArgumentException("Old Input Rows Exhausted in department " + deptt.name); } System.out.println("Sheet:" + deptt.name + ", row: " + row.getRowNum()); //Copy row from old sheet to newRow int newCellCnt = 2; Iterator<Cell> cellIterator = row.cellIterator(); //Skip columns Department and Patient Type cellIterator.next(); cellIterator.next(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellValue = null; try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = (int) (cell.getNumericCellValue()) + ""; else cellValue = cell.getStringCellValue(); newCell = newRow.createCell(newCellCnt++); newCell.setCellValue(cellValue); } catch (Exception e) { System.out.println("Could not write from old sheet cell (value:" + cellValue + ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:" + cell.getColumnIndex() + ")"); e.printStackTrace(); } } } }
From source file:uk.ac.liverpool.spreadsheet.ToXML.java
License:Apache License
private void parseFormula(Cell cell, FormulaParsingWorkbook fpwb, FormulaRenderingWorkbook frwb) { CellReference c = new CellReference(cell); String cr = c.formatAsString(); Ptg[] pp = FormulaParser.parse(cell.getCellFormula(), fpwb, FormulaType.CELL, currentSheet); for (Ptg p : pp) { if (p instanceof RefPtg) { RefPtg a = (RefPtg) p;/*from w w w. ja v a 2s.c om*/ Cell dest = cell.getSheet().getRow(a.getRow()).getCell(a.getColumn()); if (dest != null && dest.getCellType() == Cell.CELL_TYPE_FORMULA) { String cr2 = new CellReference(dest).formatAsString(); List<String> ls = crToParent.get(cr2); if (ls == null) { ls = new LinkedList<String>(); crToParent.put(cr2, ls); } ls.add(cr); } List<String> ls = cellsToFormula.get(a.toFormulaString()); if (ls == null) { ls = new LinkedList<String>(); ls.add(cr); cellsToFormula.put(a.toFormulaString(), ls); } else ls.add(cr); } if (p instanceof AreaPtg) { AreaPtg a = (AreaPtg) p; for (int i = a.getFirstColumn(); i <= a.getLastColumn(); i++) { for (int k = a.getFirstRow(); k <= a.getLastRow(); k++) { String cc = new CellReference(k, i).formatAsString(); Cell dest = cell.getSheet().getRow(k).getCell(i); if (dest != null && dest.getCellType() == Cell.CELL_TYPE_FORMULA) { String cr2 = new CellReference(dest).formatAsString(); List<String> ls = crToParent.get(cr2); if (ls == null) { ls = new LinkedList<String>(); crToParent.put(cr2, ls); } ls.add(cr); } List<String> ls = cellsToFormula.get(cc); if (ls == null) { ls = new LinkedList<String>(); ls.add(cr); cellsToFormula.put(cc, ls); } else ls.add(cr); } } } } String cellF = "[." + cr + "]=" + FormulaRenderer.toFormulaString(frwb, pp); System.out.println(cellF); cellToFormulaConverted.put(cr, cellF); }
From source file:uk.co.certait.htmlexporter.writer.excel.ExcelStyleGenerator.java
License:Apache License
public CellStyle getStyle(Cell cell, Style style) { XSSFCellStyle cellStyle;//w ww .j a va 2 s. c om if (styles.containsKey(style)) { cellStyle = styles.get(style); } else { cellStyle = (XSSFCellStyle) cell.getSheet().getWorkbook().createCellStyle(); applyBackground(style, cellStyle); applyBorders(style, cellStyle); applyFont(cell, style, cellStyle); applyHorizontalAlignment(style, cellStyle); applyverticalAlignment(style, cellStyle); applyWidth(cell, style); styles.put(style, cellStyle); } return cellStyle; }
From source file:uk.co.certait.htmlexporter.writer.excel.ExcelStyleGenerator.java
License:Apache License
protected void applyFont(Cell cell, Style style, XSSFCellStyle cellStyle) { Font font = createFont(cell.getSheet().getWorkbook(), style); cellStyle.setFont(font);//from w ww.j a v a 2s .c o m }