java 使用POI画出复杂表格1-表头

最新推荐文章于 2023-11-28 10:10:27 发布
土耳其的烧烤
最新推荐文章于 2023-11-28 10:10:27 发布
阅读量551
点赞数
分类专栏： java
本文链接：https://blog.csdn.net/ywj776199845/article/details/117590853
版权
Apache POI Excel Java 单元格合并数据导出
关键词由CSDN通过智能技术生成
java 专栏收录该内容
59 篇文章 0 订阅
订阅专栏
有三处合并单元格被注释，打开后就是另一种效果请看博客2
package tdh.platform.ofc.admin.interceptor;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.stereotype.Service;

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

/**
 * <pre>
 * Description:
 * Copyright:	Copyright (c)2021
 * Company:		上海天地
 * Author:		weijie.yang
 * Version:		1.0
 * Created at:	2021/6/4 16:30
 * </pre>
 */
@Service
public class VV {


    public static void main(String[] args) throws IOException {
        List<TAQIDataReport> list = new ArrayList<>();


        for (int i = 0; i < 10; i++) {
            TAQIDataReport obj = new TAQIDataReport();
            obj.setCity("city"+i);
            obj.setAdd("add"+i);
            obj.setSo2Concentration("so2Concentration"+i);
            obj.setSo2Subindex("so2Subindex"+i);
            obj.setNo2Concentration("no2Concentration"+i);
            obj.setNo2Subindex("no2Subindex"+i);
            obj.setPm10Concentration("pm10Concentration"+i);
            obj.setPm10Subindex("pm10Subindex"+i);
            obj.setCoConcentration("coConcentration"+i);
            obj.setCoSubindex("coSubindex"+i);
            obj.setO3Concentration("o3Concentration"+i);
            obj.setP3Subindex("p3Subindex"+i);
            obj.setPm25Concentration("pm25Concentration"+i);
            obj.setAdd("add"+i);
            obj.setAdd("add"+i);
            obj.setAdd("add"+i);
            obj.setAdd("add"+i);

            obj.setAdd("add"+i);
            list.add(obj);

        }

        HSSFWorkbook workbook = export(list);
        FileOutputStream output=new FileOutputStream("d:\\workbook.xls");
        workbook.write(output);
        output.flush();

    }

    public static HSSFWorkbook export(List<TAQIDataReport> list) {

        // 声明String数组，并初始化元素（表头名称）
        //第一行表头字段，合并单元格时字段跨几列就将该字段重复几次
        String[] excelHeader0 =
                {
                "线路", "出发城市", "出发区域", "到达城市", "到达区域","区——区(单边)", "规划里程",//非合并区域
                "资源配置","资源配置","资源配置",

                "签约客户本月业务","签约客户本月业务","签约客户本月业务","签约客户本月业务","签约客户本月业务"
                        ,"签约客户本月业务","签约客户本月业务","签约客户本月业务","签约客户本月业务",

                "非签约客户业务","非签约客户业务","非签约客户业务","非签约客户业务","非签约客户业务"
                        ,"非签约客户业务","非签约客户业务","非签约客户业务","非签约客户业务",
                "业务汇总","业务汇总","业务汇总","业务汇总","业务汇总","业务汇总","业务汇总",
                "经营状况","经营状况","经营状况","经营状况","经营状况","经营状况","经营状况","经营状况","经营状况",
                "计划定制要求" // 合并区域
                };
        //  “0,2,0,0”  ===>  “起始行，截止行，起始列，截止列”
        String[] headnum0 = {"0,2,0,0","0,2,1,1","0,2,2,2","0,2,3,3","0,2,4,4","0,2,5,5","0,2,6,6",
                            "0,0,7,9",
                            "0,0,10,18",
                            "0,0,19,27",
                            "0,0,28,34",
                            "0,0,35,43",
                            "0,2,44,44"

        };

        //第二行表头字段，其中的空的双引号是为了补全表格边框
        String[] excelHeader1 = {
                "","","","","","","",
                "班组数", "车头数", "挂箱数",
                "签约均价","签约均价","签约均价",
                "环比变化",
                "发车数","发车数","发车数",
                "环比变化", "签约标准产值",
                "散客均价","散客均价","散客均价",
                "环比变化",
                "发车数","发车数","发车数",
                "环比变化","非签约标准产值","汇总均价","环比变化","总发车数","环比变化","线路不均衡性",
                "标准产值","环比变化","标准业务毛利","标准业务毛利率","车头兜底","车头红利","挂厢兜底额","闲置亏损率",
                "经营性毛利","经营性毛利率","毛利额环比变化",""

        };
        // 合并单元格
        String[] headnum1 = {"1,2,7,7","1,2,8,8","1,2,9,9",//班组、车头、挂箱
                        "1,1,10,12",//签约均价
                        "1,2,13,13",//环比变化
                        "1,1,14,16",//发车数
                        "1,2,17,17",//环比变化
                        "1,2,18,18",//签约标准产值
                        "1,1,19,21",//散客均价
                        "1,2,22,22",//环比变化
                        "1,1,23,25",//发车数
                        "1,2,26,26",//环比变化
                        "1,2,27,27",//非签约标准产值
                        "1,2,28,28",//汇总均价
                        "1,2,29,29",//环比变化
                        "1,2,30,30",//总发车数
                        "1,2,31,31",//环比变化
                        "1,2,32,32",//线路不均衡性
                        "1,2,33,33",//标准产值
                        "1,2,34,34",//环比变化

                        "1,2,35,35",//标准业务毛利
                        "1,2,36,36",//标准业务毛利率
                        "1,2,37,37",//车头兜底
                        "1,2,38,38",//车头红利
                        "1,2,39,39",//挂厢兜底额
                        "1,2,40,40",//闲置亏损率
                        "1,2,41,41",//经营性毛利
                        "1,2,42,42",//经营性毛利率
                        "1,2,43,43",//毛利额环比变化
                        "1,2,44,44",//毛利额环比变化

                };

        //第三行表头字段
        String[] excelHeader2 = {
                "", "", "", "","","", "",
                "", "","",
                "城市", "区域", "集团",
                "",
                "城市", "区域", "集团",
                "","",//环比变化,签约标准产值
                "城市", "区域", "集团",
                "",
                "城市", "区域", "集团",
        "","","","","","","","","","","","","","","","","","",""
        };

        String[] headnum2 = {"3,3,10,10","3,3,11,11","3,3,12,12",

                "3,3,14,14","3,3,15,15","3,3,16,16",

                "3,3,19,19","3,3,20,20","3,3,21,21",

                "3,3,23,23","3,3,24,24","3,3,25,25"

        };

        // 声明一个工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = wb.createSheet("TAQIDataReport");

        // 生成一种样式
        HSSFCellStyle style = wb.createCellStyle();
        // 设置样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        // 生成一种字体
        HSSFFont font = wb.createFont();
        // 设置字体
        font.setFontName("微软雅黑");
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 在样式中引用这种字体
        style.setFont(font);

        // 生成并设置另一个样式
        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        // 生成另一种字体2
        HSSFFont font2 = wb.createFont();
        // 设置字体
        font2.setFontName("微软雅黑");
        // 设置字体大小
        font2.setFontHeightInPoints((short) 12);
        // 字体加粗
        // font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 在样式2中引用这种字体
        style2.setFont(font2);

        // 生成表格的第一行
        // 第一行表头
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < excelHeader0.length; i++) {

            sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeader0[i]);
            cell.setCellStyle(style);

            // System.out.println(excelHeader0[i]);

            if (i >= 0 && i <= 44) {
                for (int j = 0; j < excelHeader0.length; j++) {
                    // 从第j列开始填充
                    cell = row.createCell(j);
                    // 填充excelHeader1[j]第j个元素
                    cell.setCellValue(excelHeader0[j]);
                    cell.setCellStyle(style);
                }

            }

            // 设置列宽

            // sheet.setColumnWidth(0, 5500);
            // sheet.setColumnWidth(1, 6500);
            // sheet.setColumnWidth(2, 6500);
            // sheet.setColumnWidth(3, 6000);
            // sheet.setColumnWidth(4, 6500);
            // sheet.setColumnWidth(5, 6500);
            // sheet.setColumnWidth(6, 6500);
            // sheet.setColumnWidth(7, 6500);
            // sheet.setColumnWidth(8, 6500);
            // sheet.setColumnWidth(9, 6500);
            // sheet.setColumnWidth(10, 6500);
            // sheet.setColumnWidth(11, 6500);
            // sheet.setColumnWidth(12, 6500);
            // sheet.setColumnWidth(13, 6500);
            // sheet.setColumnWidth(14, 6500);
            // sheet.setColumnWidth(15, 6500);
            // sheet.setColumnWidth(16, 6500);
            // sheet.setColumnWidth(17, 6500);
            // sheet.setColumnWidth(18, 6500);
            // sheet.setDefaultRowHeight((short) 360);// 设置行高

        }

        // 动态合并单元格
//        for (int i = 0; i < headnum0.length; i++) {
//            sheet.autoSizeColumn(i, true);
//            String[] temp = headnum0[i].split(",");
//            Integer startrow = Integer.parseInt(temp[0]);
//            Integer overrow = Integer.parseInt(temp[1]);
//            Integer startcol = Integer.parseInt(temp[2]);
//            Integer overcol = Integer.parseInt(temp[3]);
//            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
//        }

        // 第二行表头
        row = sheet.createRow(1);
        for (int i = 0; i < excelHeader1.length; i++) {
            sheet.autoSizeColumn(i, true);// 自动调整宽度
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeader1[i]);
            cell.setCellStyle(style);

            if (i > 6 && i <= 44) {
                for (int j = 0; j < excelHeader1.length; j++) {
                    // 从第j+1列开始填充
                    cell = row.createCell(j );
                    // 填充excelHeader1[j]第j个元素
                    cell.setCellValue(excelHeader1[j]);
                    cell.setCellStyle(style);
                }
            }
        }

        // 动态合并单元格
//        for (int i = 0; i < headnum1.length; i++) {
//
//            sheet.autoSizeColumn(i, true);
//            String[] temp = headnum1[i].split(",");
//            Integer startrow = Integer.parseInt(temp[0]);
//            Integer overrow = Integer.parseInt(temp[1]);
//            Integer startcol = Integer.parseInt(temp[2]);
//            Integer overcol = Integer.parseInt(temp[3]);
//            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
//        }


        // 第三行表头
        row = sheet.createRow(2);
        for (int i = 0; i < excelHeader2.length; i++) {

            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeader2[i]);
            cell.setCellStyle(style);
            // System.out.println(excelHeader2[i]);
            sheet.autoSizeColumn(i, true);// 自动调整宽度

            if (i > 9 && i <= 44) {
                for (int j = 0; j < excelHeader2.length; j++) {
                    // 从第j+2列开始填充
                    cell = row.createCell(j);
                    // 填充excelHeader1[j]第j个元素
                    cell.setCellValue(excelHeader2[j]);
                    cell.setCellStyle(style);
                }
            }
        }
        // 动态合并单元格7
//        for (int i = 0; i < headnum2.length; i++) {
//            sheet.autoSizeColumn(i, true);
//            String[] temp = headnum2[i].split(",");
//            Integer startrow = Integer.parseInt(temp[0]);
//            Integer overrow = Integer.parseInt(temp[1]);
//            Integer startcol = Integer.parseInt(temp[2]);
//            Integer overcol = Integer.parseInt(temp[3]);
//            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
//        }

        // 第四行数据
        for (int i = 0; i < list.size(); i++) {

            row = sheet.createRow(i + 3);
            TAQIDataReport report = list.get(i);

            // 导入对应列的数据
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(report.getCity());
            cell.setCellStyle(style2);
            // 导入对应列的数据
            HSSFCell cell1 = row.createCell(0);
            cell.setCellValue(report.getCity());
            cell.setCellStyle(style2);
            // 导入对应列的数据
            HSSFCell cell2 = row.createCell(0);
            cell.setCellValue(report.getCity());
            cell.setCellStyle(style2);
            // 导入对应列的数据
            HSSFCell cell3 = row.createCell(0);
            cell.setCellValue(report.getCity());
            cell.setCellStyle(style2);
            // 导入对应列的数据
            HSSFCell cell4 = row.createCell(0);
            cell.setCellValue(report.getCity());
            cell.setCellStyle(style2);
            // 导入对应列的数据
            HSSFCell cell5 = row.createCell(0);
            cell.setCellValue(report.getCity());
            cell.setCellStyle(style2);
            // 导入对应列的数据
            HSSFCell cell6 = row.createCell(0);
            cell.setCellValue(report.getCity());
            cell.setCellStyle(style2);

            HSSFCell cell7 = row.createCell(1);
            cell1.setCellValue(report.getAdd());
            cell1.setCellStyle(style2);

//            HSSFCell cell2 = row.createCell(2);
//            cell2.setCellValue(report.getSo2Concentration());
//            cell2.setCellStyle(style2);
//            HSSFCell cell3 = row.createCell(3);
//            cell3.setCellValue(report.getSo2Subindex());
//            cell3.setCellStyle(style2);
//
//            HSSFCell cell4 = row.createCell(4);
//            cell4.setCellValue(report.getNo2Concentration());
//            cell4.setCellStyle(style2);
//            HSSFCell cell5 = row.createCell(5);
//            cell5.setCellValue(report.getNo2Subindex());
//            cell5.setCellStyle(style2);
//
//            HSSFCell cell6 = row.createCell(6);
//            cell6.setCellValue(report.getPm10Concentration());
//            cell6.setCellStyle(style2);
//            HSSFCell cell7 = row.createCell(7);
//            cell7.setCellValue(report.getPm10Subindex());
//            cell7.setCellStyle(style2);
//
//            HSSFCell cell8 = row.createCell(8);
//            cell8.setCellValue(report.getCoConcentration());
//            cell8.setCellStyle(style2);
//            HSSFCell cell9 = row.createCell(9);
//            cell9.setCellValue(report.getCoSubindex());
//            cell9.setCellStyle(style2);
//
//            HSSFCell cell10 = row.createCell(10);
//            cell10.setCellValue(report.getO3Concentration());
//            cell10.setCellStyle(style2);
//            HSSFCell cell11 = row.createCell(11);
//            cell11.setCellValue(report.getO3Subindex());
//            cell11.setCellStyle(style2);
//
//            HSSFCell cell12 = row.createCell(12);
//            cell12.setCellValue(report.getPm25Concentration());
//            cell12.setCellStyle(style2);
//            HSSFCell cell13 = row.createCell(13);
//            cell13.setCellValue(report.getPm25Subindex());
//            cell13.setCellStyle(style2);
//
//            HSSFCell cell14 = row.createCell(14);
//            cell14.setCellValue(report.getAirSubindex());
//            cell14.setCellStyle(style2);
//
//            HSSFCell cell15 = row.createCell(15);
//            cell15.setCellValue(report.getKeyPollution());
//            cell15.setCellStyle(style2);
//
//            HSSFCell cell16 = row.createCell(16);
//            cell16.setCellValue(report.getLevel());
//            cell16.setCellStyle(style2);
//
//            HSSFCell cell17 = row.createCell(17);
//            cell17.setCellValue(report.getType());
//            cell17.setCellStyle(style2);
//
//            HSSFCell cell18 = row.createCell(18);
//            cell18.setCellValue(report.getColor());
//            cell18.setCellStyle(style2);
        }
        return wb;
    }
}
结果：截图可能不完整但是运行一下基本就可以了
土耳其的烧烤
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
java 使用POI画出复杂表格1-表头

package tdh.platform.ofc.admin.interceptor;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.hssf.util.HSSFColor;import org.springframework.stereotype.Service;import java.io.FileOutputStream;import java.io.IOException;import java.util.A.
复制链接

扫一扫