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

迷南。 2023-01-11 09:21 291阅读 0赞

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();
            }
        }

效果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70][]

第二种方式

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70]: /images/20221119/16d782e1a6004bc2a336081e75df5cda.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1]: /images/20221119/697997b12a6b4c7892b5d4865863ac1e.png