Excel导出工具类（简单易懂）

• Excel工具类

package com.szjzscx.aqgl.utils;

import com.bidb.core.entity.BaseEntity;
import com.szjzscx.aqgl.entity.aqjsjd.AqJsjdt1151Safetytechnicaldisclosure;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;
import org.springframework.web.bind.annotation.GetMapping;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;
@Component
public class FileUtil {
    public static String ROW_NUM = "RN_XLSX";
    /**
     * @author thenstro
     * 排放记录数据导出
     * @param response
     * @param fileName 文件名+内容标题
     * @param headers 列标题数组
     * @param titlesFields 每列对应属性名
     * @param records 导出的原生数据（若导出列包含父类属性应该在子类重新定义）
     * */
    public static void exportRecordsAsExcel(HttpServletResponse response, String fileName, String[] headers, String[] titlesFields, List<?> records) throws IOException {
        Assert.hasLength(fileName, "文件名不为空");
        if(headers == null || headers.length < 1 || titlesFields == null && titlesFields.length < 1 || records == null || headers.length != titlesFields.length){
            System.err.println("参数异常，请保证所有参数非空且标题数组与标题字段数组容量一致");
            return;
        }
        //工作簿excel
        XSSFWorkbook workbook = new XSSFWorkbook();
        //工作表(sheet)
        XSSFSheet sheet = workbook.createSheet("排放记录");
        //文件名
        String hStr = "";
        if(!fileName.contains(".xlsx")){
            hStr = fileName;
            fileName += ".xlsx";
        }else{
            hStr = fileName.split(".xlsx")[0];
        }
        int width = hStr.length() * 256;

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);

        //XSSFFont font = workbook.createFont();

        /*
         * 表头标题
         * */
        XSSFRow rowHeader = sheet.createRow(0);
        rowHeader.setHeight((short)1500);
        XSSFCell cellHeader = rowHeader.createCell(0);
        cellHeader.setCellValue(hStr);
        cellHeader.setCellStyle(cellStyle);

        //合并单元格CellRangeAddress r = new CellRangeAddress(startRow, endRow, startCol, endCol);
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, 4);
        sheet.addMergedRegion(range);

        //序号、垃圾排放量、垃圾类型、运输时间、运输成本
        XSSFRow rowTitle = sheet.createRow(1);
        for(int i = 0;i < headers.length;i++){
            XSSFCell cell = rowTitle.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        int rowNum = 2;
        for (Object r : records) {
            XSSFRow row1 = sheet.createRow(rowNum);
            /*row1.createCell(0).setCellValue(rowNum - 1);
            row1.createCell(1).setCellValue(r.getDcc022Value1() + r.getDcc022Value1Unit());
            row1.createCell(2).setCellValue(r.getDcc022Value3());
            row1.createCell(3).setCellValue(r.getDcc022Value2());
            row1.createCell(4).setCellValue(r.getDcc022Value4() + r.getDcc022Value4Unit());*/
            for(int i = 0; i < titlesFields.length; i ++){
                if(titlesFields[i].equals(ROW_NUM)){
                    row1.createCell(i).setCellValue(rowNum - 1);
                }else{
                    row1.createCell(i).setCellValue(getFieldValueByClass(r, titlesFields[i]));
                }
            }
            rowNum ++;
        }
        for (int k = 0; k < headers.length; k++) {
            sheet.autoSizeColumn(k);
        }
        //自动调整列宽
        setSizeColumn(sheet, headers.length);
        //response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName.toString(),"UTF-8"));
        response.addHeader("Access-Control-Expose-Headers", "Content-disposition");
        response.flushBuffer();
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
    }


    // excel列自适应宽度(中文支持)
    private static void setSizeColumn(XSSFSheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum ++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum ++) {
                XSSFRow currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    XSSFCell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == CellType.STRING.getCode()) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }
    /**
     * 属性取值
     * @param t 实例
     * @param field 取值字段
     * @return
     */
    private static String getFieldValueByClass(Object t, String field) {
        Class<?> aClass = t.getClass();
        try {
            Field f = aClass.getDeclaredField(field);
            f.setAccessible(true);
            return f.get(t).toString();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

}

• 接口调用

public void exportExcel(Search search, HttpServletResponse response) throws IOException {
        List<AqWzglt1451Emergencysupplies> list = service.findByFilters(search);
        String fileName = "应急物资导出"+ DateUtils.getCurrentDate().getTime();
        String [] headers = new String[]{"序号","单位名称","项目名称","应急物品名称","规格/型号","数量","单位","存放地点","保管人","编制","物资存放时间","备注"};
        String [] titleFields = new String[]{FileUtil.ROW_NUM,"tyCompanyname","tyProjectname","aqWzglc1451Itemname","aqWzglc1451Specifications","aqWzglc1451Quantity","aqWzglc1451Company","aqWzglc1451Storagelocation","aqWzglc1451Custodian","aqWzglc1451Organization","aqWzglc1451Storagetime","aqWzglc1451Remarks"};
        FileUtil.exportRecordsAsExcel(response,fileName,headers,titleFields,list);
    }

• 导出的Excel数据

总结：本人实测好用拿出来分享一下，欢迎各位大佬提建议。Thanks！