List of usage examples for org.apache.poi.ss.usermodel Row getFirstCellNum
short getFirstCellNum();
From source file:se.softhouse.garden.oak.ExcelDecisionTableBuilder.java
License:Open Source License
private List<Integer> locateActionTables(Sheet sheet) { List<Integer> indexes = new ArrayList<Integer>(); Row tableTypeRow = sheet.getRow(1); Row opRow = sheet.getRow(5);/*ww w. j a va 2s.c om*/ for (int i = tableTypeRow.getFirstCellNum(); i < tableTypeRow.getLastCellNum(); i++) { Cell typeRowCell = tableTypeRow.getCell(i); if (typeRowCell == null) { throw new NullPointerException("Sheet[" + sheet.getSheetName() + "] - Row:Col[1:" + i + "] - Cell object is null. (#columns = " + tableTypeRow.getLastCellNum() + ")"); } String cellValue = typeRowCell.getStringCellValue(); if (cellValue == null) { throw new NullPointerException( "Sheet[" + sheet.getSheetName() + "] - Row:Col[1:" + i + "] - Cell value is null."); } if (!cellValue.isEmpty()) { indexes.add(i); } } indexes.add((int) opRow.getLastCellNum()); return indexes; }
From source file:service.Read_Write_File.java
public static List<Consomation> Read_Fil_XLSX(File myFile) throws FileNotFoundException, IOException { List<CorpDetat> corpDetats = new ArrayList<CorpDetat>(); List<Consomation> consomations = new ArrayList<Consomation>(); FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); XSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator<Row> rowIterator = mySheet.iterator(); Consomation consomation = null;/*from w w w. ja va2s .c om*/ CorpDetat corpDetat = null; ConsomationItem consomationItem = null; while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getRowNum() == 0) {// pour ne pas lire les titres row = rowIterator.next(); } int lascellNum = row.getLastCellNum(); int firstcellNum = row.getFirstCellNum(); System.out.println("first cell num" + firstcellNum + " last cell num" + lascellNum); if (lascellNum < 3) { if (row.getRowNum() > 1) { //corpDetats.add(corpDetat); consomations.add(consomation); corpDetat = null; System.out.println("if dyal row celll"); } consomation = new Consomation(); consomation.setId((int) row.getCell(firstcellNum).getNumericCellValue()); corpDetat = new CorpDetat(); corpDetat.setTitre(row.getCell(firstcellNum + 1).getStringCellValue()); } else { Post post = new Post(); consomationItem = new ConsomationItem(); for (int i = firstcellNum; i < lascellNum; i++) { Cell cell = row.getCell(i); switch (i) { case 0: System.out.println("num de consomation item" + cell.getStringCellValue()); consomationItem.setId(cell.getStringCellValue()); break; case 1: System.out.println("cell " + i + ":" + cell.getStringCellValue()); post.setTitre(cell.getStringCellValue()); break; case 2: consomationItem.setUnite(cell.getStringCellValue()); break; case 3: System.out.println("cell " + i + ":" + cell.getCellType()); consomationItem.setQuanite((int) cell.getNumericCellValue()); break; } } if (post != null) { post.setCorpdetat(corpDetat); consomationItem.setPost(post); post.setCorpdetat(corpDetat); corpDetat.getPosts().add(post); consomation.getConsomationItems().add(consomationItem); consomation.setCorpDetat(corpDetat); } } } consomations.add(consomation); // pour le dernier corp makydkholch l row li tab30 return consomations; }
From source file:sol.neptune.elisaboard.service.VPlanToHtml.java
License:Apache License
private void ensureColumnBounds(Sheet sheet) { if (gotBounds) { return;/*from ww w. j a v a2 s. c om*/ } Iterator<Row> iter = sheet.rowIterator(); firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0); endColumn = 0; while (iter.hasNext()) { Row row = iter.next(); short firstCell = row.getFirstCellNum(); if (firstCell >= 0) { firstColumn = Math.min(firstColumn, firstCell); endColumn = Math.max(endColumn, row.getLastCellNum()); } } if (maxColumns > 0 && endColumn > maxColumns) { endColumn = maxColumns; } gotBounds = true; }
From source file:sol.neptune.elisaboard.service.VPlanToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { /* skip column heads */ //printColumnHeads(); out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); out.format(" <tr>%n"); /* skip first col*/ /*/*from w w w . j ava 2 s . co m*/ out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); */ for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { final Date date = cell.getDateCellValue(); System.out.println("Date: " + date); System.out.println(new Date()); } if (content.equals("")) { content = " "; } } } out.format(" <td class=%s %s>%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }
From source file:sqlitemanager.Excel2Dataset.java
public static List<DataTable> readExcel(String inPath, boolean hasIntColumns, int colsHasInt) { List<DataTable> out = new ArrayList(); try {/*from w w w . j a v a2 s. co m*/ // 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:uk.ac.liverpool.spreadsheet.ExcelFeatureAnalysis.java
License:Apache License
private static void analyseSheet(Sheet ss, Element s, Namespace n, ExcelFeatureAnalysis efa) { // generic part boolean costumFormatting = false; boolean formulae = false; boolean UDF = false; boolean hasComments = false; Set<String> udfs = new HashSet<String>(); FormulaEvaluator evaluator = ss.getWorkbook().getCreationHelper().createFormulaEvaluator(); s.setAttribute("name", ss.getSheetName()); s.setAttribute("firstRow", "" + ss.getFirstRowNum()); s.setAttribute("lastRow", "" + ss.getLastRowNum()); try {/* w ww. j a v a 2s . c o m*/ s.setAttribute("forceFormulaRecalc", "" + ss.getForceFormulaRecalculation()); } catch (Throwable x) { //x.printStackTrace(); } // shapes in detail? Footer footer = ss.getFooter(); if (footer != null) { s.setAttribute("footer", "true"); } Header header = ss.getHeader(); if (header != null) { s.setAttribute("header", "true"); } PaneInformation paneInformation = ss.getPaneInformation(); if (paneInformation != null) { s.setAttribute("panels", "true"); } HSSFSheet hs = null; XSSFSheet xs = null; if (ss instanceof HSSFSheet) { hs = (HSSFSheet) ss; try { if (hs.getDrawingPatriarch() != null) { if (hs.getDrawingPatriarch().containsChart()) s.addContent(new Element("charts", sn)); if (hs.getDrawingPatriarch().countOfAllChildren() > 0) s.addContent(new Element("shapes", sn)); } } catch (Exception x) { x.printStackTrace(); } if (hs.getSheetConditionalFormatting().getNumConditionalFormattings() > 0) { s.setAttribute("conditionalFormatting", "true"); } } if (ss instanceof XSSFSheet) { xs = (XSSFSheet) ss; } Iterator<Row> rows = ss.rowIterator(); int firstColumn = (rows.hasNext() ? Integer.MAX_VALUE : 0); int endColumn = 0; while (rows.hasNext()) { Row row = rows.next(); short firstCell = row.getFirstCellNum(); if (firstCell >= 0) { firstColumn = Math.min(firstColumn, firstCell); endColumn = Math.max(endColumn, row.getLastCellNum()); } } s.setAttribute("firstColumn", "" + firstColumn); s.setAttribute("lastColumn", "" + endColumn); rows = ss.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) if (cell != null) { try { if (!cell.getCellStyle().getDataFormatString().equals("GENERAL")) costumFormatting = true; } catch (Throwable t) { } if (cell.getCellComment() != null) hasComments = true; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: // if (DateUtil.isCellDateFormatted(cell)) { // // System.out.println(cell.getDateCellValue()); // } else { // // System.out.println(cell.getNumericCellValue()); // } break; case Cell.CELL_TYPE_BOOLEAN: // System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: // System.out.println(cell.getCellFormula()); formulae = true; if (!UDF) try { evaluator.evaluate(cell); } catch (Exception x) { if (x instanceof NotImplementedException) { Throwable e = x; //e.printStackTrace(); while (e != null) { for (StackTraceElement c : e.getStackTrace()) { if (c.getClassName().contains("UserDefinedFunction")) { UDF = true; System.out.println("UDF " + e.getMessage()); udfs.add(e.getMessage()); } } e = e.getCause(); } } } break; default: } } } if (costumFormatting) { Element cf = new Element("customisedFormatting", sn); s.addContent(cf); } if (formulae) { Element cf = new Element("formulae", sn); s.addContent(cf); } if (UDF) { Element cf = new Element("userDefinedFunctions", sn); for (String sss : udfs) cf.addContent(new Element("userDefinedFunction", sn).setAttribute("functionName", sss)); s.addContent(cf); } if (hasComments) { Element cf = new Element("cellComments", sn); s.addContent(cf); } }
From source file:uk.ac.liverpool.spreadsheet.ToXML.java
License:Apache License
private void printSheetContent(Sheet sheet) { ensureColumnBounds(sheet);/* w ww . j av a 2 s.c om*/ printColumnHeads(); cellsToFormula = new HashMap<String, List<String>>(); cellToFormulaConverted = new HashMap<String, String>(); crToParent = new HashMap<String, List<String>>(); FormulaParsingWorkbook fpwb; FormulaRenderingWorkbook frwb; if (xswb != null) { XSSFEvaluationWorkbook w = XSSFEvaluationWorkbook.create(xswb); frwb = w; fpwb = w; } else if (hswb != null) { HSSFEvaluationWorkbook w = HSSFEvaluationWorkbook.create(hswb); frwb = w; fpwb = w; } else return; // first we need to determine all the dependencies ofr each formula Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (int i = firstColumn; i < endColumn; i++) { if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) try { parseFormula(cell, fpwb, frwb); } catch (Exception x) { } } } } } rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); int rowNumber = row.getRowNum() + 1; out.format(" <TableRow>%n"); out.format(" <RowHeader>%d</RowHeader>%n", rowNumber); out.format(" <TableCells>%n"); for (int i = firstColumn; i < endColumn; i++) { String content = "0"; String attrs = ""; CellStyle style = null; String valueType = "float"; Cell cell = row.getCell(i); CellReference c = new CellReference(rowNumber - 1, i); attrs += " cellID=\"." + c.formatAsString() + "\""; String cr = c.formatAsString(); // if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) { attrs += " readOnly=\"readOnly\""; try { attrs += " cellFormula=\"" + StringEscapeUtils.escapeXml(cell.getCellFormula()) + "\""; } catch (Exception x) { attrs += " cellFormula=\"FORMULA ERROR\""; } } else { List<String> cfrl = cellsToFormula.get(cr); StringBuffer formula = new StringBuffer(""); if (cfrl != null) { List<String> refs = new LinkedList<String>(); visit(cfrl, refs); System.out.println(refs); cleanup(refs); for (String s : refs) { formula.append(StringEscapeUtils.escapeXml(cellToFormulaConverted.get(s))); formula.append(" || "); } } if (formula.length() > 0) attrs += " formula=\"" + formula.substring(0, formula.length() - 4) + "\""; } if (cell != null) { style = cell.getCellStyle(); // Set the value that is rendered for the cell // also applies the format try { CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; } catch (Exception x) { content = "DATA FORMULA ERROR "; } } // } attrs += " value_type=\"" + valueType + "\""; attrs += " value=\"" + StringEscapeUtils.escapeXml(content) + "\""; out.format(" <TableCell %s>%s</TableCell>%n", // class=%s // styleName(style), attrs, StringEscapeUtils.escapeXml(content)); } out.format(" </TableCells> </TableRow>%n%n"); } }
From source file:uk.co.certait.test.ExcelToHtmlConverter.java
License:Apache License
private void ensureColumnBounds(Sheet sheet) { if (gotBounds) { return;/*w ww. j a v a 2 s.c o m*/ } Iterator<Row> iter = sheet.rowIterator(); firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0); endColumn = 0; while (iter.hasNext()) { Row row = iter.next(); short firstCell = row.getFirstCellNum(); if (firstCell >= 0) { firstColumn = Math.min(firstColumn, firstCell); endColumn = Math.max(endColumn, row.getLastCellNum()); } } gotBounds = true; }
From source file:uk.co.certait.test.ExcelToHtmlConverter.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();/*from www. ja va2 s .c om*/ out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); // Set the value that is rendered for the cell // also applies the format CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content.equals("")) { content = " "; } } } out.format(" <td class=%s %s>%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }
From source file:utilities.TableReader.java
private static int[] getExcelColumns(XSSFSheet sheet, ArrayList<TableMetaData> meta) throws UploadException { int[] columns = Tools.defaultIntArray(meta.size() + 1); Row row = sheet.getRow(0); for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { Cell cell = row.getCell(i);//from w w w.j a va 2 s.c om String colName = cell.getRichStringCellValue().toString(); checkColumn(columns, colName, i, meta); } if (Tools.contains(columns, -1)) { throw new UploadException(UploadException.MISSING_COLUMNS); } return columns; }