package cn.com.goldenwater.dcproj.utils.impexcel;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
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.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * <p>
 * 导出Excel的一些公用方法
 * </p>
 *
 * @author liyz
 * @version 2.0
 * @date 2019/3/6 9:43
 */
public class ExportExcel {


    /**
     * 本地测试方法
     */
    public static void main(String[] args) throws IOException {
        int count = 100000;
        JSONArray ja = new JSONArray();
        for (int i = 0; i < 100000; 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);
            ja.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", "性别");

        String title = "测试";
        OutputStream outXls = new FileOutputStream("C:/Users/a/Desktop/a.xls");
        System.out.println("正在导出xls...");
        Date d = new Date();
        ExportExcel.exportExcel(title, headMap, ja, null, 0, outXls);
        System.out.println("共" + count + "条数据,执行" + (System.currentTimeMillis() - d.getTime()) + "ms");
        outXls.close();

//        OutputStream outXlsx = new FileOutputStream("C:/Users/a/Desktop/b.xlsx");
//        System.out.println("正在导出xlsx....");
//        Date d2 = new Date();
//        ExcelUtils.exportExcelX(title,headMap,ja,null,0,outXlsx);
//        System.out.println("共"+count+"条数据,执行"+(System.currentTimeMillis()-d2.getTime())+"ms");
//        outXlsx.close();

    }

    /**
     * 未定义的字段
     */
    private static final String NO_DEFINE = "no_define";

    /**
     * 默认日期格式
     */
    private static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";

    /**
     * 默认列宽
     */
    private static final int DEFAULT_COLOUMN_WIDTH = 17;

    /**
     * 导出Excel 97(.xls)格式 ，少量数据
     *
     * @param title       标题行
     * @param headMap     属性-列名
     * @param jsonArray   数据集
     * @param datePattern 日期格式，null则用默认日期格式
     * @param colWidth    列宽 默认 至少17个字节
     * @param out         输出流
     */
    public static void exportExcel(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
        if (datePattern == null) {
            datePattern = DEFAULT_DATE_PATTERN;
        }
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        workbook.createInformationProperties();
        workbook.getDocumentSummaryInformation().setCompany("*****公司");
        SummaryInformation si = workbook.getSummaryInformation();
        //填加xls文件作者信息
        si.setAuthor("JinShui");
        //填加xls文件创建程序信息
        si.setApplicationName("导出程序");
        //填加xls文件最后保存者信息
        si.setLastAuthor("最后保存者信息");
        //填加xls文件作者信息
        si.setComments("JinShui is a company!");
        //填加xls文件标题信息
        si.setTitle("POI导出Excel");
        //填加文件主题信息
        si.setSubject("POI导出Excel");
        si.setCreateDateTime(new Date());
        // 表头样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        // titleFont.setBoldweight((short) 700);
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        // 列头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFFont headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        // 单元格样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont cellFont = workbook.createFont();
        // cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellFont.setBold(true);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        HSSFSheet sheet = workbook.createSheet();
        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
                0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("JinShui");
        //设置列宽
        //至少字节数
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = fieldName;

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if (rowIndex == 65535 || rowIndex == 0) {
                //如果数据超过了，则在第二页显示
                if (rowIndex != 0) {
                    sheet = workbook.createSheet();
                }
                //表头 rowIndex=0
                HSSFRow titleRow = sheet.createRow(0);
                titleRow.createCell(0).setCellValue(title);
                titleRow.getCell(0).setCellStyle(titleStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
                //列头 rowIndex =1
                HSSFRow headerRow = sheet.createRow(1);
                for (int i = 0; i < headers.length; i++) {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);
                }
                //数据内容从 rowIndex=2开始
                rowIndex = 2;
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            HSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell newCell = dataRow.createCell(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 {
                    cellValue = o.toString();
                }

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param title       标题行
     * @param headMap     属性-列头
     * @param jsonArray   数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth    列宽 默认 至少17个字节
     * @param out         输出流
     */
    public static void exportExcelX(String title, Map<String, String> headMap, JSONArray jsonArray, 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(HorizontalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        // titleFont.setBoldweight((short) 700);
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font cellFont = workbook.createFont();
        // cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellFont.setBold(true);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        //至少字节数
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (String fieldName : headMap.keySet()) {
            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);

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

                }
                //数据内容从 rowIndex=2开始
                rowIndex = 2;
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            SXSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++) {
                SXSSFCell newCell = dataRow.createCell(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) {
                    cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                } else {
                    cellValue = o.toString();
                }

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Web 导出excel
     */
    public static void downloadExcelFile(String title, Map<String, String> headMap, JSONArray ja, HttpServletResponse response) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExportExcel.exportExcelX(title, headMap, ja, null, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 下载Excel的重载方法（简化常用）
     *
     * @param response HttpServletResponse
     * @param os       字节数据输出流
     * @param fileName 文件名称
     */
    public static void downloadExcelFile(HttpServletResponse response, ByteArrayOutputStream os, String fileName) {
        try {
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static ByteArrayOutputStream getByteArrayOut(FileInputStream fis) throws Exception {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        byte[] buff = new byte[1024];
        int bytesRead;
        while ((bytesRead = fis.read(buff, 0, buff.length)) != -1) {
            baos.write(buff, 0, bytesRead);
        }
        baos.close();
        fis.close();
        return baos;
    }

    /**
     * 导出EXCEL时动态创建一个下拉列表
     *
     * @param groupNamesList 数据集
     * @param firstRow       下拉框从第几行开始，默认最终行结束
     * @param firstCol       下拉框从第几列开始
     * @param fis            输入流
     * @param bos            输出流
     * @return 输出流
     */
    public static ByteArrayOutputStream createListBox(String[] groupNamesList, int firstRow, int firstCol, FileInputStream fis, ByteArrayOutputStream bos) {
        try {
            if (groupNamesList.length == 0) {
                return bos;
            }
            HSSFWorkbook workBook = new HSSFWorkbook(fis);
            // 先创建一个隐藏的Sheet来存放下拉列表
            HSSFSheet hidden = workBook.createSheet("hidden");
            HSSFCell cell;
            for (int i = 0, length = groupNamesList.length; i < length; i++) {
                String name = groupNamesList[i];
                HSSFRow row = hidden.createRow(i);
                cell = row.createCell(0);
                cell.setCellValue(name);
            }
            Name namedCell = workBook.createName();
            namedCell.setNameName("hidden");
            namedCell.setRefersToFormula("hidden!$A$1:$A$" + groupNamesList.length);
            //加载数据,将名称为hidden的列当成下拉框内容
            DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
            HSSFSheet sheet = workBook.getSheetAt(0);
            // 对指定单元区域生效
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, 65535, firstCol, firstCol);
            // 绑定下拉框和作用区域
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
            //将名字为hidden的sheet设置为隐藏
            workBook.setSheetHidden(workBook.getSheetIndex("hidden"), true);
            // 对sheet页生效
            sheet.addValidationData(dataValidation);
            workBook.write(bos);
            bos.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return bos;
    }

    /**
     * 导出EXCEL时动态创建一个下拉列表
     *
     * @param groupNamesList 数据集
     * @param firstRow       下拉框从第几行开始，默认最终行结束
     * @param firstCol       下拉框从第几列开始
     * @param workBook       WorkBook
     * @return 输出流
     */
    public static ByteArrayOutputStream createListBox(String[] groupNamesList, int firstRow, int firstCol, Workbook workBook) {
        // 隐藏Sheet的名称最多支持31个字符，时间戳13位
        String hiddenSheetName = "hidden" + System.currentTimeMillis();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            if (groupNamesList.length == 0 || workBook == null) {
                if(workBook != null){
                    workBook.write(bos);
                    bos.flush();
                    bos.close();
                }
                return bos;
            }
            // 先创建一个隐藏的Sheet来存放下拉列表
            Sheet hidden = workBook.createSheet(hiddenSheetName);
            Cell cell;
            for (int i = 0, length = groupNamesList.length; i < length; i++) {
                String name = groupNamesList[i];
                Row row = hidden.createRow(i);
                cell = row.createCell(0);
                cell.setCellValue(name);
            }
            Name namedCell = workBook.createName();
            namedCell.setNameName(hiddenSheetName);
            namedCell.setRefersToFormula(hiddenSheetName + "!$A$1:$A$" + groupNamesList.length);
            //加载数据,将名称为hidden的列当成下拉框内容
            DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheetName);
            Sheet sheet = workBook.getSheetAt(0);
            // 对指定单元区域生效
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, 65535, firstCol, firstCol);
            // 绑定下拉框和作用区域
            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
            //将名字为hidden的sheet设置为隐藏
            workBook.setSheetHidden(workBook.getSheetIndex(hiddenSheetName), true);
            // 对sheet页生效
            sheet.addValidationData(dataValidation);
            workBook.write(bos);
            bos.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return bos;
    }

}


class Student {
    private String name;
    private int age;
    private Date birthday;
    private float height;
    private double weight;
    private boolean sex;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public float getHeight() {
        return height;
    }

    public void setHeight(float height) {
        this.height = height;
    }

    public double getWeight() {
        return weight;
    }

    public void setWeight(double weight) {
        this.weight = weight;
    }

    public boolean isSex() {
        return sex;
    }

    public void setSex(boolean sex) {
        this.sex = sex;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}