业务系统导出导出功能

 

针对业务系统导出导出功能，推荐一个框架：easypoi

官网地址：

http://easypoi.mydoc.io/

 

使用实例：

package test.easypoi;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

public class Test {
    /**
     *  easypoi：
     *      https://blog.csdn.net/LLLLLiSHI/article/details/86740873
     *      下载maven依赖
     *
     * @param args
     */
    public static void main(String[] args) throws Exception{
        // response
        HttpServletResponse response = null;
        // 准备数据
        List<ExportDO> list = new ArrayList<>();
        // 执行导出
        FileUtil.exportExcel(list,"导出内容标题","导出sheet", ExportDO.class, "导出.xls", response);
        // 需要关闭响应流，否则下载的excel打不开
        ServletOutputStream os = response.getOutputStream();
        if(os != null){
            os.flush();
            os.close();
        }
    }
}

 

 

其中FileUtil为工具类，ExportDO为导出实体：

package test.easypoi;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * 导出实体
 */
@Data
public class ExportDO implements Serializable {

    /*
        导出excel
        name：列名；
        orderNum：列出现的顺序
        replace：数组。_后边是源值，_前边是终值
        exportFormat：格式化日期
     */

    /** 姓名    */
    @Excel(name = "姓名", orderNum = "0")
    private String name;
    /**充值类型（01：线上充值；02：线下充值）*/
    @Excel(name = "支付方式", replace = {"线上充值_01", "线下充值_02"}, orderNum = "4")
    private String sex;
    /** 交易开始时间 */
    @Excel(name="交易开始时间", exportFormat = "yyyy-MM-dd HH:mm:ss", orderNum = "7")
    private Date createTime;



}

 

package test.easypoi;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/**
 *  导出工具类
 *
 */
public class FileUtil {
    public FileUtil() {
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws Exception {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws Exception {
        ExportParams exportParams = new ExportParams(title, sheetName);
        // 这个属性：默认HSSF。如果03版excel满足业务使用则默认值即可；对较大数据时：XSSF使用07版excelAPI，并可以处理大数据
        if(fileName.endsWith(".xlsx")){
            exportParams.setType(ExcelType.XSSF);
        }
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws Exception {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }

    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws Exception {
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    }

    /**
     *  依据模板导出
     * @param templatePath 模板
     * @param param 参数
     * @param response .
     * @param fileName 文件名
     */
    public static void exportExcelByTemplate(String templatePath, JSONObject param, HttpServletResponse response, String fileName) throws Exception {
        exportExcelByTemplate(templatePath, param, response, fileName, false, null);
    }

    /**
     *  依据模板导出
     *      设置excel数据有效性： 预先在excel模板某列整体设置好数据有效性，并非通过代码实现（虽然代码也可以实现）
     * @param templatePath 模板
     * @param param 参数
     * @param response .
     * @param fileName 文件名
     * @param isCalculate 是否计算单元格公式
     * @param sheetNum sheet index
     */
    public static void exportExcelByTemplate(String templatePath, JSONObject param, HttpServletResponse response, String fileName, boolean isCalculate, Integer... sheetNum) throws Exception {
        TemplateExportParams template = new TemplateExportParams(templatePath, sheetNum);
        Workbook workbook = ExcelExportUtil.exportExcel(template, param);
        if (workbook != null) {
            if(isCalculate){
                // 需要计算公式时
                workbook.setForceFormulaRecalculation(true);
                final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    for (Row r : sheet) {
                        for (Cell c : r) {
                            if (c != null) {
                                String cell = c.getStringCellValue();
                                if (cell.indexOf("=") == 0) {
                                    c.setCellFormula(cell.substring(1));
                                    evaluator.evaluate(c);
                                }
                            }
                        }
                    }
                }
            }
            downLoadExcel(fileName, response, workbook);
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    public static <T> List<T> importExcel(String filePath, ImportParams params, Class<T> pojoClass) throws Exception {
        if (StringUtils.isBlank(filePath)) {
            return null;
        } else {
            List<T> list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
            return list;
        }
    }

    public static <T> List<T> importExcel(MultipartFile file, ImportParams params, Class<T> pojoClass) throws Exception {
        if (file == null) {
            return null;
        } else {
            List<T> list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
            return list;
        }
    }
}