用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包，代码如下：
[html]
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();
}
其中
[html]
//10种订单状态 private final int[] status = new int[]{0,1,2,3,4,5,6,-1,-2,-3};
初始化sheet
[html]
//初始化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样式
[html]
/**
* 初始化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);
}
创建单元格
[html]
/**
* 创建单元格
* @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);
}
[html]
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;
}
}
