浅谈HSSF的单元格合并与cell样式

来源:互联网 发布:lsv软件 编辑:程序博客网 时间:2021/10/28 21:41

最近根据公司的需求写了一个关于HSSF的文件导出,就是将后台查到的数据,简单的封装一下,以excel报表的形式导出到本地。其实HSSF真心不难,只是牵扯到表的样式的话会比较复杂一点。这个我个人感觉就是因为HSSF的数据是一个单元格一个单元格的生成的,这样的话就会出现有些但单元格的样式比较多的话,就会需要单个设置,就搞起来比较麻烦。


代码块

public static void createGroupExcel(String fileName,            List<Object[]> activityList, List<Object[]> list,            HttpServletRequest request, HttpServletResponse response)            throws Exception {        // 第一行、第二行的标题        String[] secondRowTitle = { "序号", "销售", "渠道名称", "游客", "性别", "人员关系",                "联系方式", "美签", "加签", "定金", "团款", "押金", "特殊需求", "报名时间", "护照所在",                "住房" };        int rowNum = 0;        // 穿件一个excel文件        HSSFWorkbook workBook = new HSSFWorkbook();        // 创建一个sheet表        HSSFSheet sheet = workBook.createSheet("sheet1");        sheet.setDefaultColumnWidth(12);        sheet.autoSizeColumn(1);        sheet.setDefaultRowHeight((short) 390);        setPersonalPara(fileName, sheet);        // 创建行        HSSFRow titleRow = sheet.createRow((short) rowNum++);        // 字体设置        HSSFFont font = workBook.createFont();        font.setFontName("宋体");        font.setColor(HSSFFont.COLOR_NORMAL);        // 样式        HSSFCellStyle cellStyle = workBook.createCellStyle();        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);        cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);        cellStyle.setFont(font);        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT                .getIndex()); // 背景色-灰色        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);        getNewCenterStyle(cellStyle);        // 设置第一行        HSSFCell titleCell1 = titleRow.createCell(0);        titleCell1.setCellStyle(cellStyle);        titleCell1.setCellValue("团号:");        HSSFCell titleCell2 = titleRow.createCell(2);        titleCell2.setCellStyle(cellStyle);        HSSFCell titleCell3 = titleRow.createCell(7);        titleCell3.setCellStyle(cellStyle);        titleCell3.setCellValue("同行价:");        HSSFCell titleCell4 = titleRow.createCell(9);        titleCell4.setCellStyle(cellStyle);        // 设置第二行        HSSFRow rowTwo = sheet.createRow(rowNum++);        HSSFFont rowTwoCellFont = workBook.createFont();        rowTwoCellFont.setFontName("宋体");        rowTwoCellFont.setColor(HSSFFont.COLOR_NORMAL);        rowTwoCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        HSSFCellStyle rowTwoCellStyle = workBook.createCellStyle();        rowTwoCellStyle.setFont(rowTwoCellFont);        rowTwoCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);        rowTwoCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        getNewCenterStyle(rowTwoCellStyle);        for (int i = 0; i < secondRowTitle.length; i++) {            HSSFCell rowTwoCell = rowTwo.createCell(i);            rowTwoCell.setCellStyle(rowTwoCellStyle);            rowTwoCell.setCellValue(secondRowTitle[i]);        }        // 设置第三行        HSSFRow rowThird = sheet.createRow(rowNum++);        HSSFFont rowThirdCellFont = workBook.createFont();        rowThirdCellFont.setFontName("宋体");        rowThirdCellFont.setColor(HSSFFont.COLOR_NORMAL);        HSSFFont rowThirdCellFont1 = workBook.createFont();        rowThirdCellFont1.setFontName("宋体");        rowThirdCellFont1.setColor(HSSFFont.COLOR_RED);        HSSFCellStyle rowThreeCellStyle = workBook.createCellStyle();        rowThreeCellStyle.setFont(rowThirdCellFont);        rowThreeCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);        rowThreeCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        getNewCenterStyle(rowThreeCellStyle);        HSSFCellStyle rowThreeCellStyle1 = workBook.createCellStyle();        rowThreeCellStyle1.setFont(rowThirdCellFont1);        rowThreeCellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);        rowThreeCellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        getNewCenterStyle(rowThreeCellStyle1);        HSSFCell thirdRowCell1 = rowThird.createCell(0);        thirdRowCell1.setCellStyle(rowThreeCellStyle1);        thirdRowCell1.setCellValue("1");        HSSFCell thirdRowCell2 = rowThird.createCell(1);        thirdRowCell2.setCellStyle(rowThreeCellStyle);        thirdRowCell2.setCellValue("领队:");        HSSFCell thirdRowCell3 = rowThird.createCell(3);        thirdRowCell3.setCellStyle(rowThreeCellStyle);        // 设置金额对齐向左对齐        HSSFCellStyle moneycellStyle = workBook.createCellStyle();        moneycellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);        **moneycellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);**        getNewCenterStyle(moneycellStyle);        // 填充第一、三行的数据        for (int a = 0; a < activityList.size(); a++) {            Object[] ob = activityList.get(a);            titleCell2.setCellValue(ob[0].toString());            titleCell4.setCellValue(ob[1].toString());            thirdRowCell3.setCellValue(ob[2].toString());        }        // 填充第四行之后的数据        Map<Integer, Object> idmap = new HashMap<Integer, Object>();        for (int j = 0; j < list.size(); j++) {            Object[] o = (Object[]) list.get(j);            HSSFRow dataRow = sheet.createRow(rowNum++);            for (int n = 1; n < o.length - 2; n++) {                // 给第一列--序号列设置数值和样式                HSSFCell dataCel1 = dataRow.createCell(0);                dataCel1.setCellStyle(rowThreeCellStyle1);                dataCel1.setCellValue(String.valueOf(rowNum - 2));                // 给第一列之后的列赋值(2,3,4.....)                HSSFCell dataCel = dataRow.createCell(n);                dataCel.setCellStyle(rowThreeCellStyle);                if (o[n + 2] != null) {                    dataCel.setCellValue(o[n + 2].toString());                }                Object pid = o[0];                idmap.put(rowNum - 1, pid);            }            dataRow.getCell(9).setCellStyle(moneycellStyle);            dataRow.getCell(10).setCellStyle(moneycellStyle);            // 替换字符创中的","            HSSFCell dataCel2 = dataRow.getCell(2);            String agentInfo1 = getCellValue(dataCel2);            if (StringUtils.isNotBlank(agentInfo1)) {                String replaceAll = agentInfo1.replaceAll(",", " ");                dataCel2.setCellValue(replaceAll);            } else {                dataCel2.setCellValue("");            }        }        // 补边框        HSSFCellStyle newLeftStyle = workBook.createCellStyle();        newLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);        // getNewCenterStyle(newLeftStyle);        // 合并单元格        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));        sheet.getRow(0).getCell(0).setCellStyle(rowThreeCellStyle);        sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 6));        sheet.getRow(0).getCell(2).setCellStyle(rowThreeCellStyle);        sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 8));        sheet.getRow(0).getCell(7).setCellStyle(rowThreeCellStyle);        sheet.getRow(0).createCell(16).setCellStyle(newLeftStyle);        sheet.addMergedRegion(new CellRangeAddress(0, 0, 9, 15));        sheet.getRow(0).getCell(9).setCellStyle(rowThreeCellStyle);        sheet.getRow(0).getCell(9).setCellStyle(moneycellStyle);        sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 2));        sheet.getRow(2).getCell(1).setCellStyle(rowThreeCellStyle);        sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 15));        sheet.getRow(2).getCell(3).setCellStyle(rowThreeCellStyle);        sheet.getRow(2).createCell(16).setCellStyle(newLeftStyle);        // 读取表格中的信息        for (int j = 3; j < sheet.getLastRowNum(); j++) {            // 在合并之前首先判断是否属于同一笔订单的信息            if (idmap.get(j) != null && idmap.get(j).equals(idmap.get(j + 1))                    || idmap.get(j) == idmap.get(j + 1)) {                //合并销售列                sheet.addMergedRegion(new CellRangeAddress(j, j + 1, 1, 1));                // 合并渠道列                sheet.addMergedRegion(new CellRangeAddress(j, j + 1, 2, 2));                // 合并定金列                sheet.addMergedRegion(new CellRangeAddress(j, j + 1, 9, 9));                // 合并团款列                sheet.addMergedRegion(new CellRangeAddress(j, j + 1, 10, 10));                //合并日期                sheet.addMergedRegion(new CellRangeAddress(j, j + 1, 13, 13));            }        }        // 导出excel文档        OutputStream op = null;        fileName = fileName + ".xls";        response.reset();        response.setContentType("application/vnd.ms-excel");        setFileDownloadHeader(request, response, fileName);        op = response.getOutputStream();        workBook.write(op);        op.close();    }        /**     * 定制单元格边框     */    private static void getNewCenterStyle(HSSFCellStyle style) {        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);        style.setBorderRight(HSSFCellStyle.BORDER_THIN);        style.setBorderTop(HSSFCellStyle.BORDER_THIN);        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);        /* return style; */    }

这里写图片描述

注意:

之前有看人专门写的合并单元格的方法,我感觉没必要,只要会使用CellRangeAddress再配合sheet表的合并方法,就行了,只是合并之后的单元格的边框,需要外设置一下,还有就是单元格内容水平居中的问题,大家一定要看清,HSSFCellStyle.VERTICAL_CENTER与HSSFCellStyle.ALIGN_CENTER的区别,别再烦我这种低级错误了!

号外号外:

本人第一次写博客,经验不足,还请各位看官多多包涵,如有什么问题可以直接留言哈!
另外以后会陆续更新博客,还请大家多多关注啊!

2 0