kmall-pt/kmall-common/src/main/java/com/kmall/common/utils/excel/ExcelUtil.java

package com.kmall.common.utils.excel;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.XLSReader;
import org.springframework.stereotype.Component;
import org.xml.sax.SAXException;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipInputStream;


/**
 * 导出EXCEL表格
 *
 * @author 曾俊
 * @create 2017-05-27 10:26
 */
@Component
public class ExcelUtil {

    /**
     * @param
     * @return
     * @info 写出Excel标题内容
     */
    public static void exportExcel(String fileName, String[] titles,
                                   List<Map<Integer, String>> lists, HttpServletResponse response, HttpServletRequest request) throws IOException {
        XSSFWorkbook xls = new XSSFWorkbook();
        XSSFSheet sheet = xls.createSheet("Sheet1");
        XSSFRow row = sheet.createRow(0);// 第一行
        for (int i = 0; i < titles.length; i++) {
            sheet.setColumnWidth(i, (short) 5000);
            sheet.setColumnWidth(0, (short) 1300);
            row.createCell(i).setCellValue(titles[i]);
        }
        // 内容
        int rowNum = 1;   //第二行开始
        for (Map<Integer, String> map : lists) {
            XSSFRow rowTmp = sheet.createRow(rowNum);
            int cols = map.size();
            for (int i = 0; i < cols; i++) {
                rowTmp.createCell(i).setCellValue(map.get(i));
            }
            rowNum++;
        }
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        xls.write(os);
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        //根据浏览器不同，对文件的名字进行不同的编码设置
        final String userAgent = request.getHeader("USER-AGENT");
        String finalFileName = null;
        if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {    //IE浏览器
            finalFileName = URLEncoder.encode(fileName + ".xlsx", "UTF-8");
        } else if (StringUtils.contains(userAgent, "Mozilla")) {   //google,火狐浏览器
            finalFileName = new String((fileName + ".xlsx").getBytes("UTF-8"), "ISO-8859-1");
        } else {
            finalFileName = URLEncoder.encode(fileName + ".xlsx", "UTF-8");   //其他浏览器
        }
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="
                + finalFileName);
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            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)
                bis.close();
            if (bos != null)
                bos.close();
        }
        os.close();

    }


    /**
     * 导出文件
     *
     * @param fileName
     * @param response
     * @param inStream
     * @throws Exception
     */
    public static void exportFile(String fileName, HttpServletResponse response, InputStream inStream) throws Exception {
        byte[] data = FileUtil.readInputStream(inStream);
        ServletOutputStream os = null;
        try {
            os = response.getOutputStream();
            response.setContentType("application/octet-stream ");
            response.setHeader("Connection", "close"); // 表示不能用浏览器直接打开
            response.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));
            response.setCharacterEncoding("UTF-8");
            os.write(data);
        } catch (IOException e) {
            throw e;
        } finally {
            if (inStream != null)
                inStream.close();
        }
        os.close();
    }

    /**
     * 读取导入excel
     *
     * @param name
     * @param beans
     * @param in
     * @throws InvalidFormatException
     * @throws SAXException
     * @throws IOException
     */
    public void readExcel(String name, Map<String, Object> beans, InputStream in) throws InvalidFormatException, SAXException, IOException {
        InputStream inputXML = null;
        InputStream inputXLS = null;
        try {
            inputXML = new BufferedInputStream(this.getClass().getResourceAsStream(name));
            XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
            inputXLS = new BufferedInputStream(in);
            mainReader.read(inputXLS, beans);
        } catch (Exception e) {
            throw e;
        } finally {
            if (inputXML != null) {
                inputXML.close();
            }
            if (inputXLS != null) {
                inputXLS.close();
            }

        }

    }

    /**
     * 读取导入excel  只用于ZipInputStream 因为ZipInputStream不能在这个时候关闭
     *
     * @param name
     * @param beans
     * @param in
     * @throws InvalidFormatException
     * @throws SAXException
     * @throws IOException
     */
    public void readZipExcel(String name, Map<String, Object> beans, ZipInputStream in) throws InvalidFormatException, SAXException, IOException {
        InputStream inputXML = null;
        InputStream inputXLS = null;
        try {
            inputXML = new BufferedInputStream(this.getClass().getResourceAsStream(name));
            XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
            inputXLS = new BufferedInputStream(in);
            mainReader.read(in, beans);
        } catch (Exception e) {
            throw e;
        } finally {
//            if (inputXML != null) {
//                inputXML.close();
//            }
        }

    }


    /**
     * @desc 拿到图片OSS路径转换为流
     * @author 曾俊
     * @create 2017/7/25 0025 17:31
     **/
    public static InputStream getInputStream(String path) throws IOException {
        InputStream inputStream = null;
        HttpURLConnection httpURLConnection = null;
        try {
            URL url = new URL(path);
            if (url != null) {
                httpURLConnection = (HttpURLConnection) url.openConnection();
                httpURLConnection.setConnectTimeout(3000);
                httpURLConnection.setRequestMethod("GET");
                int responseCode = httpURLConnection.getResponseCode();
                if (responseCode == 200) {
                    inputStream = httpURLConnection.getInputStream();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return inputStream;

    }

    /**
     * @param
     * @return
     * @info 写出Excel标题内容
     */
    public static void exportFormatExcel(String fileName, String[] titles,String width[],
                                         List<Map<Integer, String>> lists, HttpServletResponse response, HttpServletRequest request) throws IOException {
        XSSFWorkbook xls = new XSSFWorkbook();
        XSSFSheet sheet = xls.createSheet("Sheet1");
        XSSFRow row = sheet.createRow(0);// 第一行
        row.setHeight((short)600);

        for (int i = 0; i < titles.length; i++) {
            row.createCell(i).setCellValue(titles[i]);
            sheet.setColumnWidth(i, Integer.valueOf(width[i]));
        }
        if(lists.size()>1){//合并单元格
            sheet.addMergedRegion(new CellRangeAddress(1,lists.size(),8,8));
            sheet.addMergedRegion(new CellRangeAddress(1,lists.size(),9,9));
            sheet.addMergedRegion(new CellRangeAddress(1,lists.size(),10,10));
        }

        // 内容
        int rowNum = 1;   //第二行开始
        for (Map<Integer, String> map : lists) {
            XSSFRow rowTmp = sheet.createRow(rowNum);
            int cols = map.size();
            for (int i = 0; i < cols; i++) {
                rowTmp.createCell(i).setCellValue(map.get(i));
            }
            rowNum++;
        }
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        xls.write(os);
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        //根据浏览器不同，对文件的名字进行不同的编码设置
        final String userAgent = request.getHeader("USER-AGENT");
        String finalFileName = null;
        if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {    //IE浏览器
            finalFileName = URLEncoder.encode(fileName + ".xlsx", "UTF-8");
        } else if (StringUtils.contains(userAgent, "Mozilla")) {   //google,火狐浏览器
            finalFileName = new String((fileName + ".xlsx").getBytes("UTF-8"), "ISO-8859-1");
        } else {
            finalFileName = URLEncoder.encode(fileName + ".xlsx", "UTF-8");   //其他浏览器
        }
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="
                + finalFileName);
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            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)
                bis.close();
            if (bos != null)
                bos.close();
        }
        os.close();

    }

}