表格数据后端导出

表格数据后端导出

---

前言

上篇文章介绍了利用BootstrapTable实现前端导出，但是前端导出只能导出前端内容显示的数据，若想将原始数据导出，则BootstrapTable显得有些力不从心，因此，本篇记录一下工作中后端导出数据的使用情况，利用的是阿里的EeasyExcel。

---

一、前端

前端代码如下所示：

<iframe id="frameForExport" src=""  style="visibility: hidden;width: 0;height: 0;padding: 0;margin: 0;border: 0" ></iframe><!-- excel导出功能使用 -->
<button class="btn btn-info btn-sm" type="button" style="margin-left: 10px"  onclick="rollDepartmentReportToExcel()">导出数据</button>

    //导出到Excel,使用时需替换相应的请求和后台方法
    function rollDepartmentReportToExcel(){
        var src = "<%=path%>/rest/feeReport/rollDepartmentReportToExcel?year="+$('#s_year').val()+"&yearMonth="+$('#s_yearMonth').val()+"&property="+$('#s_property').val()+"&SBU="+$('#s_SBU').val()+"&project="+$('#s_project').val()+"&subjectLevel="+$('#s_subjectLevel').val()+"&versionCode1="+$('#s_versionCode1').val()+"&versionCode2="+$('#s_versionCode2').val();
        $("#frameForExport").attr("src", src);
    }

二、工具类

此代码用于excel数据处理

2.读入数据

package com.gettop.bi.core.util;


import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.util.StringUtils;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class EasyExcelUtils {
    private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtils.class);

    private static Sheet initSheet;

    static {
        initSheet = new Sheet(1, 0);
        initSheet.setSheetName("sheet");
        // 设置自适应宽度
        initSheet.setAutoWidth(Boolean.TRUE);
    }

    /**
     * 读取少于1000行数据
     *
     * @param filePath 文件绝对路径
     * @return
     */
    public static List<Object> readLessThan1000Row(String filePath) {
        return readLessThan1000RowBySheet(filePath, null);
    }

    /**
     * 读取少于1000行数据，带样式的
     *
     * @param filePath 文件绝对路径
     * @param sheet
     * @return
     */
    public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet) {
        if (!StringUtils.hasText(filePath)) {
            return null;
        }

        sheet = sheet != null ? sheet : initSheet;

        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            return EasyExcelFactory.read(inputStream, sheet);
        } catch (FileNotFoundException e) {
            logger.error("找不到文件或者文件路径错误", e);
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                logger.error("excel文件读取失败，失败原因：{}", e);
            }
        }
        return null;
    }

    /**
     * 读取大于1000行数据
     * @param filePath
     * @return
     */
    public static List<Object> readMoreThan1000Row(String filePath) {
        return readMoreThan1000RowBySheet(filePath, null);
    }

    /**
     * 读取大于1000行数据
     * @param filePath
     * @param sheet
     * @return
     */
    public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet) {
        if (!StringUtils.hasText(filePath)) {
            return null;
        }
        sheet = sheet != null ? sheet : initSheet;

        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            ExcelListener excelListener = new ExcelListener();
            EasyExcelFactory.readBySax(inputStream, sheet, excelListener);
            return excelListener.getDatas();
        } catch (FileNotFoundException e) {
            logger.error("找不到文件或者文件路径错误");
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                logger.error("excel文件读取失败，失败原因：{}", e);
            }
        }
        return null;
    }

    /**
     * 导出单个sheet
     * @param response
     * @param dataList
     * @param sheet
     * @param fileName
     * @throws UnsupportedEncodingException
     */
    public static void writeExcelOneSheet(HttpServletResponse response, List<? extends BaseRowModel> dataList, Sheet sheet, String fileName) throws UnsupportedEncodingException {
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        // 如果sheet为空，则使用默认的
        if (null == sheet) {
            sheet = initSheet;
        }
        try {
            String value = "attachment; filename=" + new String(
                    (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", value);

            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = EasyExcelFactory.getWriter(out, ExcelTypeEnum.XLSX, true);
            // 设置属性类
            sheet.setClazz(dataList.get(0).getClass());
            writer.write(dataList, sheet);
            writer.finish();
            out.flush();
        } catch (IOException e) {
            logger.error("导出失败，失败原因：{}", e);
        }
    }

    /**
     * @Author lockie
     * @Description 导出excel 支持一张表导出多个sheet
     * @Param OutputStream 输出流
     * Map<String, List>  sheetName和每个sheet的数据
     * ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
     * @Date 上午12:16 2019/1/31
     */
    public static void writeExcelMutilSheet(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> dataList, String fileName) throws UnsupportedEncodingException {
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        try {
            String value = "attachment; filename=" + new String(
                    (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", value);
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
            // 设置多个sheet
            setMutilSheet(dataList, writer);
            writer.finish();
            out.flush();
        } catch (IOException e) {
            logger.error("导出异常", e);
        }
    }


    /**
     * @Author lockie
     * @Description //setSheet数据
     * @Date 上午12:39 2019/1/31
     */
    private static void setMutilSheet(Map<String, List<? extends BaseRowModel>> dataList, ExcelWriter writer) {
        int sheetNum = 1;
        for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : dataList.entrySet()) {
            Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
            sheet.setSheetName(stringListEntry.getKey());
            writer.write(stringListEntry.getValue(), sheet);
            sheetNum++;
        }
    }



    /**
     * 导出监听
     */
    @Data
    public static class ExcelListener extends AnalysisEventListener {
        private List<Object> datas = new ArrayList<>();

        /**
         * 逐行解析
         * @param object 当前行的数据
         * @param analysisContext
         */
        @Override
        public void invoke(Object object, AnalysisContext analysisContext) {
            // 当前行
//            analysisContext.getCurrentRowNum()
            if (object != null) {
                datas.add(object);
            }
        }


        /**
         * 解析完所有数据后会调用该方法
         * @param analysisContext
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {

        }
    }


    /**
     * test
     *
     * @param args
     * @param response
     * @throws UnsupportedEncodingException
     */
    public static void main(String[] args, HttpServletResponse response) throws UnsupportedEncodingException {
        // 导出多个sheet
//        List<OrderExportDTO> orderExportDTOList = new ArrayList<>();
//        Map<String, List<? extends BaseRowModel>> map = new HashMap<>();
//        map.put("自营订单", orderExportDTOList);
//        map.put("互联互通", orderExportDTOList);
//        String fileName = new String(("测试导出2019").getBytes(), "UTF-8");
//        writeExcelMutilSheet(response, map, fileName);

        // 导出单个sheet
//        writeExcelOneSheet(response, orderExportDTOList, null, fileName);
    }

}

三、通过实体类画表格

单一表头代码如下：

package com.gettop.bi.web.model.fee;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.ToString;

@Data
@ToString
public class DepartmentReport extends BaseRowModel {
    @ExcelProperty(value = "部门大类", index = 0)
    private String departmentBigSort;

    @ExcelProperty(value = "激励部门",index = 1)
    private String departmentMotivateSort;

    @ExcelProperty(value = "部门分类",index = 2)
    private String departmentSort;

    @ExcelProperty(value = "属性",index = 3)
    private String propertyCode;

    @ExcelProperty(value = "预算全年总额",index = 4)
    private String yearFeeMoneyTotal;

    @ExcelProperty(value = "预算",index = 5)
    private String monthFeeMoneyTotal;

    @ExcelProperty(value = "预算占比",index = 6)
    private String monthFeeMoneyRate;

    @ExcelProperty(value = "公共费用",index = 7)
    private String  ysPublicFeeTotal;

    @ExcelProperty(value = "项目费用-不付费",index = 8)
    private String  ysProjectNoPayTotal;

    @ExcelProperty(value = "项目费用-付费",index = 9)
    private String  ysProjectPayTotal;

    @ExcelProperty(value = "实际",index = 10)
    private String sjFeeTotal;

    @ExcelProperty(value = "实际占比",index = 11)
    private String sjFeeRate;

    @ExcelProperty(value = "GAP",index = 12)
    private String sjFeeGap;

    @ExcelProperty(value = "公共费用",index = 13)
    private String sjPublicFeeMoneyTotal;

    @ExcelProperty(value = "项目费用-不付费",index = 14)
    private String sjProjectNoPayTotal;

    @ExcelProperty(value = "项目费用-付费",index = 15)
    private String sjProjectPayTotal;

    @ExcelProperty(value = "预算全年总人力",index = 16)
    private String ysYearPersonMonth;

    @ExcelProperty(value = "预算人力",index = 17)
    private String ysPersonMonth;

    @ExcelProperty(value = "预算人力占比",index = 18)
    private String ysPersonMonthRate;

    @ExcelProperty(value = "实际",index = 19)
    private String sjPersonMonth;

    @ExcelProperty(value = "实际占比",index = 20)
    private String sjPersonMonthRate;

    @ExcelProperty(value = "GAP",index = 21)
    private String personMonthGap;
}

复杂表头代码如下：

package com.gettop.bi.web.model.fee;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.ToString;

@Data
@ToString
public class RollDepartmentReport extends BaseRowModel {

    @ExcelProperty(value = {"部门大类","部门大类"},index = 0)
    private String departmentBigSort;

    @ExcelProperty(value = {"激励部门","激励部门"},index = 1)
    private String departmentMotivateSort;

    @ExcelProperty(value = {"部门分类","部门分类"},index = 2)
    private String departmentSort;

    @ExcelProperty(value = {"属性","属性"},index = 3)
    private String propertyCode;

    @ExcelProperty(value = {"费用","预算全年总额"},index = 4)
    private String ysYearFeeMoney;

    @ExcelProperty(value = {"费用","预算"},index = 5)
    private String ysMonthFeeMoney;

    @ExcelProperty(value = {"费用","滚动版本1"},index = 6)
    private String ysVersion1FeeMoney;

    @ExcelProperty(value = {"费用","滚动版本1-预算"},index = 7)
    private String feeVersion1Gap;

    @ExcelProperty(value = {"费用","滚动版本2"},index = 8)
    private String ysVersion2FeeMoney;

    @ExcelProperty(value = {"费用","滚动版本2-预算"},index = 9)
    private String feeVersion2Gap;

    @ExcelProperty(value = {"人月","预算全年总人力"},index = 10)
    private String ysYearPersonMonth;

    @ExcelProperty(value = {"人月","预算人力"},index = 11)
    private String ysPersonMonth;

    @ExcelProperty(value = {"人月","滚动版本1"},index = 12)
    private String ysVersion1Person;

    @ExcelProperty(value = {"人月","滚动版本1-预算"},index = 13)
    private String version1PersonGap;

    @ExcelProperty(value = {"人月","滚动版本2"},index = 14)
    private String ysVersion2Person;

    @ExcelProperty(value = {"人月","滚动版本2-预算"},index = 15)
    private String version2PersonGap;

}

如图：

四、controller层

controller代码如下：

 @RequestMapping("/rollDepartmentReportToExcel")
    public void RollDepartmentReportToExcel(BiSubjectInput biSubjectInput,HttpServletResponse response){
        HashMap<String, Object> queryMap = new HashMap<>();
        queryMap.put("year", biSubjectInput.getYear());   //获取后台参数，直接利用数据的实体类
        queryMap.put("yearMonth", biSubjectInput.getYearMonth());
        queryMap.put("property", biSubjectInput.getProperty());
        queryMap.put("SBU", biSubjectInput.getSBU());
        queryMap.put("project",biSubjectInput.getProject());
        queryMap.put("subjectLevel",biSubjectInput.getSubjectLevel());
        queryMap.put("versionCode1",biSubjectInput.getVersionCode1());
        queryMap.put("versionCode2",biSubjectInput.getVersionCode2());

        try {
            // 设置列宽
            Map columnWidth = new HashMap();
            columnWidth.put(0, 8000);

            String fileName = "表名";
            //RollDepartmentReport所画表格的实体类
            List<RollDepartmentReport> rollDepartmentReportList = BiSubjectReportService.getRollDepartmentReportToExcel(queryMap);

            Sheet sheet1 = new Sheet(1, 0, SubjectReport.class);
            sheet1.setColumnWidthMap(columnWidth);
            sheet1.setSheetName(fileName);

            // 导出
            EasyExcelUtils.writeExcelOneSheet(response, rollDepartmentReportList, sheet1, fileName);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

---

五、service层

service代码如下：

	List<Map<String, Object>> getRollDepartmentList(BiSubjectInput input);

service.xml代码如下：

	@Override
	public List<RollDepartmentReport> getRollDepartmentReportToExcel(Map input) {
		return biSubjectReportMapper.getRollDepartmentReportToExcel(input);
	}

六、mapper层

mapper代码如下：

	    List<RollDepartmentReport> getRollDepartmentReportToExcel(@Param(value = "input") Map input);

mapper.xml代码如下：id:指明的是mapper中的方法， parameterType指明的是数据内容的实体类，resultType指明的是excel表格的实体类

    <select id="getRollDepartmentReportToExcel" parameterType="com.gettop.bi.web.model.fee.BiSubjectInput" resultType="com.gettop.bi.web.model.fee.RollDepartmentReport">
                    略.......
    </select>
	}

总结

提示：这里对文章进行总结：

例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。