POI 处理导出Excel

<!-- poi dependency-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.15</version>
</dependency>

package com.bc.mcode.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;

public class POIUtil {
    public static void exportExcel(HttpServletResponse response, String sheetName, String excelName,
            Object[][] dataSource) {
        @SuppressWarnings("resource")
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);

        sheet.setColumnWidth(0, 21 * 256);
        sheet.setColumnWidth(1, 21 * 256);
        sheet.setColumnWidth(2, 21 * 256);
        sheet.setColumnWidth(3, 21 * 256);
        sheet.setColumnWidth(4, 21 * 256);
        sheet.setColumnWidth(5, 21 * 256);
        sheet.setColumnWidth(6, 21 * 256);
        sheet.setColumnWidth(7, 21 * 256);
        sheet.setColumnWidth(8, 21 * 256);
        sheet.setColumnWidth(9, 21 * 256);
        sheet.setColumnWidth(10, 21 * 256);
        sheet.setColumnWidth(11, 21 * 256);
        sheet.setColumnWidth(12, 21 * 256);
        sheet.setColumnWidth(13, 21 * 256);
        sheet.setColumnWidth(14, 21 * 256);
        sheet.setColumnWidth(15, 21 * 256);
        sheet.setColumnWidth(16, 21 * 256);
        sheet.setColumnWidth(17, 21 * 256);
        sheet.setColumnWidth(18, 21 * 256);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(1, true);

        HSSFRow row;
        HSSFCell cell;
        int len = dataSource.length;

        for (int i = 0; i < len; i++) {
            row = sheet.createRow(i);
            Object[] dataSourceTemp = dataSource[i];
            int temp = dataSourceTemp.length;
            for (int j = 0; j < temp; j++) {
                cell = row.createCell(j);

                Object value = dataSourceTemp[j];

                if (null == value || "".equals(value)) {
                    continue;
                }
                cell.setCellValue(String.valueOf(value));
            }
        }
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + java.net.URLEncoder.encode(new String((excelName + ".xls").getBytes(), "UTF-8"), "UTF-8"));
            ServletOutputStream out = response.getOutputStream();

            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;

            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (bis != null)
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            if (bos != null)
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
        }
    }

    public static void exportExcel2(HttpServletResponse response, String sheetName1,String sheetName2, String excelName,
                                   Object[][] dataSource1,Object[][] dataSource2) {
        @SuppressWarnings("resource")
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet1 = workbook.createSheet(sheetName1);
        HSSFSheet sheet2 = workbook.createSheet(sheetName2);

        sheet1.setColumnWidth(0, 21 * 256);
        sheet1.setColumnWidth(1, 21 * 256);
        sheet1.setColumnWidth(2, 21 * 256);
        sheet1.setColumnWidth(3, 21 * 256);
        sheet1.setColumnWidth(4, 21 * 256);
        sheet1.setColumnWidth(5, 21 * 256);
        sheet1.setColumnWidth(6, 21 * 256);
        sheet1.setColumnWidth(7, 21 * 256);
        sheet1.setColumnWidth(8, 21 * 256);
        sheet1.setColumnWidth(9, 21 * 256);
        sheet1.setColumnWidth(10, 21 * 256);
        sheet1.setColumnWidth(11, 21 * 256);
        sheet1.setColumnWidth(12, 21 * 256);
        sheet1.setColumnWidth(13, 21 * 256);
        sheet1.setColumnWidth(14, 21 * 256);
        sheet1.setColumnWidth(15, 21 * 256);
        sheet1.setColumnWidth(16, 21 * 256);
        sheet1.setColumnWidth(17, 21 * 256);
        sheet1.setColumnWidth(18, 21 * 256);
        sheet1.autoSizeColumn(1);
        sheet1.autoSizeColumn(1, true);

        sheet2.setColumnWidth(0, 21 * 256);
        sheet2.setColumnWidth(1, 21 * 256);
        sheet2.setColumnWidth(2, 21 * 256);
        sheet2.setColumnWidth(3, 21 * 256);
        sheet2.setColumnWidth(4, 21 * 256);
        sheet2.setColumnWidth(5, 21 * 256);
        sheet2.setColumnWidth(6, 21 * 256);
        sheet2.setColumnWidth(7, 21 * 256);
        sheet2.setColumnWidth(8, 21 * 256);
        sheet2.setColumnWidth(9, 21 * 256);
        sheet2.setColumnWidth(10, 21 * 256);
        sheet2.setColumnWidth(11, 21 * 256);
        sheet2.setColumnWidth(12, 21 * 256);
        sheet2.setColumnWidth(13, 21 * 256);
        sheet2.setColumnWidth(14, 21 * 256);
        sheet2.setColumnWidth(15, 21 * 256);
        sheet2.setColumnWidth(16, 21 * 256);
        sheet2.setColumnWidth(17, 21 * 256);
        sheet2.setColumnWidth(18, 21 * 256);
        sheet2.autoSizeColumn(1);
        sheet2.autoSizeColumn(1, true);

        HSSFRow row;
        HSSFCell cell;
        int len1 = dataSource1.length;
        int len2 = dataSource2.length;

        for (int i = 0; i < len1; i++) {
            row = sheet1.createRow(i);
            Object[] dataSourceTemp = dataSource1[i];
            int temp = dataSourceTemp.length;
            for (int j = 0; j < temp; j++) {
                cell = row.createCell(j);

                Object value = dataSourceTemp[j];

                if (null == value || "".equals(value)) {
                    continue;
                }
                cell.setCellValue(String.valueOf(value));
            }
        }

        for (int i = 0; i < len2; i++) {
            row = sheet2.createRow(i);
            Object[] dataSourceTemp = dataSource2[i];
            int temp = dataSourceTemp.length;
            for (int j = 0; j < temp; j++) {
                cell = row.createCell(j);

                Object value = dataSourceTemp[j];

                if (null == value || "".equals(value)) {
                    continue;
                }
                cell.setCellValue(String.valueOf(value));
            }
        }

        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + java.net.URLEncoder.encode(new String((excelName + ".xls").getBytes(), "UTF-8"), "UTF-8"));
            ServletOutputStream out = response.getOutputStream();

            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;

            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (bis != null)
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            if (bos != null)
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
        }
    }
}