POI导出excel单元格宽度自适应

package com.tgpms.util;

import cn.hutool.core.util.StrUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author jiangli
 * @since 2021/1/26 17:42
 */
public class ExportExcelUtil {

    public static void downloadExcel(String excelName, List<String> tableHead, List<LinkedList<String>> tableBody, HttpServletResponse response) throws IOException {
        // 1:创建一个workbook
        HSSFWorkbook workbook = new HSSFWorkbook();

        // 创建样式
        HSSFCellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style.setBorderTop((short) 1);
        style.setBorderBottom((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);
        style.setWrapText(true);

        // 设置合计样式
        HSSFCellStyle style1 = workbook.createCellStyle();
        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
        style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style1.setBorderTop((short) 1);
        style1.setBorderBottom((short) 1);
        style1.setBorderLeft((short) 1);
        style1.setBorderRight((short) 1);
        style.setWrapText(true);

        HSSFSheet sheet = (HSSFSheet) workbook.createSheet(excelName);

//        // 2：合并单元格，表头。并设置值
//        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, tableHead.size() - 1);
//        sheet.addMergedRegion(cra);
//        HSSFRow row = sheet.createRow(0);
//        HSSFCell tableName = row.createCell(0);
//        tableName.setCellStyle(style);
//        tableName.setCellValue(excelName);

        //存储最大列宽
        Map<Integer,Integer> maxWidth = new HashMap<>();

        // 3：设置表head
        HSSFRow row1 = sheet.createRow(0);
        for (int i = 0; i < tableHead.size(); i++) {
            Cell createCell = row1.createCell(i);
            createCell.setCellValue(tableHead.get(i));
            createCell.setCellStyle(style);
            maxWidth.put(i,createCell.getStringCellValue().getBytes().length  * 256 + 200);
        }

        // 4：表格内容
        for (int i = 0; i < tableBody.size(); i++) {
            HSSFRow rows = sheet.createRow(i + 1);
            int j = 0;
            LinkedList<String> linkedList = tableBody.get(i);
            for (String s : linkedList) {
                HSSFCell createCell = rows.createCell(j);
                if(StrUtil.isNotBlank(s)){
                    createCell.setCellValue(s);
                }else{
                    createCell.setCellValue("");
                }
                int length = createCell.getStringCellValue().getBytes().length  * 256 + 200;
                //这里把宽度最大限制到15000
                if (length>15000){
                    length = 15000;
                }
                maxWidth.put(j,Math.max(length,maxWidth.get(j)));
                j++;
                createCell.setCellStyle(style1);
            }
        }

        // 列宽自适应
        for (int i = 0; i < tableHead.size(); i++) {
            sheet.setColumnWidth(i,maxWidth.get(i));
        }

        // 5：设置头
        response.setHeader("Content-disposition",
                "attachment; filename=" + new String(excelName.getBytes("GB2312"), "ISO8859-1") + ".xls");
        // 6：设置头类型
        response.setContentType("application/vnd.ms-excel");

        // 7：写出
        OutputStream toClient = response.getOutputStream();
        workbook.write(toClient);
        toClient.flush();
        toClient.close();
    }
}

    public void exportSummaryInfo(HealthQueryParam healthQueryParam, HttpServletResponse response) {
        ManagementSysuser managementSysuser = LoginInterceptor.loginUser.get();
        if (managementSysuser == null) {
            throw new ProjectException("请先登录");
        }
        String deptId = managementSysuser.getDeptId();
        ManagementDept dept = deptMapper.selectById(deptId);
        if (healthQueryParam.getPageNo() != null && healthQueryParam.getSize() != null) {
            PageHelper.startPage(healthQueryParam.getPageNo(), healthQueryParam.getSize());
        }
        List<HealthSummary> list = mapper.selectList(new LambdaQueryWrapper<HealthSummary>().eq(StrUtil.isNotBlank(healthQueryParam.getOpenId()), HealthSummary::getOpenId, healthQueryParam.getOpenId())
                .like(StrUtil.isNotBlank(healthQueryParam.getIdCard()), HealthSummary::getIdCard, healthQueryParam.getIdCard())
                .like(StrUtil.isNotBlank(healthQueryParam.getPhone()), HealthSummary::getPhone, healthQueryParam.getPhone())
                .like(StrUtil.isNotBlank(healthQueryParam.getPeopleName()), HealthSummary::getPeopleName, healthQueryParam.getPeopleName())
                .like(StrUtil.isNotBlank(healthQueryParam.getMainContractor()), HealthSummary::getMainContractor, healthQueryParam.getMainContractor())
                .eq(healthQueryParam.getProfileAuditing() != null, HealthSummary::getProfileAuditing, healthQueryParam.getProfileAuditing())
                .eq(healthQueryParam.getHealthAuditing() != null, HealthSummary::getHealthAuditing, healthQueryParam.getHealthAuditing())
                .eq(!"-1".equals(dept.getPId()), HealthSummary::getMainContractor, dept.getId())
                .ge(healthQueryParam.getInTimeStart() != null, HealthSummary::getBhtTime, healthQueryParam.getInTimeStart())
                .le(healthQueryParam.getInTimeEnd() != null, HealthSummary::getBhtTime, healthQueryParam.getInTimeEnd())
                .ge(healthQueryParam.getOutTimeStart() != null, HealthSummary::getOutTime, healthQueryParam.getOutTimeStart())
                .le(healthQueryParam.getOutTimeEnd() != null, HealthSummary::getOutTime, healthQueryParam.getOutTimeEnd())
                .eq(HealthSummary::getDeleted, 0)
                .orderByDesc(HealthSummary::getInsertDate));

        list.forEach(e -> {
            // 部门
            ManagementDept managementDept = deptMapper.selectById(e.getMainContractor());
            e.setMainContractorCN(managementDept.getDName());
            e.setProfileAuditingCN(e.getProfileAuditing() == 0 ? "审核中" : (e.getProfileAuditing() == 1 ? "审核已通过" : "驳回"));
            e.setHealthAuditingCN(e.getHealthAuditing() == 0 ? "审核中" : (e.getHealthAuditing() == 1 ? "审核已通过" : "驳回"));
        });

        List<String> tableHead = Arrays.asList("序号", "单位", "部门/分包单位", "姓名", "性别", "年龄", "身份证号码", "返岗前住址", "联系电话", "返岗前核酸检测时间", "返岗前核酸检测结果",
                "返岗前健康码状态", "返岗前体温", "返岗时间", "返岗乘坐交通工具", "交通工具时间班次车牌号", "到达白鹤滩工地时间", "白鹤滩工地居住地", "到达后核酸检测时间", "到达后核酸检测结果", "到达后健康码状态",
                "到达后体温", "是否乘坐非正规交通工具", "是否独自返乡,同行人员姓名", "同行人员住址", "同行人员联系电话", "是否有发热、咳嗽", "公司责任人", "项目责任人", "劳务责任人", "备注",
                "员工个人信息审核状态","员工返岗信息审核状态");
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        List<LinkedList<String>> tableBody = new LinkedList<>();
        for (int i = 0; i < list.size(); i++) {
            LinkedList<String> linkedList = new LinkedList<>();
            linkedList.add(String.valueOf(i+1));
            linkedList.add(list.get(i).getMainContractorCN());
            linkedList.add(list.get(i).getSubContractor());
            linkedList.add(list.get(i).getPeopleName());
            linkedList.add(list.get(i).getSex() == 0 ? "男" : "女");
            linkedList.add(String.valueOf(list.get(i).getAge()));
            linkedList.add(list.get(i).getIdCard());
            linkedList.add(list.get(i).getReturnAddress());
            linkedList.add(list.get(i).getPhone());
            linkedList.add(list.get(i).getReturnCheckTime() != null ? format.format(list.get(i).getReturnCheckTime()) : "");
            linkedList.add(list.get(i).getReturnCheckResult());
            linkedList.add(list.get(i).getReturnHealthCode());
            linkedList.add(list.get(i).getReturnBodyTemperature());
            linkedList.add(list.get(i).getReturnTime() != null ? format.format(list.get(i).getReturnTime()) : "");
            linkedList.add(list.get(i).getReturnTransportation());
            linkedList.add(list.get(i).getReturnTransportationNo());
            linkedList.add(list.get(i).getBhtTime() != null ? format.format(list.get(i).getBhtTime()) : "");
            linkedList.add(list.get(i).getBhtAddress());
            linkedList.add(list.get(i).getBhtCheckTime() != null ? format.format(list.get(i).getBhtCheckTime()) : "");
            linkedList.add(list.get(i).getBhtCheckResult());
            linkedList.add(list.get(i).getHealthCode());
            linkedList.add(list.get(i).getBodyTemperature());
            linkedList.add(list.get(i).getInformalTransportation());
            linkedList.add("是");
            linkedList.add("无");
            linkedList.add("无");
            linkedList.add(list.get(i).getFever() == 0 ? "否" : "是");
            linkedList.add(list.get(i).getCompanyOwner());
            linkedList.add(list.get(i).getProjectOwner());
            linkedList.add(list.get(i).getServiceOwner());
            linkedList.add(list.get(i).getRemark());
            linkedList.add(list.get(i).getProfileAuditingCN());
            linkedList.add(list.get(i).getHealthAuditingCN());
            tableBody.add(linkedList);
        }
        try {
            ExportExcelUtil.downloadExcel("员工返岗健康信息", tableHead, tableBody, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

效果

 

第二种方式

		// 设置自动列宽
		for (int i = 0; i < headers.length; i++) {
			sheet.autoSizeColumn(i);
			sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 2);
		}