怎么在springboot中实现excel文件在浏览器下载导出

文章参考链接：https://blog.youkuaiyun.com/u010277446/article/details/85130087

第一步：导入jar包依赖

注意这里使用的是poi方式还可以利用esayexcel的方法。

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <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>

第二步：导入工具类文件

1、导入ExportExcelUtil .java

@Service
public class ExportExcelUtil {

    public void expoerDataExcel(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String,Object>> src_list) throws IOException {

        String xlsFile_name = DateUtils.currtimeToString14() + ".xlsx";     //输出xls文件名称
        //内存中只创建100个对象
        Workbook wb = new SXSSFWorkbook(100);           //关键语句
        Sheet sheet = null;     //工作表对象
        Row nRow = null;        //行对象
        Cell nCell = null;      //列对象

        int rowNo = 0;      //总行号
        int pageRowNo = 0;  //页行号

        for (int k=0;k<src_list.size();k++) {
            Map<String,Object> srcMap = src_list.get(k);
            //写入300000条后切换到下个工作表
            if(rowNo%300000==0){
                wb.createSheet("工作簿"+(rowNo/300000));//创建新的sheet对象
                sheet = wb.getSheetAt(rowNo/300000);        //动态指定当前的工作表
                pageRowNo = 0;      //新建了工作表,重置工作表的行号为0
                // -----------定义表头-----------
                nRow = sheet.createRow(pageRowNo++);
                // 列数 titleKeyList.size()
                for(int i=0;i<titleKeyList.size();i++){
                    Cell cell_tem = nRow.createCell(i);
                    cell_tem.setCellValue(titleMap.get(titleKeyList.get(i)));
                }
                rowNo++;
                // ---------------------------
            }
            rowNo++;
            nRow = sheet.createRow(pageRowNo++);    //新建行对象

            // 行，获取cell值
            for(int j=0;j<titleKeyList.size();j++){
                nCell = nRow.createCell(j);
                if (srcMap.get(titleKeyList.get(j)) != null) {
                    nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString());
                } else {
                    nCell.setCellValue("");
                }
            }
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name);
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }
}

2、导入ColumnTitleMap .java
这个是表头和数据对应文件，输入你直接的表头参数和中文对照。

public class ColumnTitleMap {
    private Map<String, String> columnTitleMap = new HashMap<String, String>();
    private ArrayList<String> titleKeyList = new ArrayList<String>();

    public ColumnTitleMap(String datatype, List<String> str) {
        switch (datatype) {
            case "AuditLogAlarmItemVo":
                getAuditLogInfoTableData(str);
                break;
            default:
                break;
        }

    }
    /**
     * mysql用户表需要导出字段--显示名称对应集合
     */

    private void getAuditLogInfoTableData(List<String> strParam) {

        AuditLogAlarmItemVo vo=new  AuditLogAlarmItemVo();
        Field[] fields = vo.getClass().getDeclaredFields();//通过反射获取到该对象
        Map<String, String> aLAItemMap = getAuditLogAlarmItemVo();
        Map<String, String> auditLAItemMap=new HashMap<>();
        if(strParam.isEmpty()||strParam.size()==0) {}
        strParam.stream().forEach(str->{
            auditLAItemMap.put(str,"");});
        auditLAItemMap.forEach((s, s2) -> {
            aLAItemMap.forEach((s1, s21) -> {
                if(s.equals(s1)){
                    s21=s2;
                }
            });
        });
        for (Field field : fields) {
            field.setAccessible(true);//当要读取的属性为私有时，要设置为true
            String str= field.getName();//获取对象的属性名
            if(auditLAItemMap.containsKey(str)){
                  if(!str.equals("vendorId")&&!str.equals("softVersion")){
                      if(str.equals("alarmTime")) {
                          titleKeyList.add("fomatTime");
                      }
                      else {
                          titleKeyList.add(str);
                      }
                  }
            }
        }
    }
    public Map<String, String> getAuditLogAlarmItemVo() {
        columnTitleMap.put("alarmId","日志编号");
        columnTitleMap.put("deviceNumber","设备编号");
        columnTitleMap.put("vendorName","厂商");
        columnTitleMap.put("eventType","审日志类型");
        columnTitleMap.put("optType","操作类型");
        columnTitleMap.put("user","操作用户");
        columnTitleMap.put("fomatTime","事件时间");
        columnTitleMap.put("message","日志详情");
        return columnTitleMap;
    }


    public Map<String, String> getColumnTitleMap() {
        return columnTitleMap;
    }
    public ArrayList<String> getTitleKeyList() {
        return titleKeyList;
    }
}

3、导入ListToMapListUtils.java
该文件是数据库查询到的对象集合，然后需要把对象集合转换为map<String,object>的形式就调用该类中的方法。

public class ListToMapListUtils {
    public static  <T> List<Map<String, Object>> objectList2ListMap(List<T> objectList) throws Exception {
        ArrayList<Map<String, Object>> resultList = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();
        for (T t : objectList) {
            resultList.add(object2Map(t));
        }
        return resultList;
    }
    public static Map<String,Object> object2Map(Object obj) throws Exception{
        Map<String,Object> map = new HashMap<String, Object>();
        Field[] fields = obj.getClass().getDeclaredFields();
        for(Field field:fields){
            field.setAccessible(true);
            map.put(field.getName(), field.get(obj));
        }
        return map;
    }
}

4、在controller中写接口实现方法
注意接口中利用service服务层查数据库需要写自己的数据库服务层查询方法。

@GetMapping("test/exportByPage")
    public String getAuditLogListByPage(
            HttpServletResponse response,
            @SpringQueryMap @Valid AuditAlarmQueryParam queryParam
    ) throws IOException {
        try {
            CommonPageResult<AuditLogAlarmItemVo> re = alarmService.getAuditInfo(queryParam);
            List<AuditLogAlarmItemVo> items = re.getItems();
            items.stream().forEach(a->{
                Date date = new Date(a.getAlarmTime());
                String format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
                a.setFomatTime(format);
            });
            List<Map<String, Object>> maps = ListToMapListUtils.objectList2ListMap(items);
            String  ss= queryParam.getTableHead();        
            String[] tableHead1 = ss.split(",");
            List<String> tableHead= Arrays.asList(tableHead1);
            ArrayList<String> titleKeyList = new ColumnTitleMap("AuditLogAlarmItemVo",tableHead).getTitleKeyList();
            Map<String, String> titleMap = new ColumnTitleMap("AuditLogAlarmItemVo",tableHead).getColumnTitleMap();
           exportExcelUtil.expoerDataExcel(response, titleKeyList, titleMap, maps);
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }