需求：之前做了一个web端的excel设计器，现需要将设计好的表格获取html代码，将html转换为excel文件进行输出
一：最终效果：
html设计器制作的表格
最终生成的excel表格：
注意点：table中样式要按照标准格式去写：例如
style="font-size: 12px;border: 1px solid #000000"
二：引入依赖
pom文件：
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/dom4j/dom4j -->
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.8.1</version>
</dependency>三：创建工具文件
函数作用大家看注释就可以
CrossRangeCellMeta.java
package com.example.demo;
/**
* @Description:
* @Author: zy
* @CreateDate: 2019/4/10 14:30
*/
public class CrossRangeCellMeta {
public CrossRangeCellMeta(int firstRowIndex, int firstColIndex, int rowSpan, int colSpan) {
super();
this.firstRowIndex = firstRowIndex;
this.firstColIndex = firstColIndex;
this.rowSpan = rowSpan;
this.colSpan = colSpan;
}
private int firstRowIndex;
private int firstColIndex;
private int rowSpan;// 跨越行数
private int colSpan;// 跨越列数
int getFirstRow() {
return firstRowIndex;
}
int getLastRow() {
return firstRowIndex + rowSpan - 1;
}
int getFirstCol() {
return firstColIndex;
}
int getLastCol() {
return firstColIndex + colSpan - 1;
}
int getColSpan(){
return colSpan;
}
}
ConvertHtml2Excel.java
package com.example.demo;
import java.io.File;
import java.io.FileOutputStream;
import java.util.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.dom4j.*;
/**
* @Description:
* @Author: zy
* @CreateDate: 2019/4/10 14:30
*/
public class ConvertHtml2Excel {
private static final Map<String, Short> CSS_TO_POI_STYLE = new HashMap<>();
//像素转excel中高度转换率
private static final double PX_TO_EXCEL_HEIGHT = 15;
//像素转excel中宽度转换率
private static final double PX_TO_EXCEL_WIDTH = 37.5;
ConvertHtml2Excel() {
CSS_TO_POI_STYLE.put("center", HSSFCellStyle.ALIGN_CENTER);
CSS_TO_POI_STYLE.put("left", HSSFCellStyle.ALIGN_LEFT);
CSS_TO_POI_STYLE.put("start", HSSFCellStyle.ALIGN_LEFT);
CSS_TO_POI_STYLE.put("right", HSSFCellStyle.ALIGN_RIGHT);
CSS_TO_POI_STYLE.put("end", HSSFCellStyle.ALIGN_RIGHT);
CSS_TO_POI_STYLE.put("top", HSSFCellStyle.VERTICAL_TOP);
CSS_TO_POI_STYLE.put("middle", HSSFCellStyle.VERTICAL_CENTER);
CSS_TO_POI_STYLE.put("bottom", HSSFCellStyle.VERTICAL_BOTTOM);
CSS_TO_POI_STYLE.put("solid", CellStyle.BORDER_THIN);
CSS_TO_POI_STYLE.put("dashed", CellStyle.BORDER_DASHED);
CSS_TO_POI_STYLE.put("double", CellStyle.BORDER_DOUBLE);
}
public static void main(String[] args) {
new ConvertHtml2Excel();
//生成的前端html的table代码
String htmlStr = "<table><tbody><tr style=\"height: 25px;\"><td style=\"width: 68px;\">单位名称</td><td style=\"width: 66px;text-align: center;\" rowspan=\"1\" colspan=\"9\">深圳********有限公司</td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 123px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 100px; text-align: center; display: none;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px; vertical-align: middle; text-align: start;\">主标题</td><td style=\"width: 66px; text-align: center;\" rowspan=\"1\" colspan=\"9\">应收款明细表</td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 123px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td><td style=\"width: 100px; display: none; text-align: center;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px; vertical-align: middle; text-align: start;\">副标题</td><td style=\"width: 66px; text-align: center;\" rowspan=\"1\" colspan=\"9\">统计月份(2019-01至2019-03)</td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 123px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px; vertical-align: middle; text-align: start;\">附注</td><td style=\"width: 66px;\" rowspan=\"1\" colspan=\"3\">物业名称：豪龙大厦</td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 123px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px; text-align: right;\">打印日期：</td><td style=\"width: 100px;\">@日期</td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px;\">表头</td><td style=\"width: 66px; border: 2px solid rgb(0, 0, 0); text-align: center; vertical-align: middle;\" rowspan=\"2\" colspan=\"1\">序号</td><td style=\"width: 100px; border-width: 2px 3px 2px 2px; border-style: solid double solid solid; border-color: rgb(0, 0, 0); border-image: initial; text-align: center;\" rowspan=\"1\" colspan=\"3\">客户基本资料</td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); display: none; text-align: center;\"></td><td style=\"width: 123px; border: 2px solid rgb(0, 0, 0); display: none; text-align: center;\"></td><td style=\"width: 100px; border-width: 2px 2px 2px 3px; border-style: solid solid solid double; border-color: rgb(0, 0, 0); border-image: initial; text-align: center;\" rowspan=\"2\" colspan=\"1\">计费月份</td><td style=\"width: 100px; border-width: 2px 3px; border-style: solid double; border-color: rgb(0, 0, 0); border-image: initial; text-align: center;\" rowspan=\"1\" colspan=\"2\">应收款</td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); display: none; text-align: center;\"></td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); text-align: center;\" rowspan=\"1\" colspan=\"2\">已收款</td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); display: none; text-align: center;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px;\">表头</td><td style=\"width: 66px;border: 2px solid rgb(0, 0, 0);display: none;\"></td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); text-align: center;\">客户名称</td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); text-align: center;\">房号</td><td style=\"width: 123px; border-width: 2px 3px 2px 2px; border-style: solid double solid solid; border-color: rgb(0, 0, 0) rgb(255, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-image: initial; text-align: center;\">手机</td><td style=\"width: 100px;border: 2px solid rgb(0, 0, 0);display: none;text-align: center;\">201902</td><td style=\"width: 100px; border-width: 2px 2px 2px 3px; border-style: solid solid solid double; border-color: rgb(0, 0, 0); border-image: initial; text-align: center;\">基本费</td><td style=\"width: 100px; border-width: 2px 3px 2px 2px; border-style: solid double solid solid; border-color: rgb(0, 0, 0); border-image: initial; text-align: center;\">滞纳金</td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); text-align: center;\">基本费</td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); text-align: center;\">滞纳金</td></tr><tr style=\"height: 85px;\"><td style=\"width: 68px;\">表体</td><td style=\"width: 66px; border: 2px solid rgb(0, 0, 0); text-align: right;\">@序号</td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); vertical-align: bottom; text-align: start;\">{1.客户代码;0;0;0;;0;}</td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); vertical-align: middle; text-align: start;\">{1.房号;0;0;0;;0;}</td><td style=\"width: 123px; border-width: 2px 3px 2px 2px; border-style: solid double solid solid; border-color: rgb(0, 0, 0); border-image: initial; vertical-align: top; text-align: start;\">{1.手机;0;0;0;;0;}</td><td style=\"width: 100px; border-width: 2px; border-style: none solid solid; border-color: rgb(255, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-image: initial; vertical-align: middle; text-align: start;\">{1.计费月份;0;0;0;;0;}</td><td style=\"width: 100px; border-width: 2px 2px 2px 3px; border-style: solid solid solid double; border-color: rgb(0, 0, 0); border-image: initial; vertical-align: middle; text-align: start;\">{1.基本费;0;0;0;;0;}</td><td style=\"width: 100px; border-width: 2px 3px 2px 2px; border-style: solid double solid solid; border-color: rgb(0, 0, 0); border-image: initial; vertical-align: middle; text-align: start;\">{1.滞纳金;0;0;0;;0;}</td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); vertical-align: middle; text-align: start;\">{1.基本费1;0;0;0;;0;}</td><td style=\"width: 100px; border: 2px solid rgb(0, 0, 0); vertical-align: middle; text-align: start;\">{1.滞纳金1;0;0;0;;0;}</td></tr><tr style=\"height: 35px;\"><td style=\"width: 68px;\">表尾</td><td style=\"width: 66px; white-space: normal; color: rgb(255, 0, 0); border: 2px solid rgb(255, 128, 0);\" rowspan=\"1\" colspan=\"9\">备注说明：备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明备注说明</td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 123px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td><td style=\"width: 100px; display: none;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px;\"></td><td style=\"width: 66px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 123px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px; text-align: right;\">公式演示</td><td style=\"width: 66px;\" class=\"selectTd\">=C10+D10</td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 123px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px;\"></td><td style=\"width: 66px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 123px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px;\"></td><td style=\"width: 66px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 123px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px;\"></td><td style=\"width: 66px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 123px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px;\"></td><td style=\"width: 66px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 123px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td></tr><tr style=\"height: 25px;\"><td style=\"width: 68px;\"></td><td style=\"width: 66px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 123px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td><td style=\"width: 100px;\"></td></tr></tbody></table>";
HSSFWorkbook wb = table2Excel(htmlStr);
try {
FileOutputStream fos = new FileOutputStream(new File("excel.xls"));
wb.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 设置单元格高度
*/
private static void setBorderHeight(Element trEle, HSSFRow row) {
Attribute attribute = trEle.attribute("style");
Map<String, String> styleMap = styleToMap(attribute);
int height = Integer.parseInt(styleMap.get("height").replace("px", ""));
row.setHeight((short) (PX_TO_EXCEL_HEIGHT * height));
}
/**
* 设置单元格默认样式
*
* @param wb
* @return
*/
private static HSSFCellStyle getDefaultStyle(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font font = wb.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 10);
style.setFont(font);
return style;
}
/**
* 设置单元格宽度
* @param trEle
* @param sheet
*/
private static void setBorderWidth(Element trEle, HSSFSheet sheet) {
List<Element> tdLs = trEle.elements("td");
for (int i = 0; i < tdLs.size(); i++) {
Element td = tdLs.get(i);
Attribute attribute = td.attribute("style");
Map<String, String> styleMap = styleToMap(attribute);
int width = Integer.parseInt(styleMap.get("width").replace("px", ""));
sheet.setColumnWidth(i, (short) (PX_TO_EXCEL_WIDTH * width));
}
}
/**
* html表格转excel
*/
public static HSSFWorkbook table2Excel(String tableHtml) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
List<CrossRangeCellMeta> crossRowEleMetaLs = new ArrayList<>();
int rowIndex = 0;
try {
Document data = DocumentHelper.parseText(tableHtml);
// 生成表体
Element tbody = data.getRootElement().element("tbody");
List<HSSFCellStyle> hssfCellStyleList = new ArrayList<>();
if (tbody != null) {
List<Element> trLs = tbody.elements("tr");
setBorderWidth(trLs.get(0), sheet);
for (Element trEle : trLs) {
HSSFRow row = sheet.createRow(rowIndex);
setBorderHeight(trEle, row);
List<Element> thLs = trEle.elements("th");
int cellIndex = makeRowCell(wb, thLs, rowIndex, row, 0, crossRowEleMetaLs, hssfCellStyleList);
List<Element> tdLs = trEle.elements("td");
makeRowCell(wb, tdLs, rowIndex, row, cellIndex, crossRowEleMetaLs, hssfCellStyleList);
rowIndex++;
}
}
int i = 0;
// 处理合并单元格
for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {
CellRangeAddress region = new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol());
sheet.addMergedRegion(region);
HSSFCellStyle hssfCellStyle = hssfCellStyleList.get(i);
setBorderStyle(hssfCellStyle, region, sheet, wb);
i++;
}
} catch (DocumentException e) {
e.printStackTrace();
}
return wb;
}
private static Map<String, String> styleToMap(Attribute attribute) {
Map<String, String> styleMap = new HashMap<>();
if (attribute != null) {
String styleStr = attribute.getValue();
String[] styleArr = styleStr.split(";");
for (String style : styleArr) {
String[] styleItemArr = style.split(": ");
styleMap.put(styleItemArr[0], styleItemArr[1]);
}
}
return styleMap;
}
/**
* 生产行内容
*/
private static int makeRowCell(HSSFWorkbook wb, List<Element> tdLs, int rowIndex, HSSFRow row, int startCellIndex,
List<CrossRangeCellMeta> crossRowEleMetaLs, List<HSSFCellStyle> hssfCellStyleList) {
int i = startCellIndex;
for (int eleIndex = 0; eleIndex < tdLs.size(); i++, eleIndex++) {
Element thEle = tdLs.get(eleIndex);
Attribute attribute = thEle.attribute("style");
if (attribute.getValue().contains("display: none")) {
continue;
}
int captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
while (captureCellSize > 0) {
for (int j = 0; j < captureCellSize; j++) {
row.createCell(i);
i++;
}
captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
}
String val = thEle.getTextTrim();
Map<String, String> styleMap = styleToMap(attribute);
if (StringUtils.isBlank(val)) {
Element e = thEle.element("a");
if (e != null) {
val = e.getTextTrim();
}
}
HSSFCell c = row.createCell(i);
if (NumberUtils.isCreatable(val)) {
c.setCellValue(Double.parseDouble(val));
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
} else if (val.indexOf("=") == 0) {
//判断是否符合公式规则 可自行扩展
try {
c.setCellFormula(val.substring(1));
} catch (FormulaParseException e) {
c.setCellValue(val);
}
} else {
c.setCellValue(val);
}
HSSFCellStyle style = htmlStyleToHSSFCellStyle(wb, styleMap);
c.setCellStyle(style);
int rowSpan = NumberUtils.toInt(thEle.attributeValue("rowspan"), 1);
int colSpan = NumberUtils.toInt(thEle.attributeValue("colspan"), 1);
// 存在跨行或跨列
if (rowSpan > 1 || colSpan > 1) {
hssfCellStyleList.add(style);
crossRowEleMetaLs.add(new CrossRangeCellMeta(rowIndex, i, rowSpan, colSpan));
}
}
return i;
}
/**
* 获得因rowSpan占据的单元格
*
* @param rowIndex 行号
* @param colIndex 列号
* @param crossRowEleMetaLs 跨行列元数据
* @return 当前行在某列需要占据单元格
*/
private static int getCaptureCellSize(int rowIndex, int colIndex, List<CrossRangeCellMeta> crossRowEleMetaLs) {
int captureCellSize = 0;
for (CrossRangeCellMeta crossRangeCellMeta : crossRowEleMetaLs) {
if (crossRangeCellMeta.getFirstRow() < rowIndex && crossRangeCellMeta.getLastRow() >= rowIndex) {
if (crossRangeCellMeta.getFirstCol() <= colIndex && crossRangeCellMeta.getLastCol() >= colIndex) {
captureCellSize = crossRangeCellMeta.getLastCol() - colIndex + 1;
}
}
}
return captureCellSize;
}
/**
* 将css样式转换为poi对应样式
*/
private static HSSFCellStyle htmlStyleToHSSFCellStyle(HSSFWorkbook wb, Map<String, String> styleMap) {
HSSFCellStyle style = getDefaultStyle(wb);
HSSFFont font = style.getFont(wb);
styleMap.forEach((key, value) -> {
key = key.replaceAll(" ", "");
if ("font-size".equals(key)) {
short fontSize = (short) Integer.parseInt(value.replace("px", ""));
font.setFontHeightInPoints(fontSize);
} else if ("font-family".equals(key)) {
font.setFontName(value);
} else if ("font-weight".equals(key)) {
font.setBoldweight((short) 700);
} else if ("text-decoration-line".equals(key) && "underline".equals(value)) {
font.setUnderline(Font.U_SINGLE);
} else if ("text-decoration-line".equals(key) && "line-through".equals(value)) {
font.setStrikeout(true);
} else if ("font-style".equals(key) && "italic".equals(value)) {
font.setItalic(true);
} else if ("color".equals(key)) {
font.setColor(rgbToIndex(value, wb, 1));
} else if ("background-color".equals(key)) {
style.setFillForegroundColor(rgbToIndex(value, wb, 1));
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
} else if ("white-space".equals(key) && "normal".equals(value)) {
style.setWrapText(true);
} else if ("text-align".equals(key)) {
style.setAlignment(CSS_TO_POI_STYLE.get(value));
} else if ("vertical-align".equals(key)) {
style.setVerticalAlignment(CSS_TO_POI_STYLE.get(value));
} else if ("border-left".equals(key) || "border-right".equals(key) || "border-top".equals(key) || "border-bottom".equals(key)) {
setBorder(key, value, style, wb);
}
else if ("border-style".equals(key)) {
String[] styleArr = value.split(" ");
String colorStr = styleMap.get(" border-color");
String[] colorArr = {};
if (colorStr != null) {
colorArr = colorStr.split("rgb");
}
List<String> colorList = Arrays.asList(colorArr);
if (styleArr.length == 1) {
setBorder(style, CSS_TO_POI_STYLE.get(styleArr[0]));
} else if (styleArr.length == 2) {
setBorder(style, CSS_TO_POI_STYLE.get(styleArr[0]), CSS_TO_POI_STYLE.get(styleArr[1]),
CSS_TO_POI_STYLE.get(styleArr[0]), CSS_TO_POI_STYLE.get(styleArr[1]));
} else if (styleArr.length == 3) {
setBorder(style, CSS_TO_POI_STYLE.get(styleArr[0]), CSS_TO_POI_STYLE.get(styleArr[1]),
CSS_TO_POI_STYLE.get(styleArr[2]), CSS_TO_POI_STYLE.get(styleArr[1]));
} else if (styleArr.length == 4) {
setBorder(style, CSS_TO_POI_STYLE.get(styleArr[0]), CSS_TO_POI_STYLE.get(styleArr[1]),
CSS_TO_POI_STYLE.get(styleArr[2]), CSS_TO_POI_STYLE.get(styleArr[3]));
}
if (colorList.size() == 2) {
setBorderColor(style, wb, colorList.get(1), colorList.get(1), colorList.get(1), colorList.get(1));
} else if (colorList.size() == 3) {
setBorderColor(style, wb, colorList.get(1), colorList.get(2), colorList.get(1), colorList.get(2));
} else if (colorList.size() == 4) {
setBorderColor(style, wb, colorList.get(1), colorList.get(2), colorList.get(3), colorList.get(2));
} else if (colorList.size() == 5) {
setBorderColor(style, wb, colorList.get(1), colorList.get(2), colorList.get(3), colorList.get(4));
}
} else if ("border".equals(key)) {
String borderColor = value.substring(value.indexOf("rgb"));
String borderStyle = value.substring(value.indexOf(" "), value.indexOf(" rgb")).replaceAll(" ", "");
setBorder(style, CSS_TO_POI_STYLE.get(borderStyle), CSS_TO_POI_STYLE.get(borderStyle)
, CSS_TO_POI_STYLE.get(borderStyle), CSS_TO_POI_STYLE.get(borderStyle));
short index = rgbToIndex(borderColor, wb, 1);
setBorderColor(style, index);
}
});
style.setFont(font);
return style;
}
private static void setBorder(HSSFCellStyle style, Short border) {
if (border == null) {
return;
}
style.setBorderLeft(border);
style.setBorderRight(border);
style.setBorderTop(border);
style.setBorderBottom(border);
}
private static void setBorder(HSSFCellStyle style, Short borderTop, Short borderRight, Short borderBottom, Short borderLeft) {
if (borderTop != null) {
style.setBorderTop(borderTop);
}
if (borderRight != null) {
style.setBorderRight(borderRight);
}
if (borderBottom != null) {
style.setBorderBottom(borderBottom);
}
if (borderLeft != null) {
style.setBorderLeft(borderLeft);
}
}
private static void setBorder(String key, String value, HSSFCellStyle style, HSSFWorkbook wb) {
String color = value.substring(value.indexOf("rgb"));
String v = value.substring(value.indexOf(" ") + 1, value.indexOf(" rgb"));
if (key.equals("border-left")) {
style.setBorderLeft(CSS_TO_POI_STYLE.get(v));
style.setLeftBorderColor(rgbToIndex(color, wb, 1));
} else if (key.equals("border-right")) {
style.setBorderRight(CSS_TO_POI_STYLE.get(v));
style.setRightBorderColor(rgbToIndex(color, wb, 1));
} else if (key.equals("border-top")) {
style.setBorderTop(CSS_TO_POI_STYLE.get(v));
style.setRightBorderColor(rgbToIndex(color, wb, 1));
} else if (key.equals("border-bottom")) {
style.setBorderBottom(CSS_TO_POI_STYLE.get(v));
style.setBottomBorderColor(rgbToIndex(color, wb, 1));
}
}
private static void setBorderColor(HSSFCellStyle style, HSSFWorkbook wb, String borderTopColor,
String borderRightColor, String borderBottomColor, String borderLeftColor) {
style.setLeftBorderColor(rgbToIndex(borderLeftColor, wb, 2));
style.setRightBorderColor(rgbToIndex(borderRightColor, wb, 2));
style.setTopBorderColor(rgbToIndex(borderTopColor, wb, 2));
style.setBottomBorderColor(rgbToIndex(borderBottomColor, wb, 2));
}
private static void setBorderColor(HSSFCellStyle style, short index) {
style.setLeftBorderColor(index);
style.setRightBorderColor(index);
style.setTopBorderColor(index);
style.setBottomBorderColor(index);
}
private static String[] colorToArr(String value, int type) {
String[] rgbArr = null;
if (type == 1) {
rgbArr = value.replace("rgb(", "").replace(")", "").split(", ");
} else if (type == 2) {
rgbArr = value.replace("(", "").replace(")", "").replace(" ", "").split(",");
}
return rgbArr;
}
private static short rgbToIndex(String value, HSSFWorkbook wb, int type) {
String[] rgbArr = colorToArr(value, type);
int r = Integer.parseInt(rgbArr[0]);
int g = Integer.parseInt(rgbArr[1]);
int b = Integer.parseInt(rgbArr[2]);
HSSFPalette palette = wb.getCustomPalette();
HSSFColor hssfColor = palette.findColor((byte) r, (byte) g, (byte) b);
if (hssfColor != null) {
return hssfColor.getIndex();
} else {
hssfColor = palette.findSimilarColor((byte) r, (byte) g, (byte) b);
if (hssfColor != null) {
return hssfColor.getIndex();
} else {
return HSSFColor.BLACK.index;
}
}
}
private static void setBorderStyle(HSSFCellStyle hssfCellStyle, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook wb) {
RegionUtil.setBorderBottom(hssfCellStyle.getBorderBottom(), region, sheet, wb);
RegionUtil.setBorderLeft(hssfCellStyle.getBorderLeft(), region, sheet, wb);
RegionUtil.setBorderRight(hssfCellStyle.getBorderRight(), region, sheet, wb);
RegionUtil.setBorderTop(hssfCellStyle.getBorderTop(), region, sheet, wb);
RegionUtil.setBottomBorderColor(hssfCellStyle.getBottomBorderColor(), region, sheet, wb);
RegionUtil.setTopBorderColor(hssfCellStyle.getTopBorderColor(), region, sheet, wb);
RegionUtil.setRightBorderColor(hssfCellStyle.getRightBorderColor(), region, sheet, wb);
RegionUtil.setLeftBorderColor(hssfCellStyle.getLeftBorderColor(), region, sheet, wb);
}
} 右键运行void main方法即可
说明：
htmlStyleToHSSFCellStyle方法可自行扩展，作用是将html中style属性中的样式读取出来转换为Excel中对应的属性。
getDefaultStyle方法为设置表格默认样式，可根据自己定义的前端默认样式不同来进行自定义处理；
这里的逻辑是为处理单元格的值来进行配置，目前只处理的字符串型，公式型和数字型。
结尾：
web端excel地址：
github：github.com/MyBeany/myE…
如果对您有帮助，帮忙给个star。
我的另一个自定义拖动的图形绘画工具
