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 協議》，轉載必須註明作者和本文連結