1.导出excel文件效果
2.说明
?
? ? ? ? 先要制作一个空的excel模板（也可以全由代码写成，使用excel模板的好处是，不用代码去实现复杂的排版，只需要填写内容就可），注意文件内容不能太大，否则在生成图片时可能会导致内存溢出（内容非常多的建议不要导出带图片的）。
<!-- poi处理 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.github.ulisesbocchio</groupId>
<artifactId>jasypt-spring-boot-starter</artifactId>
<version>3.0.3</version>
</dependency>3.代码
? ? ? ? controller层
// 导出用户购物车明细 带图片
@RequestMapping("/exportusertruckdetail")
public ResponseEntity<AbstractResource> exportUserTruckDetail(@RequestParam(value="data") String data)
{
logger.debug("ExportController.exportUserTruckDetail in, param:{}", data);
try {
JSONObject json = JSON.parseObject(data);
String userId = json.getString("userId");
Date startDate = json.getDate("startDate");
Date endDate = json.getDate("endDate");
String companyId = json.getString("companyId");
boolean picFlag = json.getBooleanValue("picFlag");
File excel = exportService.exportUserTruckDetail(companyId, userId, startDate, endDate, picFlag);
return HttpUtils.export(excel);
} catch (Exception e) {
logger.error("errorCode : filemanage_exportusertruckdetail_sys, errorMessages : 导出用户购物车明细异常！", e);
return HttpUtils.exportFailed(e.getMessage());
}
}
// HttpUtils.export
public static ResponseEntity<AbstractResource> export(File file) {
if (file == null) {
return null;
} else {
HttpHeaders headers = new HttpHeaders();
headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
headers.add("Content-Disposition", "attachment; filename=" + file.getName());
headers.add("Pragma", "no-cache");
headers.add("Expires", "0");
headers.add("Last-Modified", (new Date()).toString());
headers.add("ETag", String.valueOf(System.currentTimeMillis()));
return ((BodyBuilder)ResponseEntity.ok().headers(headers)).contentLength(file.length()).contentType(MediaType.parseMediaType("application/octet-stream")).body(new FileSystemResource(file));
}
}
// HttpUtils.exportFailed
public static ResponseEntity<AbstractResource> exportFailed(String message) {
InputStream ins = new ByteArrayInputStream(message.getBytes());
return ResponseEntity.ok().contentType(MediaType.parseMediaType("application/json;charset=UTF-8")).body(new InputStreamResource(ins));
}? ? ? ? service层
@Override
public File exportUserTruckDetail(String companyId, String userId, Date startDate, Date endDate, boolean picFlag) throws Exception {
List<Map<String, Object>> detailList = unusualMapper.truckUserDetail(companyId, startDate, endDate, userId);
List<JSONObject> excelDataList = new ArrayList<>(detailList.size());
String sheetName = null;
for (Map<String, Object> detailMap : detailList) {
if(StringUtils.isBlank(sheetName)){
sheetName = detailMap.get("phone").toString();
}
JSONObject retJson = ExcelUtil.getStandardSummary(detailMap.get("standardDetail").toString());
detailMap.putAll(retJson);
JSONObject excelData = JsonUtils.bean2JSON(detailMap);
excelDataList.add(excelData);
}
if(StringUtils.isBlank(sheetName)){
sheetName = "无";
}
return createUserTruckExcel(excelDataList, sheetName, startDate, endDate, picFlag);
}
private File createUserTruckExcel(List<JSONObject> excelDataList, String sheetName, Date startDate, Date endDate, boolean picFlag)
{
long startTime = System.currentTimeMillis();
// 复制文件
Date now = new Date();
String basePath = new ApplicationHome(this.getClass()).getSource().getParentFile().getPath()
+ File.separator + "files" + File.separator;
File exportFile = new File(basePath + "ymhc_user_truck_" + DateUtils.getStrDate(now,"yyyyMMdd_HHmmssSSS") + ".xlsx");
try {
Files.copy(new File(basePath + "ymhc_user_truck.xlsx").toPath(),
exportFile.toPath());
FileInputStream fileInputStream = new FileInputStream(exportFile);
XSSFWorkbook book = new XSSFWorkbook(fileInputStream);
// 处理sheet名字
XSSFSheet sheet = book.getSheetAt(0);
book.setSheetName(0, sheetName);
CellStyle style = book.createCellStyle();
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
style.setVerticalAlignment(VerticalAlignment.CENTER); // 水平居中
style.setAlignment(HorizontalAlignment.CENTER); // 垂直居中
style.setWrapText(true);
XSSFFont font = book.createFont();
font.setFontHeightInPoints((short) 10);
font.setFontName("宋体");
style.setFont(font);
CellStyle numStyle = book.createCellStyle();
numStyle.setBorderBottom(BorderStyle.THIN); //下边框
numStyle.setBorderLeft(BorderStyle.THIN);//左边框
numStyle.setBorderTop(BorderStyle.THIN);//上边框
numStyle.setBorderRight(BorderStyle.THIN);//右边框
numStyle.setWrapText(true);
XSSFDataFormat df = book.createDataFormat();
numStyle.setDataFormat(df.getFormat("#,#0.00"));
// 导出时间
XSSFRow row0 = sheet.getRow(1);
XSSFCell cell1 = row0.getCell(14);
XSSFFont font10 = book.createFont();
font10.setFontHeightInPoints((short) 11);
font10.setFontName("宋体");
font10.setBold(true);
CellStyle style01 = book.createCellStyle();
style01.setFont(font10);
style01.setAlignment(HorizontalAlignment.CENTER);
style01.setVerticalAlignment(VerticalAlignment.CENTER);
String cell01 = DateUtils.getStrDate(startDate, "yyyy/MM/dd") + " 至 " + DateUtils.getStrDate(endDate, "yyyy/MM/dd");
cell1.setCellValue(cell01);
cell1.setCellStyle(style01);
int ossPicTotal = 0; // excel图片
XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch(); // 插入图片
Map<String, String> localPicMap = new HashMap<>(); // 本地图片缓存 itemId localPath
XSSFRow row;
int orderRowLength = excelDataList.size();
for (int j = 0; j < orderRowLength; j++) {
JSONObject excelData = excelDataList.get(j);
int rowIndex = j + 3;
row = sheet.createRow(rowIndex);
row.setHeight((short)(100*20));
JSONObject parseDataJson = parseTruckUserData(excelData);
// 1 序号
row.createCell(0).setCellValue(j+1);
// 2 店铺名
row.createCell(1).setCellValue(excelData.getString("company_name"));
// 3 产品款号
row.createCell(2).setCellValue(excelData.getString("productCode"));
// 4 商品图片
int picIndex = 3;
boolean insertPicFlag = false;
String briefPath = excelData.getString("brief_path");
String productId = excelData.getString("product_id");
try{
row.createCell(picIndex).setCellValue("");
if(picFlag && ossPicTotal < 1000){ // 导出图片最大
String productLocalPic = basePath + "pic" + File.separator + productId + briefPath.substring(briefPath.lastIndexOf("."));
if(localPicMap.containsKey(productId)){
productLocalPic = localPicMap.get(productId);
logger.debug("本地已下载图片:{}", productLocalPic);
}else{
// 下载到本地，并缓存
String httpUrlPath = constantDataUtil.signaturePicture(briefPath,250, 90);
if(!ExcelUtil.downloadPic(httpUrlPath, productLocalPic, false)){
productLocalPic = ""; // 下载图片失败，缓存空，后续不再下载
}
localPicMap.put(productId, productLocalPic);
}
if(StringUtils.isNotBlank(productLocalPic)){// 插入图片
ExcelUtil.insertExcelPic(book, drawingPatriarch, rowIndex, picIndex, productLocalPic);
ossPicTotal++;
insertPicFlag = true;
}
}
}catch (Exception e){
logger.error("{}图片插入失败!", briefPath);
}
if(!insertPicFlag){ // 导出有效期为24小时的图片链接
row.createCell(picIndex).setCellValue(constantDataUtil.signaturePicture(briefPath,800,90,24*60));
}
// 5 商品名称
row.createCell(4).setCellValue(excelData.getString("product_name"));
// 6 选购规格
row.createCell(5).setCellValue(excelData.getString("selectStandardStr"));
// 7 选购件数
row.createCell(6).setCellValue(excelData.getString("goods_amount"));
// 8 选购价格
row.createCell(7).setCellValue(excelData.getString("showPrice"));
// 9 所有颜色
row.createCell(8).setCellValue(parseDataJson.getString("colorStr"));
// 10 所有尺码
row.createCell(9).setCellValue(parseDataJson.getString("sizeStr"));
// 11 价格范围
row.createCell(10).setCellValue(parseDataJson.getString("allPrice"));
// 12 供应商名称
row.createCell(11).setCellValue(excelData.getString("supplierName"));
// 13 供应商地址
row.createCell(12).setCellValue(excelData.getString("address"));
// 14 商品状态
row.createCell(13).setCellValue(ExcelUtil.getItemState(excelData.getString("item_state")));
// 15 加购时间
row.createCell(14).setCellValue(excelData.getString("create_date"));
// 16 备注
row.createCell(15).setCellValue("");
for(int k = 0; k < 16; k++) {
row.getCell(k).setCellStyle(style);
}
}
OutputStream os = new FileOutputStream(exportFile);
os.flush();
book.write(os);
book.close();
os.close();
fileInputStream.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
logger.debug("导出文件完成，花费时间:{}", System.currentTimeMillis()-startTime);
return exportFile;
}
