ZipTestUtils.java

package com.subsidy.util;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
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 javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class ZipTestUtils {

     /*
        原链接：https://blog.csdn.net/houxuehan/article/details/89189820
     */

    public static String NO_DEFINE = "no_define";//未定义的字段
    public static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";//默认日期格式
    public static int DEFAULT_COLOUMN_WIDTH = 17;

    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param title       标题行
     * @param headList     属性-列头
     * @param students    数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth    列宽 默认 至少17个字节
     * @param out         输出流
     */
    public static void exportExcelX(String title, List<String> headList, List<Student> students, String datePattern, int colWidth, OutputStream out) {
        if (datePattern == null) {
            datePattern = DEFAULT_DATE_PATTERN;
        }
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
        int[] arrColWidth = new int[headList.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headList.size()];
        String[] headers = new String[headList.size()];
        int ii = 0;
        for (int i = 0 ;i < headList.size(); i++) {

            //properties[ii] = fieldName;
            headers[ii] = headList.get(i);

            int bytes = headList.get(i).getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Student student : students) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) {
                    sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
                }
                SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
                titleRow.createCell(0).setCellValue(title);
                titleRow.getCell(0).setCellStyle(titleStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headList.size() - 1));

                SXSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
                for (int i = 0; i < headers.length; i++) {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);

                }
                rowIndex = 2;//数据内容从 rowIndex=2开始
            }

            SXSSFRow dataRow = sheet.createRow(rowIndex);

            SXSSFCell newCell = dataRow.createCell(0);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(student.getName());

            newCell = dataRow.createCell(1);
            //newCell.setCellStyle(cellStyle);
            newCell.setCellValue(student.getAge());

            newCell = dataRow.createCell(2);
            newCell.setCellValue(new SimpleDateFormat(datePattern).format(student.getBirthday()));

            newCell = dataRow.createCell(3);
            newCell.setCellValue(student.getHeight());

            newCell = dataRow.createCell(4);
            newCell.setCellValue(student.getWeight());

            newCell = dataRow.createCell(5);
            newCell.setCellValue(student.isSex());

            //
            //for (int i = 0; i < properties.length; i++) {
            //
            //    Object o = jo.get(properties[i]);
            //    String cellValue = "";
            //    if (o == null) {
            //        cellValue = "";
            //    } else if (o instanceof Date) {
            //        cellValue = new SimpleDateFormat(datePattern).format(o);
            //    }
            //    /*else if(o instanceof Float || o instanceof Double) {
            //    	double d = (double) o;
            //    	if(d%1==0)  cellValue=o.toString();
            //    	else cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
            //    }*/
            //    else {
            //        cellValue = o.toString();
            //    }
            //    newCell.setCellValue(cellValue);
            //    newCell.setCellStyle(cellStyle);
            //}
            rowIndex++;
        }
        // 自动调整宽度
        /*for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }*/
        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 压缩单个excel文件的输出流 到zip输出流,注意zipOutputStream未关闭，需要交由调用者关闭之
     *
     * @param zipOutputStream   zip文件的输出流
     * @param excelOutputStream excel文件的输出流
     * @param excelFilename     文件名可以带目录，例如 TestDir/test1.xlsx
     */
    public static void compressFileToZipStream(ZipOutputStream zipOutputStream,
                                               ByteArrayOutputStream excelOutputStream, String excelFilename) {
        byte[] buf = new byte[1024];
        try {
            // Compress the files
            byte[] content = excelOutputStream.toByteArray();
            ByteArrayInputStream is = new ByteArrayInputStream(content);
            BufferedInputStream bis = new BufferedInputStream(is);
            // Add ZIP entry to output stream.
            zipOutputStream.putNextEntry(new ZipEntry(excelFilename));
            // Transfer bytes from the file to the ZIP file
            int len;
            while ((len = bis.read(buf)) > 0) {
                zipOutputStream.write(buf, 0, len);
            }
            // Complete the entry
            //excelOutputStream.close();//关闭excel输出流
            zipOutputStream.closeEntry();
            bis.close();
            is.close();
            // Complete the ZIP file
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws IOException {
        int count = 100;
        List<Student> list = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            Student s = new Student();
            s.setName("POI" + i);
            s.setAge(i);
            s.setBirthday(new Date());
            s.setHeight(i);
            s.setWeight(i);
            s.setSex(i / 2 == 0 ? false : true);
            list.add(s);
        }
        //Map<String, String> headMap = new LinkedHashMap<String, String>();
        //headMap.put("name", "姓名");
        //headMap.put("age", "年龄");
        //headMap.put("birthday", "生日");
        //headMap.put("height", "身高");
        //headMap.put("weight", "体重");
        //headMap.put("sex", "性别");

        List<String> memberList = Arrays.asList(new String[]{"成员名称","账号","联系方式","签到次数","课程进度","培训时长","评级测试","答疑"});


        //导出zip
        OutputStream outXlsx = new FileOutputStream("E://test.zip");
        ZipOutputStream zipOutputStream = new ZipOutputStream(outXlsx);

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        exportExcelX("testExcel", memberList, list, null, 0, baos);
        compressFileToZipStream(zipOutputStream, baos, "/test/aaa.xlsx");
        baos.close();

        zipOutputStream.flush();
        zipOutputStream.close();
        outXlsx.close();

        System.out.println("导出zip完成");
    }


    public void exportZip(HttpServletResponse response, List<Student> students) throws Exception {

        ServletOutputStream sos = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(sos);
        String zipname = "test.zip";
        response.reset();
        response.setContentType("application/x-download");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((zipname).getBytes(), "iso-8859-1"));
        ByteArrayOutputStream baos = new ByteArrayOutputStream();

        /**
         * 班级成员
         */
        List<String> memberList = Arrays.asList(new String[]{"成员名称","账号","联系方式","签到次数","课程进度","培训时长","评级测试","答疑"});

        /**
         * 注册签到
         */
        List<String> signList = Arrays.asList(new String[]{"成员名称","培训时长","课程进度","完成率"});

        /**
         * 测试成绩
         */
        List<String> scoreList = Arrays.asList(new String[]{"成员名称","测试结果","最高分"});

        /**
         * 答疑记录
         */
        List<String> answerList = Arrays.asList(new String[]{"提问","答疑","提问时间","提问成员","答疑时间"});

        //Map<String, String> headMap = new LinkedHashMap<String, String>();
        //headMap.put("name", "成员名称");
        //headMap.put("age", "年龄");
        //headMap.put("birthday", "生日");
        //headMap.put("height", "身高");
        //headMap.put("weight", "体重");
        //headMap.put("sex", "性别");

        exportExcelX("title", memberList, students, null, 0, baos);
        compressFileToZipStream(zipOutputStream, baos, "/test/aaa.xlsx");
        baos.close();

        zipOutputStream.flush();
        zipOutputStream.close();
        sos.close();
        System.out.println("导出zip完成");
    }

}