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

﹏ヽ暗。殇╰゛Y 2023-03-01 06:14 26阅读 0赞

注：  
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;
        }