위 소스를 수정함.
package ToHtml;
import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_CENTER;
import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_CENTER_SELECTION;
import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_FILL;
import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_GENERAL;
import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_JUSTIFY;
import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_LEFT;
import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_RIGHT;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_DASHED;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_DASH_DOT;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_DASH_DOT_DOT;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_DOTTED;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_DOUBLE;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_HAIR;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_MEDIUM;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_MEDIUM_DASHED;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_MEDIUM_DASH_DOT;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_MEDIUM_DASH_DOT_DOT;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_NONE;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_SLANTED_DASH_DOT;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_THICK;
import static org.apache.poi.ss.usermodel.CellStyle.BORDER_THIN;
import static org.apache.poi.ss.usermodel.CellStyle.VERTICAL_BOTTOM;
import static org.apache.poi.ss.usermodel.CellStyle.VERTICAL_CENTER;
import static org.apache.poi.ss.usermodel.CellStyle.VERTICAL_TOP;
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.ArrayList;
import java.util.Formatter;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.examples.html.HSSFHtmlHelper;
import org.apache.poi.ss.examples.html.HtmlHelper;
import org.apache.poi.ss.examples.html.XSSFHtmlHelper;
import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.format.CellFormatResult;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* This example shows how to display a spreadsheet in HTML using the classes for
* spreadsheet display.
*
* @author Ken Arnold, Industrious Media LLC
*/
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 List mergedMap = new ArrayList();
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 ALIGN = mapFor(ALIGN_LEFT, "left",
ALIGN_CENTER, "center", ALIGN_RIGHT, "right", ALIGN_FILL, "left",
ALIGN_JUSTIFY, "left", ALIGN_CENTER_SELECTION, "center");
private static final Map VERTICAL_ALIGN = mapFor(
VERTICAL_BOTTOM, "bottom", VERTICAL_CENTER, "middle", VERTICAL_TOP,
"top");
private static final Map BORDER = mapFor(BORDER_DASH_DOT,
"dashed 1pt", BORDER_DASH_DOT_DOT, "dashed 1pt", BORDER_DASHED,
"dashed 1pt", BORDER_DOTTED, "dotted 1pt", BORDER_DOUBLE,
"double 3pt", BORDER_HAIR, "solid 1px", BORDER_MEDIUM, "solid 2pt",
BORDER_MEDIUM_DASH_DOT, "dashed 2pt", BORDER_MEDIUM_DASH_DOT_DOT,
"dashed 2pt", BORDER_MEDIUM_DASHED, "dashed 2pt", BORDER_NONE,
"none", BORDER_SLANTED_DASH_DOT, "dashed 2pt", BORDER_THICK,
"solid 3pt", BORDER_THIN, "dashed 1pt");
@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 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 ".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 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((XSSFWorkbook) wb);
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(
"%n");
out.format("%n");
out.format("%n");
out.format("
%n");
out.format("%n");
}
print();
if (completeHTML) {
out.format("
%n");
out.format("
%n");
}
} finally {
if (out != null)
out.close();
if (output instanceof Closeable) {
Closeable closeable = (Closeable) output;
closeable.close();
}
}
}
public void print() {
printInlineStyle();
printSheets();
}
private void printInlineStyle() {
//out.format("%n");
out.format("%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 {
if (in != null) {
try {
in.close();
} catch (IOException e) {
//noinspection ThrowFromFinallyBlock
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(), ALIGN);
styleOut("vertical-align", style.getAlignment(), VERTICAL_ALIGN);
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.getBoldweight() >= HSSFFont.BOLDWEIGHT_NORMAL)
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);
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 int ultimateCellType(Cell c) {
int type = c.getCellType();
if (type == Cell.CELL_TYPE_FORMULA)
type = c.getCachedFormulaResultType();
return type;
}
private void printSheets() {
ensureOut();
Sheet sheet = wb.getSheetAt(0);
printSheet(sheet);
}
public void printSheet(Sheet sheet) {
ensureOut();
out.format("%n", DEFAULTS_CLASS);
printCols(sheet);
printSheetContent(sheet);
out.format("
%n");
}
private void printCols(Sheet sheet) {
out.format("
ensureColumnBounds(sheet);
for (int i = firstColumn; i < endColumn; i++) {
out.format("
}
}
private void ensureColumnBounds(Sheet sheet) {
if (gotBounds)
return;
Iterator 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();
int mergeCount = sheet.getNumMergedRegions();
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 = "";
String colAttr = "";
String rowAttr = "";
int mergeCol = 0;
int mergeRow = 0;
boolean isWillRemoveCell= false;
CellStyle style = null;
if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
Cell cell = row.getCell(i);
if (cell != null) {
for (int j=0;j
%n");
CellRangeAddress address = sheet.getMergedRegion(j);
mergeCol = address.getLastColumn() - address.getFirstColumn();
mergeRow = address.getLastRow() - address.getFirstRow();
boolean testcase = address.isInRange(cell.getRowIndex(), cell.getColumnIndex());
if( testcase == true){
if ( !mergedMap.contains(address.formatAsString())){
mergedMap.add(address.formatAsString());
System.out.println(address.formatAsString()+" / "+cell.getStringCellValue()+ " / "+mergeCol+","+mergeRow);
colAttr = mergeCol >0 ? ("colspan="+(mergeCol+1)) : "";
rowAttr = mergeRow >0 ? ("rowspan="+(mergeRow+1)) : "";
}else{
isWillRemoveCell= true;
}
}
}
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 = " ";
}
}
if(isWillRemoveCell!= true){
out.format(" %s %n", styleName(style),
attrs, colAttr, rowAttr, content);
}
colAttr = "";
rowAttr = "";
mergeCol = 0;
mergeRow = 0;
isWillRemoveCell= false;
}
out.format("
}
out.format("
%n");
}
private String tagStyle(Cell cell, CellStyle style) {
if (style.getAlignment() == ALIGN_GENERAL) {
switch (ultimateCellType(cell)) {
case HSSFCell.CELL_TYPE_STRING:
return "style=\"text-align: left;\"";
case HSSFCell.CELL_TYPE_BOOLEAN:
case HSSFCell.CELL_TYPE_ERROR:
return "style=\"text-align: center;\"";
case HSSFCell.CELL_TYPE_NUMERIC:
default:
// "right" is the default
break;
}
}
return "";
}
}
댓글