需求：之前做了一個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。
我的另一個自定義拖動的圖形繪畫工具