poi 导出多个excel为zip

84 阅读 0 评论 56 点赞

我是靠谱客的博主粗暴萝莉，最近开发中收集的这篇文章主要介绍poi 导出多个excel为zip，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

private String templatePath = "D:/file/test";
private String tempPath= "D:/file/excel";

public void reportZipExcel(List<DeviceEnergyReport> deviceEnergyReportList,HttpServletResponse response){
        List<File> srcfile = new ArrayList<File>();  //声明一个集合，用来存放多个Excel文件路径及名称
      
        for(DeviceEnergyReport deviceEnergyReport:deviceEnergyReportList){
            
            DeviceEnergyReport exportData = getExportData(deviceEnergyReport);
            // 文件模板路径
            String rootpath = templatePath+File.separator;
            File file=new File(rootpath + File.separator + "tongjis.xls");
            //生成临时文件
            String filename=exportData.getReportName() + ".xls";
            File newFile = new File(tempPath+File.separator,filename);
            try {
                ExcelUtil.createNewFile(filename,file,tempPath);
            } catch (Exception e) {
                e.printStackTrace();
            }
            // 新文件写入数据，并下载*****************************************************
            if(exportData.getReportType() == 1){
                ymdDataReportExcel(newFile,exportData);
            }else if(exportData.getReportType() == 3){
                rangeDataReportExcel(newFile,exportData);
            }
            //生成的excel文件保存，用来后面进行压缩
            srcfile.add(newFile);
        }

        //压缩实现代码
        //指定磁盘目录
        String strpath=tempPath+File.separator;//例如D://file
        //指定.zip格式和名称
        String pathname=new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date())  + ".zip";
        File zipfile = new File(strpath+pathname);
        //压缩多个excel文件为.zip格式并删除
        ExcelUtil.zipFiles(srcfile,zipfile);
        ExcelUtil.deleteFiles(srcfile);
        //下载.zip格式文件并删除
        ExcelUtil.downFile(response,strpath,pathname);
        ExcelUtil.deleteZip(zipfile);
    }

poi操作导出excel内容

private void ymdDataReportExcel(File newFile,DeviceEnergyReport exportData){
        InputStream is = null;
        HSSFWorkbook sheets = null;
        HSSFSheet hssfSheet = null;
        FileOutputStream fos = null;
        Integer sheetIndex = 0;
        try {
            is = new FileInputStream(newFile);// 将excel文件转为输入流
            sheets = new HSSFWorkbook(is);// 创建个workbook，
            // 写数据
            fos = new FileOutputStream(newFile);

            int numberOfSheets = sheets.getNumberOfSheets();
            String sheetName = "";
            String reportName = exportData.getReportName();
            if(exportData.getStructureType() == 1){
                Map<Integer, Station> stationMap = deviceMixService.getSysStation().stream().collect(Collectors.toMap(Station::getRowId, station -> station, (e1, e2) -> e1));
                sheetName = stationMap.get(exportData.getStationId()).getName();
            }else{
                Map<Integer, Device> deviceMap = deviceMixService.getDeviceMap(deviceMixService.getAllDevice());
                sheetName = deviceMap.get(exportData.getDeviceId()).getName();
            }
            Map<String, HSSFRow> hSSFRowMap = setYmdExcelBasic(sheets, exportData.getExportlatitude(), reportName,sheetName,sheetIndex,numberOfSheets);
            int num=0;
            HSSFRow energyTimeRow = hSSFRowMap.get("energyTimeRow");
            HSSFRow energyValueRow = hSSFRowMap.get("energyValueRow");
            for(EnergyLog energyLog:exportData.getEnergyLogList()){
                num = num+1;
                energyTimeRow.createCell(num).setCellValue(energyLog.getDateTime());
                energyValueRow.createCell(num).setCellValue(energyLog.getResult());
            }

            String[] split = reportName.split(":");
            if(exportData.getStructureType() == 1){//站点下设备导出
                Map<Integer, Device> deviceMap = deviceMixService.getDeviceMap(deviceMixService.getAllDevice());
                QueryWrapper<DeviceReport> wrapper = new QueryWrapper<>();
                wrapper.eq("report_id",exportData.getRowId());
                List<DeviceReport> list = deviceReportService.list(wrapper);
                JSONArray jsonArray;
                List<EnergyLog> energyList;
                for(DeviceReport deviceReport:list){
                    sheetIndex = sheetIndex+1;
                    jsonArray = JSONObject.parseArray(deviceReport.getReportContent());
                    energyList = jsonArray.toJavaList(EnergyLog.class);
                    sheetName = deviceMap.get(deviceReport.getDeviceId()).getName();
                    reportName = split[0]+":"+deviceMap.get(deviceReport.getDeviceId()).getName()+"能耗数据";
                    hSSFRowMap = setYmdExcelBasic(sheets, exportData.getExportlatitude(), reportName,sheetName,sheetIndex,numberOfSheets);
                    num=0;
                    energyTimeRow = hSSFRowMap.get("energyTimeRow");
                    energyValueRow = hSSFRowMap.get("energyValueRow");
                    for(EnergyLog energyLog:energyList){
                        num = num+1;
                        energyTimeRow.createCell(num).setCellValue(energyLog.getDateTime());
                        energyValueRow.createCell(num).setCellValue(energyLog.getResult());
                    }
                }
            }

            sheets.write(fos);
            fos.flush();
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(null != fos){
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private void rangeDataReportExcel(File newFile,DeviceEnergyReport exportData){
        InputStream is = null;
        HSSFWorkbook sheets = null;
        HSSFSheet hssfSheet = null;
        FileOutputStream fos = null;
        Integer sheetIndex = 0;
        try {

            is = new FileInputStream(newFile);// 将excel文件转为输入流
            sheets = new HSSFWorkbook(is);// 创建个workbook，
            int numberOfSheets = sheets.getNumberOfSheets();
            // 写数据
            fos = new FileOutputStream(newFile);
            String sheetName = "";
            String reportName = exportData.getReportName();
            if(exportData.getStructureType() == 1){
                Map<Integer, Station> stationMap = deviceMixService.getSysStation().stream().collect(Collectors.toMap(Station::getRowId, station -> station, (e1, e2) -> e1));
                sheetName = stationMap.get(exportData.getStationId()).getName();
            }else{
                Map<Integer, Device> deviceMap = deviceMixService.getDeviceMap(deviceMixService.getAllDevice());
                sheetName = deviceMap.get(exportData.getDeviceId()).getName();
            }
            Map<String, Object> hssfSheetMap = setSpanExcelBasic(sheets, exportData.getExportlatitude(), reportName, sheetName,sheetIndex,numberOfSheets);
            hssfSheet = (HSSFSheet)hssfSheetMap.get("hssfSheet");
            HSSFCell titleCell;
            HSSFCellStyle titleStyle = (HSSFCellStyle)hssfSheetMap.get("titleStyle");

            Map<String, List<EnergyLog>> energyCollect = exportData.getEnergyLogList().stream().collect(Collectors.groupingBy(EnergyLog::getAnlysisDate));
            Set<String> keySet = energyCollect.keySet();
            List<String> keyList = new ArrayList<>();
            keySet.stream().forEach(key->keyList.add(key));
            List<String> sortKeyList = DateUtils.sortDate(keyList);
//            Iterator<Map.Entry<String, List<EnergyLog>>> energyIterator = energyCollect.entrySet().iterator();
            int cellNum=0;
            int rowNum = 1;
            for(String key:sortKeyList){
                cellNum=+1;
                rowNum = rowNum+1;
                HSSFRow hssfRow = hssfSheet.createRow(rowNum);
                hssfSheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 10, 12));//标题
                hssfRow.setHeight((short) (25 * 20));
                titleCell = hssfRow.createCell(10);
                titleCell.setCellValue(key);
                titleCell.setCellStyle(titleStyle);
                rowNum = rowNum+1;
                HSSFRow hssfRow1 = hssfSheet.createRow(rowNum);
                hssfRow1.setHeight((short) (25 * 20));
                titleCell = hssfRow1.createCell(0);
                titleCell.setCellValue("时间");
                titleCell.setCellStyle(titleStyle);
                rowNum = rowNum+1;
                HSSFRow hssfRow2 = hssfSheet.createRow(rowNum);
                hssfRow2.setHeight((short) (25 * 20));
                titleCell = hssfRow2.createCell(0);
                titleCell.setCellValue("用能值");
                titleCell.setCellStyle(titleStyle);
                List<EnergyLog> value = energyCollect.get(key);
                for(EnergyLog energyLog:value){
                    hssfRow1.createCell(cellNum).setCellValue(energyLog.getDateTime());
                    hssfRow2.createCell(cellNum).setCellValue(energyLog.getResult());
                    cellNum = cellNum+1;
                }
            }

            String[] split = reportName.split(":");
            if(exportData.getStructureType() == 1){//站点下设备导出
                Map<Integer, Device> deviceMap = deviceMixService.getDeviceMap(deviceMixService.getAllDevice());
                QueryWrapper<DeviceReport> wrapper = new QueryWrapper<>();
                wrapper.eq("report_id",exportData.getRowId());
                List<DeviceReport> list = deviceReportService.list(wrapper);
                JSONArray jsonArray;
                List<EnergyLog> energyList;
                for(DeviceReport deviceReport:list){
                    sheetIndex = sheetIndex+1;
                    jsonArray = JSONObject.parseArray(deviceReport.getReportContent());
                    energyList = jsonArray.toJavaList(EnergyLog.class);
                    sheetName = deviceMap.get(deviceReport.getDeviceId()).getName();
                    reportName = split[0]+":"+deviceMap.get(deviceReport.getDeviceId()).getName()+"能耗数据";
                    hssfSheetMap = setSpanExcelBasic(sheets, exportData.getExportlatitude(), reportName, sheetName,sheetIndex,numberOfSheets);
                    hssfSheet = (HSSFSheet)hssfSheetMap.get("hssfSheet");
                    titleStyle = (HSSFCellStyle)hssfSheetMap.get("titleStyle");
                    energyCollect = energyList.stream().collect(Collectors.groupingBy(EnergyLog::getAnlysisDate));
                    keySet = energyCollect.keySet();
                    List<String> keys = new ArrayList<>();
                    keySet.stream().forEach(key->keys.add(key));
                    sortKeyList = DateUtils.sortDate(keys);
                    cellNum=0;
                    rowNum = 1;
                    for(String key:sortKeyList){
                        cellNum=+1;
                        rowNum = rowNum+1;
                        HSSFRow hssfRow = hssfSheet.createRow(rowNum);
                        hssfSheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 10, 12));//标题
                        hssfRow.setHeight((short) (25 * 20));
                        titleCell = hssfRow.createCell(10);
                        titleCell.setCellValue(key);
                        titleCell.setCellStyle(titleStyle);
                        rowNum = rowNum+1;
                        HSSFRow hssfRow1 = hssfSheet.createRow(rowNum);
                        hssfRow1.setHeight((short) (25 * 20));
                        titleCell = hssfRow1.createCell(0);
                        titleCell.setCellValue("时间");
                        titleCell.setCellStyle(titleStyle);
                        rowNum = rowNum+1;
                        HSSFRow hssfRow2 = hssfSheet.createRow(rowNum);
                        hssfRow2.setHeight((short) (25 * 20));
                        titleCell = hssfRow2.createCell(0);
                        titleCell.setCellValue("用能值");
                        titleCell.setCellStyle(titleStyle);
                        List<EnergyLog> value = energyCollect.get(key);
                        for(EnergyLog energyLog:value){
                            hssfRow1.createCell(cellNum).setCellValue(energyLog.getDateTime());
                            hssfRow2.createCell(cellNum).setCellValue(energyLog.getResult());
                            cellNum = cellNum+1;
                        }
                    }
                }
            }

            sheets.write(fos);
            fos.flush();
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(null != fos){
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

excel样式内容操作

/**
     *
     * @param sheets
     * @param exportlatitude  导出纬度
     * @param reportName  报表名称
     * @param sheetName  sheet页名称
     * @param sheetIndex   sheet页下标  即第几页
     * @param numberOfSheets   sheet原先有几页
     * @return
     */
    private Map<String,HSSFRow> setYmdExcelBasic(HSSFWorkbook sheets,String exportlatitude,String reportName,String sheetName,
                                                 int sheetIndex,int numberOfSheets){
        Map<String,HSSFRow> map = new HashMap<>();
        //创建sheet页名称
        HSSFSheet hssfSheet;
        if(sheetIndex >-1 && sheetIndex<numberOfSheets){//导出zip的excel 是复制excel来导出，已经有sheet页
            hssfSheet = sheets.getSheetAt(sheetIndex);
            if(null == hssfSheet){
                hssfSheet = sheets.createSheet(sheetName);
            }else{
                sheets.setSheetName(sheetIndex,sheetName);
            }
        }else{
            hssfSheet = sheets.createSheet(sheetName);
        }

        //单元格合并
        hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 10, 12));//标题
        hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));//导出对象名称
        hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 6));//导出内容

        // Sheet样式
        HSSFCellStyle titleStyle =sheets.createCellStyle();
        // 背景色的设定
        titleStyle.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        // 前景色的设定
        titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
// 填充模式
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        titleStyle.setAlignment(HorizontalAlignment.CENTER);//水平对齐
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐

        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());

        //创建标题行
        HSSFRow titleRow = hssfSheet.createRow(0);
        titleRow.setHeight((short) (25 * 20));
        HSSFCell titleCell = titleRow.createCell(10);
        titleCell.setCellValue("统计报表");
        titleCell.setCellStyle(titleStyle);

        HSSFRow detailRow = hssfSheet.createRow(1);
        detailRow.setHeight((short) (25 * 20));
        titleCell = detailRow.createCell(0);
        titleCell.setCellValue("导出维度");
        titleCell.setCellStyle(titleStyle);
        titleCell = detailRow.createCell(1);
        titleCell.setCellValue(exportlatitude);
        titleCell.setCellStyle(titleStyle);
        titleCell = detailRow.createCell(3);
        titleCell.setCellValue("导出内容");
        titleCell.setCellStyle(titleStyle);
        titleCell = detailRow.createCell(4);
        titleCell.setCellValue(reportName);
        titleCell.setCellStyle(titleStyle);


        HSSFRow energyTimeRow = hssfSheet.createRow(2);
        energyTimeRow.setHeight((short) (25 * 20));
        titleCell = energyTimeRow.createCell(0);
        titleCell.setCellValue("时间");
        titleCell.setCellStyle(titleStyle);

        HSSFRow energyValueRow = hssfSheet.createRow(3);
        energyValueRow.setHeight((short) (25 * 20));
        titleCell = energyValueRow.createCell(0);
        titleCell.setCellValue("用能值");
        titleCell.setCellStyle(titleStyle);
        map.put("energyTimeRow",energyTimeRow);
        map.put("energyValueRow",energyValueRow);
        return map;
    }

工具类

package com.basic.cloud.platform.core.utils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class ExcelUtil {

    /**
     * 读取excel模板，并复制到新文件中供写入和下载
     *path   保存文件的路径
     * @return
     */
    public static File createNewFile(String filename,File file,String path) {
        // 读取模板，并赋值到新文件************************************************************
        // 判断路径是否存在
        File dir = new File(path);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        // 写入到新的excel
        File newFile = new File(path, filename);
        try {
            newFile.createNewFile();
            // 复制模板到新文件
            fileChannelCopy(file, newFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return newFile;
    }

    /**
     * 复制文件
     *
     * @param s
     *            源文件
     * @param t
     *            复制到的新文件
     */
    public static void fileChannelCopy(File s, File t) {
        try {
            InputStream in = null;
            OutputStream out = null;
            try {
                in = new BufferedInputStream(new FileInputStream(s), 1024);
                out = new BufferedOutputStream(new FileOutputStream(t), 1024);
                byte[] buffer = new byte[1024];
                int len;
                while ((len = in.read(buffer)) != -1) {
                    out.write(buffer, 0, len);
                }
            } finally {
                if (null != in) {
                    in.close();
                }
                if (null != out) {
                    out.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将多个Excel打包成zip文件
     * @param srcfile
     * @param zipfile
     */
    public static void zipFiles(List<File> srcfile, File zipfile) {
        byte[] buf = new byte[1024];
        try {
            // Create the ZIP file
            ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));
            // Compress the files
            for (int i = 0; i < srcfile.size(); i++) {
                File file = srcfile.get(i);
                FileInputStream in = new FileInputStream(file);
                // Add ZIP entry to output stream.
                out.putNextEntry(new ZipEntry(file.getName()));
                // Transfer bytes from the file to the ZIP file
                int len;
                while ((len = in.read(buf)) > 0) {
                    out.write(buf, 0, len);
                }
                // Complete the entry
                out.closeEntry();
                in.close();
            }
            // Complete the ZIP file
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除多个文件方法
     *
     * @param srcfile
     */
    public static void deleteFiles(List<File> srcfile) {
        for (File file : srcfile) {
            if (file.exists()) {
                file.delete();
            }
        }
    }

    public static void downFile(HttpServletResponse response, String serverPath, String str) {
        try {
            String path = serverPath + str;
            File file = new File(path);
            if (file.exists()) {
                InputStream ins = new FileInputStream(path);
                BufferedInputStream bins = new BufferedInputStream(ins);// 放到缓冲流里面
                OutputStream outs = response.getOutputStream();// 获取文件输出IO流
                BufferedOutputStream bouts = new BufferedOutputStream(outs);
                response.setContentType("application/x-download");// 设置response内容的类型
                response.setHeader(
                        "Content-disposition",
                        "attachment;filename="
                                + URLEncoder.encode(str, "GBK"));// 设置头部信息
                int bytesRead = 0;
                byte[] buffer = new byte[8192];
                //开始向网络传输文件流
                while ((bytesRead = bins.read(buffer, 0, 8192)) != -1) {
                    bouts.write(buffer, 0, bytesRead);
                }
                bouts.flush();// 这里一定要调用flush()方法
                ins.close();
                bins.close();
                outs.close();
                bouts.close();
            } else {
                response.sendRedirect("../error.jsp");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除zip
     */
    public static void deleteZip(File path) {
        if (path.exists()) {
            path.delete();
        }
    }

}

参考博文：poi根据模板导出多个excel文件并压缩成.zip格式_小志的博客的博客-CSDN博客_poi导出多个excel文件