cn.afterturn.easypoi.excel.html.HtmlToExcelService Maven / Gradle / Ivy
The newest version!
package cn.afterturn.easypoi.excel.html;
import java.util.Map;
import java.util.List;
import cn.afterturn.easypoi.excel.html.entity.ExcelAttrConstant;
import cn.afterturn.easypoi.util.PoiMergeCellUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTDataValidationImpl;
import org.slf4j.Logger;
import java.util.HashMap;
import java.util.LinkedList;
import org.slf4j.LoggerFactory;
import cn.afterturn.easypoi.excel.export.styler.ExcelExportStylerDefaultImpl;
import cn.afterturn.easypoi.excel.html.css.CssParseService;
import cn.afterturn.easypoi.excel.html.css.ICssConvertToExcel;
import cn.afterturn.easypoi.excel.html.css.impl.AlignCssConvertImpl;
import cn.afterturn.easypoi.excel.html.css.impl.BackgroundCssConvertImpl;
import cn.afterturn.easypoi.excel.html.css.impl.BorderCssConverImpl;
import cn.afterturn.easypoi.excel.html.css.impl.HeightCssConverImpl;
import cn.afterturn.easypoi.excel.html.css.impl.TextCssConvertImpl;
import cn.afterturn.easypoi.excel.html.css.impl.WidthCssConverImpl;
import cn.afterturn.easypoi.excel.html.entity.ExcelCssConstant;
import cn.afterturn.easypoi.excel.html.entity.HtmlCssConstant;
import cn.afterturn.easypoi.excel.html.entity.style.CellStyleEntity;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import javax.management.RuntimeErrorException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* 读取Table数据生成Excel
* @author JueYue
* 2017年3月19日
*/
public class HtmlToExcelService {
private final Logger LOGGER = LoggerFactory
.getLogger(HtmlToExcelService.class);
//样式
private static final List STYLE_APPLIERS = new LinkedList();
static {
STYLE_APPLIERS.add(new AlignCssConvertImpl());
STYLE_APPLIERS.add(new BackgroundCssConvertImpl());
STYLE_APPLIERS.add(new BorderCssConverImpl());
STYLE_APPLIERS.add(new TextCssConvertImpl());
}
//Cell 高宽
private static final List SHEET_APPLIERS = new LinkedList();
static {
SHEET_APPLIERS.add(new WidthCssConverImpl());
SHEET_APPLIERS.add(new HeightCssConverImpl());
}
private Sheet sheet;
private Map cellsOccupied = new HashMap();
private Map cellStyles = new HashMap();
private CellStyle defaultCellStyle;
private int maxRow = 0;
private CssParseService cssParse = new CssParseService();
// --
// private methods
private void processTable(Element table) {
int rowIndex = 0;
if (maxRow > 0) {
// blank row
maxRow += 2;
rowIndex = maxRow;
}
LOGGER.debug("Interate Table Rows.");
String freezeCol = null;
int freezeColIndex = -1;
for (Element row : table.select("tr")) {
LOGGER.debug("Parse Table Row [{}]. Row Index [{}].", row, rowIndex);
// 获取当前行
final Row orCreateRow = getOrCreateRow(rowIndex);
// 冻结行属性
String freezeRow = row.attr(ExcelCssConstant.FREEZE_ROW);
// 隐藏行属性
String zeroHeightRow = row.attr(ExcelCssConstant.ZEROH_EIGHT_ROW);
if ("true".equals(freezeRow)) {
sheet.createFreezePane(0, rowIndex + 1, 0, rowIndex + 1);
}
// 隐藏行
if ("true".equals(zeroHeightRow)) {
orCreateRow.setZeroHeight(true);
}
int colIndex = 0;
LOGGER.debug("Interate Cols.");
for (Element td : row.select("td, th")) {
freezeCol = td.attr(ExcelCssConstant.FREEZE_COL);
if ("true".equals(freezeCol)) {
if (colIndex > freezeColIndex) {
freezeColIndex = colIndex;
}
}
// skip occupied cell
while (cellsOccupied.get(rowIndex + "_" + colIndex) != null) {
LOGGER.debug("Cell [{}][{}] Has Been Occupied, Skip.", rowIndex, colIndex);
++colIndex;
}
LOGGER.debug("Parse Col [{}], Col Index [{}].", td, colIndex);
int rowSpan = 0;
String strRowSpan = td.attr("rowspan");
if (StringUtils.isNotBlank(strRowSpan) && StringUtils.isNumeric(strRowSpan)) {
LOGGER.debug("Found Row Span [{}].", strRowSpan);
rowSpan = Integer.parseInt(strRowSpan);
}
int colSpan = 0;
String strColSpan = td.attr("colspan");
if (StringUtils.isNotBlank(strColSpan) && StringUtils.isNumeric(strColSpan)) {
LOGGER.debug("Found Col Span [{}].", strColSpan);
colSpan = Integer.parseInt(strColSpan);
}
// col span & row span
if (colSpan > 1 && rowSpan > 1) {
spanRowAndCol(td, rowIndex, colIndex, rowSpan, colSpan);
colIndex += colSpan;
}
// col span only
else if (colSpan > 1) {
spanCol(td, rowIndex, colIndex, colSpan);
colIndex += colSpan;
}
// row span only
else if (rowSpan > 1) {
spanRow(td, rowIndex, colIndex, rowSpan);
++colIndex;
}
// no span
else {
// 创建下拉框
createDataValidation(rowIndex, colIndex, td);
final Cell cell = createCell(td, orCreateRow, colIndex);
// 创建单元个批注
createCellComment(td, cell);
cell.setCellValue(td.text());
++colIndex;
}
}
++rowIndex;
}
if (freezeColIndex != -1) {
sheet.createFreezePane(freezeColIndex + 1, 0, freezeColIndex + 1, 0);
}
}
/**
* 创建单元个批注
*
* @param td 模板单元格
* @param cell 列
*/
private void createCellComment(final Element td, final Cell cell) {
// 批注
final String comment = td.attr(ExcelAttrConstant.COMMENT_ATTR);
if (StringUtils.isNotBlank(comment)) {
//创建绘图对象
Drawing drawingPatriarch = sheet.getDrawingPatriarch();
if (drawingPatriarch == null) {
drawingPatriarch = sheet.createDrawingPatriarch();
}
ClientAnchor clientAnchor;
RichTextString richTextString;
if (sheet.getWorkbook() instanceof XSSFWorkbook) {
clientAnchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6);
richTextString = new XSSFRichTextString(comment);
} else {
clientAnchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6);
richTextString = new HSSFRichTextString(comment);
}
final Comment cellComment = drawingPatriarch.createCellComment(clientAnchor);
cellComment.setString(richTextString);
cell.setCellComment(cellComment);
}
}
/**
* 创建数据校验（下拉框值）
*
* @param rowIndex 行
* @param colIndex 列
* @param td 表格的列
*/
private void createDataValidation(final int rowIndex, final int colIndex, final Element td) {
final String attr = td.attr(ExcelAttrConstant.SELECT_ATTR);
final String selectSheetAttr = td.attr(ExcelAttrConstant.SELECT_SHEET_ATTR);
//CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)设置行列范围
CellRangeAddressList addressList = new CellRangeAddressList(rowIndex, rowIndex, colIndex, colIndex);
DataValidationHelper helper = sheet.getDataValidationHelper();
if (StringUtils.isNotBlank(attr)) {
String[] strs = attr.split(",");
final Workbook workbook = sheet.getWorkbook();
//设置下拉框数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(strs);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
//处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
// 根据sheet页创建下拉校验
else if (StringUtils.isNotBlank(selectSheetAttr)) {
// 部门!$A$2:$A$96
final String sheetName = selectSheetAttr.split("!")[0];
// 公式名称
final String formulaName = sheetName + "_Formula";
Name name = sheet.getWorkbook().getName(formulaName);
if (name == null) {
name = sheet.getWorkbook().createName();
}
name.setNameName(formulaName);
name.setRefersToFormula(selectSheetAttr);
//设置下拉框数据
DataValidationConstraint constraint = helper.createFormulaListConstraint(formulaName);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
//处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
}
/**
* 创建sheet页
*
* @param html 模板
* @param workbook 工作簿
* @return 工作簿
*/
public Workbook createSheet(String html, Workbook workbook) {
Elements els = Jsoup.parseBodyFragment(html).select("table");
Map sheets = new HashMap();
Map maxrowMap = new HashMap();
for (Element table : els) {
String sheetName = table.attr(ExcelCssConstant.SHEET_NAME);
String sheetHidden = table.attr(ExcelCssConstant.SHEET_HIDDEN);
if (StringUtils.isBlank(sheetName)) {
LOGGER.error("table必须存在name属性!");
throw new RuntimeErrorException(null, "table必须存在name属性");
}
if (sheets.containsKey(sheetName)) {
maxRow = maxrowMap.get(sheetName);
//cellStyles = csStyleMap.get(sheetName);
//cellsOccupied = cellsOccupiedMap.get(sheetName);
sheet = sheets.get(sheetName);
} else {
maxRow = 0;
cellStyles.clear();
cellsOccupied.clear();
sheet = workbook.createSheet(sheetName);
}
//生成一个默认样式
defaultCellStyle = new ExcelExportStylerDefaultImpl(workbook).stringNoneStyle(workbook,
false);
processTable(table);
maxrowMap.put(sheetName, maxRow);
sheets.put(sheetName, sheet);
// 隐藏sheet页
if (StringUtils.isNotBlank(sheetHidden) && "true".equals(sheetHidden)) {
workbook.setSheetHidden(workbook.getSheetIndex(sheet), true);
}
}
return workbook;
}
private void spanRow(Element td, int rowIndex, int colIndex, int rowSpan) {
LOGGER.debug("Span Row , From Row [{}], Span [{}].", rowIndex, rowSpan);
mergeRegion(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex);
for (int i = 0; i < rowSpan; ++i) {
Row row = getOrCreateRow(rowIndex + i);
createCell(td, row, colIndex);
cellsOccupied.put((rowIndex + i) + "_" + colIndex, true);
}
getOrCreateRow(rowIndex).getCell(colIndex).setCellValue(td.text());
}
private void spanCol(Element td, int rowIndex, int colIndex, int colSpan) {
LOGGER.debug("Span Col, From Col [{}], Span [{}].", colIndex, colSpan);
mergeRegion(rowIndex, rowIndex, colIndex, colIndex + colSpan - 1);
Row row = getOrCreateRow(rowIndex);
for (int i = 0; i < colSpan; ++i) {
createCell(td, row, colIndex + i);
}
row.getCell(colIndex).setCellValue(td.text());
}
private void spanRowAndCol(Element td, int rowIndex, int colIndex, int rowSpan, int colSpan) {
LOGGER.debug("Span Row And Col, From Row [{}], Span [{}].", rowIndex, rowSpan);
LOGGER.debug("From Col [{}], Span [{}].", colIndex, colSpan);
mergeRegion(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex + colSpan - 1);
for (int i = 0; i < rowSpan; ++i) {
Row row = getOrCreateRow(rowIndex + i);
for (int j = 0; j < colSpan; ++j) {
createCell(td, row, colIndex + j);
cellsOccupied.put((rowIndex + i) + "_" + (colIndex + j), true);
}
}
getOrCreateRow(rowIndex).getCell(colIndex).setCellValue(td.text());
}
private Cell createCell(Element td, Row row, int colIndex) {
Cell cell = row.getCell(colIndex);
if (cell == null) {
LOGGER.debug("Create Cell [{}][{}].", row.getRowNum(), colIndex);
cell = row.createCell(colIndex);
}
return applyStyle(td, cell);
}
private Cell applyStyle(Element td, Cell cell) {
String style = td.attr(HtmlCssConstant.STYLE);
CellStyle cellStyle = null;
if (StringUtils.isNotBlank(style)) {
CellStyleEntity styleEntity = cssParse.parseStyle(style.trim());
cellStyle = cellStyles.get(styleEntity.toString());
if (cellStyle == null) {
LOGGER.debug("No Cell Style Found In Cache, Parse New Style.");
cellStyle = cell.getRow().getSheet().getWorkbook().createCellStyle();
cellStyle.cloneStyleFrom(defaultCellStyle);
for (ICssConvertToExcel cssConvert : STYLE_APPLIERS) {
cssConvert.convertToExcel(cell, cellStyle, styleEntity);
}
cellStyles.put(styleEntity.toString(), cellStyle);
}
for (ICssConvertToExcel cssConvert : SHEET_APPLIERS) {
cssConvert.convertToExcel(cell, cellStyle, styleEntity);
}
if (cellStyles.size() >= 4000) {
LOGGER.info(
"Custom Cell Style Exceeds 4000, Could Not Create New Style, Use Default Style.");
cellStyle = defaultCellStyle;
}
} else {
LOGGER.debug("Style is null ,Use Default Cell Style.");
cellStyle = defaultCellStyle;
}
cell.setCellStyle(cellStyle);
return cell;
}
private Row getOrCreateRow(int rowIndex) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
LOGGER.debug("Create New Row [{}].", rowIndex);
row = sheet.createRow(rowIndex);
if (rowIndex > maxRow) {
maxRow = rowIndex;
}
}
return row;
}
private void mergeRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
LOGGER.debug("Merge Region, From Row [{}], To [{}].", firstRow, lastRow);
LOGGER.debug("From Col [{}], To [{}].", firstCol, lastCol);
PoiMergeCellUtil.addMergedRegion(sheet, firstRow, lastRow, firstCol, lastCol);
}
}