@RequestMapping(value = "/batchExport", method = RequestMethod.POST)
public void batchExport(GoodsStore goodsStore,HttpServletResponse response,HttpServletRequest request){
List<?> goodsStoreLiat= service.exportList(bean);
OutputStream sos = null;
String filename=String.valueOf(System.currentTimeMillis());
response.setContentType("application/msexcel;charset=UTF-8");
try {
sos = response.getOutputStream();
response.addHeader("Content-Disposition", "attachment;filename=\""+ new String((filename+".xlsx").getBytes("GBK"), "ISO8859_1") + "\"");
Sheet tempSheet = null;
int num = 2;//从第几行还是写入数据 0为第一行1为第二行
int cellnum = 0;
//从jar包获取模板
XSSFWorkbook templatewb = new XSSFWorkbook(GoodsStoreController.class.getClassLoader().getResource("template/门店商品导出模板.xlsx").openStream());
tempSheet = templatewb.getSheetAt(0);//获取第一个sheet页
templatewb.setSheetName(0, "商品列表");
//设置样式
CellStyle styleText = templatewb.createCellStyle();
// 获取模板sheet页
styleText.setAlignment(HorizontalAlignment.RIGHT);
styleText.setVerticalAlignment(VerticalAlignment.CENTER);
// 增加表格边框的样式 例子
// styleText.setBorderBottom(BorderStyle.THIN);
// styleText.setBorderLeft(BorderStyle.THIN);// 左边框
// styleText.setBorderRight(BorderStyle.THIN);// 右边框
// styleText.setBorderTop(BorderStyle.THIN);// 上边框
// 用POI导出excel时,较长的数字不想被自动变为科学计数法的解决方式
DataFormat format = templatewb.createDataFormat();
styleText.setDataFormat(format.getFormat("@"));
// 1) 文本:vnd.ms-excel.numberformat:@
// 2) 日期:vnd.ms-excel.numberformat:yyyy/mm/dd
// 3) 数字:vnd.ms-excel.numberformat:#,##0.00
// 4) 货币:vnd.ms-excel.numberformat:¥#,##0.00
// 5) 百分比:vnd.ms-excel.numberformat: #0.00%
Font font = templatewb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);// 字体大小
styleText.setFont(font); // 调用字体样式对象
styleText.setWrapText(false);//不自动换行
//将数据写入excel
if(goodsStoreLiat!=null){
int dataSize=goodsStoreLiat.size();
for (int i = 0; i < dataSize; i++) {
Row row = tempSheet.createRow(num++);
//row.setHeightInPoints(22); // 设置行高
GoodsStore goods = goodsStoreLiat.get(i);
cellnum = -1;
// 商品sku
Cell sku = row.createCell(++cellnum);
sku.setCellValue(goods.getSku());
sku.setCellStyle(styleText);
}
}
templatewb.write(sos);
templatewb.close();
} catch (Exception e){
logger.error("门店商品导出失败",e);
}finally{
if(null!=sos){
try {
sos.flush();
sos.close();
} catch (Exception e2) {
}
}
}
}
jave excel导出
最新推荐文章于 2023-06-09 16:42:49 发布