简介
- 使用poi3.15进行Excel读取，比较老旧了，历史项目遗留问题，不过本文只是记录本次优化的一些思路。
- 程序会使用通用css、生成css、生成HTML文件完成Excel预览的预览功能
- 程序不会去读取边框样式，统一为1像素、细线、黑色（好吧，是我偷懒了，但也有为了减小生成文件大小的原因在里面）
- 程序只会读取文字内容，会忽略里面的公式，图片等
- 程序不会读取行高，通用样式为每个Excel设置了
padding - 工具类分为xls和xlsx两种
程序实现思路
- 将sheet页的一些样式好而功能分别编写好，分别为
excel-sheet.css和excel-sheet.js，无非是一些默认样式和切换sheet页的操作，也会进入jQuery.js（我懒不想写原生js罢了） - 将一些通用的样式写入
excel-sheet.css中，如表格默认样式、文字对其方式、字体样式等，命名尽可能的简单，我就是要压缩文件大小 - 由于单元格每列的样式都可能不同，所以该程序会将每个
td都添加一个class属性，通过不同的class来控制单元格样式，class的名字我会尽量从简 - 由于有些样式不可能是通用样式，比如字体大小、字体颜色、背景色，我会通过不同前缀+色号的方式生成样式表到
excel-table.css中，每个需要预览的Excel都会有一个自己的excel-table.css，列宽样式也是生成在其中。 - 为了解决每行读取到的列数不同，我这里会提前扫一遍，获取到最大列坐标
maxLastCellNum，通过获取到的最大列坐标生成每行内全部单元格的默认值集合List<Object> rowValues都为空字符串，后续会在读取单元格值时替换掉对应坐标的值 - 合并单元格的处理是单独处理的，将合并后的单元格的值替换成
td代码，如：<td rowspan="5" colspan="1">我被合并了</td>，并记录下坐标，当生成td时直接使用代码；将无效的值替换为指定字符串，在通过表格数据生成HTML时读取到这些字符串会忽略生成td，以保证可以正常显示合并单元格的样式
工具类型实现
ExcelToHtmlUtils
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.util.*;
public class ExcelToHtmlUtils {
private static final Logger logger = LoggerFactory.getLogger(ExcelToHtmlUtils.class);
private static final String INVALID_CELL = "ExcelToHtml_invalidCell";
private static final String CURLY_BRACES_LEFT = "{";
private static final String BRACKETS_RIGHT_AND_CURLY_BRACES_LEFT = "){";
private static final String CLASS_NAME_SHEET = ".sheet";
private static final String WIDTH_COLON = "width:";
private static final String CURLY_BRACES_RIGHT = "}";
private static final String PX_SEMICOLON = "px;";
private static final String FONT_SIZE = "font-size:";
private static final String CLASS_SELECTOR = ".";
private static final String BACKGROUND_COLOR_CLASS_PREFIX = "bc";
private static final String COLOR_CLASS_PREFIX = "c";
private static final String FONT_SIZE_CLASS_PREFIX = "fs";
private static final String TEXT_ALIGN_CENTER_CLASS = "tac";
private static final String TEXT_ALIGN_RIGHT_CLASS = "tar";
private static final String BOLD_CLASS = "bold";
private static final String ITALIC_CLASS = "italic";
private static final String STRIKEOUT_CLASS = "strikeout";
private static final String UNDERLINE_CLASS = "underline";
private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0");
public static String xlsToHtml(HttpServletRequest request, String excelPath, String excelName) {
String basePath = request.getContextPath() + "/";
String htmlPath = excelPath + File.separator + excelName + "_show" + File.separator;
String htmlName = excelName + ".html";
File htmlFilePath = new File(htmlPath);
if (!htmlFilePath.exists()) {
htmlFilePath.mkdirs();
}
Map<String, String> cssMap = new HashMap<>();
HSSFWorkbook hssfWorkbook = null;
InputStream hssfStream = null;
FileWriter cssFileWriter = null;
BufferedWriter htmlBufferedWriter = null;
try {
hssfStream = Files.newInputStream(Paths.get(excelPath, excelName + ".xls"));
hssfWorkbook = new HSSFWorkbook(hssfStream);
StringBuilder html = new StringBuilder("<!DOCTYPE html><html><head><meta charset='UTF-8'><title>");
StringBuilder style = new StringBuilder();
StringBuilder sheetTabHtml = new StringBuilder();
html.append(excelName).append(".xls");
html.append("</title>");
html.append("<link rel='stylesheet' type='text/css' href='");
html.append(basePath);
html.append("css/excel-sheet.css'/>");
html.append("<link rel='stylesheet' type='text/css' href='excel-table.css'/>");
html.append("<script src='");
html.append(basePath);
html.append("js/plugins/jquery/jquery.min.js'></script>");
html.append("<script src='");
html.append(basePath);
html.append("js/excel-sheet.js'></script>");
html.append("</head><body>");
for (int st = 0; st < hssfWorkbook.getNumberOfSheets(); st++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(st);
String sheetName = hssfSheet.getSheetName();
sheetTabHtml.append("<li ");
sheetTabHtml.append(st == 0 ? "class='current'" : "");
sheetTabHtml.append("sheet-filter='sheet");
sheetTabHtml.append(st + 1);
sheetTabHtml.append("'>");
sheetTabHtml.append(sheetName);
sheetTabHtml.append("</li>");
List<List<Object>> tableData = new ArrayList<>();
StringBuilder tableCssStyle = new StringBuilder();
List<List<List<String>>> tableRowCellClass = new ArrayList<>();
Map<Integer, Float> columnWidth = new HashMap<>();
short maxLastCellNum = 0;
for (Row row : hssfSheet) {
short lastCellNum = row.getLastCellNum();
maxLastCellNum = lastCellNum > maxLastCellNum ? lastCellNum : maxLastCellNum;
}
for (Row row : hssfSheet) {
List<List<String>> rowCellClass = new ArrayList<>();
List<Object> rowValues = new ArrayList<>();
for (int i = 0; i < maxLastCellNum; i++) {
rowValues.add("");
}
for (Cell cell : row) {
List<String> cellClass = new ArrayList<>();
CellStyle cellStyle = cell.getCellStyle();
int columnIndex = cell.getColumnIndex();
int columnWidthKey = columnIndex + 1;
float width = hssfSheet.getColumnWidthInPixels(columnIndex);
width = (width / 72) * 96;
Float colWidth = columnWidth.get(columnWidthKey);
if (colWidth == null || width > colWidth) {
columnWidth.put(columnWidthKey, width);
}
createCellStyle(cellClass, cellStyle);
if (cellStyle.getFillPatternEnum() == FillPatternType.SOLID_FOREGROUND) {
Color color = cellStyle.getFillForegroundColorColor();
HSSFColor hssfColor = HSSFColor.toHSSFColor(color);
if (hssfColor != null) {
short[] triplet = hssfColor.getTriplet();
String className = COLOR_CLASS_PREFIX + triplet[0] + triplet[1] + triplet[2];
cssMap.put(className, "background-color:rgb(" + triplet[0] + "," + triplet[1] + "," + triplet[2] + ");");
cellClass.add(className);
}
}
HSSFFont hssfFont = hssfWorkbook.getFontAt(cellStyle.getFontIndex());
addFontStyle(cssMap, hssfFont, cellClass);
HSSFColor hssfColor = hssfFont.getHSSFColor(hssfWorkbook);
if (hssfColor != null) {
short[] triplet = hssfColor.getTriplet();
if (triplet[0] != 0 || triplet[1] != 0 || triplet[2] != 0) {
String className = COLOR_CLASS_PREFIX + triplet[0] + triplet[1] + triplet[2];
cssMap.put(className, "color:rgb(" + triplet[0] + "," + triplet[1] + "," + triplet[2] + ");");
cellClass.add(className);
}
}
setValues(rowValues, cell);
rowCellClass.add(cellClass);
}
tableData.add(rowValues);
tableRowCellClass.add(rowCellClass);
}
List<String> mergedRegions = getMergedRegions(hssfSheet, tableData, tableRowCellClass);
boolean hasMerged = CollectionUtils.isNotEmpty(mergedRegions);
tableData = trimTableData(tableData, columnWidth);
appendColWidthCss(tableCssStyle, st, columnWidth);
style.append(tableCssStyle);
html.append("<div class='sheet sheet");
html.append(st + 1);
html.append(st == 0 ? " sheet-show" : "");
html.append("'>");
html.append(createTableHtml(tableData, tableRowCellClass, hasMerged, mergedRegions));
html.append("</div>");
}
html.append("<div class='sheet-tab'><button class='roll-left' disabled='disabled'>◀</button><ul>");
html.append(sheetTabHtml);
html.append("</ul><button class='roll-right'>▶</button></div>");
html.append("</body></html>");
htmlBufferedWriter = new BufferedWriter(new OutputStreamWriter(Files.newOutputStream(Paths.get(htmlPath + htmlName)), StandardCharsets.UTF_8));
htmlBufferedWriter.write(html.toString());
appendCss(style, cssMap);
cssFileWriter = new FileWriter(htmlPath + "excel-table.css");
cssFileWriter.write(style.toString());
return excelName + "_show/" + htmlName;
} catch (IOException e) {
logger.error("附件解析Excel异常", e);
} finally {
if (hssfWorkbook != null) {
try {
hssfWorkbook.close();
} catch (IOException e) {
logger.error("附件解析Excel：关闭 hssfWorkbook 异常", e);
}
}
if (hssfStream != null) {
try {
hssfStream.close();
} catch (IOException e) {
logger.error("附件解析Excel：关闭 hssfStream 异常", e);
}
}
closeFileWriter(htmlBufferedWriter, cssFileWriter);
}
return "";
}
public static String xlsxToHtml(HttpServletRequest request, String excelPath, String excelName) {
String basePath = request.getContextPath() + "/";
String htmlPath = excelPath + File.separator + excelName + "_show" + File.separator;
String htmlName = excelName + ".html";
File htmlFilePath = new File(htmlPath);
if (!htmlFilePath.exists()) {
htmlFilePath.mkdirs();
}
Map<String, String> cssMap = new HashMap<>();
InputStream xssfStream = null;
XSSFWorkbook xssfWorkbook = null;
FileWriter cssFileWriter = null;
BufferedWriter htmlBufferedWriter = null;
try {
xssfStream = Files.newInputStream(Paths.get(excelPath, excelName + ".xlsx"));
xssfWorkbook = new XSSFWorkbook(xssfStream);
StringBuilder html = new StringBuilder("<!DOCTYPE html><html><head><meta charset='UTF-8'><title>");
StringBuilder style = new StringBuilder();
html.append(excelName).append(".xlsx");
html.append("</title>");
html.append("<link rel='stylesheet' type='text/css' href='");
html.append(basePath);
html.append("css/excel-sheet.css'/>");
html.append("<link rel='stylesheet' type='text/css' href='excel-table.css'/>");
html.append("<script src='");
html.append(basePath);
html.append("js/plugins/jquery/jquery.min.js'></script>");
html.append("<script src='");
html.append(basePath);
html.append("js/excel-sheet.js'></script>");
html.append("</head><body>");
StringBuilder sheetTabHtml = new StringBuilder();
for (int st = 0; st < xssfWorkbook.getNumberOfSheets(); st++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(st);
String sheetName = xssfSheet.getSheetName();
sheetTabHtml.append("<li ");
sheetTabHtml.append(st == 0 ? "class='current'" : "");
sheetTabHtml.append("sheet-filter='sheet");
sheetTabHtml.append(st + 1);
sheetTabHtml.append("'>");
sheetTabHtml.append(sheetName);
sheetTabHtml.append("</li>");
List<List<Object>> tableData = new ArrayList<>();
StringBuilder tableCssStyle = new StringBuilder();
List<List<List<String>>> tableRowCellClass = new ArrayList<>();
Map<Integer, Float> columnWidth = new HashMap<>();
short maxLastCellNum = 0;
for (Row row : xssfSheet) {
short lastCellNum = row.getLastCellNum();
maxLastCellNum = lastCellNum > maxLastCellNum ? lastCellNum : maxLastCellNum;
}
for (Row row : xssfSheet) {
List<List<String>> rowCellClass = new ArrayList<>();
List<Object> rowValues = new ArrayList<>();
for (int i = 0; i < maxLastCellNum; i++) {
rowValues.add("");
}
for (Cell cell : row) {
List<String> cellClass = new ArrayList<>();
CellStyle cellStyle = cell.getCellStyle();
int columnIndex = cell.getColumnIndex();
int columnWidthKey = columnIndex + 1;
float width = xssfSheet.getColumnWidthInPixels(columnIndex);
width = (width / 72) * 96;
Float colWidth = columnWidth.get(columnWidthKey);
if (colWidth == null || width > colWidth) {
columnWidth.put(columnWidthKey, width);
}
createCellStyle(cellClass, cellStyle);
if (cellStyle.getFillPatternEnum() == FillPatternType.SOLID_FOREGROUND) {
Color color = cellStyle.getFillForegroundColorColor();
XSSFColor xssfColor = XSSFColor.toXSSFColor(color);
if (xssfColor != null) {
String hex = xssfColor.getARGBHex().substring(2);
String className = BACKGROUND_COLOR_CLASS_PREFIX + hex;
cssMap.put(className, "background-color:#" + hex + ";");
cellClass.add(className);
}
}
XSSFFont xssfFont = xssfWorkbook.getFontAt(cellStyle.getFontIndex());
addFontStyle(cssMap, xssfFont, cellClass);
XSSFColor xssfColor = xssfFont.getXSSFColor();
if (xssfColor != null) {
String hex = xssfColor.getARGBHex().substring(2);
if (!"000000".equals(hex)) {
String className = COLOR_CLASS_PREFIX + hex;
cssMap.put(className, "color:#" + hex + ";");
cellClass.add(className);
}
}
setValues(rowValues, cell);
rowCellClass.add(cellClass);
}
tableData.add(rowValues);
tableRowCellClass.add(rowCellClass);
}
List<String> mergedRegions = getMergedRegions(xssfSheet, tableData, tableRowCellClass);
boolean hasMerged = CollectionUtils.isNotEmpty(mergedRegions);
tableData = trimTableData(tableData, columnWidth);
appendColWidthCss(tableCssStyle, st, columnWidth);
style.append(tableCssStyle);
html.append("<div class='sheet sheet");
html.append(st + 1);
html.append(st == 0 ? " sheet-show" : "");
html.append("'>");
html.append(createTableHtml(tableData, tableRowCellClass, hasMerged, mergedRegions));
html.append("</div>");
}
html.append("<div class='sheet-tab'><button class='roll-left' disabled='disabled'>◀</button><ul>");
html.append(sheetTabHtml);
html.append("</ul><button class='roll-right'>▶</button></div>");
html.append("</body></html>");
htmlBufferedWriter = new BufferedWriter(new OutputStreamWriter(Files.newOutputStream(Paths.get(htmlPath + htmlName)), StandardCharsets.UTF_8));
htmlBufferedWriter.write(html.toString());
appendCss(style, cssMap);
cssFileWriter = new FileWriter(htmlPath + "excel-table.css");
cssFileWriter.write(style.toString());
return excelName + "_show/" + htmlName;
} catch (IOException e) {
logger.error("附件解析Excel异常", e);
} finally {
if (xssfWorkbook != null) {
try {
xssfWorkbook.close();
} catch (IOException e) {
logger.error("附件解析Excel：关闭 xssfWorkbook 异常", e);
}
}
if (xssfStream != null) {
try {
xssfStream.close();
} catch (IOException e) {
logger.error("附件解析Excel：关闭 xssfStream 异常", e);
}
}
closeFileWriter(htmlBufferedWriter, cssFileWriter);
}
return "";
}
private static void createCellStyle(List<String> cellClass, CellStyle cellStyle) {
switch (cellStyle.getAlignmentEnum()) {
case CENTER:
cellClass.add(TEXT_ALIGN_CENTER_CLASS);
break;
case RIGHT:
cellClass.add(TEXT_ALIGN_RIGHT_CLASS);
break;
default:
break;
}
}
private static void addFontStyle(Map<String, String> cssMap, Font font, List<String> cellClass) {
double fontSize = (font.getFontHeightInPoints() / 72.0) * 96;
if (fontSize >= 15) {
String fontSizeStr = DECIMAL_FORMAT.format(fontSize);
String className = FONT_SIZE_CLASS_PREFIX + fontSizeStr;
cssMap.put(className, FONT_SIZE + fontSizeStr + PX_SEMICOLON);
cellClass.add(className);
}
if (font.getBold()) {
cellClass.add(BOLD_CLASS);
}
if (font.getItalic()) {
cellClass.add(ITALIC_CLASS);
}
if (font.getStrikeout()) {
cellClass.add(STRIKEOUT_CLASS);
}
if (font.getUnderline() > 0) {
cellClass.add(UNDERLINE_CLASS);
}
}
private static void appendColWidthCss(StringBuilder tableCssStyle, int st, Map<Integer, Float> columnWidth) {
Set<Integer> keySet = columnWidth.keySet();
for (Integer key : keySet) {
String colWidthCss = CLASS_NAME_SHEET +
(st + 1) +
" table tr td:nth-child(" +
key +
BRACKETS_RIGHT_AND_CURLY_BRACES_LEFT +
WIDTH_COLON +
DECIMAL_FORMAT.format(columnWidth.get(key)) +
PX_SEMICOLON +
CURLY_BRACES_RIGHT;
tableCssStyle.append(colWidthCss);
}
}
private static void appendCss(StringBuilder style, Map<String, String> cssMap) {
if (MapUtils.isEmpty(cssMap)) {
return;
}
Set<String> classNames = cssMap.keySet();
if (CollectionUtils.isEmpty(classNames)) {
return;
}
for (String className : classNames) {
String statement = cssMap.get(className);
if (StringUtils.isBlank(statement)) {
continue;
}
style.append(CLASS_SELECTOR).append(className).append(CURLY_BRACES_LEFT).append(statement).append(CURLY_BRACES_RIGHT);
}
}
private static List<List<Object>> trimTableData(List<List<Object>> tableData, Map<Integer, Float> columnWidth) {
if (CollectionUtils.isEmpty(tableData)) {
return new ArrayList<>();
}
int maxCellSize = 0;
int maxRowNotNullIndex = 0;
int maxCellNotNullIndex = 0;
int tableDataSize = tableData.size();
for (int i = 0; i < tableData.size(); i++) {
List<Object> rowData = tableData.get(i);
if (CollectionUtils.isEmpty(rowData)) {
continue;
}
int rowMaxCellIndex = -1;
int rowDataSize = rowData.size();
for (int j = 0; j < rowDataSize; j++) {
Object val = rowData.get(j);
if (val != null && !"".equals(val)) {
rowMaxCellIndex = j;
}
}
maxCellSize = Math.max(maxCellSize, rowDataSize);
maxCellNotNullIndex = Math.max(maxCellNotNullIndex, rowMaxCellIndex);
if (rowMaxCellIndex > -1) {
maxRowNotNullIndex = i;
}
}
if (maxRowNotNullIndex + 1 < tableDataSize) {
tableData = tableData.subList(0, maxRowNotNullIndex + 1);
}
List<List<Object>> list = new ArrayList<>();
for (List<Object> rowData : tableData) {
if (CollectionUtils.isEmpty(rowData)) {
continue;
}
if (maxCellNotNullIndex + 1 < rowData.size()) {
List<Object> subList = rowData.subList(0, maxCellNotNullIndex + 1);
list.add(subList);
} else {
list.add(rowData);
}
}
for (int i = maxCellNotNullIndex + 2; i <= maxCellSize; i++) {
columnWidth.remove(i);
}
return list;
}
private static String createCellClass(List<String> cellClass) {
StringBuilder classHtml = new StringBuilder();
if (CollectionUtils.isNotEmpty(cellClass)) {
classHtml.append(" class='");
for (String className : cellClass) {
classHtml.append(className).append(" ");
}
classHtml.deleteCharAt(classHtml.length() - 1);
classHtml.append("'");
}
return classHtml.toString();
}
private static String createDefaultTdHtml(List<List<String>> rowCellClass, List<Object> rowValues) {
StringBuilder tdHtml = new StringBuilder();
int rowCellClassSize = 0;
if (CollectionUtils.isNotEmpty(rowCellClass)) {
rowCellClassSize = rowCellClass.size();
}
for (int j = 0; j < rowValues.size(); j++) {
Object rowValue = rowValues.get(j);
tdHtml.append("<td");
if (rowCellClassSize > j && rowValue != null && !"".equals(rowValue)) {
List<String> cellClass = rowCellClass.get(j);
tdHtml.append(createCellClass(cellClass));
}
tdHtml.append(">");
tdHtml.append(rowValue);
tdHtml.append("</td>");
}
return tdHtml.toString();
}
private static String createTableHtml(List<List<Object>> tableData, List<List<List<String>>> tableRowCellClass, boolean hasMerged, List<String> mergedRegions) {
int tableRowCellClassSize = tableRowCellClass.size();
StringBuilder rowHtml = new StringBuilder("<table>");
for (int i = 0; i < tableData.size(); i++) {
List<List<String>> rowCellClass = null;
if (tableRowCellClassSize > i) {
rowCellClass = tableRowCellClass.get(i);
}
List<Object> rowValues = tableData.get(i);
rowHtml.append("<tr>");
if (hasMerged) {
for (int j = 0; j < rowValues.size(); j++) {
Object rowValue = rowValues.get(j);
if (!INVALID_CELL.equals(rowValue)) {
if (mergedRegions.contains(i + ":" + j)) {
rowHtml.append(rowValue);
} else {
int rowCellClassSize = 0;
if (CollectionUtils.isNotEmpty(rowCellClass)) {
rowCellClassSize = rowCellClass.size();
}
rowHtml.append("<td");
if (rowCellClassSize > j && rowValue != null && !"".equals(rowValue)) {
List<String> cellClass = rowCellClass.get(j);
rowHtml.append(createCellClass(cellClass));
}
rowHtml.append(">");
rowHtml.append(rowValue);
rowHtml.append("</td>");
}
}
}
} else {
rowHtml.append(createDefaultTdHtml(rowCellClass, rowValues));
}
rowHtml.append("</tr>");
}
return rowHtml.append("</table>").toString();
}
private static List<String> getMergedRegions(Sheet sheet, List<List<Object>> tableData, List<List<List<String>>> tableRowCellClass) {
List<String> mergedRegions = new ArrayList<>();
if (sheet.getNumMergedRegions() > 0) {
List<CellRangeAddress> cellRangeAddresses = sheet.getMergedRegions();
for (CellRangeAddress cellRangeAddress : cellRangeAddresses) {
int firstRow = cellRangeAddress.getFirstRow();
int lastRow = cellRangeAddress.getLastRow();
int firstColumn = cellRangeAddress.getFirstColumn();
int lastColumn = cellRangeAddress.getLastColumn();
int rowspan = lastRow - firstRow + 1;
int colspan = lastColumn - firstColumn + 1;
String tdProperty = " rowspan='" + rowspan + "' colspan='" + colspan + "'";
for (int i = firstRow; i <= lastRow; i++) {
List<Object> rowValues = tableData.get(i);
for (int j = firstColumn; j < lastColumn; j++) {
rowValues.set(j + 1, INVALID_CELL);
}
if (rowspan > 1) {
if (i > firstRow) {
tableData.get(i).set(firstColumn, INVALID_CELL);
}
}
}
List<Object> rowValues = tableData.get(firstRow);
StringBuffer tdHtml = new StringBuffer();
tdHtml.append("<td");
if (CollectionUtils.isNotEmpty(tableRowCellClass) && tableRowCellClass.size() > firstRow) {
List<List<String>> rowCellClass = tableRowCellClass.get(firstRow);
if (CollectionUtils.isNotEmpty(rowCellClass) && rowCellClass.size() > firstColumn) {
List<String> cellClass = rowCellClass.get(firstColumn);
tdHtml.append(createCellClass(cellClass));
}
}
tdHtml.append(tdProperty);
tdHtml.append(">");
tdHtml.append(rowValues.get(firstColumn));
tdHtml.append("</td>");
rowValues.set(firstColumn, tdHtml);
mergedRegions.add(firstRow + ":" + firstColumn);
}
}
return mergedRegions;
}
private static void setValues(List<Object> rowValues, Cell cell) {
int columnIndex = cell.getColumnIndex();
switch (cell.getCellTypeEnum()) {
case STRING:
rowValues.set(columnIndex, cell.getStringCellValue());
break;
case BOOLEAN:
rowValues.set(columnIndex, cell.getBooleanCellValue());
break;
case FORMULA:
switch (cell.getCachedFormulaResultTypeEnum()) {
case NUMERIC:
rowValues.set(columnIndex, cell.getNumericCellValue());
break;
case STRING:
rowValues.set(columnIndex, cell.getStringCellValue());
break;
case BOOLEAN:
rowValues.set(columnIndex, cell.getBooleanCellValue());
break;
default:
rowValues.set(columnIndex, "");
break;
}
break;
case NUMERIC:
double cellValue = cell.getNumericCellValue();
DecimalFormat df = new DecimalFormat("#");
String strIntegerVal = df.format(cellValue);
double integer = Double.parseDouble(strIntegerVal);
rowValues.set(columnIndex, cellValue == integer ? strIntegerVal : cellValue);
break;
default:
rowValues.set(columnIndex, "");
break;
}
}
private static void closeFileWriter(BufferedWriter htmlBufferedWriter, FileWriter cssFileWriter) {
if (htmlBufferedWriter != null) {
try {
htmlBufferedWriter.flush();
} catch (IOException e) {
logger.error("附件解析Excel：flush htmlBufferedWriter 异常", e);
}
try {
htmlBufferedWriter.close();
} catch (IOException e) {
logger.error("附件解析Excel：关闭 htmlBufferedWriter 异常", e);
}
}
if (cssFileWriter != null) {
try {
cssFileWriter.flush();
} catch (IOException e) {
logger.error("附件解析Excel：flush cssFileWriter 异常", e);
}
try {
cssFileWriter.close();
} catch (IOException e) {
logger.error("附件解析Excel：关闭 cssFileWriter 异常", e);
}
}
}
}
excel-sheet.css
* {
box-sizing: border-box;
}
html,
body {
margin: 0;
padding: 0;
overflow: hidden;
}
a:hover,
a:visited,
a:link,
a:active {
color: inherit;
cursor: inherit;
text-decoration: none;
}
table {
border-collapse: collapse;
border-style: none;
white-space: pre-line;
width: max-content;
width: -moz-max-content;
}
.sheet-show {
overflow: auto !important;
z-index: 0 !important;
}
.sheet {
position: absolute;
top: 0px;
left: 0px;
width: 100%;
height: calc(100% - 30px);
z-index: -1;
background-color: #FFFFFF;
overflow: hidden;
}
.sheet-tab {
position: fixed;
bottom: 0px;
width: 100%;
height: 30px;
background-color: #E6E6E6;
border: 1px solid #BFBFBF;
z-index: 9999999;
}
.sheet-tab button {
background-color: #E6E6E6;
border: 0px;
height: 30px;
width: 30px;
position: absolute;
}
.sheet-tab button:hover {
background-color: #BFBFBF;
}
.sheet-tab button[disabled]:hover {
background-color: #E6E6E6;
}
.sheet-tab button:focus {
outline: 0;
}
.sheet-tab .roll-left {
left: 0px;
bottom: -1px;
border-right: solid 1px #999999;
}
.sheet-tab .roll-right {
right: 0px;
bottom: -1px;
border-left: solid 1px #999999;
}
.sheet-tab ul {
font-size: 0px;
list-style: none;
padding: 0px;
margin: 0px 30px;
white-space: nowrap;
overflow: hidden;
width: max-content;
width: -moz-max-content;
}
.sheet-tab ul li {
cursor: default;
color: #46494E;
font-size: 12px;
display: inline-block;
height: 25px;
line-height: 25px;
padding-left: 15px;
padding-right: 15px;
margin-top: 2px;
border-right: 1px solid #999999;
}
.sheet-tab ul li:hover {
color: #282C33;
}
.sheet-tab .current {
margin-top: -2px;
margin-left: -2px;
height: 27px;
line-height: 27px;
color: #217346;
background-color: #FFFFFF;
border-left: 1px solid #999999;
border-bottom: 2px solid #217346;
}
.sheet-tab .current:hover {
color: #217346;
}
table tr td {
border: 1px solid #000;
padding: 3px 5px;
font-size: 14px;
color: #000;
}
.tac {
text-align: center;
}
.tar {
text-align: right;
}
.bold {
font-weight: 600;
}
.italic {
font-style: italic;
}
.strikeout {
text-decoration: line-through;
}
.underline {
text-decoration: underline;
}
excel-sheet.js
$(function(){
var $sheets = $(".sheet-tab > ul"),
sheetsWidth = $(".sheet-tab").width(),
initialLeft = $sheets.offset().left,
ulWidth = $("ul").width();
if (ulWidth <= sheetsWidth) {
$(".roll-right").attr("disabled", "disabled");
}
$(".roll-left").on("click", function(){
var ulLeft = $sheets.offset().left;
var left = ulLeft + sheetsWidth / 2;
$sheets.css("margin-left", left);
if (initialLeft <= left) {
$(".roll-left").attr("disabled", "disabled");
$sheets.css("margin-left", initialLeft);
}
$(".roll-right").removeAttr("disabled");
});
$(".roll-right").on("click", function(){
var ulLeft = $sheets.offset().left;
var left = ulLeft - sheetsWidth / 2;
$sheets.css("margin-left", left);
console.log(left * -1);
console.log(ulWidth - sheetsWidth);
if (left * -1 >= ulWidth - sheetsWidth) {
$(".roll-right").attr("disabled", "disabled");
}
$(".roll-left").removeAttr("disabled");
});
$(".sheet-tab > ul > li").on("click", function(){
$(this).addClass("current").siblings(".current").removeClass("current");
var sheetClass = $(this).attr("sheet-filter");
$("." + sheetClass).addClass("sheet-show").siblings(".sheet").removeClass("sheet-show");
});
});
扫一扫
3346
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
