用HSSFSheet可以很方便的创建如下复杂的excel表格:
商户名称 | 月份 | 订单状态 | 订单数量 | 现金总额(元) | 投诉件数 |
NIKE | 2010-11 | 未支付 | 0 | 0.00 | 5 |
支付中 | 0 | 0.00 | |||
待发货 | 3 | 0.00 | |||
已发货 | 0 | 0.00 | |||
已签收 | 0 | 0.00 | |||
已支付 | 0 | 0.00 | |||
已完结 | 0 | 0.00 | |||
已取消 | 0 | 0.00 | |||
已退费 | 0 | 0.00 | |||
未核实 | 0 | 0.00 | |||
全部 | 3 | 0.00 | |||
红双喜 | 2010-11 | 未支付 | 0 | 0.00 | 13 |
支付中 | 2 | 5,495.00 | |||
待发货 | 0 | 0.00 | |||
已发货 | 2 | 3,297.00 | |||
已签收 | 0 | 0.00 | |||
已支付 | 0 | 0.00 | |||
已完结 | 0 | 0.00 | |||
已取消 | 0 | 0.00 | |||
已退费 | 0 | 0.00 | |||
未核实 | 0 | 0.00 | |||
全部 | 4 | 8,792.00 | |||
总和 | 14 | 26,278.51 | 18 |
得导入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;
- }
- }
- 可参考hr系统excel导入导出功能