List of usage examples for org.apache.poi.ss.usermodel CellStyle getDataFormatString
String getDataFormatString();
From source file:org.tiefaces.components.websheet.utility.CellStyleUtility.java
License:MIT License
/** * get symbol position from format string e.g. [$CAD] #,##0.00 will return * p. While #,##0.00 $ will return s// www . j a v a 2 s. c o m * * @param cell * the cell * @return symbol position of the formatted string */ private static String getSymbolPositionFromFormat(final Cell cell) { CellStyle style = cell.getCellStyle(); if (style == null) { return "p"; } String formatString = style.getDataFormatString(); if (formatString == null) { return "p"; } int symbolpos = formatString.indexOf('$'); int numberpos = formatString.indexOf('#'); if (numberpos < 0) { numberpos = formatString.indexOf('0'); } if (symbolpos < numberpos) { return "p"; } else { return "s"; } }
From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();//from w ww . j a v a 2 s.c om out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); int lastNum = -1; while (rows.hasNext()) { Row row = rows.next(); int curNum = row.getRowNum(); if (curNum - lastNum > 1) { for (int i = lastNum + 2; i <= curNum; i++) { out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, i); out.format(" <td colspan=%d style=\"%s\"> </td>%n", (endColumn - firstColumn + 1), styleSimpleContents(null, false)); out.format(" </tr>%n"); } } lastNum = curNum; 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; boolean isNumeric = false; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content != null && !content.equals("") && (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC)) { if (DateUtil.isCellDateFormatted(cell)) { // Date if ("mmm-yy".equals(style.getDataFormatString())) { SimpleDateFormat sdfRus = new SimpleDateFormat("MMM.yy"); content = sdfRus.format(cell.getDateCellValue()); } else if ("h:mm".equals(style.getDataFormatString())) { SimpleDateFormat sdfRus = new SimpleDateFormat("HH:mm"); content = sdfRus.format(cell.getDateCellValue()); } else if (style.getDataFormatString() != null && style.getDataFormatString().contains("mm")) { SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss"); content = sdfRus.format(cell.getDateCellValue()); } else { SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy"); content = sdfRus.format(cell.getDateCellValue()); } } else { // Number if ("- 0".equals(content.trim())) content = " "; else content = "<nobr>" + content.replace(",", " ").replace(".", ",") + "</nobr>"; isNumeric = true; } } if (content == null || content.equals("")) content = " "; } } boolean isInRangeNotFirst = false; for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress rangeAddress = sheet.getMergedRegion(j); if (row.getRowNum() == rangeAddress.getFirstRow() && i == rangeAddress.getFirstColumn()) { if (rangeAddress.getLastRow() - row.getRowNum() > 0) attrs += " rowspan=" + (rangeAddress.getLastRow() - row.getRowNum() + 1); if (rangeAddress.getLastColumn() - i > 0) attrs += " colspan=" + (rangeAddress.getLastColumn() - i + 1); break; } else if (row.getRowNum() >= rangeAddress.getFirstRow() && row.getRowNum() <= rangeAddress.getLastRow() && i >= rangeAddress.getFirstColumn() && i <= rangeAddress.getLastColumn()) { isInRangeNotFirst = true; break; } } if (!isInRangeNotFirst) { out.format(" <td style=\"%s\" %s>%s</td>%n", styleSimpleContents(style, isNumeric), attrs, content); } } // columns out.format(" </tr>%n"); } // rows out.format("</tbody>%n"); }
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 o m 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:uk.ac.liverpool.spreadsheet.ToXML.java
License:Apache License
private void printSheetContent(Sheet sheet) { ensureColumnBounds(sheet);//www . j a va 2 s. c o 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
private void printSheetContent(Sheet sheet) { printColumnHeads();/*from ww w . j a v a2s . 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"); }