1.设置导出excel的model
package net.tartan.pds.pojo.excel.standard; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.metadata.BaseRowModel; import lombok.Data; import java.util.Date; @Data public class ExportHistoryProduction extends BaseRowModel { @ExcelProperty(value = "Uwi",index = 0) private String uwi; @ExcelProperty(value = "Date",index = 1) private String date; @ExcelProperty(value = "Gas",index = 2) private Double gasRate; @ExcelProperty(value = "Oil",index = 3) private Double oilRate; @ExcelProperty(value = "Water",index = 4) private Double waterRate; @ExcelProperty(value = "ProductionDays",index = 5) private Integer prodDays; }2.定义excelUtil
package net.tartan.pds.util; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.metadata.BaseRowModel; import com.alibaba.excel.metadata.Sheet; import com.alibaba.excel.support.ExcelTypeEnum; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; public class ExportExcelUtil { /** * 导出 Excel ：一个 sheet，带表头 * * @param response HttpServletResponse * @param list 数据 list，每个元素为一个 BaseRowModel * @param fileName 导出的文件名 * @param sheetName 导入文件的 sheet 名 * @param model 映射实体类，Excel 模型 */ public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName, String sheetName, BaseRowModel model)throws Exception { ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX); Sheet sheet = new Sheet(1, 0, model.getClass()); sheet.setSheetName(sheetName); sheet.setAutoWidth(true); writer.write(list, sheet); writer.finish(); } /** * 导出文件时为Writer生成OutputStream * * @param fileName * @param response * @return */ private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception { try { fileName = URLEncoder.encode(fileName, "UTF-8"); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf8"); response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); response.setHeader("Pragma", "public"); response.setHeader("Cache-Control", "no-store"); response.addHeader("Cache-Control", "max-age=0"); return response.getOutputStream(); } catch (IOException e) { throw new Exception("导出excel表格失败!", e); } } }
3.controller调用，网页直接下载
/** * 到出历史产量 * * @param body * @return */ @RequestMapping(value = "/export/wellCompletion", method = RequestMethod.POST) public void exportCompletionWell(@RequestBody String body,HttpServletResponse response) { try { JSONObject params = JSON.parseObject(body); JSONArray list1 = params.getJSONArray("uwiList"); List<String> uwiList = list1.toJavaList(String.class); List<ExportWellCompletionExcel> list = new ArrayList<>(); // 按条件筛选records if (uwiList != null && uwiList.size()>0) { for (String s : uwiList) { List<WellCompletion> wellCompletions = WellCompletionService.getWellInterListByUwi(s); if (wellCompletions != null && wellCompletions.size()>0) { for (WellCompletion wellCompletion : wellCompletions) { ExportWellCompletionExcel exportWellCompletionExcel = new ExportWellCompletionExcel(); BeanUtils.copyProperties(wellCompletion,exportWellCompletionExcel); list.add(exportWellCompletionExcel); } } } } String name = "完井数据.xlsx"; Date date = Calendar.getInstance().getTime(); SimpleDateFormat time = new SimpleDateFormat("yyyyMMddHHmmss"); String formatDate = time.format(date); // 设置文件名 String fileName = formatDate + name; String sheetName = "sheet1"; // easyexcel工具类实现Excel文件导出 ExportExcelUtil.writeExcel(response, list, fileName, sheetName, new ExportWellCompletionExcel()); } catch (Exception e) { log.error("查询失败", e); throw new BusinessException(CodeMsg.查询失败); } }
