java poi 导出excal合并单元格并设置单元格样式_java导出excel给合并单元格添加样式-优快云博客

本文链接：https://blog.youkuaiyun.com/chendu500qiang/article/details/91520001

本文介绍了如何使用Java的POI库来导出Excel文件，并详细讲解了如何合并单元格以及设置单元格的样式，通过提供的工具类可以方便地实现Excel的定制化导出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、控制层
/**
     * @Author shenWB
     * @Date  11:06
     * @Param [response, sheetName]
     * @Return void
     * 导出Ecaxl
     */
//    @RequestMapping(value = "/exportExcel")
    @PostMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response,Integer typeNumber) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        creatSheet(wb,typeNumber);
        response.setContentType("application/binary;charset=UTF-8");
        ServletOutputStream out=response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("111.xls", "UTF-8"));

        wb.write(out);
        out.flush();
        out.close();
    }
    /**
     * @Author shenWB
     * @Date  11:08
     * @Param [wb, sheetName]
     * @Return void
     * 获取导出数据 这里不再多说自己根据自己业务来
     */
    public void creatSheet(HSSFWorkbook wb,Integer typeNumber){
        List<TodayCount> todayCount = todayCountService.getTodayCountList(typeNumber);
        ExcalDownloadUtil.creatSheet(wb,todayCount);
    }

2、导出excal工具类

/**
 * @ClassName ExcalDownloadUtil
 * @Description TODO
 * @Author shenWB
 * @Date 2019/5/31 9:19
 * @Version 1.0
 **/
public class ExcalDownloadUtil {
    public static HSSFWorkbook creatSheet(HSSFWorkbook wb, List<TodayCount> todayCount){
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet();//新建sheet页
        //========
        HSSFCellStyle jz = wb.createCellStyle();//新建单元格样式
        jz.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        jz.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        jz.setWrapText(true);//自动换行
        //========
        // 设置列宽
        for(int i = 0 ; i<=21;i++){
            if (i==0){
                sheet.setColumnWidth((short) i, (short) 4500);
            }else{
                sheet.setColumnWidth((short) i, (short) 3400);
            }
        }
        HSSFRow row0=sheet.createRow(0);//第一行
        row0.setHeight((short) ((short) 30*20));//设置行高
        HSSFCell cellTitle=row0.createCell(0); //创建单元格
        HSSFCellStyle styleTitle = creatStyle(wb, "黑体", 20, HSSFCellStyle.ALIGN_CENTER,false,true);//设置单元格样式
        cellTitle.setCellStyle(styleTitle);
        cellTitle.setCellValue("今日各单位清单数量情况汇总表");//设置单元格内容
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,21));//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列

        //第二行
        // poi做多行合并，一定需要先绘制单元格，然后写入数据，最后合并，不然各种坑啊
        HSSFRow row1 =sheet.createRow(1);
        row1.setHeight((short) 300);//设置行高
        HSSFCell cell1_0 = row1.createCell(0);//列
        cell1_0.setCellValue("区化");
        cell1_0.setCellStyle(cellStyle(wb));//背景色

        HSSFCell cell1_1 = row1.createCell(1);
        cell1_1.setCellValue("单位名称");
        cell1_1.setCellStyle(cellStyle(wb));//背景色

        HSSFCell cell1_2 = row1.createCell(2);
        cell1_2.setCellValue("总目录数量");
        cell1_2.setCellStyle(cellStyle(wb));//背景色

        HSSFCell cell1_3 = row1.createCell(3);
        sheet.addMergedRegion(new CellRangeAddress(1,1,2,3));
        cell1_3.setCellStyle(cellStyle(wb));//背景色

        HSSFCell cell1_4 = row1.create