为您推荐:
精华内容
最热下载
问答
  • 5星
    6.43MB bala5569 2021-01-03 19:22:39
  • 9KB m0_37827754 2020-12-18 14:13:09
  • 5KB limj1987 2021-01-05 09:46:52
  • 一般是导出有固定字段的excel 本文介绍 XSSFWorkbook 导出Excel多行表头合并单元格的表格 引入pom依赖 <!-- 构建Excel --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml...

    poi导出excel有两种方式:

    第一种:从无到有的创建整个excel,通过HSSFWorkbook,HSSFSheet HSSFCell, 等对象一步一步的创建出工作簿,sheet,和单元格,并添加样式,数据等。

    第二种:通过excel.xls 模板的方式,自己在桌面创建一个excel, 然后修改这个excel为模板,复制到项目中(我是放在根目录下),再然后读取模板,修改模板,给模板填充数据,最后把模板写入到另外一个excel2.xls中(硬盘中的)。 按我自己的理解,这种方式只适合,需要导出的内容是固定格式的,只需要填充一次数据的 情况。比如简历。

    本文内容如下:

        1. 使用 HSSFWorkbook 对象 实现excel导出。一般是导出excel2003
    
        2. 使用 XSSFWorkbook 对象实现excel导出。 一般是导出excel2007
    
        3. 使用 SXSSFWorkbook 对象实现excel导出。 一般是导出百万级数据的excel
    
        4. 使用 template.xls 格式模板,实现excel导出。 一般是导出有固定字段的excel
    

    本文介绍 XSSFWorkbook 导出Excel多行表头、合并单元格的表格
    引入pom依赖

    <!-- 构建Excel -->
    <dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
    	<version>3.9</version>
    </dependency>
    

    主要的方法如下:

    XSSFWorkbook workBook = new XSSFWorkbook(); //新建一个Excel文件
    XSSFSheet sheet = workBook.createSheet();  //新建一个sheet,index为sheet的页码
    XSSFRow row = sheet.createRow(int index);  //新建一行,index为行号
    XSSFCell cell = row.createCell(int index)  //新建一个单元格,index为单元格的列号
    cell.setCellValue(String str);  //给单元格赋值
    

    简单示例

    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    import com.elasticsearch.pojo.Student;
    import org.apache.poi.xssf.usermodel.*;
    
    /**
     * @Author:刘德安
     * @Date: 2021/1/11 10:37
     */
    public class TestExcelDemo {
    
        public static void main(String[] args) {
            Student student =new Student();
            student.setName("XXX ");
            student.setResult("95");
            student.setClazz("二班");
            student.setGrade("五年级");
            student.setNumber("66");
            List<Student> list =new ArrayList<>();
            list.add(student);
            list.add(student);
            list.add(student);
            testExcelDemo(list);
        }
        /**
         *
         * @param list 需要写入excel的数据 从数据库或者其他途径读取
         */
        public static void testExcelDemo(List<Student> list) {
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet();
            // 表头
            XSSFRow row = sheet.createRow(0);
            row.createCell(0).setCellValue("学号");
            row.createCell(1).setCellValue("姓名");
            row.createCell(2).setCellValue("年级");
            row.createCell(3).setCellValue("班别");
            row.createCell(4).setCellValue("成绩");
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i+1);
                row.createCell(0).setCellValue(list.get(i).getNumber());
                row.createCell(1).setCellValue(list.get(i).getName());
                row.createCell(2).setCellValue(list.get(i).getGrade());
                row.createCell(3).setCellValue(list.get(i).getClazz());
                row.createCell(4).setCellValue(list.get(i).getResult());
            }
            String filePath = "D:\\Demo\\";
            String fileName = "testExcelDemo.xlsx";
            File file = new File(filePath + fileName);
            FileOutputStream fos = null;
            try {
                fos = new FileOutputStream(file);
                // 写入磁盘
                workBook.write(fos);
                fos.close();//记得关闭
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    
    

    复杂示例(多行表头、合并单元格)

    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    
    import com.elasticsearch.pojo.Student;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.*;
    
    /**
     * @Author:刘德安
     * @Date: 2021/1/11 10:37
     */
    public class TestExcelDemo {
    
        public static void main(String[] args) {
            Student student =new Student();
            student.setName("XXX ");
            student.setResult("95");
            student.setClazz("二班");
            student.setGrade("五年级");
            student.setNumber("66");
            List<Student> list =new ArrayList<>();
            list.add(student);
            list.add(student);
            list.add(student);
            testExcelDemo(list);
        }
        /**
         *
         * @param list 需要写入excel的数据 从数据库或者其他途径读取
         */
        public static void testExcelDemo(List<Student> list) {
            /** 第一步,创建一个Workbook,对应一个Excel文件  */
            XSSFWorkbook wb = new XSSFWorkbook();
            /** 第二步,在Workbook中添加一个sheet,对应Excel文件中的sheet  */
            XSSFSheet sheet = wb.createSheet("excel导出标题");
            /** 第三步,设置样式以及字体样式*/
            XSSFCellStyle titleStyle = createTitleCellStyle(wb);
            XSSFCellStyle headerStyle = createHeadCellStyle(wb);
            XSSFCellStyle contentStyle = createContentCellStyle(wb);
            /** 第四步,创建标题 ,合并标题单元格 */
            // 行号
            int rowNum = 0;
            // 创建第一页的第一行,索引从0开始
            XSSFRow row0 = sheet.createRow(rowNum++);
            row0.setHeight((short) 800);// 设置行高
    
            String title = "excel导出标题";
            XSSFCell c00 = row0.createCell(0);
            c00.setCellValue(title);
            c00.setCellStyle(titleStyle);
            // 合并单元格,参数依次为起始行,结束行,起始列,结束列 (索引0开始)
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));//标题合并单元格操作,6为总列数
            //第二行
            XSSFRow row2 = sheet.createRow(rowNum++);
            row2.setHeight((short) 700);
            String[] row_third = {"学号", "姓名", "年级", "班级", "成绩"};
            for (int i = 0; i < row_third.length; i++) {
                XSSFCell tempCell = row2.createCell(i);
                tempCell.setCellValue(row_third[i]);
                tempCell.setCellStyle(headerStyle);
            }
            for (Student student : list) {
                XSSFRow tempRow = sheet.createRow(rowNum++);
                tempRow.setHeight((short) 500);
                // 循环单元格填入数据
                for (int j = 0; j < 5; j++) {
                    XSSFCell tempCell = tempRow.createCell(j);
                    tempCell.setCellStyle(contentStyle);
                    String tempValue = "";
                    if (j == 0) {
                        // 学号
                        tempValue = student.getNumber();
                    } else if (j == 1) {
                        // 姓名
                        tempValue = student.getName();
                    } else if (j == 2) {
                        // 年级
                        tempValue = student.getGrade();
                    } else if (j == 3) {
                        // 班级
                        tempValue = student.getClazz();
                    } else if (j == 4) {
                        // 成绩
                        tempValue = student.getResult();
                    }
                    tempCell.setCellValue(tempValue);
                }
            }
            String filePath = "D:\\Demo\\";
            String fileName = "testExcelDemo.xlsx";
            File file = new File(filePath + fileName);
            FileOutputStream fos = null;
            try {
                fos = new FileOutputStream(file);
                // 写入磁盘
                wb.write(fos);
                fos.close();//记得关闭
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
    
    
        /**
         * 创建标题样式
         * @param wb
         * @return
         */
        private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {
            XSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色
    
            XSSFFont headerFont1 = (XSSFFont) wb.createFont(); // 创建字体样式
            headerFont1.setBold(true); //字体加粗
            headerFont1.setFontName("黑体"); // 设置字体类型
            headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
            cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
            return cellStyle;
        }
    
        /**
         * 创建表头样式
         * @param wb
         * @return
         */
        private static XSSFCellStyle createHeadCellStyle(XSSFWorkbook wb) {
            XSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setWrapText(true);// 设置自动换行
            cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
            cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
            cellStyle.setBorderRight(BorderStyle.THIN); //右边框
            cellStyle.setBorderTop(BorderStyle.THIN); //上边框
    
            XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式
            headerFont.setBold(true); //字体加粗
            headerFont.setFontName("黑体"); // 设置字体类型
            headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
            cellStyle.setFont(headerFont); // 为标题样式设置字体样式
    
            return cellStyle;
        }
    
        /**
         * 创建内容样式
         * @param wb
         * @return
         */
        private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {
            XSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
            cellStyle.setWrapText(true);// 设置自动换行
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
            cellStyle.setBorderRight(BorderStyle.THIN); //右边框
            cellStyle.setBorderTop(BorderStyle.THIN); //上边框
    
            // 生成12号字体
            XSSFFont font = wb.createFont();
            font.setColor((short)8);
            font.setFontHeightInPoints((short) 12);
            cellStyle.setFont(font);
    
            return cellStyle;
        }
    }
    
    
    展开全文
    weixin_43851064 2021-01-11 14:42:49
  • 4星
    8MB shunlu 2018-10-28 23:54:30
  • //第一行表头字段,合并单元格时字段跨几列就将该字段重复几次 String[] excelHeader0 = { "城市名称" , "监测点" , "污染物浓度及空气质量分指数(AQI)" , "污染物浓度及空气质量分指数(AQI)" , "污染物...

    1、jsp页面代码:

    <button type="button" class="btn btn-danger btn-optional oper-box-btn-export-common">导出到Excel
    </button>

    2、js中的请求代码:

    $(".oper-box-btn-export-common").click(function(){var flag=confirm("是否确定将查询的通用参数导出Excel表格?");
            if(flag == false){
                return;
            }
             location.href="${ctp}/para/excel/export/common";  
     });

    3、controller中的代码:

    @RequestMapping("/excel/export/common")
    public void exportExcelCommon(HttpServletRequestrequest, HttpServletResponse response )throws IOException{
    
            List<TParams> list = new ArrayList<>();
            List<TParams> params = paramService.getlist();
            for (TParams tParams : params) {
                list.add(tParams);
            }
            HSSFWorkbook wb = paramService.exportCommon(list);
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-Disposition", "attachment;filename="+ java.net.URLEncoder.encode("通用参数列表", "UTF-8")+".xls");  
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } 

    4、ServiceImpl代码:

    @SuppressWarnings("deprecation")
        @Override
        public HSSFWorkbook export(List<TAQIDataReport> list) {
    
            // 声明String数组,并初始化元素(表头名称)
            //第一行表头字段,合并单元格时字段跨几列就将该字段重复几次
            String[] excelHeader0 = { "城市名称", "监测点", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)",
                    "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)",
                    "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)",
                    "污染物浓度及空气质量分指数(AQI)", "空气质量指数(AQI)", "首要污染物", "空气质量指数级别", "空气质量指数类别", "空气质量指数类别" };
               //  “0,2,0,0”  ===>  “起始行,截止行,起始列,截止列”
            String[] headnum0 = { "0,2,0,0", "0,2,1,1", "0,0,2,13", "0,2,14,14", "0,2,15,15", "0,2,16,16", "0,1,17,18" };
    
            //第二行表头字段,其中的空的双引号是为了补全表格边框
            String[] excelHeader1 = { "二氧化硫(SO₂)24小时平均", "二氧化硫(SO₂)24小时平均", "二氧化氮(NO₂)24小时平均", "二氧化氮(NO₂)24小时平均",
                    "颗粒物(粒径小于等于10μm)24小时平均", "颗粒物(粒径小于等于10μm)24小时平均", "一氧化碳(CO)24小时平均", "一氧化碳(CO)24小时平均", "臭氧(O₃)最大8小时平均",
                    "臭氧(O₃)最大8小时平均", "颗粒物(粒径小于等于2.5μm)24小时平均", "颗粒物(粒径小于等于2.5μm)24小时平均","","","","","" };
            // 合并单元格
            String[] headnum1 = { "1,1,2,3", "1,1,4,5", "1,1,6,7", "1,1,8,9", "1,1,10,11", "1,1,12,13" };
    
            //第三行表头字段
            String[] excelHeader2 = {  "", "", "浓度/(μg/m3)", "分指数", "浓度/(μg/m3)", "分指数", "浓度/(μg/m3)", "分指数", "浓度/(μg/m3)", "分指数",
                    "浓度/(μg/m3)", "分指数", "浓度/(μg/m3)", "分指数","", "类别", "颜色" };
    
            String[] headnum2 = { "2,2,2,2", "2,2,3,3", "2,2,4,4", "2,2,5,5", "2,2,6,6", "2,2,7,7", "2,2,8,8", "2,2,9,9",
                    "2,2,10,10", "2,2,11,11", "2,2,12,12", "2,2,13,13", "2,2,17,17", "2,2,18,18" };
    
            // 声明一个工作簿
            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 <= 18) {
                    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 + 1);
                cell.setCellValue(excelHeader1[i]);
                cell.setCellStyle(style);
    
                if (i >= 2 && i <= 18) {
                    for (int j = 0; j < excelHeader1.length; j++) {
                        // 从第j+1列开始填充
                        cell = row.createCell(j + 2);
                        // 填充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 + 2);
                cell.setCellValue(excelHeader2[i]);
                cell.setCellStyle(style);
                // System.out.println(excelHeader2[i]);
                sheet.autoSizeColumn(i, true);// 自动调整宽度
    
                if (i > 1 && i <= 18) {
                    for (int j = 0; j < excelHeader2.length; j++) {
                        // 从第j+2列开始填充
                        cell = row.createCell(j);
                        // 填充excelHeader1[j]第j个元素
                        cell.setCellValue(excelHeader2[j]);
                        cell.setCellStyle(style);
                    }
                }
            }
            // 动态合并单元格
            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(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;
        }
    

    5、效果图展示:

    效果图

    展开全文
    Damon_wdc 2017-12-28 18:10:40
  • 5星
    762KB wuzhongxian 2013-12-25 16:07:12
  • 4星
    53KB yguang_zh 2013-09-16 11:01:32
  • 204KB weixin_38588520 2020-12-20 08:31:03
  • 50KB weixin_38725902 2020-10-16 22:45:57
  • 做项目的时候一个导出的需求,使用框架...本文介绍 HSSFWorkbook 导出Excel多行表头合并单元格的表格。 表头样式如下: 直接上代码: ** * 导出excel(月度业务明细) */ @GetMapping("/export") public void...

    做项目的时候一个导出的需求,使用框架封装的导出功能不能满足复杂动态表头的导出,因此,使用POI原生导出,借鉴了一些网上的导出demo。
    本文介绍 HSSFWorkbook 导出Excel多行表头、合并单元格的表格。
    表头样式如下:
    3行16列的表头,动态日期
    直接上代码:

    **
         * 导出excel(月度业务明细)
         */
        @GetMapping("/export")
        public void exportExcel(String startTime,BusinessDetail businessDetail){
    		//表头动态日期
            String year= startTime.substring(0,4);
            String month = startTime.substring(5,7);
            String day = startTime.substring(8,10);
            Integer srcyear = (Integer.parseInt(year)-1);
            //1.创建一个workbook,对应一个excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
    
            //2.在workbook中添加一个sheet,对应Excel中的sheet
            HSSFSheet sheet = wb.createSheet("月度业务明细");
    
    		//设置每一列的列宽
    		sheet.setColumnWidth(0,256*15);
            sheet.setColumnWidth(1,256*15);
            sheet.setColumnWidth(2,256*15);
            sheet.setColumnWidth(3,256*15);
            sheet.setColumnWidth(4,256*15);
            sheet.setColumnWidth(5,256*15);
            sheet.setColumnWidth(6,256*15);
            sheet.setColumnWidth(7,256*15);
            sheet.setColumnWidth(8,256*15);
            sheet.setColumnWidth(9,256*15);
            sheet.setColumnWidth(10,256*15);
            sheet.setColumnWidth(11,256*15);
            sheet.setColumnWidth(12,256*15);
            sheet.setColumnWidth(13,256*15);
            sheet.setColumnWidth(14,256*15);
            sheet.setColumnWidth(15,256*15);
    
            //3.设置样式以及字体样式
            HSSFCellStyle titleStyle = ExcelUtils.createTitleCellStyle(wb);
            HSSFCellStyle headerStyle = ExcelUtils.createHeadCellStyle(wb);
            HSSFCellStyle contentStyle = ExcelUtils.createContentCellStyle(wb);
    
            //4.创建标题,合并标题单元格
            //行号
            int rowNum = 0;
    
            //创建第一行,索引从0开始(标题行)
            HSSFRow row0 = sheet.createRow(rowNum++);
            row0.setHeight((short) 800);// 设置行高
            String title = "月度业务明细";
            HSSFCell c00 = row0.createCell(0);
            c00.setCellValue(title);
            c00.setCellStyle(titleStyle);
            // 合并单元格,参数依次为起始行,结束行,起始列,结束列 (索引0开始)
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 15));//标题合并单元格操作,总列数为16
    
            //第二行
            HSSFRow row1 = sheet.createRow(rowNum++);
            row1.setHeight((short)500);
            String[] row_first = {"客户",month+"月发车",srcyear+"年12月31日前货款回收(期前欠款)",year+"年货款回收","截止"+year+"年"+month+"月"+day+"日业务明细","","","","","","","","","",month+"月份新增欠款",month+"月收款合计"};
            for (int i = 0; i < row_first.length; i++) {
                HSSFCell tempCell = row1.createCell(i);
                tempCell.setCellValue(row_first[i]);
                tempCell.setCellStyle(headerStyle);
            }
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));//经销商或客户
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));//12月发车
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 2, 2));//期前欠款
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 3, 3));//2019年货款回收
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 13));//截止2019年12月27日业务明细
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 14, 14));//12月份新增加欠款
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 15, 15));//12月份收款合计
    
            //第三行
            HSSFRow row2 = sheet.createRow(rowNum++);
            row2.setHeight((short)500);
            String[] row_second = {"","","","",month+"月合同发车","","",month+"月分期管理费及利息","","",month+"月代收代付运费","","",month+"月预付款","",""};
            for (int i = 0; i < row_second.length; i++) {
                HSSFCell tempCell = row2.createCell(i);
                tempCell.setCellValue(row_second[i]);
                tempCell.setCellStyle(headerStyle);
            }
    
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 6));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 9));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 10, 12));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 13, 13));
    
            //第四行
            HSSFRow row3 = sheet.createRow(rowNum++);
            row3.setHeight((short)500);
            String[] row_third = {"","","","","应收","实收","欠款","应收","实收","欠款","应收","实收","欠款","","",""};
            for (int i = 0; i < row_third.length; i++) {
                HSSFCell tempCell = row3.createCell(i);
                tempCell.setCellValue(row_third[i]);
                tempCell.setCellStyle(headerStyle);
            }
    
            //查询月度明细列表
            List<BusinessDetail> list = businessService.selectMonthBusinessList(businessDetail);
    
            for(int i = 0;i<list.size();i++){
                HSSFRow tempRow = sheet.createRow(rowNum++);
                tempRow.setHeight((short)500);
                //循环单元格填入数据
                for(int j=0;j<16;j++){
                    HSSFCell tempCell = tempRow.createCell(j);
                    tempCell.setCellStyle(contentStyle);
                    String cellValue = "";
                    if(j ==0){
                        //经销商
                        cellValue = list.get(i).getClientName();
                    }else if(j == 1){
                        cellValue = list.get(i).getDespatchAmount();
                    }else if(j ==2){
                        if(list.get(i).getBalance() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getBalance().toString();
                        }
                    }else if(j == 3){
                        if(list.get(i).getLoanRecovery() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getLoanRecovery().toString();
                        }
                    }else if(j ==4){
                        if(list.get(i).getTotalMoney() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getTotalMoney().toString();
                        }
                    }else if(j == 5){
                        if(list.get(i).getRepayTotal() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getRepayTotal().toString();
                        }
                    }else if(j == 6){
                        if(list.get(i).getRestTotal() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getRestTotal().toString();
                        }
                    }else if(j ==7){
                        if(list.get(i).getTotalManage() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getTotalManage().toString();
                        }
                    }else if(j == 8){
                        if(list.get(i).getRepayManage() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getRepayManage().toString();
                        }
                    }else if(j == 9){
                        if(list.get(i).getRestManage() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getRestManage().toString();
                        }
                    }else if(j == 10){
                        if(list.get(i).getTotalFreight() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getTotalFreight().toString();
                        }
                    }else if(j == 11){
                        if(list.get(i).getRepayFreight() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getRepayFreight().toString();
                        }
                    }else if( j == 12){
                        if(list.get(i).getRestFreight() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getRestFreight().toString();
                        }
                    }else if(j == 13){
                        if(list.get(i).getImprest() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getImprest().toString();
                        }
                    }else if(j ==14){
                        if(list.get(i).getNewBalance() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getNewBalance().toString();
                        }
                    }else if(j == 15){
                        if(list.get(i).getRepayTotal() ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).getRepayTotal().toString();
                        }
                    }
                    tempCell.setCellValue(cellValue);
                }
            }
    
            //导出excel
            HttpServletResponse response = this.getResponse();
            String fileName = "月度业务明细.xls";
            try {
                fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
                response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
                OutputStream stream = response.getOutputStream();
                if(null != wb && null != stream){
                    wb.write(stream);
                    wb.close();
                    stream.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    

    设置标题样式、表头样式以及内容样式:
    单独写了一个ExcelUtils类,代码如下:

    /**
         * 创建标题样式
         * @param wb
         * @return
         */
        public static  HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb){
            HSSFCellStyle cellStyle = wb.createCellStyle();
            //水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //垂直对齐
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //背景颜色
            cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    
            HSSFFont headerFont1 = (HSSFFont) wb.createFont();
            //字体加粗
            headerFont1.setBold(true);
            //字体类型
            headerFont1.setFontName("黑体");
            //字体大小
            headerFont1.setFontHeightInPoints((short)15);
            cellStyle.setFont(headerFont1);
            return cellStyle;
        }
    
        /**
         * 创建表头样式
         * @param wb
         * @return
         */
        public static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb){
            HSSFCellStyle cellStyle = wb.createCellStyle();
            //设置自动换行
            cellStyle.setWrapText(true);
            //设置背景颜色
            cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            //水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //垂直对齐
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
            //下边框
            cellStyle.setBorderBottom(BorderStyle.THIN);
            //左边框
            cellStyle.setBorderLeft(BorderStyle.THIN);
            //右边框
            cellStyle.setBorderRight(BorderStyle.THIN);
            //上边框
            cellStyle.setBorderTop(BorderStyle.THIN);
    
            //创建字体样式
            HSSFFont headerFont = (HSSFFont)wb.createFont();
            //字体加粗
            headerFont.setBold(true);
            //字体类型
            headerFont.setFontName("黑体");
            //字体大小
            headerFont.setFontHeightInPoints((short)12);
            //为标题样式添加字体样式
            cellStyle.setFont(headerFont);
    
            return cellStyle;
        }
    
        /**
         *  设置表格内容样式
         * @param wb
         * @return
         */
        public static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb){
            HSSFCellStyle cellStyle = wb.createCellStyle();
            //水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置自动换行
            cellStyle.setWrapText(true);
            //上边框
            cellStyle.setBorderTop(BorderStyle.THIN);
            //下边框
            cellStyle.setBorderBottom(BorderStyle.THIN);
            //左边框
            cellStyle.setBorderLeft(BorderStyle.THIN);
            //右边框
            cellStyle.setBorderRight(BorderStyle.THIN);
    
            //设置字体
            HSSFFont font = (HSSFFont)wb.createFont();
            font.setColor((short)8);
            font.setFontHeightInPoints((short)12);
    
            return cellStyle;
        }
    

    HTML页面请求:

    
    <a class="btn btn-warning" onclick="exportMonthDetail()">
    	<i class="fa fa-download"></i> 导出
    </a>
    //导出excel
        function exportMonthDetail() {
            var startTime = document.getElementById("startTime").value;
    
            // window.location.href = prefix1 + "/export";   //不含参数
    
            //导出
            window.location.href = prefix1 + "/export?startTime="+startTime;
    
        }
    

    使用post请求时,参数传递到后台,但是导出时无法处理文件,一次使用了get请求的方式。
    最终导出结果如下:
    设置了内容宽度的结果
    在数据处理时的代码比较冗余,没来得及处理,结果为null时会报java.lang.NullPointerException,所以暂时这样处理了一下。

    参考文章:https://www.cnblogs.com/hxun/p/11387726.html

    展开全文
    weixin_45768223 2020-01-11 14:09:42
  • 使用easyexcel进行导出的时候,如何设置多行表头合并呢? 表头合并,自然容易想到使用cellRange。参考 《poi 导出带复杂表头的内容》 目标生成: 动态生成这样的表格 准备内容: 参考:Easyexcel 动态...

    Easyexcel 动态导出多行表头-使用cellRange

     

    使用easyexcel进行导出的时候,如何设置多行表头的合并呢?

    表头合并,自然容易想到使用cellRange。参考 《poi 导出带复杂表头的内容》 

     

    目标生成: 

    动态生成这样的表格

     

    准备内容:

     参考: Easyexcel 动态导出多行表头(非注解)

     

    使用:OnceAbsoluteMergeStrategy

     

    @Test
    public void testOnceAbsoluteMergeStrategy() {
    
        List<List<String>> titleList = new ArrayList<>();
        List<String> titleOne = Lists.newArrayList("统计","","","","");
        List<String> titleTwo = Lists.newArrayList("数值","","","比例","");
        titleList.add(titleOne);
        titleList.add(titleTwo);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy1 = new OnceAbsoluteMergeStrategy(0, 0, 0, 4);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy2 = new OnceAbsoluteMergeStrategy(1, 1, 0, 2);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy3 = new OnceAbsoluteMergeStrategy(1, 1, 3, 4);
    
        String fileName = TestFileUtil.getPath() + "mul range" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName)
                .registerWriteHandler(onceAbsoluteMergeStrategy1)
                .registerWriteHandler(onceAbsoluteMergeStrategy2)
                .registerWriteHandler(onceAbsoluteMergeStrategy3)
                .head(roateHeadFields(titleList))
                .sheet("模板").doWrite(data2());
    
    }

    结果:

      

    打开后会提示 修复的问题(这个问题不懂怎么处理,感觉是head方法里面先进行了处理,cellRange再进行,出现了重复合并单元格情况,去看head的源码也没看出个所以然来)。

    先按下不表,继续。 抛开重复合并单元格的问题,上面的代码里面调用了

    registerWriteHandler(onceAbsoluteMergeStrategy1)

    如果要处理的cellRange很多呢? 要怎么处理?

    参考:OnceAbsoluteMergeStrategy 类实现的方式,也添加一个处理的方式

     

    添加handler:

    1,代码:

    public class MulHeaderHandler implements SheetWriteHandler {
    
        private List<CellRangeAddress> cellRangeAddressList;
    
        public MulHeaderHandler(List<CellRangeAddress> cellRangeAddressList) {
            this.cellRangeAddressList = cellRangeAddressList;
        }
    
        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    
        }
    
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            Sheet sheet = writeSheetHolder.getSheet();
            ListUtils.emptyIfNull(cellRangeAddressList).forEach(sheet::addMergedRegion);
        }
    }
    

     

    2,测试:

    @Test
    public void testMulRangeHeader() {
    
        List<List<String>> titleList = new ArrayList<>();
        List<String> titleOne = Lists.newArrayList("统计","","","","");
        List<String> titleTwo = Lists.newArrayList("数值","","","比例","");
        titleList.add(titleOne);
        titleList.add(titleTwo);
        List<CellRangeAddress> cellRangeAddressList = addMergeTwo();
    
        String fileName = TestFileUtil.getPath() + "mul range" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName)
                .registerWriteHandler(new MulHeaderHandler(cellRangeAddressList))
                .head(roateHeadFields(titleList))
                .sheet("sheet").doWrite(data2());
    }
    
    private List<CellRangeAddress> addMergeTwo() {
        List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
        cellRangeAddressList.add(new CellRangeAddress(0, 0, 0, 4));
        cellRangeAddressList.add(new CellRangeAddress(1, 1, 0, 2));
        cellRangeAddressList.add(new CellRangeAddress(1, 1, 3, 4));
        return cellRangeAddressList;
    }
    

    结果:同上

     这样处理,感觉就正常了,但是还是同样的问题。目前处理的方式就是不使用它,直接使用easyexcel表头空白填充的方式。

    这边使用了自定义的合并方式,同理表格的样式或是其他的处理也可以用类似的方式。

     

    总结:

      使用easyexcel设置表头,如果使用cellRange的时候,会出现重复合并单元格的情况,导致打开excel出现提示,不然这边就不用去特意拼接表头和旋转数据了。目前也没找到处理方式,如果您有方法处理?请不吝赐教!

       导出关联文章poi操作excel的内容汇总

    展开全文
    qq_35461948 2021-05-11 15:58:53
  • ranbolwb 2016-04-18 18:05:28
  • weixin_39874269 2021-07-05 01:47:41
  • 4星
    21KB dystocia 2012-06-28 15:33:46
  • qq_33212500 2017-06-06 10:08:50
  • xiezhongyuan07 2018-09-26 19:58:24
  • DanChunErYongGan 2020-08-18 17:26:29

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,775
精华内容 1,910
关键字:

多行表头表格合并