protected HttpServletResponse response; //初始化对象
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + DateUtil.format(new Date(),"yyyy-MM-dd") +".xls");//指定下载的文件名
OutputStream out = response.getOutputStream();
HSSFWorkbook wb = useStorgeBatService.exportUseStorageBat(params);//调用查询数据方法(构建excel机构)
if(wb!=null){
wb.write(out);
}
out.flush();
out.close();
//导出excel构建数据
public HSSFWorkbook exportUseStorageBat(Map<String, Object> params)throws YTOXLException {
//数据集合
Collection<ZebraUseStorgeBatModel> result = batMapper.searchUseStorgeBat(params);
//创建EXCEL工具对象
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
ExportExcelPoi exportExcel = new ExportExcelPoi(wb, sheet);
//创建列标头LIST
List<String> headList = new ArrayList<String>();
headList.add("仓库");
headList.add("商品名称");
headList.add("商家商品编码");
headList.add("规格编码/规格名称");
headList.add("商品型号");
headList.add("可销售库存");
headList.add("正品库存");
headList.add("坏品库存");
headList.add("批次属性");
// 计算该报表的列数
int number = headList.size();
// 给工作表列定义列宽(实际应用自己更改列数)
for (int i = 0; i < number; i++) {
sheet.setColumnWidth(i, 4000);
}
// 创建单元格样式
HSSFCellStyle cellStyle = wb.createCellStyle();
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 指定当单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 创建报表头部
exportExcel.createNormalHead("批次库存余量", number-1);
HSSFRow row2 = sheet.createRow(1);
// 设置行高
row2.setHeight((short) 800);
HSSFCell row2Cell = null;
int m = 0;
// 创建不同的LIST的列标题
for (int i = 0; i < number; i++) {
row2Cell = row2.createCell(i);
row2Cell.setCellStyle(cellStyle);
row2Cell.setCellValue(new HSSFRichTextString(headList.get(m).toString()));
m++;
}
// 循环创建中间的单元格的各项的值
int rowIndex = 2;
for(ZebraUseStorgeBatModel stock:result) {
HSSFRow row = sheet.createRow((short) rowIndex);
rowIndex ++;
//仓库
exportExcel.cteateCell(wb, row, (short) 0,HSSFCellStyle.ALIGN_CENTER_SELECTION, stock.getStorageName());
//商品名称
exportExcel.cteateCell(wb, row, (short) 1,HSSFCellStyle.ALIGN_CENTER_SELECTION, stock.getProductName());
//商家商品编码
exportExcel.cteateCell(wb, row, (short) 2,HSSFCellStyle.ALIGN_CENTER_SELECTION, stock.getProductCode());
//规格编码/规格名称
exportExcel.cteateCell(wb, row, (short) 3,HSSFCellStyle.ALIGN_CENTER_SELECTION, outSkuAndName(stock.getSkuCode(),stock.getSkuname()));
//商品型号
exportExcel.cteateCell(wb, row, (short) 4,HSSFCellStyle.ALIGN_CENTER_SELECTION, stock.getPattern());
//可销售库存
exportExcel.cteateCell(wb, row, (short) 5,HSSFCellStyle.ALIGN_CENTER_SELECTION, String.valueOf(stock.getSellstock()));
//正品库存
exportExcel.cteateCell(wb, row, (short) 6,HSSFCellStyle.ALIGN_CENTER_SELECTION, String.valueOf(stock.getGoodstock()));
//坏品库存
exportExcel.cteateCell(wb, row, (short) 7,HSSFCellStyle.ALIGN_CENTER_SELECTION, String.valueOf(stock.getBadstock()));
//批次属性
exportExcel.cteateCell(wb, row, (short) 8,HSSFCellStyle.ALIGN_CENTER_SELECTION, stock.getBatAttr());
}
return wb;
}