List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:sol.neptune.elisaboard.service.VPlanToHtml.java
License:Apache License
private void ensureColumnBounds(Sheet sheet) { if (gotBounds) { return;/*from w w w .j a v a2 s .co 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()); } } 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();//from w w w . j a v a2 s . c om out.format(" <tr>%n"); /* skip first col*/ /* 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:tot.bill.service.ReadExcel.java
public static int readQABill(String filePath, ArrayList<BILL_QA> billQaList) { System.out.println("ReadExcel.readQABill Start"); try {/*from ww w. jav a2 s. com*/ FileInputStream inputStreamFile = new FileInputStream(filePath); Workbook wb = WorkbookFactory.create(inputStreamFile); Sheet firstSheet = wb.getSheetAt(0); Iterator<Row> iterator = firstSheet.rowIterator(); Row firstRow = iterator.next(); int reccordNum = 0; while (iterator.hasNext()) { reccordNum++; Row nextRow = iterator.next(); BILL_QA bill = new BILL_QA(); System.out.println( "ReadExcel.readQABill-->Record " + reccordNum + " acc_id:" + nextRow.getCell(0).toString()); if (nextRow.getCell(0).toString().trim().length() == 0) { break; } bill.setACCOUNT_ID(nextRow.getCell(0) == null ? "" : nextRow.getCell(0).toString()); bill.setCYCLE_CODE(nextRow.getCell(1) == null ? "" : nextRow.getCell(1).toString()); bill.setCYCLE_YEAR(nextRow.getCell(2) == null ? "" : nextRow.getCell(2).toString()); bill.setCYCLE_MONTH(nextRow.getCell(3) == null ? "" : nextRow.getCell(3).toString()); bill.setSYS_CREATION_DATE(nextRow.getCell(4) == null ? "" : nextRow.getCell(4).toString()); bill.setSYS_UPDATE_DATE(nextRow.getCell(5) == null ? "" : nextRow.getCell(5).toString()); bill.setDESCRIPTION(nextRow.getCell(6) == null ? "" : nextRow.getCell(6).toString()); billQaList.add(bill); } System.out.println("ReadExcel.readQABill End"); } catch (Exception ex) { ex.printStackTrace(); } return 0; }
From source file:ui.MainWindow.java
/** * Creates new form MainWindow/*from w w w .j a v a 2s . c om*/ */ public MainWindow() { initComponents(); jFileChooser1.setFileFilter(FILTER); int returnVal = jFileChooser1.showOpenDialog(this); jLabel2.setVisible(false); jLabel3.setVisible(false); jLabel4.setVisible(false); jLabel5.setVisible(false); jLabel6.setVisible(false); jLabel7.setVisible(false); if (returnVal == JFileChooser.APPROVE_OPTION) { workbook = WorksheetFactory.importExcel(jFileChooser1.getSelectedFile().getAbsolutePath()); } else { System.out.println("Cancelado"); System.exit(0); } Sheet sheet = workbook.getSheetAt(0); Iterator iter = sheet.rowIterator(); Row row; Cell c; while (iter.hasNext()) { row = (Row) iter.next(); if (row.getRowNum() >= 4 && !row.getCell(1).getStringCellValue().isEmpty()) { c = row.getCell(1); jComboBox1.addItem(c.getStringCellValue()); } } Calendar mon = Calendar.getInstance(), sun = Calendar.getInstance(); mon.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY); sun.set(Calendar.DAY_OF_WEEK, Calendar.FRIDAY); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); while (!(sun.get(Calendar.MONTH) == Calendar.DECEMBER && sun.get(Calendar.WEEK_OF_MONTH) > 2)) { jComboBox2.addItem(df.format(mon.getTime()) + "-" + df.format(sun.getTime())); mon.add(Calendar.DAY_OF_YEAR, 7); sun.add(Calendar.DAY_OF_YEAR, 7); } }
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 av a 2 s . com*/ 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
/** * Spread sheet level conversion//from w w w . j ava 2 s. c o m * the Output file will be named inputWorkbook.[sheetNumber].xml * @param File to convert * @throws IOException */ private void convertSheets(String filename) throws IOException { int total = wb.getNumberOfSheets(); String start = filename.substring(0, filename.lastIndexOf('.')); String end = filename.substring(filename.lastIndexOf('.')); for (int c = 0; c < total; c++) { try { Sheet sheet = wb.getSheetAt(c); if (!sheet.rowIterator().hasNext()) continue; output = new PrintWriter(new FileWriter(start + c + end)); out = new Formatter(output); out.format("<?xml version='1.0' encoding='iso-8859-1'?>\n" + "<?xml-stylesheet type=\"text/xsl\" href=\"spreadsheet.xsl\"?>\n"); out.format("<spreadsheets>"); currentSheet = c; printSheet(sheet); out.format("%n"); out.format("</spreadsheets>"); } finally { if (out != null) out.close(); if (output instanceof Closeable) { Closeable closeable = (Closeable) output; closeable.close(); } } } }
From source file:uk.ac.liverpool.spreadsheet.ToXML.java
License:Apache License
private void printSheetContent(Sheet sheet) { ensureColumnBounds(sheet);/*w w w . j a v a 2 s . co m*/ 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
public void printStyles() { ensureOut();/* ww w .j a v a 2s .c o m*/ // First, copy the base css BufferedReader in = null; try { in = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("/excelStyle.css"))); String line; while ((line = in.readLine()) != null) { out.format("%s%n", line); } } catch (IOException e) { throw new IllegalStateException("Reading standard css", e); } finally { IOUtils.closeQuietly(in); } // now add css for each used style Set<CellStyle> seen = new HashSet<CellStyle>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) { CellStyle style = cell.getCellStyle(); if (!seen.contains(style)) { printStyle(style); seen.add(style); } } } } }
From source file:uk.co.certait.test.ExcelToHtmlConverter.java
License:Apache License
private void ensureColumnBounds(Sheet sheet) { if (gotBounds) { return;//from www . j a va 2s . co 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();// w w w . j av a 2 s . com 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"); }