org.apache.poi.examples.ss.html.ToHtml Maven / Gradle / Ivy
The newest version!
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.examples.ss.html;
import java.io.BufferedReader;
import java.io.Closeable;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.nio.charset.StandardCharsets;
import java.util.Formatter;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.format.CellFormatResult;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* This example shows how to display a spreadsheet in HTML using the classes for
* spreadsheet display.
*/
@SuppressWarnings({"java:S106","java:S4823","java:S1192"})
public final class ToHtml {
private final Workbook wb;
private final Appendable output;
private boolean completeHTML;
private Formatter out;
private boolean gotBounds;
private int firstColumn;
private int endColumn;
private HtmlHelper helper;
private static final String DEFAULTS_CLASS = "excelDefaults";
private static final String COL_HEAD_CLASS = "colHeader";
private static final String ROW_HEAD_CLASS = "rowHeader";
private static final Map HALIGN = mapFor(
HorizontalAlignment.LEFT, "left",
HorizontalAlignment.CENTER, "center",
HorizontalAlignment.RIGHT, "right",
HorizontalAlignment.FILL, "left",
HorizontalAlignment.JUSTIFY, "left",
HorizontalAlignment.CENTER_SELECTION, "center");
private static final Map VALIGN = mapFor(
VerticalAlignment.BOTTOM, "bottom",
VerticalAlignment.CENTER, "middle",
VerticalAlignment.TOP, "top");
private static final Map BORDER = mapFor(
BorderStyle.DASH_DOT, "dashed 1pt",
BorderStyle.DASH_DOT_DOT, "dashed 1pt",
BorderStyle.DASHED, "dashed 1pt",
BorderStyle.DOTTED, "dotted 1pt",
BorderStyle.DOUBLE, "double 3pt",
BorderStyle.HAIR, "solid 1px",
BorderStyle.MEDIUM, "solid 2pt",
BorderStyle.MEDIUM_DASH_DOT, "dashed 2pt",
BorderStyle.MEDIUM_DASH_DOT_DOT, "dashed 2pt",
BorderStyle.MEDIUM_DASHED, "dashed 2pt",
BorderStyle.NONE, "none",
BorderStyle.SLANTED_DASH_DOT, "dashed 2pt",
BorderStyle.THICK, "solid 3pt",
BorderStyle.THIN, "dashed 1pt");
private static final int IDX_TABLE_WIDTH = -2;
private static final int IDX_HEADER_COL_WIDTH = -1;
@SuppressWarnings({"unchecked"})
private static Map mapFor(Object... mapping) {
Map map = new HashMap<>();
for (int i = 0; i < mapping.length; i += 2) {
map.put((K) mapping[i], (V) mapping[i + 1]);
}
return map;
}
/**
* Creates a new examples to HTML for the given workbook.
*
* @param wb The workbook.
* @param output Where the HTML output will be written.
*
* @return An object for converting the workbook to HTML.
*/
public static ToHtml create(Workbook wb, Appendable output) {
return new ToHtml(wb, output);
}
/**
* Creates a new examples to HTML for the given workbook. If the path ends
* with "{@code .xlsx}" an {@link XSSFWorkbook} will be used; otherwise
* this will use an {@link HSSFWorkbook}.
*
* @param path The file that has the workbook.
* @param output Where the HTML output will be written.
*
* @return An object for converting the workbook to HTML.
*/
public static ToHtml create(String path, Appendable output)
throws IOException {
return create(new FileInputStream(path), output);
}
/**
* Creates a new examples to HTML for the given workbook. This attempts to
* detect whether the input is XML (so it should create an {@link
* XSSFWorkbook} or not (so it should create an {@link HSSFWorkbook}).
*
* @param in The input stream that has the workbook.
* @param output Where the HTML output will be written.
*
* @return An object for converting the workbook to HTML.
*/
public static ToHtml create(InputStream in, Appendable output)
throws IOException {
Workbook wb = WorkbookFactory.create(in);
return create(wb, output);
}
private ToHtml(Workbook wb, Appendable output) {
if (wb == null) {
throw new NullPointerException("wb");
}
if (output == null) {
throw new NullPointerException("output");
}
this.wb = wb;
this.output = output;
setupColorMap();
}
private void setupColorMap() {
if (wb instanceof HSSFWorkbook) {
helper = new HSSFHtmlHelper((HSSFWorkbook) wb);
} else if (wb instanceof XSSFWorkbook) {
helper = new XSSFHtmlHelper();
} else {
throw new IllegalArgumentException(
"unknown workbook type: " + wb.getClass().getSimpleName());
}
}
/**
* Run this class as a program
*
* @param args The command line arguments.
*
* @throws Exception Exception we don't recover from.
*/
public static void main(String[] args) throws Exception {
if(args.length < 2){
System.err.println("usage: ToHtml inputWorkbook outputHtmlFile");
return;
}
try (PrintWriter pw = new PrintWriter(args[1], StandardCharsets.UTF_8.name())) {
ToHtml toHtml = create(args[0], pw);
toHtml.setCompleteHTML(true);
toHtml.printPage();
}
}
public void setCompleteHTML(boolean completeHTML) {
this.completeHTML = completeHTML;
}
public void printPage() throws IOException {
try {
ensureOut();
if (completeHTML) {
out.format(
"%n");
out.format("%n");
out.format("%n");
out.format("%n");
out.format("%n");
}
print();
if (completeHTML) {
out.format("%n");
out.format("%n");
}
} finally {
IOUtils.closeQuietly(out);
if (output instanceof Closeable) {
IOUtils.closeQuietly((Closeable) output);
}
}
}
public void print() {
printInlineStyle();
printSheets();
}
private void printInlineStyle() {
//out.format("%n");
out.format("%n");
}
private void ensureOut() {
if (out == null) {
out = new Formatter(output, Locale.ROOT);
}
}
public void printStyles() {
ensureOut();
// First, copy the base css
try (BufferedReader in = new BufferedReader(new InputStreamReader(
getClass().getResourceAsStream("excelStyle.css"), StandardCharsets.ISO_8859_1))){
String line;
while ((line = in.readLine()) != null) {
out.format("%s%n", line);
}
} catch (IOException e) {
throw new IllegalStateException("Reading standard css", e);
}
// now add css for each used style
Set seen = new HashSet<>();
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
Iterator 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);
}
}
}
}
}
private void printStyle(CellStyle style) {
out.format(".%s .%s {%n", DEFAULTS_CLASS, styleName(style));
styleContents(style);
out.format("}%n");
}
private void styleContents(CellStyle style) {
styleOut("text-align", style.getAlignment(), HALIGN);
styleOut("vertical-align", style.getVerticalAlignment(), VALIGN);
fontStyle(style);
borderStyles(style);
helper.colorStyles(style, out);
}
private void borderStyles(CellStyle style) {
styleOut("border-left", style.getBorderLeft(), BORDER);
styleOut("border-right", style.getBorderRight(), BORDER);
styleOut("border-top", style.getBorderTop(), BORDER);
styleOut("border-bottom", style.getBorderBottom(), BORDER);
}
private void fontStyle(CellStyle style) {
Font font = wb.getFontAt(style.getFontIndex());
if (font.getBold()) {
out.format(" font-weight: bold;%n");
}
if (font.getItalic()) {
out.format(" font-style: italic;%n");
}
int fontheight = font.getFontHeightInPoints();
if (fontheight == 9) {
//fix for stupid ol Windows
fontheight = 10;
}
out.format(" font-size: %dpt;%n", fontheight);
// Font color is handled with the other colors
}
private String styleName(CellStyle style) {
if (style == null) {
style = wb.getCellStyleAt(0);
}
StringBuilder sb = new StringBuilder();
try (Formatter fmt = new Formatter(sb, Locale.ROOT)) {
fmt.format("style_%02x", style.getIndex());
return fmt.toString();
}
}
private void styleOut(String attr, K key, Map mapping) {
String value = mapping.get(key);
if (value != null) {
out.format(" %s: %s;%n", attr, value);
}
}
private static CellType ultimateCellType(Cell c) {
CellType type = c.getCellType();
if (type == CellType.FORMULA) {
type = c.getCachedFormulaResultType();
}
return type;
}
private void printSheets() {
ensureOut();
Sheet sheet = wb.getSheetAt(0);
printSheet(sheet);
}
public void printSheet(Sheet sheet) {
ensureOut();
Map widths = computeWidths(sheet);
int tableWidth = widths.get(IDX_TABLE_WIDTH);
out.format("%n", DEFAULTS_CLASS, tableWidth);
printCols(widths);
printSheetContent(sheet);
out.format("
%n");
}
/**
* computes the column widths, defined by the sheet.
*
* @param sheet The sheet for which to compute widths
* @return Map with key: column index; value: column width in pixels
*
special keys:
*
{@link #IDX_HEADER_COL_WIDTH} - width of the header column
*
{@link #IDX_TABLE_WIDTH} - width of the entire table
*/
private Map computeWidths(Sheet sheet) {
Map ret = new TreeMap<>();
int tableWidth = 0;
ensureColumnBounds(sheet);
// compute width of the header column
int lastRowNum = sheet.getLastRowNum();
int headerCharCount = String.valueOf(lastRowNum).length();
int headerColWidth = widthToPixels((headerCharCount + 1) * 256.0);
ret.put(IDX_HEADER_COL_WIDTH, headerColWidth);
tableWidth += headerColWidth;
for (int i = firstColumn; i < endColumn; i++) {
int colWidth = widthToPixels(sheet.getColumnWidth(i));
ret.put(i, colWidth);
tableWidth += colWidth;
}
ret.put(IDX_TABLE_WIDTH, tableWidth);
return ret ;
}
/**
* Probably platform-specific, but appears to be a close approximation on some systems
* @param widthUnits POI's native width unit (twips)
* @return the approximate number of pixels for a typical display
*/
protected int widthToPixels(final double widthUnits) {
return Math.toIntExact(Math.round(widthUnits * 9 / 256));
}
private void printCols(Map widths) {
int headerColWidth = widths.get(IDX_HEADER_COL_WIDTH);
out.format(" 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;
}
private void printColumnHeads() {
out.format("%n");
out.format(" %n", COL_HEAD_CLASS);
out.format(" ◊ %n", COL_HEAD_CLASS);
//noinspection UnusedDeclaration
StringBuilder colName = new StringBuilder();
for (int i = firstColumn; i < endColumn; i++) {
colName.setLength(0);
int cnum = i;
do {
colName.insert(0, (char) ('A' + cnum % 26));
cnum /= 26;
} while (cnum > 0);
out.format(" %s %n", COL_HEAD_CLASS, colName);
}
out.format(" %n");
out.format("%n");
}
private void printSheetContent(Sheet sheet) {
printColumnHeads();
out.format("%n");
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
Row row = rows.next();
out.format(" %n");
out.format(" %d %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; //never null
if (content.isEmpty()) {
content = " ";
}
}
}
out.format(" %s %n", styleName(style),
attrs, content);
}
out.format(" %n");
}
out.format("
%n");
}
private String tagStyle(Cell cell, CellStyle style) {
if (style.getAlignment() == HorizontalAlignment.GENERAL) {
switch (ultimateCellType(cell)) {
case STRING:
return "style=\"text-align: left;\"";
case BOOLEAN:
case ERROR:
return "style=\"text-align: center;\"";
case NUMERIC:
default:
// "right" is the default
break;
}
}
return "";
}
}