List of usage examples for org.apache.poi.ss.format CellFormat getInstance
public static CellFormat getInstance(String format)
From source file:cn.edu.zucc.chenxg.preview.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();//w ww. ja va2s .co m 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:com.common.report.util.html.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { // printColumnHeads(); out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next();/* ww w.j a v a 2 s .co m*/ 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:com.googlecode.testcase.annotation.handle.toexcel.strategy.ToHtmlWithExcel.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads(sheet);/* www . j ava 2s. 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"); }
From source file:com.hurry.excel.html.Excel2Html.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();/* ww w .j a v a 2 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:examples.toHTML.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();//w w w. ja v a 2s .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; content = replaceUmlaut(content); 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:ro.dabuno.office.integration.Data.java
private void readExcelFile(File excelFile) throws EncryptedDocumentException, InvalidFormatException, IOException { try (Workbook wb = WorkbookFactory.create(excelFile, null, true)) { Sheet sheet = wb.getSheetAt(0);/* w w w. jav a2s . c o m*/ if (sheet == null) { throw new IllegalArgumentException( "Provided Microsoft Excel file " + excelFile + " does not have any sheet"); } final int start; final int end; { // read headers Row row = sheet.getRow(0); if (row == null) { throw new IllegalArgumentException("Provided Microsoft Excel file " + excelFile + " does not have data in the first row in the first sheet, " + "but we expect the header data to be located there"); } start = row.getFirstCellNum(); end = row.getLastCellNum(); for (int cellnum = start; cellnum <= end; cellnum++) { Cell cell = row.getCell(cellnum); if (cell == null) { // add null to the headers if there are columns without title in the sheet headers.add(null); log.info("Had empty header for column " + CellReference.convertNumToColString(cellnum)); } else { String value = cell.toString(); headers.add(value); log.info("Had header '" + value + "' for column " + CellReference.convertNumToColString(cellnum)); } } } for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum); if (row == null) { // ignore missing rows continue; } List<String> data = new ArrayList<>(); for (int colnum = start; colnum <= end; colnum++) { Cell cell = row.getCell(colnum); if (cell == null) { // store null-data for empty/missing cells data.add(null); } else { final String value; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // ensure that numeric are formatted the same way as in the Excel file. value = CellFormat.getInstance(cell.getCellStyle().getDataFormatString()) .apply(cell).text; break; default: // all others can use the default value from toString() for now. value = cell.toString(); } data.add(value); } } values.add(data); } } }
From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();//w w w .ja va 2 s. com 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 ww .j a va2 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);//ww w.j ava2s. 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 printSheetContent(Sheet sheet) { printColumnHeads();/*w ww . j a va 2 s . c o m*/ 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"); }