如何将POIExcel模板进行导出

最新推荐文章于 2023-07-11 10:48:45 发布

雨会停rain

最新推荐文章于 2023-07-11 10:48:45 发布

阅读量807

点赞数

文章标签： eureka 蓝桥杯 java

本文链接：https://blog.csdn.net/wang20010104/article/details/124693075

版权

1.简单导出excel

1.导入POI的依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>

2.编写需要导出的excel的实体类

@Data
public class EventDetailExport extends DisEventInfoModel {

    //申请退回人所在部门
    private String returnApplyDeptName;
    //申请退回时间
    private Date returnApplyTime;
    //申请退回原因
    private String returnApplyReason;
    //审批人名称
    private String returnReplierName;
    //审批时间
    private Date returnReplyTime;
    //审批状态（退回状态 31 驳回 32同意 33待审批）
    private String returnState;

}

3.编写POIExcelUtils工具类(直接复制使用即可，这是编写的工具类)


/**
 * @author wangli
 * @data 2022/5/10 16:36
 * @Description:
 */

import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class POIExcelUtils {
    private static final String excel2003L = ".xls";
    private static final String excel2007U = ".xlsx";

    public POIExcelUtils() {
    }

    public static HSSFWorkbook createExcel(String tableTitle, String[] tableHeads, String[] columns, int[] widths, List<? extends Object> datas) throws Exception {
        int rowIndex = 0;
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet();
        int size;
        int i;
        if (widths != null && widths.length > 0) {
            for(size = 0; size < widths.length; ++size) {
                sheet.setColumnWidth(size, widths[size] * 256);
            }
        } else {
            if (columns != null) {
                size = columns.length;
            } else if (tableHeads != null) {
                size = tableHeads.length;
            } else {
                size = 0;
            }

            for(i = 0; i < size; ++i) {
                sheet.setColumnWidth(i, 7680);
            }
        }

        HSSFRow headRow;
        HSSFCellStyle dataCellStyle;
        if (StringUtils.isNotBlank(tableTitle)) {
            dataCellStyle = getTitleCellStyle(hssfWorkbook);
            headRow = sheet.createRow(rowIndex++);
            HSSFCell titleCell = headRow.createCell(0);
            titleCell.setCellValue(tableTitle);
            titleCell.setCellType(1);
            int currRowIndex = titleCell.getRowIndex();
            CellRangeAddress cellRangeAddress = new CellRangeAddress(currRowIndex, currRowIndex, 0, tableHeads.length - 1);
            sheet.addMergedRegion(cellRangeAddress);
            titleCell.setCellStyle(dataCellStyle);
        }

        if (tableHeads != null && tableHeads.length > 0) {
            dataCellStyle = getHeadCellStyle(hssfWorkbook);
            headRow = sheet.createRow(rowIndex++);

            for(i = 0; i < tableHeads.length; ++i) {
                HSSFCell headCell = headRow.createCell(i);
                headCell.setCellValue(tableHeads[i]);
                headCell.setCellType(1);
                headCell.setCellStyle(dataCellStyle);
            }
        }

        if (datas != null && !datas.isEmpty()) {
            dataCellStyle = getDataCellStyle(hssfWorkbook);

            for(i = 0; i < datas.size(); ++i) {
                HSSFRow dataRow = sheet.createRow(rowIndex++);
                Object data = datas.get(i);
                int j;
                HSSFCell dataCell;
                if (data instanceof Map) {
                    Map<String, Object> dataMap = (Map)data;

                    for(j = 0; j < columns.length; ++j) {
                        dataCell = dataRow.createCell(j);
                        dataCell.setCellValue(objectToString(dataMap.get(columns[j])));
                        dataCell.setCellType(1);
                        dataCell.setCellStyle(dataCellStyle);
                    }
                } else if (data instanceof List) {
                    List<List<String>> dataList = (List)data;

                    for(j = 0; j < dataList.size(); ++j) {
                        dataCell = dataRow.createCell(j);
                        dataCell.setCellValue(objectToString(dataList.get(j)));
                        dataCell.setCellType(1);
                        dataCell.setCellStyle(dataCellStyle);
                    }
                } else {
                    for(j = 0; j < columns.length; ++j) {
                        dataCell = dataRow.createCell(j);
                        Method readMethod = (new PropertyDescriptor(columns[j], data.getClass())).getReadMethod();
                        Object value = readMethod.invoke(data);
                        dataCell.setCellValue(objectToString(value));
                        dataCell.setCellType(1);
                        dataCell.setCellStyle(dataCellStyle);
                    }
                }
            }
        }
        return hssfWorkbook;
    }
    public static void writeExcelToResponse(HSSFWorkbook excel, HttpServletResponse response, String fileName) throws Exception {
        response.setContentType("application/force-download");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "utf-8"));
        excel.write(response.getOutputStream());
        response.flushBuffer();
    }
    public static HSSFCellStyle getTitleCellStyle(HSSFWorkbook hssfWorkbook) {
        HSSFFont hssfFont = hssfWorkbook.createFont();
        hssfFont.setBoldweight((short)700);
        hssfFont.setFontName("宋体");
        hssfFont.setFontHeightInPoints((short)12);
        HSSFCellStyle titleCellStyle = hssfWorkbook.createCellStyle();
        titleCellStyle.setAlignment((short)2);
        titleCellStyle.setFont(hssfFont);
        return titleCellStyle;
    }
    public static String objectToString(Object obj) {
        return obj instanceof Date ? parseDateToString((Date)obj) : StringUtils.valueOf(obj);
    }
    public static String parseDateToString(long milliseconds) {
        return parseDateToString(new Date(milliseconds));
    }

    public static String parseDateToString(Date date) {
        if (date == null) {
            return null;
        } else {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYMMDDHHMMSS");
            return simpleDateFormat.format(date);
        }
    }

    public static HSSFCellStyle getHeadCellStyle(HSSFWorkbook hssfWorkbook) {
        HSSFFont hssfFont = hssfWorkbook.createFont();
        hssfFont.setBoldweight((short)700);
        hssfFont.setFontName("宋体");
        hssfFont.setFontHeightInPoints((short)11);
        HSSFCellStyle hearCellStyle = hssfWorkbook.createCellStyle();
        hearCellStyle.setBorderBottom((short)1);
        hearCellStyle.setBorderLeft((short)1);
        hearCellStyle.setBorderTop((short)1);
        hearCellStyle.setBorderRight((short)1);
        hearCellStyle.setFont(hssfFont);
        return hearCellStyle;
    }
    public static HSSFCellStyle getDataCellStyle(HSSFWorkbook hssfWorkbook) {
        HSSFFont hssfFont = hssfWorkbook.createFont();
        hssfFont.setFontName("宋体");
        hssfFont.setFontHeightInPoints((short)11);
        HSSFCellStyle dataCellStyle = hssfWorkbook.createCellStyle();
        dataCellStyle.setBorderBottom((short)1);
        dataCellStyle.setBorderLeft((short)1);
        dataCellStyle.setBorderTop((short)1);
        dataCellStyle.setBorderRight((short)1);
        dataCellStyle.setAlignment((short)1);
        dataCellStyle.setVerticalAlignment((short)1);
        dataCellStyle.setWrapText(true);
        dataCellStyle.setFont(hssfFont);
        DataFormat format = hssfWorkbook.createDataFormat();
        dataCellStyle.setDataFormat(format.getFormat("@"));
        return dataCellStyle;
    }
}

4.编写导出方法integratedQueryExport

    @Override
    @SystemLog(descrption = "综合查询案件列表导出", type = "查询", systemCode = Constants.SYSTEM_MODELCODE_CORE, modelName = "事件分拨")
    @PostMapping("/integratedQueryExport")
    @ApiOperation(value = "综合查询案件列表导出", notes = "综合查询案件列表导出", response = StandardResult.class)
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "form", name = "instanceIds", value = "流程实例ID，逗号分隔", required = false, dataType = "String", defaultValue = "4850,4849"),
            @ApiImplicitParam(paramType = "form", name = "searchConditionJson", value = "查询条件", required = false, dataType = "String", defaultValue = SearchCondition.JSON_EXAMPLE),
            @ApiImplicitParam(paramType = "form", name = "accessToken", value = "令牌", required = true, dataType = "String") })
    public StandardResult integratedQueryExport(String instanceIds, String searchConditionJson, HttpServletResponse response) {
        try{
            //添加退回信息
            List<EventDetailExport> exports = disEventInfoService.findLastBackRecord(detailInfos);



            // 组装 excel
            String tableTitle = "综合查询";
            String[] tableHeads = new String[] {"案件公文号","id", "环节名称","案件类型","案件来源", "上报时间", "案件描述",
                    "地址编码", "事件发生地址","所属社区", "上报人", "联系方式","规定完成时间","处置单位","处置结果","办结时间","在线办结原因",
                    "申请退回单位","申请时间","申请理由","审批人","审批时间","审批结果"
            };
            // 处置结果暂用 remark 字段存储21
            String[] columns = new String[] {"caseNumber","id","linkName", "eventTypeStr","eventSource", "createTime", "eventContent",
                    "eventAddressCode", "eventAddress", "communityName","reportName", "reportPhone", "eventRegCompleteTime","deptName","disposeMsg","closingTime","directlyCloseReason",
                    "returnApplyDeptName","returnApplyTime","returnApplyReason","returnReplierName","returnReplyTime","returnState"
            };
            int[] widths = new int[] { 20,15, 15, 30, 15, 20, 40, 30, 40, 15, 10, 15, 20, 20, 25, 20, 20, 20,20,20,20,20 };
            HSSFWorkbook excel = POIExcelUtils.createExcel(tableTitle, tableHeads, columns, widths, exports);
            // 将 excel 写入 response 并设置响应头
            POIExcelUtils.writeExcelToResponse(excel, response, "综合查询案件列表.xls");
            return null;
        } catch (Exception e) {
            logger.error("异常信息:{}", e);
            return PaginationResult.faild("异常信息:" + e.getClass().getName());
        }
    }