用HSSFSheet创建复杂的excel表格

来源：互联网发布：黑大网络教学平台编辑：程序博客网时间：2024/05/18 06:10

用HSSFSheet可以很方便的创建如下复杂的excel表格：

商户名称月份订单状态订单数量现金总额(元)投诉件数NIKE2010-11未支付00.005支付中00.00待发货30.00已发货00.00已签收00.00已支付00.00已完结00.00已取消00.00已退费00.00未核实00.00全部30.00红双喜2010-11未支付00.0013支付中25,495.00待发货00.00已发货23,297.00已签收00.00已支付00.00已完结00.00已取消00.00已退费00.00未核实00.00全部48,792.00总和1426,278.5118

得导入poi.jar包，代码如下：

 public void export() throws ServiceException, IOException{        NumberFormat nf = NumberFormat.getInstance();        nf.setMaximumFractionDigits(2);        nf.setMinimumFractionDigits(2);        HSSFWorkbook wb = new HSSFWorkbook();        HSSFSheet sheet = wb.createSheet();        this.initSheet(sheet);//初始化sheet，设置列数和每列宽度        HSSFCellStyle centerStyle = wb.createCellStyle();//设置为水平居中        centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);        centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        HSSFCellStyle rightStyle = wb.createCellStyle();//水平靠右        rightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);        rightStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        this.initHeader(sheet, centerStyle);//初始化头部为水平居中        List list = service.listReport(orderReportVo);//拿出数据        int orderCount = 0;        int INTEGRAL = 0;        double cash = 0;        int complaintCount = 0;        //整理数据        Map<String, Map<Integer, OrderReportVo>> map = new TreeMap<String, Map<Integer,OrderReportVo>>();        for(int i = 0; i < list.size(); i++){            OrderReportVo vo = (OrderReportVo) list.get(i);            orderCount += vo.getOrderCount();            cash += vo.getCashAmount();            complaintCount += vo.getComplaintCount();            String key = vo.getMerchantCode() + vo.getMonth();            Map<Integer, OrderReportVo> tempMap = map.get(key);            if(tempMap == null){                tempMap = new HashMap<Integer, OrderReportVo>();                OrderReportVo tempVo = new OrderReportVo();                tempVo.setMerchantName(vo.getMerchantName());                tempVo.setCashAmount(0.0);                tempVo.setOrderCount(0);                tempVo.setMonth(vo.getMonth());                tempVo.setComplaintCount(0);                for (int j = 0; j < status.length; j++) {                    tempMap.put(status[j], tempVo);                }                map.put(key, tempMap);            }            tempMap.put(vo.getOrderStatus(), vo);        }        if(!AssertUtil.isEmpty(map)){            int rowNumPerMerchant = 11;//每个商家一个月的统计记录占11个表格，10个订单状态+1个全部（统计）            int rowNum = 0;            int merchantCount = 0;//记录数            //按要求创建各单元格            for(Map<Integer, OrderReportVo> report : map.values()){                merchantCount++;                HSSFRow row = sheet.createRow(++rowNum);                OrderReportVo vo0 = report.get(0);                int OrderCountPerMerchant = vo0.getOrderCount();                int ComplaintCountPerMerchant = vo0.getComplaintCount();                double CashAmountPerMerchant = vo0.getCashAmount();                                this.createCell(row, 0, vo0.getMerchantName() , centerStyle);                this.createCell(row, 1, vo0.getMonth(), centerStyle);                sheet.addMergedRegion(new Region(rowNum,(short)0,rowNum + 10,(short)0));                sheet.addMergedRegion(new Region(rowNum,(short)1,rowNum + 10,(short)1));                this.createCell(row, 2, orderStatusToString(0), centerStyle);                this.createCell(row, 3, vo0.getOrderCount(), rightStyle);                this.createCell(row, 4, nf.format(vo0.getCashAmount()), rightStyle);                                //循环按订单状态设置单元格，（i从1开始）                for (int i = 1; i < status.length; i++) {                    OrderReportVo vo = report.get(status[i]);                    OrderCountPerMerchant += vo.getOrderCount();                    ComplaintCountPerMerchant += vo.getComplaintCount();                    CashAmountPerMerchant += vo.getCashAmount();                                            HSSFRow row2 = sheet.createRow(++rowNum);                    this.createCell(row2, 2, orderStatusToString(status[i]), centerStyle);//订单状态                    this.createCell(row2, 3, vo.getOrderCount(), rightStyle);//订单数量                    //this.createCell(row2, 4, vo2.getIntegralAmount(), rightStyle);                    this.createCell(row2, 4, nf.format(vo.getCashAmount()), rightStyle);//现金总额                }                HSSFRow allrow = sheet.createRow(++rowNum);                this.createCell(allrow, 2, "全部", centerStyle);                this.createCell(allrow, 3, OrderCountPerMerchant, rightStyle);                //this.createCell(row, 4, INTEGRAL, rightStyle);                this.createCell(allrow, 4, nf.format(CashAmountPerMerchant), rightStyle);                int rowIndex = (merchantCount-1)*rowNumPerMerchant;//计算投诉件数单元格的行数位置                HSSFRow row0 = sheet.createRow(rowIndex+1);                this.createCell(row0, 5, ComplaintCountPerMerchant, centerStyle);//投诉件数                sheet.addMergedRegion(new Region(rowIndex+1,(short)5,rowIndex+11,(short)5));                            }            //底部总结单元格            HSSFRow row = sheet.createRow(++rowNum);            this.createCell(row, 2, "总和", centerStyle);            this.createCell(row, 3, orderCount, rightStyle);            //this.createCell(row, 4, INTEGRAL, rightStyle);            this.createCell(row, 4, nf.format(cash), rightStyle);            this.createCell(row, 5, complaintCount, centerStyle);//投诉件数                    }        response.setContentType("application/ms-excel;charset=UTF-8");        response.setHeader("Content-disposition", "attachment; filename=" + new String("订单统计报表".getBytes("GBK"), "ISO-8859-1") + ".xls");        OutputStream out = response.getOutputStream();        wb.write(out);        out.flush();        out.close();            }

其中

    //10种订单状态    private final int[] status = new int[]{0,1,2,3,4,5,6,-1,-2,-3};

初始化sheet

    //初始化sheet，设置列数和每列宽度    private void initSheet(HSSFSheet sheet){        sheet.setColumnWidth((short)0, (short) (35.7 * 150));        sheet.setColumnWidth((short)1, (short)(35.7 * 100));        sheet.setColumnWidth((short)2, (short)(35.7 * 150));        sheet.setColumnWidth((short)3, (short)(35.7 * 60));        sheet.setColumnWidth((short)4, (short)(35.7 * 120));        sheet.setColumnWidth((short)5, (short)(35.7 * 120));    }

初始化sheet样式

 /**     * 初始化sheet样式     * @param sheet     * @param style     */    private void initHeader(HSSFSheet sheet,HSSFCellStyle style){        HSSFRow row1 = sheet.createRow((short) 0);        createCell(row1, 0, "商户名称", style);        createCell(row1, 1, "月份", style);        createCell(row1, 2, "订单状态", style);        createCell(row1, 3, "订单数量", style);        createCell(row1, 4, "现金总额(元)", style);        createCell(row1, 5, "投诉件数", style);    }

创建单元格

 /**     * 创建单元格     * @param row 行     * @param column 列位置     * @param value 值     * @param style 样式     */    private void createCell(HSSFRow row,int column,Object value,HSSFCellStyle style){        HSSFCell cell = row.createCell((short)column);        cell.setEncoding((short) 1);        cell.setCellValue(String.valueOf(value));        cell.setCellStyle(style);    }

private  String orderStatusToString(int code){        switch (code) {        case 0:            return "未支付";        case 1:            return "支付中";        case 2:            return "待发货";        case 3:            return "已发货";        case 4:            return "已签收";        case 5:            return "已支付";        case 6:            return "已完结";        case -1:            return "已取消";        case -2:            return "已退费";        case -3:            return "未核实";        default:            return null;        }            }