JAVA导出EXCEL定制型

       我们在JAVA导出EXCEL通用型 中导出的是通用型的excel。只要是普通类型的都可以用上面的方法。但是一些特别的，需要合并单元格的话通用型的就不可以用了。所以需要我们做特别处理。我们接下来就说下。

1先创建工作簿

HSSFWorkbook workbook = new HSSFWorkbook();

2：准备需要合并的单元格。如果没有合并操作的不用管。

//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress = new CellRangeAddress(0, 1, 0, 16);
//标题
CellRangeAddress callRangeAddress11 = new CellRangeAddress(2, 4, 0, 0);//城市名称
CellRangeAddress callRangeAddress12 = new CellRangeAddress(2, 4, 1, 1);//去年批次
CellRangeAddress callRangeAddress13 = new CellRangeAddress(2, 4, 2, 2);//今年批次

CellRangeAddress callRangeAddress21 = new CellRangeAddress(2, 2, 3, 5);//去年数据
CellRangeAddress callRangeAddress22 = new CellRangeAddress(3, 4, 3, 3);//总计
CellRangeAddress callRangeAddress23 = new CellRangeAddress(3, 4, 4, 4);//线上
CellRangeAddress callRangeAddress24 = new CellRangeAddress(3, 4, 5, 5);//线下


CellRangeAddress callRangeAddress31 = new CellRangeAddress(2, 2, 6, 16);//今年数据

CellRangeAddress callRangeAddress32 = new CellRangeAddress(3, 3, 6, 8);//线上与线下

CellRangeAddress callRangeAddress33 = new CellRangeAddress(3, 3, 9, 11);//比率


CellRangeAddress callRangeAddress34 = new CellRangeAddress(3, 3, 12, 16);//关系

//2.创建工作表
HSSFSheet sheet = workbook.createSheet("工作");


//加载合并单元格对象
sheet.addMergedRegion(callRangeAddress);
sheet.addMergedRegion(callRangeAddress11);
sheet.addMergedRegion(callRangeAddress12);
sheet.addMergedRegion(callRangeAddress13);

sheet.addMergedRegion(callRangeAddress21);
sheet.addMergedRegion(callRangeAddress22);
sheet.addMergedRegion(callRangeAddress23);
sheet.addMergedRegion(callRangeAddress24);

sheet.addMergedRegion(callRangeAddress31);
sheet.addMergedRegion(callRangeAddress32);
sheet.addMergedRegion(callRangeAddress33);
sheet.addMergedRegion(callRangeAddress34);

3:准备相应的样式，包括标题，内容等

           //设置首行样式
            HSSFCellStyle headStyle = createCellStyle(workbook, (short) 10, false, true);

            headStyle.setFillForegroundColor((short) 13);// 设置背景色

            //标题样式
            HSSFCellStyle colStyle = createCellStyle(workbook, (short) 10, true, true);

            colStyle.setFillForegroundColor((short) 13);// 设置背景色

            //内容样式
            HSSFCellStyle cellStyle = createCellStyle(workbook, (short) 10, false, true);


            HSSFCellStyle sanStyle = createCellStyle(workbook, (short) 10, false, false);

4：设置标题内容

 //1：设置默认列宽
            sheet.setDefaultColumnWidth(16);
            //2.创建行
            //2.1创建头标题行;并且设置头标题
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            //2.2加载单元格样式
            cell.setCellStyle(headStyle);
            cell.setCellValue(time);


            //3.1创建列标题;并且设置列标题
            HSSFRow row2 = sheet.createRow(2);
            String[] titles = {"城市", "去年批次", "今年批次", "去年数据", "今年数据"};//
            for (int i = 0; i < titles.length; i++) {
                int j = i;
                if (i == 4) {
                    j = i + 2;
                    HSSFCell cell2 = row2.createCell(j);
                    //加载单元格样式
                    cell2.setCellStyle(colStyle);
                    cell2.setCellValue(titles[i]);
                } else {
                    HSSFCell cell2 = row2.createCell(j);
                    //加载单元格样式
                    cell2.setCellStyle(colStyle);
                    cell2.setCellValue(titles[i]);
                }

            }

            HSSFRow rowOne = sheet.createRow(3);
            String[] titleOne = {"总计", "线上", "线下"};
            for (int i = 0; i < titleOne.length; i++) {
                int f = i;
                HSSFCell cell21 = rowOne.createCell(f + 3);
                //加载单元格样式
                cell21.setCellStyle(colStyle);
                cell21.setCellValue(titleOne[i]);
            }

            String[] titleSecond = {"线上与线下", "比率", "关系"};
            for (int i = 0; i < titleSecond.length; i++) {
                int j = 0;
                if (i == 0) {
                    j = 6;
                } else if (i == 1) {
                    j = 9;
                } else if (i == 2) {
                    j = 12;
                }
                HSSFCell cell2 = rowOne.createCell(j);
                //加载单元格样式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titleSecond[i]);
            }

            HSSFRow rowThird1 = sheet.createRow(4);
            for (int i = 0; i < titleOne.length; i++) {

                HSSFCell cell2 = rowThird1.createCell(i + 6);
                //加载单元格样式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titleOne[i]);
            }
            for (int i = 0; i < titleOne.length; i++) {
                HSSFCell cell2 = rowThird1.createCell(i + 9);
                //加载单元格样式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titleOne[i]);
            }

            String[] titleThird3 = {"本人", "配偶", "父母", "子女", "其他"};
            for (int i = 0; i < titleThird3.length; i++) {
                HSSFCell cell2 = rowThird1.createCell(i + 12);
                //加载单元格样式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titleThird3[i]);
            }

5：将数据写入

 //4.操作单元格;将用户列表写入excel
            if (list!= null) {
                int i = 1;
                for (int j = 0; j < list.size(); j++) {


                    //创建数据行,前面有两行,头标题行和列标题行
                    HSSFRow row3 = sheet.createRow(j + 5);

                    HSSFCell cell1 = row3.createCell(0);
                    cell1.setCellStyle(cellStyle);
                    cell1.setCellValue(EmptyUtils.isNotEmpty(list.get(j).getCity()) ? list.get(j).getCity() : "未知");

               ................

                }
            }

6;最后统计的数据

 //设置统计行
            HSSFRow rownumber = sheet.createRow(cityDtos.size() + 5);
            //创建合并单元格对象
            CellRangeAddress address = new CellRangeAddress(cityDtos.size() + 5, cityDtos.size() + 5, 0, 2);//起始行,结束行,起始列,结束列
            sheet.addMergedRegion(address);
            HSSFCell cellnumber = rownumber.createCell(0);
            //普通的excel
            HSSFCell lastTotalNumber = rownumber.createCell(3);

。。。。。

 //加载单元格样式
            cellnumber.setCellStyle(sanStyle);
            cellnumber.setCellValue("总计");
            
            lastTotalNumber.setCellStyle(sanStyle);
            lastTotalNumber.setCellValue(lastToatl + "");

。。。。

7：最后将excel导出。

 response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            //设置浏览器响应头对应的Content-disposition
            String fileName = time + ".xls";
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            workbook.write(response.getOutputStream());
            response.getOutputStream().close();

结果如下

 

参考：java导出Excel合并单元格