Java tutorial
/* * XlsToHtml.java 2010-11-12 * * Copyright 2010 Guangzhou Donghong Software Technology Inc. * Licensed under the www.jxstar.org */ package org.jxstar.report.util; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.util.CellRangeAddress; import org.jxstar.report.ReportException; import org.jxstar.util.factory.FactoryUtil; import org.jxstar.util.log.Log; import org.jxstar.util.resource.JsMessage; /** * ?xlshtml * * @author TonyTan * @version 1.0, 2010-11-12 */ public class XlsToHtml { private Log _log = Log.getInstance(); //?html table id private static final String PARSERTABLEID = "xls_parser_table"; //xls? private static final String EMPTYCOLOR = "DBDBDB"; //?33 private static final int EMPTY_ROWNUM = 3; private static final int EMPTY_COLNUM = 3; //??? private static Map<String, String> _mpCss = FactoryUtil.newMap(); public String parserXls(String fileName) throws ReportException { // HSSFWorkbook hssfWB = ReportXlsUtil.readWorkBook(fileName); if (hssfWB == null) {//"?{0}??" throw new ReportException(JsMessage.getValue("xlstohtml.hint01"), fileName); } HSSFSheet sheet = hssfWB.getSheetAt(0); //???0 int lastRowNum = sheet.getLastRowNum(); if (lastRowNum == 0) { _log.showDebug("xls file row num is 0!!"); return ""; } //?xls?tablehtml StringBuilder sbTable = new StringBuilder(); sbTable.append("<table id='" + PARSERTABLEID + "' class='xls_table' >\n"); //? List<Integer> lsemp = FactoryUtil.newList(); //?1 int hasnum = 0, tableColNum = 0; //??1IE1? //table-layout:fixed;??????1?? List<Integer> lswidth = FactoryUtil.newList(); //?? for (int i = 0; i <= lastRowNum; i++) { HSSFRow row = sheet.getRow(i); if (row == null) {//?? lsemp.add(i); sbTable.append("<tr height='22px' >\n{EMPTY_LINE}</tr>\n"); continue; } //??? int lastCellNum = row.getLastCellNum(); //cells-1? if (lastCellNum <= 0) { lsemp.add(i); sbTable.append("<tr height='22px' >\n{EMPTY_LINE}</tr>\n"); continue; } else { //? if (hasnum == 0) tableColNum = lastCellNum + EMPTY_COLNUM; hasnum++; } //tr sbTable.append("<tr height='" + getHeightPixel(row.getHeightInPoints()) + "px' >\n"); //_log.showDebug("row=" + i + "; nums=" + cells); for (int j = 0; j < tableColNum; j++) { HSSFCell cell = null; if (j < lastCellNum) cell = row.getCell(j); //?ID String tdid = i + "," + j; // if (cell == null) { String ls = ""; if (hasnum == 1) { //?30px int width = (j < lastCellNum) ? 10 : 30; ls = " style='width:" + width + "px;'"; lswidth.add(width); } sbTable.append("\t<td id='" + tdid + "' class='xls_emp'" + ls + " > </td>\n"); continue; } //td? String style = getCellStyle(cell); //???cssName String cssName = getTdCss(style); //?td String value = getCellValue(cell); if (value == null || value.length() == 0) { value = " "; } else { value = value.replaceAll("\\s", " "); } //1????? if (hasnum == 1) { // int colw = getWidthPixel(sheet.getColumnWidth(j)); lswidth.add(colw); //td sbTable.append("\t<td id='" + tdid + "' class='" + cssName + "' style='width:" + colw + "px;' >" + value + "</td>\n"); } else { sbTable.append("\t<td id='" + tdid + "' class='" + cssName + "' >" + value + "</td>\n"); } } sbTable.append("</tr>\n"); } //_log.showDebug(sbTable.toString()); //?? for (int i = 1; i <= EMPTY_ROWNUM; i++) { lsemp.add(lastRowNum + i); //tr sbTable.append("<tr height='22px' >\n"); sbTable.append("{EMPTY_LINE}"); sbTable.append("</tr>\n"); } //cells-1? if (tableColNum > 0 && lsemp.size() > 0) { sbTable = fillEmptyLine(sbTable, tableColNum, lsemp, lswidth); lsemp.clear(); } sbTable.append("</table>\n"); //?html StringBuilder sbHtml = new StringBuilder(); sbHtml.append("<html>\n<body style='margin:1px;'>\n"); sbHtml.append(getCssStyle()); sbHtml.append(sbTable); //??? sbHtml.append(mergedRegion(sheet)); //html sbHtml.append("</body>\n</html>\n"); //_log.showDebug(sbHtml.toString()); return sbHtml.toString(); } /** * ?html * @param sbHtml -- html * @param cells -- ?? * @param lsemp -- ? * @param lswidth -- ?0? * @return */ private StringBuilder fillEmptyLine(StringBuilder sbHtml, int cells, List<Integer> lsemp, List<Integer> lswidth) { String oldHtml = sbHtml.toString(); //_log.showDebug("lsemp=" + lsemp.toString()); _log.showDebug("lswidth=" + lswidth.toString()); //_log.showDebug("html old=" + sbHtml.toString()); for (int i = 0; i < lsemp.size(); i++) { StringBuilder sbEmpty = new StringBuilder(); int row = lsemp.get(i); for (int j = 0; j < cells; j++) { //?ID String tdid = row + "," + j; //0? String width = ""; if (row == 0 && lswidth.size() > 0) { width = "style='width:" + lswidth.get(j) + "px;' "; } sbEmpty.append("\t<td id='" + tdid + "' class='xls_emp' " + width + "> </td>\n"); } //_log.showDebug("html empty=" + sbEmpty.toString()); oldHtml = oldHtml.replaceFirst("\\{EMPTY_LINE\\}", sbEmpty.toString()); } //_log.showDebug("html new=" + oldHtml.toLowerCase()); return new StringBuilder(oldHtml); } /** * ??? * @param sheet -- * @return */ private String mergedRegion(HSSFSheet sheet) { //??? int mergnum = sheet.getNumMergedRegions(); _log.showDebug("xls file merg cell num is: " + mergnum); if (mergnum <= 0) return ""; StringBuilder sbJs = new StringBuilder(); sbJs.append("<script>\n"); sbJs.append("var xlsmerged = function(){\n"); //???? StringBuilder sbMergpos = new StringBuilder(); //???? for (int i = 0; i < mergnum; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int y1 = range.getFirstColumn(); int x1 = range.getFirstRow(); int y2 = range.getLastColumn(); int x2 = range.getLastRow(); String curid = x1 + "," + y1; //? int rowspan = x2 - x1 + 1; int colspan = y2 - y1 + 1; sbJs.append("\tvar curdom = document.getElementById('" + curid + "');\n"); sbJs.append("\tcurdom.style.width = '';\n"); sbJs.append("\tcurdom.rowSpan = " + rowspan + ";\n"); sbJs.append("\tcurdom.colSpan = " + colspan + ";\n"); //??cell?? for (int j = x1; j <= x2; j++) { for (int k = y1; k <= y2; k++) { if (j == x1 && k == y1) continue; sbMergpos.append('\'').append(j).append(',').append(k).append("',"); } } _log.showDebug("xls file merg cell range:" + x1 + "," + y1 + "-" + x2 + "," + y2); } String spos = sbMergpos.substring(0, sbMergpos.length() - 1); sbJs.append("\tvar mergpos = [" + spos + "];\n"); sbJs.append("\tfor (var i = 0, n = mergpos.length; i < n; i++) {\n"); sbJs.append("\t\tdocument.getElementById(mergpos[i]).style.display = 'none';\n"); sbJs.append("\t}\n"); sbJs.append("};\nxlsmerged();\n"); sbJs.append("</script>\n"); return sbJs.toString(); } /** * ?xls?html? * @param cell -- xls? * @return */ private String getCellStyle(HSSFCell cell) { HSSFCellStyle style = cell.getCellStyle(); HSSFSheet sheet = cell.getSheet(); String value = getCellValue(cell); //? StringBuilder sbStyle = new StringBuilder(); //?? sbStyle.append(getBorderStyle(style)); //??? sbStyle.append(getAlignStyle(style)); //?? HSSFFont font = style.getFont(sheet.getWorkbook()); sbStyle.append(getFontStyle(font)); //cell if (value == null || value.length() == 0) { sbStyle.append("color:red;"); } return sbStyle.toString(); } /** * ?? * @param cell * @return */ private String getCellValue(HSSFCell cell) { String value = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { value = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { value = Double.toString(cell.getNumericCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { value = Boolean.toString(cell.getBooleanCellValue()); } else { value = ""; } if (value != null) { value = value.trim(); } else { value = ""; } return value; } /** * ?? * @param style -- xls? * @return */ private String getFontStyle(HSSFFont font) { StringBuilder sbStyle = new StringBuilder(); //??? sbStyle.append("word-break:keep-all;"); sbStyle.append("font-family:" + font.getFontName() + ";"); sbStyle.append("font-size:" + font.getFontHeightInPoints() + "pt;"); short bold = font.getBoldweight(); if (bold == Font.BOLDWEIGHT_BOLD) { sbStyle.append("font-weight:bold;"); } return sbStyle.toString(); } /** * ?? * @param style -- xls? * @return */ private String getBorderStyle(HSSFCellStyle style) { StringBuilder sbStyle = new StringBuilder(); //?cell short lw = style.getBorderLeft(); String lc = "000"; if (lw == 0) { lw = 1; lc = EMPTYCOLOR; } sbStyle.append("border-left:" + lw + "px solid #" + lc + ";"); short tw = style.getBorderTop(); String tc = "000"; if (tw == 0) { tw = 1; tc = EMPTYCOLOR; } sbStyle.append("border-top:" + tw + "px solid #" + tc + ";"); //?? if (lc.equals(EMPTYCOLOR) && tc.equals(EMPTYCOLOR)) { return sbStyle.toString(); } short rw = style.getBorderRight(); String rc = "000"; if (rw == 0) { rw = 1; rc = EMPTYCOLOR; } sbStyle.append("border-right:" + rw + "px solid #" + rc + ";"); short bw = style.getBorderBottom(); String bc = "000"; if (bw == 0) { bw = 1; bc = EMPTYCOLOR; } sbStyle.append("border-bottom:" + bw + "px solid #" + bc + ";"); return sbStyle.toString(); } /** * ??? * @param style -- xls? * @return */ private String getAlignStyle(HSSFCellStyle style) { //??? short align = style.getAlignment(); String strAlign = "text-align:left;"; if (align == CellStyle.ALIGN_CENTER) { strAlign = "text-align:center;"; } else if (align == CellStyle.ALIGN_RIGHT) { strAlign = "text-align:right;"; } //??? short valign = style.getVerticalAlignment(); String strVAlign = "vertical-align:bottom;"; if (valign == CellStyle.VERTICAL_CENTER) { strVAlign = "vertical-align:middle;"; } else if (valign == CellStyle.VERTICAL_TOP) { strVAlign = "vertical-align:top;"; } return strAlign + strVAlign; } /** * ?? * @return */ private String getCssStyle() { if (_mpCss.isEmpty()) return ""; StringBuilder sbStyle = new StringBuilder(); sbStyle.append("\n<style type='text/css'>\n"); //?main.css //sbStyle.append("\t.xls_table {border-collapse:collapse;table-layout:fixed;border:1px solid #"+EMPTYCOLOR+";border-width:1 0 0 1px;cursor:pointer;}\n"); //? //sbStyle.append("\t.xls_emp {border:1px solid #"+EMPTYCOLOR+";border-width:0 1 1 0px;color:red;}\n"); Iterator<String> itr = _mpCss.keySet().iterator(); while (itr.hasNext()) { String key = itr.next(); String value = _mpCss.get(key); sbStyle.append("\t." + value + " {" + key + "}\n"); } sbStyle.append("</style>\n"); return sbStyle.toString(); } /** * ?td?????? * @param style -- ? * @return */ private String getTdCss(String style) { String css = ""; if (_mpCss.containsKey(style)) { return _mpCss.get(style); } else { css = "xls_td" + _mpCss.size(); _mpCss.put(style, css); } return css; } /** * xls?html???1??? * @param h -- xls * @return */ private int getHeightPixel(float h) { double pixels = 3.78; //?? double points = 2.83; //? //h*4/3 return (int) (h * pixels / points); } /** * xls?html???1??? * @param w -- xls * @return */ private int getWidthPixel(int w) { int factor = 256; int offset = 7; //w*11/400 return (int) w * offset / factor; } }