HTML实例

package com.steerinfo.dil.util; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.IOException; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.HashMap; import java.util.Map; /** * POIExcelToHtml 文件转换： * * @author generator * @version 1.0-SNAPSHORT 2021-08-09 18:06 * 类描述 * 修订历史： * 日期：2021-08-09 * 作者：shadow * 参考： * 描述：Execl转HTML * @Copyright 湖南视拓信息技术股份有限公司. All rights reserved. * @see null */ public class POIExcelToHtml { public static String excelToHtml(InputStream is) { String htmlExcel = "预览文件失败"; try { Workbook wb = WorkbookFactory.create(is); if (wb instanceof XSSFWorkbook) { XSSFWorkbook xWb = (XSSFWorkbook) wb; htmlExcel = POIExcelToHtml.getExcelInfo(xWb); } else if (wb instanceof HSSFWorkbook) { HSSFWorkbook hWb = (HSSFWorkbook) wb; htmlExcel = POIExcelToHtml.getExcelInfo(hWb); } } catch (Exception e) { e.printStackTrace(); } finally { try { is.close(); } catch (IOException e) { e.printStackTrace(); } } return htmlExcel; } private static String getExcelInfo(Workbook wb) { StringBuffer sb = new StringBuffer(); int sheetCounts = wb.getNumberOfSheets(); // sb.append(" HTML实例 "); for (int i = 0; i < sheetCounts; i++) { Sheet sheet = wb.getSheetAt(i);// 获取第一个Sheet的内容 int lastRowNum = sheet.getLastRowNum(); Map map[] = getRowSpanColSpanMap(sheet); // sb.append("

"); sb.append(sheet.getSheetName()); sb.append(""); Row row = null; // 兼容 Cell cell = null; // 兼容 for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { row = sheet.getRow(rowNum); if (row == null) { sb.append(""); continue; } sb.append(""); int lastColNum = row.getLastCellNum(); for (int colNum = 0; colNum < lastColNum; colNum++) { cell = row.getCell(colNum); if (cell == null) { // 特殊情况空白的单元格会返回null sb.append(""); continue; } String stringValue = getCellValue(cell);//```````````` if (map[0].containsKey(rowNum + "," + colNum)) { String pointString = map[0].get(rowNum + "," + colNum); map[0].remove(rowNum + "," + colNum); int bottomeRow = Integer.valueOf(pointString.split(",")[0]); int bottomeCol = Integer.valueOf(pointString.split(",")[1]); int rowSpan = bottomeRow - rowNum + 1; int colSpan = bottomeCol - colNum + 1; sb.append(""); } sb.append(""); } sb.append("


	"); if (stringValue == null \|\| "".equals(stringValue.trim())) { sb.append(" "); } else { // 将ascii码为160的空格转换为html下的空格（） sb.append(stringValue.replace(String.valueOf((char) 160), " ")); } sb.append("

"); } // sb.append(" "); return sb.toString(); } private static Map[] getRowSpanColSpanMap(Sheet sheet) { Map map0 = new HashMap(); Map map1 = new HashMap(); int mergedNum = sheet.getNumMergedRegions(); CellRangeAddress range = null; for (int i = 0; i < mergedNum; i++) { range = sheet.getMergedRegion(i); int topRow = range.getFirstRow(); int topCol = range.getFirstColumn(); int bottomRow = range.getLastRow(); int bottomCol = range.getLastColumn(); map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol); // System.out.println(topRow + "," + topCol + "," + bottomRow + "," // + bottomCol); int tempRow = topRow; while (tempRow <= bottomRow) { int tempCol = topCol; while (tempCol <= bottomCol) { map1.put(tempRow + "," + tempCol, ""); tempCol++; } tempRow++; } map1.remove(topRow + "," + topCol); } Map[] map = {map0, map1}; return map; } /** * 200 * 获取表格单元格Cell内容 201 * @param cell 202 * @return 203 */ private static String getCellValue(Cell cell) { String result = new String(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC:// 数字类型 if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式 SimpleDateFormat sdf = null; if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("hh:mm")) { sdf = new SimpleDateFormat("HH:mm"); } else if (cell.getCellStyle().getDataFormat() == HSSFDataFormat .getBuiltinFormat("yyyy-MM-dd HH:mm:ss")) {// 日期 sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); } else { sdf = new SimpleDateFormat("yyyy-MM-dd"); } Date date = cell.getDateCellValue(); result = sdf.format(date); } else if (cell.getCellStyle().getDataFormat() == 58) { // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58) SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); double value = cell.getNumericCellValue(); Date date = DateUtil.getJavaDate(value); result = sdf.format(date); } else { double value = cell.getNumericCellValue(); CellStyle style = cell.getCellStyle(); DecimalFormat format = new DecimalFormat(); String temp = style.getDataFormatString(); // 单元格设置成常规 if (temp.equals("General")) { format.applyPattern("#"); } result = format.format(value); } break; case Cell.CELL_TYPE_STRING:// String类型 result = cell.getRichStringCellValue().toString(); break; case Cell.CELL_TYPE_BLANK: result = ""; break; default: result = ""; break; } return result; } /** * 251 * 处理表格样式 252 * @param wb 253 * @param sheet 254 * @param cell 255 * * @param sb 256 */ private static void dealExcelStyle(Workbook wb, Sheet sheet, Cell cell, StringBuffer sb) { CellStyle cellStyle = cell.getCellStyle(); if (cellStyle != null) { //short alignment = cellStyle.getAlignment();旧写法获取枚举数字；新写法获取枚举 sb.append("align='" + convertAlignToHtml(cellStyle.getAlignmentEnum()) + "' ");// 单元格内容的水平对齐方式 //short verticalAlignment = cellStyle.getVerticalAlignment(); //short verticalAlignment = cellStyle.getVerticalAlignmentEnum().getCode(); sb.append("valign='" + convertVerticalAlignToHtml(cellStyle.getVerticalAlignmentEnum()) + "' ");// 单元格中内容的垂直排列方式 if (wb instanceof XSSFWorkbook) { XSSFFont xf = ((XSSFCellStyle) cellStyle).getFont(); Boolean boldWeight = xf.getBold(); //short boldWeight = xf.getBoldweight(); String fontfamily = xf.getFontName(); int underline = xf.getUnderline(); boolean Italic = xf.getItalic(); sb.append("style='"); if (underline >= 1) { sb.append("text-decoration:underline;"); // 字体型号 } if (Italic) { sb.append("font-style: italic;"); // 字体型号 } sb.append("font-family:" + fontfamily + ";"); // 字体型号 sb.append("font-weight:" + (boldWeight ? "bold" : "100") + ";"); // 字体加粗 sb.append("font-size: " + xf.getFontHeight() / 2 + "%;"); // 字体大小 int columnWidth = sheet.getColumnWidth(cell.getColumnIndex()); sb.append("width:" + columnWidth + "px;"); XSSFColor xc = xf.getXSSFColor(); if (xc != null && !"".equals(xc)) { sb.append("color:#" + xc.getARGBHex().substring(2) + ";"); // 字体颜色 } XSSFColor bgColor = (XSSFColor) cellStyle.getFillForegroundColorColor(); if (bgColor != null && !"".equals(bgColor)) { sb.append("background-color:#" + bgColor.getARGBHex().substring(2) + ";"); // 背景颜色 } sb.append(getBorderStyle(0, cellStyle.getBorderTop(), ((XSSFCellStyle) cellStyle).getTopBorderXSSFColor())); sb.append(getBorderStyle(1, cellStyle.getBorderRight(), ((XSSFCellStyle) cellStyle).getRightBorderXSSFColor())); sb.append(getBorderStyle(2, cellStyle.getBorderBottom(), ((XSSFCellStyle) cellStyle).getBottomBorderXSSFColor())); sb.append(getBorderStyle(3, cellStyle.getBorderLeft(), ((XSSFCellStyle) cellStyle).getLeftBorderXSSFColor())); } else if (wb instanceof HSSFWorkbook) { HSSFFont hf = ((HSSFCellStyle) cellStyle).getFont(wb); short fontColor = hf.getColor(); Boolean boldWeight = hf.getBold(); //short boldWeight = hf.getBoldweight(); String fontfamily = hf.getFontName(); int underline = hf.getUnderline(); boolean Italic = hf.getItalic(); sb.append("style='"); if (underline >= 1) { // 字体型号 sb.append("text-decoration:underline;"); } if (Italic) { // 字体型号 sb.append("font-style: italic;"); } // 类HSSFPalette用于求的颜色的国际标准形式 HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); HSSFColor hc = palette.getColor(fontColor); // 字体加粗 sb.append("font-weight:" + (boldWeight ? "bold" : "100") + ";"); // 字体型号 sb.append("font-family:" + fontfamily + ";"); // 字体大小 sb.append("font-size: " + hf.getFontHeight() / 2 + "%;"); String fontColorStr = convertToStardColor(hc); // 字体颜色 if (fontColorStr != null && !"".equals(fontColorStr.trim())) { sb.append("color:" + fontColorStr + ";"); } int columnWidth = sheet.getColumnWidth(cell.getColumnIndex()); sb.append("width:" + columnWidth + "px;"); short bgColor = cellStyle.getFillForegroundColor(); //short bgColor = cellStyle.getFillBackgroundColor(); hc = palette.getColor(bgColor); String bgColorStr = convertToStardColor(hc); if (bgColorStr != null && !"".equals(bgColorStr.trim())) { // 背景颜色 sb.append("background-color:" + bgColorStr + ";"); } /** * 旧版本 cellStyle.getBorderTop() * 新版本 cellStyle.getBorderTopEnum() */ sb.append(getBorderStyle(palette, 0, cellStyle.getBorderTop(), cellStyle.getTopBorderColor())); sb.append(getBorderStyle(palette, 1, cellStyle.getBorderRight(), cellStyle.getRightBorderColor())); sb.append(getBorderStyle(palette, 3, cellStyle.getBorderLeft(), cellStyle.getLeftBorderColor())); sb.append(getBorderStyle(palette, 2, cellStyle.getBorderBottom(), cellStyle.getBottomBorderColor())); } sb.append("' "); } } /** * 330 * 单元格内容的水平对齐方式 331 * @param alignment 332 * @return 333 * * @param alignment */ private static String convertAlignToHtml(HorizontalAlignment alignment) { String align = "left"; /** * CellStyle.ALIGN_CENTER:旧写法 * HorizontalAlignment.LEFT:新写法 */ switch (alignment) { case LEFT: align = "left"; break; case CENTER: align = "center"; break; case RIGHT: align = "right"; break; default: break; } return align; } /** * 354 * 单元格中内容的垂直排列方式 355 * @param verticalAlignment 356 * @return 357 * * @param verticalAlignment */ private static String convertVerticalAlignToHtml(VerticalAlignment verticalAlignment) { /** * 旧写法CellStyle.VERTICAL_BOTTOM: * 新写法VerticalAlignment.BOTTOM: */ String valign = "middle"; switch (verticalAlignment) { case BOTTOM: valign = "bottom"; break; case CENTER: valign = "center"; break; case TOP: valign = "top"; break; default: break; } return valign; } private static String convertToStardColor(HSSFColor hc) { /** * 颜色定义变化 * 旧版本 : HSSFColor.BLACK.index * 新版本 : IndexedColors.BLACK.index */ StringBuffer sb = new StringBuffer(""); if (hc != null) { if (IndexedColors.AUTOMATIC.index == hc.getIndex()) { return null; } sb.append("#"); for (int i = 0; i < hc.getTriplet().length; i++) { sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i]))); } } return sb.toString(); } private static String fillWithZero(String str) { if (str != null && str.length() < 2) { return "0" + str; } return str; } static String[] bordesr = {"border-top:", "border-right:", "border-bottom:", "border-left:"}; static String[] borderStyles = {"solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid", "solid", "solid", "solid", "solid"}; private static String getBorderStyle(HSSFPalette palette, int b, short s, short t) { if (s == 0) { return bordesr[b] + borderStyles[s] + "#d0d7e5 1px;"; } String borderColorStr = convertToStardColor(palette.getColor(t)); borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000" : borderColorStr; return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;"; } private static String getBorderStyle(int b, short s, XSSFColor xc) { if (s == 0) { return bordesr[b] + borderStyles[s] + "#d0d7e5 1px;"; } if (xc != null && !"".equals(xc)) { // t.getARGBHex(); String borderColorStr = xc.getARGBHex(); borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000" : borderColorStr.substring(2); return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;"; } return ""; } }