@RequestMapping("cusAreasExcelExport")
public void cusAreasExcelExport(HttpServletRequest request,HttpServletResponse response) throws IOException{
logger.info("客户月结订单导出");
String ids=request.getParameter("gdIds");
String[] gdIds = ids.substring(0, ids.length()-1).split(",");
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = workBook.createSheet("客户月结统计表");
String fileName = new String("客户月结统计表".getBytes(),"iso-8859-1")+".xls";
int rownum = 0;
String[] headers = {"下单日期","单号","订单状态","结款状态","客户","电话","运费","加急费用","特殊费用","总计"};
HSSFRow headRow = sheet.createRow(rownum++);
HSSFCellStyle cellStyle = workBook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
sheet.addMergedRegion(new CellRangeAddress(0,0,0,headers.length-1));
HSSFCell headCell=headRow.createCell(0);
headCell.setCellValue("客户月结统计表");
headCell.setCellStyle(cellStyle);
HSSFRow row = sheet.createRow(rownum++);
for(int i = 0;i < headers.length;i++){
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
BigDecimal freightTotal = new BigDecimal("0.00");
BigDecimal jiajiTotal = new BigDecimal("0.00");
BigDecimal teshuTotal = new BigDecimal("0.00");
BigDecimal allTotal = new BigDecimal("0.00");
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for(String gdId:gdIds){
N25175Goods gd = gdService.selectByPrimaryKey(Integer.parseInt(gdId));
HSSFRow row1 = sheet.createRow(rownum++);
//下单日期
row1.createCell(0).setCellValue(format.format(gd.getGdCtime()));
//单号
row1.createCell(1).setCellValue(gd.getGdNo());
//订单状态
if(gd.getGdState()== ConstantUtil.GD_STATE_DAIJIEDAN_0){
row1.createCell(2).setCellValue("待接单");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_YIJIEDAN_1){
row1.createCell(2).setCellValue("已接单");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_YILANJIAN_2){
row1.createCell(2).setCellValue("已揽件");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_DAIFACHE_3){
row1.createCell(2).setCellValue("待发车");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_YIFACHE_4){
row1.createCell(2).setCellValue("已发车");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_YIDAOHUO_5){
row1.createCell(2).setCellValue("已到货");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_DAIPAISONG_6){
row1.createCell(2).setCellValue("待派送");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_PAISONGZHONG_7){
row1.createCell(2).setCellValue("派送中");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_YIQIANSHOU_8){
row1.createCell(2).setCellValue("已签收");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_TUIJIAN_9){
row1.createCell(2).setCellValue("退件");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_YIQUXIAO_10){
row1.createCell(2).setCellValue("已取消");
}else if(gd.getGdState() == ConstantUtil.GD_STATE_ZITI_11){
row1.createCell(2).setCellValue("自提");
}
//结款状态
if(gd.getGdPaymethod() == ConstantUtil.GD_PAYMETHOD_YUEJIEWEIJIE_2){
row1.createCell(3).setCellValue("月结未结");
}else if(gd.getGdPaymethod()==ConstantUtil.GD_PAYMETHOD_YUEJIEYIJIE_3){
row1.createCell(3).setCellValue("月结已结");
}
//客户姓名
row1.createCell(4).setCellValue(gd.getCusName1());
//客户电话
row1.createCell(5).setCellValue(gd.getCusPhone1());
//运费
row1.createCell(6).setCellValue(gd.getGdFreight().doubleValue());
freightTotal=freightTotal.add(gd.getGdFreight()==null?zero:gd.getGdFreight());
//加急费用
row1.createCell(7).setCellValue(gd.getGdJiaji().doubleValue());
jiajiTotal = jiajiTotal.add(gd.getGdJiaji()==null?zero:gd.getGdJiaji());
//特殊费用
row1.createCell(8).setCellValue(gd.getGdTeshu().doubleValue());
teshuTotal = teshuTotal.add(gd.getGdTeshu()==null?zero:gd.getGdTeshu());
//总计
row1.createCell(9).setCellValue(gd.getGdTotal().doubleValue());
allTotal=allTotal.add(gd.getGdTotal()==null?zero:gd.getGdTotal());
}
//统计行
HSSFRow row2 = sheet.createRow(++rownum);
row2.createCell(0).setCellValue("总计:");
//运费总和
row2.createCell(6).setCellValue(freightTotal.doubleValue());
//加急费用
row2.createCell(7).setCellValue(jiajiTotal.doubleValue());
//特殊费用
row2.createCell(8).setCellValue(teshuTotal.doubleValue());
//总计
row2.createCell(9).setCellValue(allTotal.doubleValue());
//宽度为1.5倍
for(int i = 0;i < headers.length; i++){
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, sheet.getColumnWidth(i)*15/10);
}
response.setContentType("application/octent-stream");
response.setHeader("Content-disposition","attachement;filename="+fileName);
response.flushBuffer();
workBook.write(response.getOutputStream());
}
java poi excel 导出
最新推荐文章于 2024-07-04 15:49:17 发布