java使用poi操作excel导出为html

最近做了个项目需要对excel进行发送邮件。但是有个需求是需要将excel里面的sheet0的内容原本复制为邮件的正文。样式都要。所以只能操作excel转为html然后嵌套再邮件中发送了。

1.导入pom

 <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.9</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.9</version>
            </dependency>

2.操作

package statement;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
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.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import statement.base.StringUtil;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName: PoiExcelToHtmlUtil
 * @Description: TODO(poi转excel为html)
 */
public class ExcelToHtml {
    private static  Map<String, Object> map[];

    public static  void main(String [] args){
        String html=excelWriteToHtml(filepath);
        System.out.print(html);
    }

    /**
     * 程序入口方法（读取指定位置的excel，将其转换成html形式的字符串，
     * @return <table>...</table> 字符串
     */
    public static String excelWriteToHtml(String sourcePath){
        File sourceFile = new File(sourcePath);
        try {
            InputStream fis = new FileInputStream(sourceFile);
            String excelHtml = ExcelToHtml.readExcelToHtml(fis, true);
            return excelHtml;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return null;
        }
    }
    /**
     * 程序入口方法（将指定路径的excel文件读取成字符串）
     * @param filePath 文件的路径
     * @param isWithStyle 是否需要表格样式 包含 字体 颜色 边框 对齐方式
     * @return <table>...</table> 字符串
     */
    public static String readExcelToHtml(String filePath , boolean isWithStyle){
        InputStream is = null;
        String htmlExcel = null;
        try {
            File sourcefile = new File(filePath);
            is = new FileInputStream(sourcefile);
            Workbook wb = WorkbookFactory.create(is);
            htmlExcel = readWorkbook(wb, isWithStyle);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return htmlExcel;
    }
    /**
     * 程序入口方法（将指定路径的excel文件读取成字符串）
     * @param is excel转换成的输入流
     * @param isWithStyle 是否需要表格样式 包含 字体 颜色 边框 对齐方式
     * @return <table>...</table> 字符串
     */
    public static String readExcelToHtml(InputStream is,boolean isWithStyle){
        String htmlExcel = null;
        try {
            Workbook wb = WorkbookFactory.create(is);
            htmlExcel = readWorkbook(wb,isWithStyle);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return htmlExcel;
    }

    /**
     * 根据excel的版本分配不同的读取方法进行处理
     * @param wb
     * @param isWithStyle
     * @return
     */
    private static String readWorkbook(Workbook wb,boolean isWithStyle){
        String htmlExcel = "";
        if (wb instanceof XSSFWorkbook) {
            XSSFWorkbook xWb = (XSSFWorkbook) wb;
            htmlExcel = getExcelInfo(xWb, isWithStyle);
        }else if(wb instanceof HSSFWorkbook){
            HSSFWorkbook hWb = (HSSFWorkbook) wb;
            htmlExcel = getExcelInfo(hWb, isWithStyle);
        }
        return htmlExcel;
    }

    /**
     * 读取excel成string
     * @param wb
     * @param isWithStyle
     * @return
     */
    public static String getExcelInfo(Workbook wb, boolean isWithStyle){

        StringBuffer sb = new StringBuffer();
        //获取第一个Sheet的内容
        Sheet sheet = wb.getSheetAt(0);
        // map等待存储excel图片
        Map<String, PictureData> sheetIndexPicMap = getSheetPictrues(0, sheet, wb);
        //临时保存位置，正式环境根据部署环境存放其他位置
        try {
            if(sheetIndexPicMap != null)
                printImg(sheetIndexPicMap);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //读取excel拼装html
        int lastRowNum = sheet.getLastRowNum();
        map = getRowSpanColSpanMap(sheet);
        sb.append("<table style='border-collapse:collapse;width:100%;'>");
        Row row = null;        //兼容
        Cell cell = null;    //兼容

        for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum ++) {
            if(rowNum > 1000) break;
            row = sheet.getRow(rowNum);

            int lastColNum = ExcelToHtml.getColsOfTable(sheet)[0];
            int rowHeight = ExcelToHtml.getColsOfTable(sheet)[1];

            if(null != row) {
                lastColNum = row.getLastCellNum();
                rowHeight = row.getHeight();
            }

            if (null == row) {
                sb.append("<tr><td >  </td></tr>");
                continue;
            }else if(row.getZeroHeight()){
                continue;
            }else if(0 == rowHeight){
                continue;     //针对jxl的隐藏行（此类隐藏行只是把高度设置为0，单getZeroHeight无法识别）
            }
            sb.append("<tr>");

            for (int colNum = 0; colNum < lastColNum; colNum ++) {
                if(sheet.isColumnHidden(colNum))	continue;
                String imageRowNum = "0_" + rowNum + "_" + colNum;
                String imageHtml = "";
                cell = row.getCell(colNum);
                if ((sheetIndexPicMap != null && !sheetIndexPicMap.containsKey(imageRowNum) || sheetIndexPicMap == null) && cell == null) {    //特殊情况 空白的单元格会返回null+//判断该单元格是否包含图