JAVA使用poi导出Excel，合并单元格，设置自动列宽

注：
1、poi导出Excel
2、合并单元，根据自己业务进行处理
3、设置自动列宽

ExportExcelUtils

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Class ExportExcelUtils
 * @Date 2020/7/17 16:40
 * @Description
 * @Author zp
 **/
public class ExportExcelUtils {

    /**
     * 封装 out 信息
     */
    public static void exportExcel(HSSFWorkbook workbook, int sheetNum,
                             String sheetTitle, String[] headers, List<List<String>> result,
                             OutputStream out, Integer maxColumnWidth, List<Integer> columnWidth) {

//        导出Excel的宽度系数
        int widthFactor = 256;

        //默认最大长度20个字
        if(maxColumnWidth==null){
            maxColumnWidth=20;
        }
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 14);
        font.setFontName("宋体");
        font.setBold(true);
        style.setFont(font);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);

        Map<Integer,Integer> maxWidth = new HashMap<>(headers.length);
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell((short) i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
            maxWidth.put(i,text.toString().getBytes().length*widthFactor+400);
        }
        // 遍历集合数据，产生数据行
        if (result != null) {
//            记录 需要合并的单元格
            List<Integer> hbList = new ArrayList<>();

            int index = 1;
            HSSFCellStyle styleContent = workbook.createCellStyle();
            // 设置这些样式
            HSSFFont fontContent = workbook.createFont();
            fontContent.setFontHeightInPoints((short) 12);
            fontContent.setFontName("宋体");
            styleContent.setFont(fontContent);
            styleContent.setWrapText(true);
            styleContent.setVerticalAlignment(VerticalAlignment.CENTER);
            styleContent.setAlignment(HorizontalAlignment.CENTER);

            for (List<String> m : result) {

//                当空值时，记录 需要合并的单元格
                if(StringUtils.isBlank(m.get(1))){
                    hbList.add(index);
                }

                row = sheet.createRow(index);
                int cellIndex = 0;
                for (String str : m) {

                    HSSFCell cell = row.createCell((short) cellIndex);
                    cell.setCellStyle(styleContent);
                    cell.setCellValue(str);
                    int length = 0;
                    if(StringUtils.isNotBlank(str)){
                        length = str.getBytes().length  * widthFactor;
                    }
                    length = length + 400;
                    if (length>(maxColumnWidth*(widthFactor*3))){
                        length = (maxColumnWidth*(widthFactor*3));
                    }
                    maxWidth.put(cellIndex,Math.max(length,maxWidth.get(cellIndex)));
                    cellIndex++;
                }

               index++;
            }

//            循环合并单元格
            if(CollectionUtils.isNotEmpty(hbList)){
                for (Integer integer : hbList){
                    CellRangeAddress region = new CellRangeAddress(integer, integer, 0, 6);
                    sheet.addMergedRegion(region);
                }
            }

        }
        for (int i= 0; i<headers.length;i++){
            if(CollectionUtils.isNotEmpty(columnWidth)){
                sheet.setColumnWidth(i,columnWidth.get(i)*(widthFactor*3));
            }else {
                sheet.setColumnWidth(i,maxWidth.get(i));
            }
        }
    }

}

Controller方法

@GetMapping(value = "export", name = "导出")
    public void export(Params params) {

//        取需要导出的数据，封装格式化，与下面titles对应
        List<List<String>> list = service.queryList(params);

        String[] titles = new String[]{"学校名称","学院名称","专业名称","班级名称","学号","学生名称","内容","时间"};
        String sheetName = "学生信息";

        String name = "学生信息";

        response.setHeader("Content-Disposition", "attachment;filename="+encodingFilename(name+".xlsx") );
        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");

        HSSFWorkbook workbook = new HSSFWorkbook();
        try {
            OutputStream out = response.getOutputStream();

            ExportExcelUtils.exportExcel(workbook, 0, sheetName,
                    titles, list, out,null,null);

            workbook.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

编码文件名，解决中文乱码问题
encodingFilename方法

    /**
     * 编码文件名，解决中文乱码问题
     * @param filename 原文件名
     * @return
     */
    private String encodingFilename(String filename) {
        String exploerType = request.getHeader("User-Agent");
        log.debug("浏览器类型：{}", exploerType);
        String newName;
        try {
            if (exploerType.contains("Trident")) {
                newName = java.net.URLEncoder.encode(filename, "utf-8");
            } else {
                newName = new String(filename.getBytes("utf-8"), "iso-8859-1");
            }
        } catch (UnsupportedEncodingException e) {
            log.warn("文件名编码失败，分配随机文件名");
            newName = UUID.randomUUID().toString();
        }
        return newName;
    }

本作品采用《CC 协议》，转载必须注明作者和本文链接