Excel大批量数据多sheet导出

感谢Crab2Died，Excel4J挺好用 https://gitee.com/Crab2Died/Excel4J

六. 多sheet数据导出

1. 多sheet数据导出包装类,详见com.github.sheet.wrapper包内包装类

多sheet数据导出只需将待导出数据封装入com.github.sheet.wrapper包内的Wrapper类即可实现多sheet一键导出

2. 无模板、无注解的多sheet导出com.github.sheet.wrapper.SimpleSheetWrapper

1) 调用方法

    // 多sheet无模板、无注解导出
    @Test
    public void testBatchSimple2Excel() throws Exception {

        // 生成sheet数据
        List<SimpleSheetWrapper> list = new ArrayList<>();
        for (int i = 0; i <= 2; i++) {
            //表格内容数据
            List<String[]> data = new ArrayList<>();
            for (int j = 0; j < 1000; j++) {

                // 行数据(此处是数组) 也可以是List数据
                String[] rows = new String[5];
                for (int r = 0; r < 5; r++) {
                    rows[r] = "sheet_" + i + "row_" + j + "column_" + r;
                }
                data.add(rows);
            }
            // 表头数据
            List<String> header = new ArrayList<>();
            for (int h = 0; h < 5; h++) {
                header.add("column_" + h);
            }
            list.add(new SimpleSheetWrapper(data, header, "sheet_" + i));
        }
        ExcelUtils.getInstance().simpleSheet2Excel(list, "K.xlsx");
    }

以下是实现：

@RequestMapping(value = "/alltoexcel")  
public void alltoexcel(HttpServletRequest request,HttpServletResponse response) throws Exception {  
    // 生成sheet数据  
    List<SimpleSheetWrapper> list = new ArrayList<>();  

    String where = " 1=1 ";  
    where  += " order by rc.rc_id desc ";  
    String where1 ="";  
    List<RechargeCard> rList = rechargeCardMapper.selectAll();  
    int size = rList.size();  
    if(size%10000==0){  
        for(int i=0;i<size/10000;i++){  
            where1 = where;  
            where1 += " limit "+i*10000+",10000 ";  

            List<Map<String, Object>> models =rechargeCardMapper.toexcel(where1);//一万条数据导出一次  

            //表格内容数据  
            List<String\[\]> data = new ArrayList<>();  
            for (int j = 0; j < models.size(); j++) {  
                // 行数据(此处是数组) 也可以是List数据  
                String\[\] rows = new String\[5\];  
                rows\[0\] =  models.get(j).get("rc_sn").toString();  
                rows\[1\] =  models.get(j).get("rc_password").toString();  
                rows\[2\] =  models.get(j).get("rc_money").toString();  
                rows\[3\] =  models.get(j).get("rc\_lot\_number").toString();  
                rows\[4\] =  models.get(j).get("ctime").toString();  
                data.add(rows);  
            }  
            // 表头数据  
            List<String> header = new ArrayList<>();  
            header.add("卡号");  
            header.add("密码");  
            header.add("面值");  
            header.add("批次");  
            header.add("生成时间");  
            list.add(new SimpleSheetWrapper(data, header, "sheet_" + i));  

            where1 = "";  
        }  
    }else if(size%10000>0){  
        for(int i=0;i<(size/10000+1);i++){  
            where1 = where;  
            where1 += " limit "+i*10000+",10000 ";  

            List<Map<String, Object>> models =rechargeCardMapper.toexcel(where1);//一万条数据导出一次  

            //表格内容数据  
            List<String\[\]> data = new ArrayList<>();  
             for (int j = 0; j < models.size(); j++) {  
                // 行数据(此处是数组) 也可以是List数据  
                String\[\] rows = new String\[5\];  
                rows\[0\] =  models.get(j).get("rc_sn").toString();  
                rows\[1\] =  models.get(j).get("rc_password").toString();  
                rows\[2\] =  models.get(j).get("rc_money").toString();  
                rows\[3\] =  models.get(j).get("rc\_lot\_number").toString();  
                rows\[4\] =  models.get(j).get("ctime").toString();  
                data.add(rows);  
            }  
            // 表头数据  
            List<String> header = new ArrayList<>();  
            header.add("卡号");  
            header.add("密码");  
            header.add("面值");  
            header.add("批次");  
            header.add("生成时间");  
            list.add(new SimpleSheetWrapper(data, header, "sheet_" + i));  

            where1 = "";  
        }  
    }      

    String path = request.getContextPath();  
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path;  

    String filePath = request.getServletContext().getRealPath("/download");// 保存文件路径  
    // 判断文件夹是否存在 否：创建；是：保存在文件夹下  
    File downloadFile = new File(filePath);  
    if (!downloadFile.exists()) {  
        downloadFile.mkdirs();//包括父目录*  
    }  

    ExcelUtils.getInstance().simpleSheet2Excel(list, filePath+"/cards.xlsx");//充值卡表  
    downloadexcel(response,filePath+"/cards.xlsx");//导出后下载  
}  


private static void downloadexcel(HttpServletResponse response, String filePath) throws Exception {  
    File f = new File(filePath);    
    if (!f.exists()) {    
        response.sendError(404, "File not found!");    
        return;    
    }  
    BufferedInputStream bis = null;  
    BufferedOutputStream bos = null;  
    try {  
        // 设置response参数，可以打开下载页面  
        response.reset();  
        response.setContentType("application/vnd.ms-excel;charset=utf-8");  
        response.setHeader("Content-Disposition",  
                "attachment;filename=" + f.getName());  
        ServletOutputStream out = response.getOutputStream();  
        bis = new BufferedInputStream(new FileInputStream(f));  
        bos = new BufferedOutputStream(out);  
        byte\[\] buff = new byte\[2048\];  
        int bytesRead;  
        // Simple read/write loop.  
        while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {  
            bos.write(buff, 0, bytesRead);  
        }  
    } catch (IOException e) {  
        e.printStackTrace();  
    } finally {  
            try {  
                if (bis != null)  
                bis.close();  
                if (bos != null)  
                    bos.close();  
            } catch (IOException e) {  
            }  

    }  
}  

Maven依赖，关键是：

<dependency>  
    <groupId>com.github.crab2died</groupId>  
    <artifactId>Excel4J</artifactId>  
    <version>2.1.4-Alpha</version>  
</dependency>  
<dependency>    
   <groupId>org.apache.commons</groupId>    
   <artifactId>commons-collections4</artifactId>    
   <version>4.1</version>    
</dependency>  
<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi</artifactId>  
    <version>3.17</version>  
</dependency>

转载于:https://my.oschina.net/u/3575900/blog/1828027