excel xssf导出到挂载中，从浏览器上下载

写这些随笔主要就是为了记录一下

背景：使用XSSF的目的在于内存有限，而下载到挂载的任务是定时任务，不考虑时长，所以单线程执行。

在页面上配置sql，标题栏，将查到的数据和标题栏组装成excel。

可直接从查到的数据中遍历出标题栏，但是本次的数据太多，遍历数据得到标题栏占用的内存很大，故不采用这个方法。

用的是springboot

直接上代码

controller

  1 @Controller
  2 @RequestMapping("/exceldownload")
  3 public class ITCMSExcelController {
  4 
  5     private final ITCMSExcelService excelService;
  6     private JdbcTemplate jdbcTemplate;
  7 
  8     public ITCMSExcelController(ITCMSExcelService excelService, JdbcTemplate jdbcTemplate) {
  9         this.excelService = excelService;
 10         this.jdbcTemplate = jdbcTemplate;
 11     }
 12 
 13     /**
 14      * 引入日志对象，用来记录日志信息
 15      */
 16     private static final Log logger = LogFactory.getLog(ITCMSExcelController.class);
 17 
 18 
 19 
 20 
 21     @RequestMapping(value = "/exportWeb")
 22     @ResponseBody
 23     public ResultDomain exportWeb() {
 24         logger.info("---ITCMSExcelController---exportWeb--start");
 25         try {
 26             File file = new File(EasyExcelUtils.dirName);
 27             if (file.exists()) {
 28                 EasyExcelUtils.deleteFile(file);
 29                 excelService.exportToExcel();
 30             } else {
 31                 return ResultDomain.error("500", "挂载目录不存在");
 32             }
 33         } catch (Exception e) {
 34             logger.info("----exportWeb---e"+e);
 35             return ResultDomain.error("500", "--" + e);
 36         }
 37         logger.info("---ITCMSExcelController---exportWeb--end");
 38         return ResultDomain.ok();
 39     }
 40 
 41 
 42     @RequestMapping(value = "/exportZipFile")
 43     @ResponseBody
 44     public ResultDomain exportZipFile(HttpServletResponse response) throws Exception {
 45         logger.info("---ITCMSExcelController---exportZipFile--start");
 46         File file = new File(EasyExcelUtils.dirName);
 47         if (file.exists()) {
 48             response.setContentType("application/octet-stream");
 49             String zipfileName = new String(("绩效考核数据-" + EasyExcelUtils.getTime()).getBytes(), "iso-8859-1");
 50             response.setHeader("Content-Disposition", "attachment; filename=" + zipfileName + ".zip");
 51             ZipOutputStream zos = null;
 52             BufferedInputStream input = null;
 53             InputStream is = null;
 54             try {
 55                 zos = new ZipOutputStream(response.getOutputStream());
 56                 String[] files = file.list();
 57                 logger.info("---exportZipFile--files-" + JSON.toString(files));
 58                 if (null != files && files.length > 0) {
 59                     //循环把文件流添加到压缩包中
 60                     for (String fileName : files) {
 61                         if (fileName.contains(".xls")) {
 62                             String filePath = EasyExcelUtils.dirName + "/" + fileName;
 63                             try {
 64                                 //获取文件流
 65                                 is = new FileInputStream(filePath);
 66                                 input = new BufferedInputStream(is);
 67                                 //压缩文件名称 设置ZipEntry对象
 68                                 zos.putNextEntry(new ZipEntry(fileName));
 69                                 byte[] buffer = new byte[1024];
 70                                 int len = input.read(buffer);
 71                                 while (len != -1) {
 72                                     zos.write(buffer, 0, len);
 73                                     len = input.read(buffer);
 74                                 }
 75                                 zos.flush();
 76                             } catch (Exception e) {
 77                                 logger.info("-exportZipFile--for--e" + e);
 78                                 continue;
 79                             }
 80                         }
 81                     }
 82                 }
 83             } catch (Exception e) {
 84                 logger.info("---exportZipFile--e" + e);
 85             } finally {
 86                 try {
 87                     if (null != is) {
 88                         is.close();
 89                     }
 90                     if (null != input) {
 91                         input.close();
 92                     }
 93                     if (null != zos) {
 94                         zos.close();
 95                     }
 96                 } catch (IOException e) {
 97                     logger.info("--exportZipFile--e-" + e);
 98                 }
 99             }
100         } else {
101             return ResultDomain.error("500", "挂载目录不存在");
102         }
103         logger.info("---ITCMSExcelController---exportZipFile--end");
104         return null;
105     }
106 
107 }

  1 @Service
  2 public class ITCMSExcelServiceImpl  implements ITCMSExcelService {
  3 
  4     @Autowired
  5     private ITCMSExcelDao excelDao;
  6 
  7     @Autowired
  8     private JdbcTemplate jdbcTemplate;
  9 
 10     
 11 
 12 
 13     /**
 14      * 生成excel到挂载中
 15      *
 16      * @return 删除数据量
 17      */
 18     public void exportToExcel() {
 19         logger.info("----exportToExcel----start");
 20         try {
 21             // 1.根据id获取记录
 22             List<ITCMSExcelDomain> domainList = new ArrayList<>(20);
 23             domainList = excelDao.queryAll();
 24             for (ITCMSExcelDomain domain : domainList) {
 25                 // 2.定义excel名称和路径
 26                 String filePath = EasyExcelUtils.dirName + "/" + domain.getFileName() + ".xlsx";
 27                 //  3.检查参数是否为空，sql只能为select
 28                 if (!EasyExcelUtils.checkExcelParam(domain) || !EasyExcelUtils.checkExecuteSql(domain.getQuerySql())) {
 29                     domain.setErrorMsg("param error");
 30                 } else {
 31                     // 创建表
 32                     SXSSFWorkbook workbook = new SXSSFWorkbook(100);
 33                     SXSSFSheet sheet = workbook.createSheet(domain.getFileName());
 34                     FileOutputStream out = null;
 35                     try {
 36                         out = new FileOutputStream(filePath);
 37                         // sheet页第一行写创建日期
 38                         SXSSFRow rowDate = sheet.createRow(0);
 39                         SXSSFCell cell1 = rowDate.createCell(0);
 40                         cell1.setCellValue("当前文件创建时间：" + EasyExcelUtils.getSimpleTime());
 41                         // 获取标题，并写入excel中
 42                         String[] headName;
 43                         headName = domain.getHeadName().split(",");
 44                         SXSSFRow row = sheet.createRow(1);
 45                         for (int i = 0; i < headName.length; i++) {
 46                             SXSSFCell cell = row.createCell(i);
 47                             cell.setCellValue(headName[i]);
 48                         }
 49                         String[] finalHeadName = headName;
 50                         List<List<String>> dataList = new ArrayList<>(4000);
 51                         jdbcTemplate.query(StringEscapeUtils.unescapeJava(domain.getQuerySql()), new RowCallbackHandler() {
 52                             @Override
 53                             public void processRow(ResultSet resultSet) throws SQLException {
 54                                 ArrayList<String> valueList = new ArrayList<>(30);
 55                                 for (String head : finalHeadName) {
 56                                     String value = resultSet.getString(head.trim());
 57                                     value = value == null ? "" : value;
 58                                     // 处理日期转为String后多的小数点问题
 59                                     String va = EasyExcelUtils.checkStr(value);
 60                                     valueList.add(va);
 61                                 }
 62                                 dataList.add(valueList);
 63                             }
 64                         });
 65                         if (null != dataList && dataList.size() > 0) {
 66                             // 写入数据
 67                             for (int i = 0; i < dataList.size(); i++) {
 68                                 int rowNum = 2 + i;
 69                                 SXSSFRow rowData = sheet.createRow(rowNum);
 70                                 List<String> data = dataList.get(i);
 71                                 for (int j = 0; j < data.size(); j++) {
 72                                     SXSSFCell cell = rowData.createCell(j);
 73                                     cell.setCellValue(data.get(j));
 74                                 }
 75                             }
 76                             // 写excel到挂载中
 77                             workbook.write(out);
 78                         } else {
 79                             domain.setErrorMsg("no data!");
 80                         }
 81                     } catch (Exception e) {
 82                         logger.info("----exportToExcel-------e" + e);
 83                         domain.setErrorMsg(e.toString());
 84                     } finally {
 85                         if (null != out) {
 86                             out.close();
 87                         }
 88                         workbook.close();
 89                     }
 90 
 91                 }
 92                 if (!StringUtils.isEmpty(domain.getErrorMsg())) {
 93                     EasyExcelUtils.ErrorToExcel(domain, filePath);
 94                 }
 95             }
 96         } catch (Exception e) {
 97             logger.info("--exportToExcel--for--e" + e);
 98         }
 99     }
100 }

serviceImpl

工具类

package com.sinolife.utils.commons;


import com.sinolife.domain.ITCMSExcelDomain;
import com.sinolife.sf.tools.util.SXSSFExcelUtil;
import com.sinolife.utils.commons.enums.SQLCheck;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;


@Component
public class EasyExcelUtils extends SXSSFExcelUtil {


    private static final Log logger = LogFactory.getLog(EasyExcelUtils.class);
        public static String dirName = "/mwbase/apptemp/ITCMS/basicdataKPI";
//    public static String dirName = "D:\\a";


    // 创建excel记录错误点
    public static void ErrorToExcel(ITCMSExcelDomain domain, String filePath) throws IOException {
        // 创建表
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        FileOutputStream out = new FileOutputStream(filePath);
        try {
            SXSSFSheet sheet = workbook.createSheet(domain.getFileName() + EasyExcelUtils.getTime());
            SXSSFRow row = sheet.createRow(1);
            SXSSFCell cell = row.createCell(0);
            cell.setCellValue("导出错误：" + domain.getErrorMsg());
            workbook.write(out);
        } catch (Exception e) {
            logger.info("ErrorToExcel---e" + e);
        } finally {
            if (out != null) {
                out.close();
            }
            workbook.close();
        }
    }

    /**
     * 校验参数是否为空
     *
     * @param domain 需要检查的实体类
     * @return 检查通过？true:false
     */
    public static boolean checkExcelParam(ITCMSExcelDomain domain) {
        if (StringUtils.isEmpty(domain.getFileName()) ||
                StringUtils.isEmpty(domain.getHeadName()) ||
                StringUtils.isEmpty(domain.getSheetName()) ||
                StringUtils.isEmpty(domain.getQuerySql())) {
            return false;
        }
        return true;
    }

    /**
     * 校验参数SQL中是否除了select语句外，
     * 还有其他的执行语句
     *
     * @param sql 需要检查的SQL
     * @return 检查通过?true:false
     */
    public static boolean checkExecuteSql(String sql) {
        SQLCheck[] values = SQLCheck.values();
        String upperCaseSql = sql.toUpperCase();
        for (SQLCheck sqlCheck : values) {
            if (upperCaseSql.contains(CommonConstant.SPLIT_SPACE + sqlCheck.getSqlHead() + CommonConstant.SPLIT_SPACE)) {
                return false;
            }
        }
        return true;
    }

    // 获取当前时间
    public static String getTime() {
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH_mm_ss");
        return sdf.format(date);
    }

    // 获取SDF当前时间
    public static String getSimpleTime() {
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return sdf.format(date);
    }

    public static String checkStr(String str) {
        if (str.contains(":") && str.contains("-") && str.contains(".")) {
            int i = str.indexOf(".");
            String substring = str.substring(0, i);
            return substring;
        }
        return str;
    }

    // 删除目录下的所有文件
    public static void deleteFile(File file) {
        File[] files = file.listFiles();
        if (null != files) {
            for (int i = 0; i < files.length; i++) {
                if (files[i].isFile()) {
                    files[i].delete();
                } else if (files[i].isDirectory()) {
                    deleteFile(files[i]);
                }
                files[i].delete();
            }
        }
    }

    /**
     * 检查sql是否只以call 开头，并且（）中只能包含select 语句
     * 还有其他的执行语句
     *
     * @param sql 需要检查的SQL
     * @return 检查通过?true:false
     */
    public static boolean checkSqlFormat(String sql) {
        String upperCaseSql = sql.toUpperCase();
        // 检测sql是否以call开头
        boolean flag = upperCaseSql.startsWith("CALL");
        if (flag) {
            SQLCheck[] values = SQLCheck.values();
            for (SQLCheck sqlCheck : values) {
                if (!upperCaseSql.contains(sqlCheck.getSqlHead())) {
                    return true;
                }
            }
        }
        return false;
    }

}

EasyExcelUtils

前端用的是easyui框架，但是没影响，用的是href标签，点击就从浏览器页面下载

// 将excel导出到挂载上
function exportWeb(){
window.location.href = getRootPath() + "exceldownload/exportWeb";
}

// 将excel从挂载下载到浏览器上
function exportZipFile() {
window.location.href = getRootPath() + "exceldownload/exportZipFile";
}

比较仓促，有问题的地方希望大家在评论区批评指正

posted @ 2021-07-20 10:36 guo176 阅读(87) 评论(0) 收藏举报

刷新页面返回顶部

guo176

excel xssf导出到挂载中，从浏览器上下载

公告