欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

通过easyexcel导出excel

程序员文章站 2022-03-15 16:30:49
...

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.查询失败);
        }
    }