springboot+mybatis+vue导出数据库数据为excel

冷不防 2023-06-07 10:53 14阅读 0赞

pom文件：

<dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi-ooxml</artifactId>
       <version>3.16</version>
    </dependency>

工具类：

@Repository
    @Slf4j
    public class Common {
    
    
        public static void startPage(Map<String, Object> query) {
            int pageNum = 0;
            int pageSize = 0;
    
            if (query.containsKey("pageNum") && query.containsKey("pageSize")) {
                pageNum = Integer.parseInt(String.valueOf(query.get("pageNum")));
                pageSize = Integer.parseInt(String.valueOf(query.get("pageSize")));
            } else {
                log.error("StartPage Failed!");
            }
    
            PageHelper.startPage(pageNum, pageSize);
        }
    
        public static XSSFWorkbook exportExcel(Class schema, Map<String, String> title, List<?> list) {
            try {
                XSSFWorkbook workbook = new XSSFWorkbook();
                XSSFSheet sheet = workbook.createSheet("sheet1");
    
                sheet.setDefaultColumnWidth((short) 15);
    
                XSSFRow row = sheet.createRow(0);
    
                Field[] fields = schema.getDeclaredFields();
    
                int index1 = 0;
                for (Field field : fields) {
                    if (title.containsKey(field.getName())) {
                        XSSFCell cell = row.createCell(index1);
                        XSSFRichTextString text = new XSSFRichTextString(title.get(field.getName()));
                        cell.setCellValue(text);
                        index1++;
                    }
                }
    
                for (int i = 0, len = list.size(); i < len; i++) {
                    row = sheet.createRow(i + 1);
                    Object object = list.get(i);
    
                    int index2 = 0;
                    for (Field field: fields) {
                        String filedName = field.getName();
    
                        if (title.containsKey(filedName)) {
                            XSSFCell cell = row.createCell(index2);
    
                            String getMethodName = "get" + filedName.substring(0, 1).toUpperCase() + filedName.substring(1);
    
                            Method getMethod = schema.getMethod(getMethodName);
                            Object value = getMethod.invoke(object);
                            XSSFRichTextString text = new XSSFRichTextString(String.valueOf(value));
                            cell.setCellValue(text);
    
                            index2++;
                        }
                    }
                }
    
                return workbook;
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
    
            return null;
        }
    
        public static void exportFile(HttpServletResponse response, XSSFWorkbook workbook) {
            if (workbook != null) {
                String fileName = "gzyr_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xlsx";
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
                response.setContentType("application/octet-stream;charset=GB2312");
                response.setCharacterEncoding("GB2312");
                OutputStream outputStream;
                try {
                    outputStream = response.getOutputStream();
                    workbook.write(outputStream);
                    outputStream.flush();
                    outputStream.close();
                } catch (Exception e) {
                    log.error(e.getMessage());
                }
            } else {
                try {
                    response.getWriter().print("error");
                } catch (Exception e) {
                    log.error(e.getMessage());
                }
            }
        }
    
    }

控制层：

@Autowired
        private HttpServletResponse response;
    
        @RequestMapping("/export")
        public void exportData(@RequestBody xxxParam param) {
            Map<String, String> titleMap = new HashMap<>();
            titleMap.put("xxx", "xxx");
    
            param.setPageNum(1);
            param.setPageSize(50000);
            List<xxx> data = xxxService.findData(param);
    
            XSSFWorkbook workbook = Common.exportExcel(xxx.class, titleMap, data);
            Common.exportFile(response, workbook);
        }

导出来的文件行标题就是map的value，查询数据就不写了。

前端：

<el-button @click="exportData">导出</el-button>
    
          exportData () {
            let url = 'XXX'
            this.common.exportData(url, this.queryForm, '文件_' + this.common.getTime())
          }

其中common.exportData方法：

exportData (url, data, fileName) {
        axios({
          method: 'POST',
          url: url,
          data: data,
          responseType: 'blob'
        }).then(response => {
          if (!response) {
            return
          }
          let u = window.URL.createObjectURL(response.data)
          let aLink = document.createElement('a')
          aLink.style.display = 'none'
          aLink.href = u
          aLink.setAttribute('download', fileName + '.xlsx')
          document.body.appendChild(aLink)
          aLink.click()
          document.body.removeChild(aLink)
          window.URL.revokeObjectURL(u)
        }).catch(error => {
          throw error
        })
      }

补充：

ie11兼容问题，以上代码在ie上会出现无法下载的问题，将common.exportData修改成：

exportData (url, data, fileName) {
        axios({
          method: 'POST',
          url: url,
          data: data,
          responseType: 'blob'
        }).then(response => {
          if (!response) {
            return
          }
          const blob = new Blob([response.data])
          if (window.navigator && window.navigator.msSaveOrOpenBlob) {
            navigator.msSaveBlob(blob, fileName)
          } else {
            let u = window.URL.createObjectURL(response.data)
            let aLink = document.createElement('a')
            aLink.style.display = 'none'
            aLink.href = u
            aLink.setAttribute('download', fileName + '.xlsx')
            document.body.appendChild(aLink)
            aLink.click()
            document.body.removeChild(aLink)
            window.URL.revokeObjectURL(u)
          }
        }).catch(error => {
          throw error
        })
      }