Java 使用 Apache POI 库将 Excel 电子表格转换为 HTML
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9740215/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-16 06:58:40 来源:igfitidea点击:
Convert Excel Spreadsheets to HTML using the Apache POI library
提问by user1162040
I am trying to use ExcelToHtmlConverter class to convert a word document in HTML, but the documentation is not clear.
我正在尝试使用 ExcelToHtmlConverter 类在 HTML 中转换 word 文档，但文档不清楚。
Does anyone have a sample program on how to load a excel document and convert it into html.
有没有人有关于如何加载excel文档并将其转换为html的示例程序。
回答by pap
Did you look at this?: http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/html/ToHtml.java
你看过这个吗？：http: //svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/html/ToHtml.java
/* ====================================================================
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.ss.examples.html;
import java.io.BufferedReader;
import java.io.Closeable;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.util.Formatter;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.
*/
public 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<HorizontalAlignment, String> 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<VerticalAlignment, String> VALIGN = mapFor(
VerticalAlignment.BOTTOM, "bottom",
VerticalAlignment.CENTER, "middle",
VerticalAlignment.TOP, "top");
private static final Map<BorderStyle, String> 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");
@SuppressWarnings({"unchecked"})
private static <K, V> Map<K, V> mapFor(Object... mapping) {
Map<K, V> map = new HashMap<K, V>();
for (int i = 0; i < mapping.length; i += 2) {
map.put((K) mapping[i], (V) mapping[i + 1]);
}
return map;
}
/**
* Creates a new converter 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 converter to HTML for the given workbook. If the path ends
* with "<tt>.xlsx</tt>" 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 converter 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 {
try {
Workbook wb = WorkbookFactory.create(in);
return create(wb, output);
} catch (InvalidFormatException e){
throw new IllegalArgumentException("Cannot create workbook from stream", e);
}
}
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;
}
ToHtml toHtml = create(args[0], new PrintWriter(new FileWriter(args[1])));
toHtml.setCompleteHTML(true);
toHtml.printPage();
}
public void setCompleteHTML(boolean completeHTML) {
this.completeHTML = completeHTML;
}
public void printPage() throws IOException {
try {
ensureOut();
if (completeHTML) {
out.format(
"<?xml version=\"1.0\" encoding=\"iso-8859-1\" ?>%n");
out.format("<html>%n");
out.format("<head>%n");
out.format("</head>%n");
out.format("<body>%n");
}
print();
if (completeHTML) {
out.format("</body>%n");
out.format("</html>%n");
}
} finally {
IOUtils.closeQuietly(out);
if (output instanceof Closeable) {
IOUtils.closeQuietly((Closeable) output);
}
}
}
public void print() {
printInlineStyle();
printSheets();
}
private void printInlineStyle() {
//out.format("<link href=\"excelStyle.css\" rel=\"stylesheet\" type=\"text/css\">%n");
out.format("<style type=\"text/css\">%n");
printStyles();
out.format("</style>%n");
}
private void ensureOut() {
if (out == null) {
out = new Formatter(output);
}
}
public void printStyles() {
ensureOut();
// 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);
}
}
}
}
}
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.getAlignmentEnum(), HALIGN);
styleOut("vertical-align", style.getVerticalAlignmentEnum(), VALIGN);
fontStyle(style);
borderStyles(style);
helper.colorStyles(style, out);
}
private void borderStyles(CellStyle style) {
styleOut("border-left", style.getBorderLeftEnum(), BORDER);
styleOut("border-right", style.getBorderRightEnum(), BORDER);
styleOut("border-top", style.getBorderTopEnum(), BORDER);
styleOut("border-bottom", style.getBorderBottomEnum(), 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((short) 0);
}
StringBuilder sb = new StringBuilder();
Formatter fmt = new Formatter(sb);
try {
fmt.format("style_%02x", style.getIndex());
return fmt.toString();
} finally {
fmt.close();
}
}
private <K> void styleOut(String attr, K key, Map<K, String> 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.getCellTypeEnum();
if (type == CellType.FORMULA) {
type = c.getCachedFormulaResultTypeEnum();
}
return type;
}
private void printSheets() {
ensureOut();
Sheet sheet = wb.getSheetAt(0);
printSheet(sheet);
}
public void printSheet(Sheet sheet) {
ensureOut();
out.format("<table class=%s>%n", DEFAULTS_CLASS);
printCols(sheet);
printSheetContent(sheet);
out.format("</table>%n");
}
private void printCols(Sheet sheet) {
out.format("<col/>%n");
ensureColumnBounds(sheet);
for (int i = firstColumn; i < endColumn; i++) {
out.format("<col/>%n");
}
}
private void ensureColumnBounds(Sheet sheet) {
if (gotBounds) {
return;
}
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;
}
private void printColumnHeads() {
out.format("<thead>%n");
out.format(" <tr class=%s>%n", COL_HEAD_CLASS);
out.format(" <th class=%s>◊</th>%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(" <th class=%s>%s</th>%n", COL_HEAD_CLASS, colName);
}
out.format(" </tr>%n");
out.format("</thead>%n");
}
private void printSheetContent(Sheet sheet) {
printColumnHeads();
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");
}
private String tagStyle(Cell cell, CellStyle style) {
if (style.getAlignmentEnum() == 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 "";
}
}
回答by Ahmed Hashem
Check http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/html/
检查http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/html/
You need to include the classes and style sheet in your project. You can refactor and change the packages names to your project package names.
您需要在项目中包含类和样式表。您可以重构包名称并将其更改为您的项目包名称。
Also you can change the styles in the CSS file "excelStyle.css" to change the colors of the output HTMLs.
您也可以更改 CSS 文件“excelStyle.css”中的样式以更改输出 HTML 的颜色。
回答by o2tha1
I'm leaving this example using ExcelToHtmlConverter()here because I couldn't find one anywhere when I was searching for the same thing.
我在ExcelToHtmlConverter()这里使用这个例子是因为我在搜索同样的东西时找不到任何地方。
HSSFWorkbook excelDoc = ExcelToHtmlUtils.loadXls(inputDocument);
ExcelToHtmlConverter converter = new ExcelToHtmlConverter(
DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument()
);
converter.processWorkbook(excelDoc);
Document htmlDoc = converter.getDocument();
ByteArrayOutputStream out = new ByteArrayOutputStream();
DOMSource domSource = new DOMSource(htmlDoc);
StreamResult streamResult = new StreamResult(out);
TransformerFactory transfFactory = TransformerFactory.newInstance();
Transformer serializer = transfFactory.newTransformer();
serializer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
serializer.setOutputProperty(OutputKeys.INDENT, "yes");
serializer.setOutputProperty(OutputKeys.METHOD, "html");
serializer.transform(domSource, streamResult);
out.close();
String result = new String(out.toByteArray());
Hope this helps some of you.
希望这对你们中的一些人有所帮助。
回答by Miller Cy Chan
Required jars:
poi-3.17.jar
poi-examples-3.17.jar
poi-excelant-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.15.0.jar
dom4j-1.6.1.jar
poi-scratchpad-3.17.jar
xmlbeans-2.6.0.jar
所需的 jars：
poi-3.17.jar
poi-examples-3.17.jar
poi-excelant-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.15.0.jar
dom4j-1.6.1.jar
poi-scratchpad -3.17.jar
xmlbeans-2.6.0.jar
/**
* Excel to HTML
*
*/
public static void excel2Html(String filename, String htmlid, String ctxPath) throws Exception{
InputStream input=new FileInputStream(filename);
HSSFWorkbook excelBook=new HSSFWorkbook(input);
ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
excelToHtmlConverter.processWorkbook(excelBook);
List pics = excelBook.getAllPictures();
if (pics != null) {
for (int i = 0; i < pics.size(); i++) {
Picture pic = (Picture) pics.get (i);
try {
pic.writeImageContent(new FileOutputStream (ctxPath + pic.suggestFullFileName() ) );
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
}
Document htmlDocument = excelToHtmlConverter.getDocument();
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
DOMSource domSource = new DOMSource (htmlDocument);
StreamResult streamResult = new StreamResult(outStream);
TransformerFactory tf = TransformerFactory.newInstance();
Transformer serializer = tf.newTransformer();
serializer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
serializer.setOutputProperty(OutputKeys.INDENT, "yes");
serializer.setOutputProperty(OutputKeys.METHOD, "html");
serializer.transform (domSource, streamResult);
outStream.close();
OutputStream os = new FileOutputStream(new File(ctxPath, htmlid+".html"));
final PrintStream printStream = new PrintStream(os);
printStream.print(new String(outStream.toByteArray()));
printStream.close();
}
