Java操作百万数据量Excel导入导出工具类（维护中）

最新推荐文章于 2024-08-09 16:43:56 发布

置顶 Andy Chen 陈郑游

最新推荐文章于 2024-08-09 16:43:56 发布

阅读量3.1w

点赞数 76

分类专栏： ————[ Java与JVM ] 文章标签： SXSSFWorkbook java Excel操作通用工具类 poi Excel大数据量导入导出百万数据量Excel导入导出 Java 百万数据量Excel导入导出

本文链接：https://blog.csdn.net/JavaWebRookie/article/details/80843653

版权

————[ Java与JVM ] 专栏收录该内容

23 篇文章 2 订阅

订阅专栏

作者序言：把简单的事情做好、才做好更复杂的事情。

======================================================================

1、更新日志

1.response.reset();注释掉reset，否在会出现跨域错误。
2.可导出多个单元、poi官方建议大数据量解决方案：SXSSFWorkbook。
3.自定义下拉列表：对每个单元格自定义下拉列表。
4.数据遍历方式换成数组(效率较高)、可提供模板下载、每个表格的大标题[2018-09-14]
5.自定义列宽：对每个单元格自定义列宽[2018-09-18]
6.自定义样式：对每个单元格自定义样式[2018-10-22]-[2018-10-25修复]
7.自定义单元格合并：对每个单元格合并[2018-10-22]
8.固定表头[2018-10-23、自定义样式：单元格自定义某一列或者某一行样式[2018-10-30]
9.已解决 SimpleDateFormat 与 DecimalFormat 线程安全问题[2018-11-07]。 localhost01博友提出问题
10.版本3.0开始支持对象编程(之前都是直接调用函数、现支持先写入对象再调用)。[2018-12-07]
11.速度调优、修复100万数据导出内存溢出bug（临时方案）、解决本地测试报servlet找不到问题。[2019-01-31]
12.导入日期格式和数字小数点位数可自定义【看教程】。[2019-12-14]  
13、本地文件导入函数   ExcelUtils.importForExcelDataToFile(......)。[2020-02-21]             
14、删除 LocalExcelUtils.importForExcelData(......)方法。[2020-02-21]

1.1、版本说明

以下这种没有提交到maven库，需要的话下载源代码使用（4.0版本没有以下功能） 
1、是否添加边框改为是否忽略边框？默认单元格都带边框。
2、添加导出图片。  
3、可设置默认列宽大小。默认是16
4、可设置默认字体大小。默认是12
5、删除：导出函数 ExcelUtils.exportForExcel(......)过期、4.0以下版本有。
6、本地文件导入函数   ExcelUtils.importForExcelDataToFile(......)。           
7、删除 LocalExcelUtils.importForExcelData(......)方法。

<!--
maven：https://mvnrepository.com/artifact/com.github.andyczy/java-excel-utils
Excel导入导出教程文档：
    https://github.com/andyczy/czy-nexus-commons-utils/blob/master/README.md
    https://blog.csdn.net/JavaWebRookie/article/details/80843653
-->
<dependency>
     <groupId>com.github.andyczy</groupId>
     <artifactId>java-excel-utils</artifactId>
     <version>4.0</version>
</dependency>

2、实现功能

1、自定义导入数据格式，支持配置时间、小数点类型（支持单/多sheet）（2种方式：本地文件路径导入（只支持xls、xlsx格式）、MultipartFile 请求方式 ）              
2、浏览器（response响应）导出Excel文件、模板文件（支持单/多sheet）           
3、指定路径导出Excel文件（支持单/多sheet）           
4、自定义样式：行、列、某个单元格（字体大小、字体颜色、左右对齐、居中、是否忽略边框。支持单/多sheet）           
5、自定义固定表头（支持单/多sheet）            
6、自定义下拉列表值（支持单/多sheet）           
7、自定义合并单元格、自定义列宽、自定义大标题（支持单/多sheet）
8、导出图片、图片地址和数据一样，只要是能访问的图片都可以导出（有需求、图片适应大小待解决），图片格式：.JPEG|.jpeg|.JPG|.jpg|.png|.gif

注:POI SXSSFWorkbook 最高限制1048576行,16384列（数据量大于1048576行，自行处理成多sheet形式）

3、外部链接教程

代码传送门 Git 地址 :

教程说明：https://github.com/andyczy/czy-nexus-commons-utils

工具仓库：https://github.com/andyczy/czy-study-java-commons-utils

开源中国：https://www.oschina.net/p/java-excel-utils（大家多多支持评论收藏）

该代码逻辑是经过多个项目和很多次验证，如果有更好的优化，如发现bug和有优化方案欢迎评论或者私信。支持的话记得评论，不然可能忘记~。

4、导出实现截图

直接列举，具体使用方法代码逻辑写的很清楚。

5、导入实例

多表格数据获取、从第几行开始获取、多单元根据那些列为空来忽略行数据。

导入不转换成对象，直接获取到数据（支持多sheet表格），直接获取数据更加方便、根据列循环获取数据。

1、本地文件路径导入（只支持xls、xlsx格式）

//  第一个表格从第三行开始获取
HashMap hashMapIndex = new HashMap();
hashMapIndex.put(1, 4);
Integer sheetSize = 2;
String filePath = "E:\\20200221145513.xlsx";
String excelType = "xlsx";
List<List<LinkedHashMap<String, String>>> importList = ExcelUtils.importForExcelDataToFile(filePath, excelType, sheetSize, hashMapIndex, null);

2、MultipartFile 请求方式

//  第一个表格从第三行开始获取
HashMap hashMapIndex = new HashMap();
hashMapIndex.put(1, 4);
List<List<LinkedHashMap<String, String>>> list = ExcelUtils.importForExcelData(book, sheetNames, hashMapIndex, null);

结果集：[[{0=1, 1=小学, 2=1, 3=2, 4=3}, {0=2, 1=三中, 2=3, 3=4, 4=2}]，[{0=1, 1=小学, 2=1, 3=2, 4=3}]]

多个sheetList数据，键值对的形式。获取第一个sheet表格数据则：hashMapList.get(0).get("0"); //第一个表格第一列数据=1

1、Controller 代码  

    @RequestMapping(value = "/importExcelToData", method = RequestMethod.POST)
    @ResponseBody
    public Result importExcell(@RequestParam("proFile") MultipartFile proFile) {
        //导入的Excel单元表格名称顺序
        String[] sheetNames = new String[]{"导入测试"};
        Workbook book = null;
        try {
            if (!proFile.isEmpty()) {
                InputStream inputStream = proFile.getInputStream();
                String extName = fileName.substring(proFile.getOriginalFilename().lastIndexOf('.') + 1);
          
                if (StringUtils.equals("xls", extName)) {
                    book = new HSSFWorkbook(inputStream);
                } else if (StringUtils.equals("xlsx", extName)) {
                    book = new XSSFWorkbook(inputStream);
                } else {
                    return new Result(-1001, "无法识别的Excel文件,请下载模板文件,进行导入!");
                }
                return iService.importExcelToDataService(book, user.getId(), sheetNames);
            } else {
                return new Result(-1001, "模板文件为空!");
            }
        } catch (Exception e) {
            return new Result(-1001, "数据异常、请下载模本重新导入!");
        }
    }

6、get与post请求导出、请求区别

1、get 请求前端（方式一）：
    window.open("请求地址URL?参数="+参数值);  //获取其他get请求方式

2、post请求前端（方式二）：常见于参数是对象，get无法传参才使用。
    // post请求，将数据以流的方式返回，再进行处理。
    exportExcel(参数).then((res)=>{
            let blob = new Blob([res], {type: "application/vnd.ms-excel;charset=utf-8"});
            let url =window.URL.createObjectURL(blob);
            let link = document.createElement('a');
            link.download = '测试.xls';
            link.href = url;
            link.click();
    });

3、post 与 get 的区别，再请求头加上：responseType: 'blob'

    例如：axios 请求
        export function exportExcel(data) {
          return request({
              url: '/test/exportExcel',
              method: 'POST',
              responseType: 'blob',
              data
          });
        }


4、get 与 psot  Controller

    @RequestMapping(value = "/exportExcel",method = RequestMethod.POST)
    @ResponseBody
    public void exportLicensingRequirementExcel(HttpServletResponse response ,  @RequestBody LicensingRequirements licensingList) throws Exception{
        String excelName =  "测试Excel导出" + DateUtil.getNeededDateStyle(new Date(),"yyyyMMddHHmmss");
        String[] LabelNameList = new String[]{"sheet1"};
        String[] sheetNameList = null;
        String[] labelNameList = new String[]{excelName};
        //dataList 数据接口
        List<List<String[]>>  dataList = null;
        //自定义列宽
        //第几行样式
        ExcelUtils excelUtils = ExcelUtils.initialization();
        excelUtils.setLabelName(LabelNameList);
        excelUtils.setDataLists(dataList);
        excelUtils.setFileName(excelName);
        excelUtils.setResponse(response);
        excelUtils.setSheetName(LabelNameList);
        excelUtils.exportForExcelsOptimize();
    }


4、数据逻辑层
    @Override
    public List<List<String[]>> exportExcel(String 参数) {
        List<BaseEnterpriseInfo> list = null; //数据接口

        List<List<String[]>> listArray = new ArrayList<>();
        List<String[]> stringList = new ArrayList<>();
        String[] valueList = null;
        String[] headers = {"序号", "企业名称", "地址"};
        stringList.add(headers);
        for (int i = 0; i < list.size(); i++) {
                valueList = new String[]{(i + 1) + "", list.get(i).getEntName(), list.get(i).getDom()
            };
            stringList.add(valueList);
        }
        listArray.add(stringList);
        return listArray;
    }