java生成excel公共方法

ExportExcel公共类,此类支持包含图片导出

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author wuyang
 * @date 2022年09月14日
 */
public class ExportExcel {
    /**
     *公用excel导出
     *@author wuyang
     *@date 2022/9/14 14:26
     * @param listdata 带到处数据对象集合
     * @param response
     * @param reportName 导出模板配置
     */
    public static void export(List<?> listdata, HttpServletResponse response,String reportName) throws IllegalAccessException, IOException {
        export(listdata, response,reportName,null,false,null,null);
    }
    /**
     *公用excel导出(带图片导出)
     *@author wuyang
     *@date 2022/9/14 14:26
     * @param listdata 带到处数据对象集合
     * @param response
     * @param reportName 导出模板配置
     * @param filePathAndKeyMap <key,path></>图片路径集合
     * @param isPatriarch 是否需要导出图片
     * @param columnNumPatriarch 插入图片的列
     * @param filePath 文件上传路径
     */
    public static void export(List<?> listdata, HttpServletResponse response,String reportName,Map<String, String> filePathAndKeyMap,Boolean isPatriarch,Integer columnNumPatriarch,String  filePath) throws IllegalAccessException, IOException {
        //reportColumn.properties配合文件中配置列名
        String porpertiesName = "reportColumn";
//        String reportName = "oneTimeCompletionTaskOrderList";
        ResourceBundle rb = ResourceBundle.getBundle(porpertiesName);
        // 文件名
        String fileName = rb.getString(reportName + ".fileName");
        // 表名
        String sheetName = reportName + ".sheetName";
        // 字段名
        String sheetcolumn = reportName + ".columnName";
        // 取出list长度
        int size = listdata.size();
        // 读取properties中的内容
        String Name = rb.getString(sheetName);
        int reportColumnNum=getReportColumnNum(rb,sheetcolumn);
        // 在Excel工作簿中建一工作表
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        // 设置表格样式
        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        // 报表边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        XSSFFont font = (XSSFFont) workbook.createFont();
        // 单元格字体
        font.setFontName("宋体");
        font.setFontHeight(9);
        cellStyle.setFont(font);
        // 新建一名为sheetName的工作表
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(Name);
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        Drawing patriarch = sheet.createDrawingPatriarch();
        // 在索引的位置创建行（根据记录数添加）
        SXSSFRow[] row = new SXSSFRow[size + 1];
        row[0] = (SXSSFRow) sheet.createRow(0);
        int g = 0;
        int m = 0;
        int i = 0;
        // 写入表头
        try {
            for (i = 0; i < reportColumnNum; i++) {
                CellUtil.createCell(row[0], i, rb.getString(sheetcolumn + "[" + i + "]." + i), cellStyle);
            }
        } catch (MissingResourceException e) {
            m = i;
        }

        // 写入具体数据
        for (int j = 1; j <= size; j++) {
            row[j] = (SXSSFRow) sheet.createRow(j);
            SXSSFCell cell0 = (SXSSFCell) row[j].createCell(0);
            cell0.setCellStyle(cellStyle);
            cell0.setCellValue(j);
            g = 1;
            for (Field data : listdata.get(j - 1).getClass().getDeclaredFields()) {
                data.setAccessible(true);
                if (isPatriarch && g == columnNumPatriarch) {//图片列
                    List<String> list2 = new ArrayList<>();
                    //图片应冲对象中获取，先以本地图片进行测试
//                    String photograph = list.get(j - 1).getQuestionPicture();
                    String photograph = (String) data.get(listdata.get(j - 1));
                    if (StringUtils.isNotEmpty(photograph)) {
                        String[] split = photograph.split(",");
                        for (String str : split) {
                            String picturePath = filePath + filePathAndKeyMap.get(str);
                            list2.add(picturePath);
                        }
                    }
                    writePicture(workbook, sheet, patriarch, list2, j, columnNumPatriarch);
                } else {

                    // 创建单元格
                    SXSSFCell cell = (SXSSFCell) row[j].createCell(g);
                    cell.setCellStyle(cellStyle);
                    if (data.get(listdata.get(j - 1)) != null && !data.get(listdata.get(j - 1)).equals("")) {
                        // 填入数据
                        if (data.get(listdata.get(j - 1)) instanceof String) {
                            cell.setCellValue((String) data.get(listdata.get(j - 1)));
                        }
                        if (data.get(listdata.get(j - 1)) instanceof Double) {
                            cell.setCellValue((Double) data.get(listdata.get(j - 1)));
                        }
                        if (data.get(listdata.get(j - 1)) instanceof Date) {
                            cell.setCellValue((Date) data.get(listdata.get(j - 1)));
                        }
                    }
                }
                g++;
            }
        }
        // 宽度自适应
        sheet.trackAllColumnsForAutoSizing();
        for (int k = 0; k < m; k++) {
            sheet.autoSizeColumn(k);
        }
        for (int columnNum = 0; columnNum < reportColumnNum; columnNum++) {

            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                Cell a = row[rowNum].getCell(columnNum);
                if (a != null) {
                    if (a.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        int length = a.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            // 限定最大宽度
            if (columnWidth >= 40) {
                columnWidth = 40;
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
        outputStream(response, fileName, workbook);
    }

    /**
     * @param wb              文档对象
     * @param sheet1          sheet页
     * @param patriarch       图片对象
     * @param picturePathList 图片路径集合
     * @param index           纵坐标
     * @param index1          横坐标
     */
    public static void writePicture(SXSSFWorkbook wb, SXSSFSheet sheet1, Drawing patriarch, List<String> picturePathList, int index, int index1) {
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;
        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        try {
            int i = 0;
            int a = XSSFShape.EMU_PER_PIXEL;
            for (String picturePath : picturePathList) {
                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                bufferImg = ImageIO.read(new File(picturePath));
                //图片后缀
                String pictureType = picturePath.substring(picturePath.lastIndexOf(".") + 1);
                ImageIO.write(bufferImg, pictureType, byteArrayOut);
                //anchor主要用于设置图片的属性
                int dx1 = i * 50 * a;
                int dx2 = dx1 + 40 * a;
                XSSFClientAnchor anchor = new XSSFClientAnchor(dx1, 0, dx2, 40 * a, (short) index1, index, (short) index1, index);
                anchor.setAnchorType(ClientAnchor.AnchorType.byId(1));
                //插入图片
                patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                i++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fileOut != null) {
                try {
                    fileOut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    /**
     * 输出excel文件
     *
     * @param response
     * @param fileName
     * @param workbook
     */
    private static  void outputStream(HttpServletResponse response, String fileName, SXSSFWorkbook workbook) throws IOException {
        // 将excel放入输出流推出
        OutputStream os = response.getOutputStream();
        BufferedOutputStream out = new BufferedOutputStream(os);
        // fileDownload插件以此判断下载是否成功,不设置则不执行成功回调函数
        response.setHeader("Set-Cookie", "fileDownload=true; path=/");
        // 设置文件类型
        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        // 关闭流
        workbook.write(out);
        out.flush();
        out.close();
        workbook.close();
    }
    /**
     *获取配置列的数量
     *@author wuyang
     *@date 2022/9/14 10:35
     * @param rb
     * @param sheetcolumn
     * @return int
     */
    public static int getReportColumnNum(ResourceBundle rb,String sheetcolumn){
        Enumeration<String> keys = rb.getKeys();
        int reportColumnNum =0;
        while (keys.hasMoreElements()){
            String str = keys.nextElement();
            if(StringUtils.isNotEmpty(str)&&str.contains(sheetcolumn+"[")){
                reportColumnNum++;
            }
        }
        return reportColumnNum;
    }
}

reportColumn.properties配置文件 

oneTimeCompletionPsResultList.fileName=#工艺结果.xlsx
oneTimeCompletionPsResultList.sheetName=工艺结果
oneTimeCompletionPsResultList.columnName[0].0=序号
oneTimeCompletionPsResultList.columnName[1].1=一级部件
oneTimeCompletionPsResultList.columnName[2].2=二级部件
oneTimeCompletionPsResultList.columnName[3].3=检查项
oneTimeCompletionPsResultList.columnName[4].4=检查标准

FasPsResultExcel实体

/**
 * @author wuyang
 * @date 2022年09月14日
 */
public class FasPsResultExcel {
    private String oneLevelPosition;
    private String twoLevelPosition;
    private String checkContent;
    private String checkOperationMethod;
    private String specification;
    private String safetyPrecautions;
    private String isCheckResult;
    private String isPhotograph;
    private String isRecord;
    private String checkResult;
    private String operateUserName;
    private String operateTime;
    private String record;
    private String photograph;

    public String getOneLevelPosition() {
        return oneLevelPosition;
    }

    public void setOneLevelPosition(String oneLevelPosition) {
        this.oneLevelPosition = oneLevelPosition;
    }

    public String getTwoLevelPosition() {
        return twoLevelPosition;
    }

    public void setTwoLevelPosition(String twoLevelPosition) {
        this.twoLevelPosition = twoLevelPosition;
    }

    public String getCheckContent() {
        return checkContent;
    }

    public void setCheckContent(String checkContent) {
        this.checkContent = checkContent;
    }

    public String getCheckOperationMethod() {
        return checkOperationMethod;
    }

    public void setCheckOperationMethod(String checkOperationMethod) {
        this.checkOperationMethod = checkOperationMethod;
    }

    public String getSpecification() {
        return specification;
    }

    public void setSpecification(String specification) {
        this.specification = specification;
    }

    public String getSafetyPrecautions() {
        return safetyPrecautions;
    }

    public void setSafetyPrecautions(String safetyPrecautions) {
        this.safetyPrecautions = safetyPrecautions;
    }

    public String getIsCheckResult() {
        return isCheckResult;
    }

    public void setIsCheckResult(String isCheckResult) {
        this.isCheckResult = isCheckResult;
    }

    public String getIsPhotograph() {
        return isPhotograph;
    }

    public void setIsPhotograph(String isPhotograph) {
        this.isPhotograph = isPhotograph;
    }

    public String getIsRecord() {
        return isRecord;
    }

    public void setIsRecord(String isRecord) {
        this.isRecord = isRecord;
    }

    public String getCheckResult() {
        return checkResult;
    }

    public void setCheckResult(String checkResult) {
        this.checkResult = checkResult;
    }

    public String getOperateUserName() {
        return operateUserName;
    }

    public void setOperateUserName(String operateUserName) {
        this.operateUserName = operateUserName;
    }

    public String getOperateTime() {
        return operateTime;
    }

    public void setOperateTime(String operateTime) {
        this.operateTime = operateTime;
    }

    public String getRecord() {
        return record;
    }

    public void setRecord(String record) {
        this.record = record;
    }

    public String getPhotograph() {
        return photograph;
    }

    public void setPhotograph(String photograph) {
        this.photograph = photograph;
    }
}

测试类

public static void main(String[] args) {
List<FasPsResultExcel> excelList = new ArrayList<>();
FasPsResultExcel excel = new FasPsResultExcel();
excel.setOneLevelPosition("一级");
excel.setTwoLevelPosition("二级");

excelList.add(excel);
ExportExcel.export(excelList,response,"oneTimeCompletionPsResultList");
}