使用了poi的ExcelToHtmlConverter,原方法只支持xls。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.9</version>
</dependency>
写了 *public static void transformXSSF(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew)*手动进行转换;
原来的多sheet是并列展示,考虑到实际写了js进行标签切换;
可手动对样式进行调整。
/**
* 版权: taylor
* 描述: excel转html
* 创建时间:2020年08月17日
*/
package com.taylor.excel.util;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.converter.ExcelToHtmlConverter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.boot.system.ApplicationHome;
import org.w3c.dom.Document;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.*;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* 〈功能简述〉excel转html
* easyPoi的excel转html不能自定义格式,尝试自己实现转换
*
* @author Taylor
* @version [V1.0, 2020年08月17日]
* @since [产品/模块版本]
*/
public class ExcelToHtmlUtil {
/**文件在本地服务保存地址的前缀*/
private static final String LOCAL_PATH = new ApplicationHome(ExcelToHtmlUtil.class).getSource().getParent()+File.separator+"upload"+File.separator;
/**通过属性利于重复利用HSSFCellStyle*/
private static HashMap<Integer, HSSFCellStyle> styleMap = new HashMap();
/**
* 转换excel为html
* @param path 原文件路径
* @return 新文件路径
* @throws TransformerException
* @throws IOException
* @throws ParserConfigurationException
*/
public static String excelToHtml(String path) throws TransformerException, IOException, ParserConfigurationException {
InputStream inputStream = new FileInputStream(path);
HSSFWorkbook excelBook= new HSSFWorkbook();
try(Workbook workbook = WorkbookFactory.create(inputStream)){
if (workbook instanceof XSSFWorkbook) {
transformXSSF((XSSFWorkbook) workbook, excelBook);
}else {
excelBook = (HSSFWorkbook)workbook;
}
} catch (InvalidFormatException e) {
throw new RuntimeException("excel解析失败:"+e.getMessage(),e);
}
return getString(excelBook);
}
/**
* 将xlsx文件转为xls
* @param workbookOld 原xls
* @param workbookNew 新的xlsx
*/
public static void transformXSSF(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew) {
HSSFSheet sheetNew;
XSSFSheet sheetOld;
workbookNew.setMissingCellPolicy(workbookOld.getMissingCellPolicy());
for (int i = 0; i < workbookOld.getNumberOfSheets(); i++) {
sheetOld = workbookOld.getSheetAt(i);
sheetNew = workbookNew.createSheet(sheetOld.getSheetName());
transform(workbookOld, workbookNew, sheetOld, sheetNew);
}
}
private static String getString(HSSFWorkbook excelBook) throws ParserConfigurationException, TransformerException, IOException {
//生成html在本地的保存地址
String targetFileName = LOCA