POI导出Excel合并单元格及设置单元样式解决合并单元格显示全部框线

最新推荐文章于 2024-04-29 10:28:47 发布

蜀山雪松

最新推荐文章于 2024-04-29 10:28:47 发布

阅读量2.7k

点赞数 2

分类专栏：话说业务系统功能设计日常工具类文章标签： poi Excel 单元格合并合并单元格边框线显示全

本文链接：https://blog.csdn.net/jianxia801/article/details/108492152

版权

话说业务系统功能设计同时被 2 个专栏收录

21 篇文章 1 订阅

订阅专栏

日常工具类

15 篇文章 1 订阅

订阅专栏

1、使用场景

最近因参与海外项目，要求实现一个复杂的Excel导出功能。里面涉及到多处单元格合并。具体格式如下图所示

首先1-4行要求合并单元格，其中第5行第5，6列也要求合并；同时后面的列是可变化的。于是本人设计1-5行的1-6列是固定的。

同时在实践过程之中可以设置单元字体和样式。本项目使用POI3.17版本实现

2、代码实现

2.1、关键代码

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public static void main(String[] args) throws IOException {
        //创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建SHEET页
        HSSFSheet sheet = workbook.createSheet("sheet");
        //创建行，从0开始
        HSSFRow row0 = sheet.createRow(0);
        //创建列，从0开始
        HSSFCell cell00 = row0.createCell(0);
        
        HSSFCellStyle cellStyle =workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //加边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        //cellStyle.setFillForegroundColor((short)9);

        HSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("楷体");
        font.setFontHeightInPoints((short)14);//设置字号
        font.setItalic(false);//设置是否为斜体
        font.setBold(true);//设置是否加粗
        font.setColor(IndexedColors.BLACK1.index);//设置字体颜色
        cellStyle.setFont(font);
        
        //设置背景
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        //设置自动换行
        cellStyle.setWrapText(true);

        //居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        //设置宽度和高度
        row0 .setHeightInPoints(30);//设置行的高度
        sheet.setColumnWidth(0, 20 * 256);//设置列的宽度

        //渲染单元格
        cell00.setCellStyle(cellStyle);
        
        //单元格合并操作
        //设置第1行左边单元格合并
        CellRangeAddress gradeItemRegion = new CellRangeAddress(0, 0, 0, 5);
        sheet.addMergedRegion(gradeItemRegion);
        // 补充下面的操作，可以显示全部框线
        RegionUtil.setBorderBottom(BorderStyle.THIN, gradeItemRegion, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, gradeItemRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, gradeItemRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, gradeItemRegion, sheet);
        
}

2.2 完整代码

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class GradeUserExportCsvTest {

    public static List<String[]> initDynamicColumnHeadList(){
        List<String[]> headList=new ArrayList<>();
        String outOf100="out of 100";
        String[] headColumnItem0={"My team are great...","Discussion","1-Feb-20","Exercise(10%)",outOf100};
        String[] headColumnItem1={"Connecting with team...(offline)","Assignment","1-Feb-20","Exercise(10%)",outOf100};
        String[] headColumnItem2={"Effective Teams...","Assignment","1-Mar-20","Exercise(0%)",outOf100};
        String[] headColumnItem3={"Discussion after watcthing...","Discussion","1-Apr-20","Exercise(10%)",outOf100};
        String[] headColumnItem4={"Building effective teams quiz","Quiz","1-Jul-20","Exercise(10%)",outOf100};
        String[] headColumnItem5={"Test3","Quiz","1-Jul-22","Exercise(0%)",outOf100};
        String[] headColumnItem6={"final exammination","Quiz","1-Jul-23","Exercise(200%)",outOf100};
        headList.add(headColumnItem0);
        headList.add(headColumnItem1);
        headList.add(headColumnItem2);
        headList.add(headColumnItem3);
        headList.add(headColumnItem4);
        headList.add(headColumnItem5);
        headList.add(headColumnItem6);
        return headList;
    }
    public static void main(String[] args) throws IOException {
        List<String[]> headList=initDynamicColumnHeadList();
        HSSFWorkbook workbook = new HSSFWorkbook();

        HSSFCellStyle cellStyle =workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //加边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        //cellStyle.setFillForegroundColor((short)9);

        HSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("楷体");
        font.setFontHeightInPoints((short)14);//设置字号
        font.setItalic(false);//设置是否为斜体
        font.setBold(true);//设置是否加粗
        font.setColor(IndexedColors.BLACK1.index);//设置字体颜色
        cellStyle.setFont(font);

        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        //设置自动换行
        cellStyle.setWrapText(true);
        
        HSSFSheet sheet = workbook.createSheet("sheet");

        HSSFRow row0 = sheet.createRow(0);
        HSSFCell cell00 = row0.createCell(0);
        cell00.setCellStyle(cellStyle);
        cell00.setCellValue("gradeItemRegion");
        for(int i=0;i<headList.size();i++){
            HSSFCell cell01 = row0.createCell(6+i);
            cell01.setCellStyle(cellStyle);
            String[] columnItemArray= headList.get(i);
            cell01.setCellValue(columnItemArray[0]);
        }
        HSSFRow row1 = sheet.createRow(1);
        HSSFCell cell10 = row1.createCell(0);
        cell10.setCellStyle(cellStyle);
        cell10.setCellValue("typeRegion");
        for(int i=0;i<headList.size();i++){
            HSSFCell cell11 = row1.createCell(6+i);
            cell11.setCellStyle(cellStyle);
            String[] columnItemArray= headList.get(i);
            cell11.setCellValue(columnItemArray[1]);
        }
        HSSFRow row2 = sheet.createRow(2);
        HSSFCell cell20 = row2.createCell(0);
        cell20.setCellStyle(cellStyle);
        cell20.setCellValue("dueRegion");
        for(int i=0;i<headList.size();i++){
            HSSFCell cell21 = row2.createCell(6+i);
            cell21.setCellStyle(cellStyle);
            String[] columnItemArray= headList.get(i);
            cell21.setCellValue(columnItemArray[2]);
        }

        HSSFRow row3 = sheet.createRow(3);
        HSSFCell cell30 = row3.createCell(0);
        cell30.setCellStyle(cellStyle);
        cell30.setCellValue("calculatedWeightRegion");

        for(int i=0;i<headList.size();i++){
            HSSFCell cell31 = row3.createCell(6+i);
            cell31.setCellStyle(cellStyle);
            String[] columnItemArray= headList.get(i);
            cell31.setCellValue(columnItemArray[3]);
        }

        HSSFRow row4 = sheet.createRow(4);
        HSSFCell cell40 = row4.createCell(0);
        cell40.setCellStyle(cellStyle);
        cell40.setCellValue("Students(7)");
        HSSFCell cell41 = row4.createCell(1);
        cell41.setCellStyle(cellStyle);
        cell41.setCellValue("Missing(3)");
        HSSFCell cell42 = row4.createCell(2);
        cell42.setCellStyle(cellStyle);
        cell42.setCellValue("Done Late(4)");

        HSSFCell cell43 = row4.createCell(3);
        cell43.setCellStyle(cellStyle);
        cell43.setCellValue("Ungraded(2)");

        HSSFCell cell45 = row4.createCell(4);
        cell45.setCellStyle(cellStyle);
        cell45.setCellValue("overallRegion");

        for(int i=0;i<headList.size();i++){
            HSSFCell cell46 = row4.createCell(6+i);
            cell46.setCellStyle(cellStyle);
            String[] columnItemArray= headList.get(i);
            cell46.setCellValue(columnItemArray[4]);
        }

        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
        // 行和列都是从0开始计数，且起始结束都会合并
        // 这里是合并excel中日期的两行为一行

        //设置第1行左边单元格合并
        CellRangeAddress gradeItemRegion = new CellRangeAddress(0, 0, 0, 5);
        sheet.addMergedRegion(gradeItemRegion);
        // 补充下面的操作，可以显示全部框线
        RegionUtil.setBorderBottom(BorderStyle.THIN, gradeItemRegion, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, gradeItemRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, gradeItemRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, gradeItemRegion, sheet);

        CellRangeAddress typeRegion = new CellRangeAddress(1, 1, 0, 5);
        sheet.addMergedRegion(typeRegion);
        // 补充下面的操作，可以显示全部框线
        RegionUtil.setBorderBottom(BorderStyle.THIN, typeRegion, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, typeRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, typeRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, typeRegion, sheet);

        CellRangeAddress dueRegion = new CellRangeAddress(2, 2, 0, 5);
        sheet.addMergedRegion(dueRegion);
        // 补充下面的操作，可以显示全部框线
        RegionUtil.setBorderBottom(BorderStyle.THIN, dueRegion, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, dueRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, dueRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, dueRegion, sheet);

        CellRangeAddress calculatedWeightRegion = new CellRangeAddress(3, 3, 0, 5);
        sheet.addMergedRegion(calculatedWeightRegion);
        // 补充下面的操作，可以显示全部框线
        RegionUtil.setBorderBottom(BorderStyle.THIN, calculatedWeightRegion, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, calculatedWeightRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, calculatedWeightRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, calculatedWeightRegion, sheet);

        CellRangeAddress overallRegion = new CellRangeAddress(4, 4, 4, 5);
        sheet.addMergedRegion(overallRegion);
        // 补充下面的操作，可以显示全部框线
        RegionUtil.setBorderBottom(BorderStyle.THIN, overallRegion, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, overallRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, overallRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, overallRegion, sheet);
        sheet.setColumnWidth(0, 20 * 256);//设置列的宽度
        sheet.setColumnWidth(1, 20 * 256);//设置列的宽度
        sheet.setColumnWidth(2, 20 * 256);//设置列的宽度
        sheet.setColumnWidth(3, 20 * 256);//设置列的宽度
        sheet.setColumnWidth(4, 20 * 256);//设置列的宽度
        sheet.setColumnWidth(5, 20* 256);//设置列的宽度

        sheet.setColumnWidth(6, 40* 256);//设置列的宽度
        sheet.setColumnWidth(7, 40* 256);//设置列的宽度
        sheet.setColumnWidth(8, 40* 256);//设置列的宽度
        sheet.setColumnWidth(9, 40* 256);//设置列的宽度
        sheet.setColumnWidth(10,40* 256);//设置列的宽度
        sheet.setColumnWidth(11,40* 256);//设置列的宽度
        sheet.setColumnWidth(12,40* 256);//设置列的宽度

        //CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
        //sheet.setColumnWidth(0,  30* 256);//设置列的宽度
        File file = new File("d:/demo.xlsx");
        FileOutputStream fout = new FileOutputStream(file);
        workbook.write(fout);
        fout.close();
    }
}

3、成果展现

4、总结

因本组其他同事处理这种复杂表格经验欠缺，实现起来有畏难心里。个人总结解决问题主要是要求思路和方法。思路对了，解决方法可以网上方案。同时也需要把一个问题拆分几个模块分步来解决。

5、参考文章

POI 3.17 导出样式（与3.9方式不同）

java操作excel合并单元格，出现框线显示不全

java poi 导出excal合并单元格并设置单元格样式

蜀山雪松

关注

2
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
POI导出Excel合并单元格及设置单元样式解决合并单元格显示全部框线

目录1、使用场景2、代码实现2.1、关键代码2.2 完整代码3、成果展现4、总结5、参考文章1、使用场景最近因参与海外项目，要求实现一个复杂的Excel导出功能。里面涉及到多处单元格合并。具体格式如下图所示首先1-4行要求合并单元格，其中第5行第5，6列也要求合并；同时后面的列是可变化的。于是本人设计1-5行的1-6列是固定的。同时在实践过程之中可以设置单元字体和样式。本项目使用POI3.17版本实现2、代码实现2.1、关键代...
复制链接

扫一扫