package com.emr.util;

import com.emr.entity.Emr_Fault_Vo;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;

import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import org.apache.commons.beanutils.PropertyUtils;

import javax.servlet.http.HttpServletResponse;

/**
 * 多线程多sheet导出数据
 */
public class ThreadExcelUtils {
    /**
     * 定义每个 sheet 最多数量
     */
    public static final Integer EXCEL_MAX_CNT = 50000;

    private Workbook wb;
    private String fileName;
    private String filePath;
    private String[] hearders;
    private String[] fields;

    public Workbook getWb() {
        return wb;
    }

    public String getFileName() {
        return fileName;
    }

    public String getFilePath() {
        return filePath;
    }

    public String[] getHearders() {
        return hearders;
    }

    public String[] getFields() {
        return fields;
    }

    /**
     * @param fileName
     *            文件名称
     * @param filePath
     *            文件路径
     * @param hearders
     *            文件头
     * @param fields
     *            字段属性
     */
    public ThreadExcelUtils(String fileName, String filePath,
                            String[] hearders, String[] fields) {
        this.wb = new HSSFWorkbook();
        this.fileName = fileName;
        this.filePath = filePath;
        this.hearders = hearders;
        this.fields = fields;
    }

    public <T> List<Map<String, Object>> listConvert(List<T> list) {
        List<Map<String, Object>> list_map = new ArrayList<Map<String, Object>>(); // 定义List<Map<String, Object>>数组<br>　　　　　　　　　　// list为外部传进来的list集合
        if (CollectionUtils.isNotEmpty(list)) {
            list.forEach(item ->{
                Map<String, Object> map = null;
                try {
                    map = (Map<String, Object>) PropertyUtils.describe(item);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                }
                list_map.add(map );

            });
        }
        return list_map;
    }
    public static void main(String[] args) throws Exception {
        HttpServletResponse response = null;
       // List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
        List list = new ArrayList();
        // 造数据
        for (int i = 0; i < 10000; i++) {
            Emr_Fault_Vo emrFaultVo=new Emr_Fault_Vo();
            emrFaultVo.setId(i);
            emrFaultVo.setDeptName("xxxxxx");
            emrFaultVo.setCreater("xxxxxx");
            emrFaultVo.setUpdater("xxxxxxx");
            list.add(emrFaultVo);
        }

        String[] header = { "id", "部门", "创建人","修改人" };
        String[] fileNames = { "id", "deptName", "creater","updater" };
        String filePath = "C:\\Users\\Administrator\\Desktop\\test\\excelDir";
        String filename = "测试表 " + getDate(new Date()) + ".xls";
        ThreadExcelUtils utils = new ThreadExcelUtils(filename, filePath,
                header, fileNames);
        System.out.println("开始执行导出.......");
        long start = System.currentTimeMillis();
        utils.exportExcelToFilePath(response,utils.listConvert(list));
        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - start) / 1000 + "秒");
    }

    /**
     * @param list
     *            数据
     */
    public String exportExcelToFilePath(HttpServletResponse response, List<Map<String, Object>> list)
            throws Exception {
        /**
         * 代码里面使用Content-Disposition来确保浏览器弹出下载对话框的时候。
         * response.addHeader("Content-Disposition","attachment");一定要确保没有做过关于禁止浏览器缓存的操作
         */
       /* response.setHeader("Cache-Control", "No-cache");
        response.flushBuffer();   */

        String result="成功导出Excel!";
        int excelSize = EXCEL_MAX_CNT; // 每个Excel文件条数
        int totalCount = list.size(); // 查询结果总条数
        int pageCount = 0;// 总sheet页个数
        int numPage = totalCount % excelSize; // 是否整页数
        if (numPage > 0)
            pageCount = totalCount / excelSize + 1;
        else
            pageCount = totalCount / excelSize;
        // 创建线程池 多sheet多线程写入 线程数 为sheet页的 1/4
        Integer threadNumber =pageCount;// pageCount / 4;
        if (threadNumber == 0)
            threadNumber = 1;
        ExecutorService threadPool = Executors.newFixedThreadPool(threadNumber);
        // 创建栅栏 等待任务完成
        CountDownLatch countDownLatch = new CountDownLatch(pageCount);
        // 循环遍历投递任务
        for (int i = 1; i <= pageCount; i++) {
            ThraedExcel thraedExcel = new ThraedExcel(list, i, pageCount,
                    numPage, this);
            thraedExcel.setCountDownLatch(countDownLatch);
            threadPool.execute(thraedExcel);
        }
        countDownLatch.await();
        Workbook wb = getWb();
        OutputStream os = response.getOutputStream();
        //导出excel建议加上重置输出流，可以不加该代码，但是如果不加必须要保证输出流中不应该在存在其他数据，否则导出会有问题
        response.reset();
        //处理乱码
        fileName = new String(fileName.getBytes("gbk"), "iso-8859-1");
        //attachment为以附件方式下载
        response.setContentType("application/vnd.ms-excel");
        //代码里面使用Content-Disposition来确保浏览器弹出下载对话框的时候。
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.setBufferSize(1024);
       /* File file = new File(filePath);
        if (!file.exists()) {//文件夹不存在
            file.mkdirs();//创建目录
        }*/
        //FileOutputStream fout = new FileOutputStream(new File(file, fileName));
        try {
            //wb.write(fout);
            wb.write(os);//将excel中的数据写到输出流中，用于文件的输出
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
            result="导出Excel异常!";
        }
        // System.out.println("文件写入完成");
        // 立即销毁线程池
        threadPool.shutdownNow();
        return result;
    }

    public static String getDate(Date date) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
                "yyyyMMddHHmmss");
        String format = simpleDateFormat.format(date);
        return format;
    }

    /**
     * JavaBean转Map
     *
     * @param obj
     * @return
     */
    public static Map<String, Object> beanToMap(Object obj) {
        Map<String, Object> params = new HashMap<String, Object>(0);
        try {
            PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
            PropertyDescriptor[] descriptors = propertyUtilsBean
                    .getPropertyDescriptors(obj);
            int length = descriptors.length;
            for (int i = 0; i < length; i++) {
                String name = descriptors[i].getName();
                if (!StringUtils.equals(name, "class")) {
                    params.put(name, propertyUtilsBean.getNestedProperty(obj,
                            name));
                }
            }
        } catch (Exception e) {
            System.err.println("bean 转Map出错");
            e.printStackTrace();
        }
        return params;
    }

    /***
     * 线程写入sheet
     */
    private static class ThraedExcel implements Runnable {

        private List<Map<String, Object>> list;// 数据
        private Integer sheetNumber;// 当前sheet页
        private Integer totalSheetCount;// 总数据
        private int numPage; // 是否整页数
        private Integer excelSize;
        private ThreadExcelUtils threadExcelUtils;
        // 栅栏对象
        private CountDownLatch countDownLatch;

        /**
         * @param list
         *            总数据
         * @param sheetNumber
         *            当前sheet页
         * @param totalSheetCount
         *            总sheet页
         * @param numPage
         *            是否整数
         */
        public ThraedExcel(List<Map<String, Object>> list, Integer sheetNumber,
                           Integer totalSheetCount, Integer numPage,
                           ThreadExcelUtils threadExcelUtils) {
            this.list = list;// 总数据
            this.sheetNumber = sheetNumber;// 当前sheet页
            this.totalSheetCount = totalSheetCount;// 总sheet页
            this.numPage = numPage;// 是否整除
            this.excelSize = ThreadExcelUtils.EXCEL_MAX_CNT;// 没个sheet最大数量
            this.threadExcelUtils = threadExcelUtils;// 当前线程对象
        }

        public void setCountDownLatch(CountDownLatch countDownLatch) {
            this.countDownLatch = countDownLatch;
        }

        @Override
        public void run() {
            List<Map<String, Object>> sheetList = null;
            if (totalSheetCount > 1) {
                if (numPage == 0) {
                    sheetList = list.subList((sheetNumber - 1) * excelSize,
                            excelSize * sheetNumber);
                } else {
                    if (sheetNumber == totalSheetCount) {
                        sheetList = list.subList((sheetNumber - 1) * excelSize,
                                list.size());
                    } else {
                        sheetList = list.subList((sheetNumber - 1) * excelSize,
                                excelSize * (sheetNumber));
                    }
                }
            } else
                sheetList = list;
            // 开始写入数据
            createWorkBook(sheetList);

            if (this.countDownLatch != null)
                this.countDownLatch.countDown();
        }

        /**水平居中、垂直居中
         * 字体：宋体
         * 字体大小：16号
         * 加粗
         *@paramworkbook
         *@return
         */
        public static CellStyle getStyle() {
            HSSFWorkbook wb=new HSSFWorkbook();
            CellStyle cellstyle=wb.createCellStyle();
            HSSFCellStyle setBorder = wb.createCellStyle();
            setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
            setBorder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
            Font font=wb.createFont();//字体
            font.setFontName("宋体");//字体
            font.setFontHeightInPoints((short)16);//字号
            font.setColor((short) 15);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
            cellstyle.setFont(font);
            setBorder.setFont(font);//选择需要用到的字体格式;
            return cellstyle;

        }

        /***
         * 写出数据
         */
        private void createWorkBook(List<Map<String, Object>> sheetList) {
            Sheet sheet = null;
            Row row = null;
            CellStyle cellStyle =null;
            synchronized (ThreadExcelUtils.class) {
                String fileName = threadExcelUtils.getFileName();
                Workbook wb = threadExcelUtils.getWb();
                sheet = wb.createSheet(fileName + "_" + this.sheetNumber);
                row = sheet.createRow(0);
                //row.setRowStyle(getStyle());
                cellStyle = wb.createCellStyle();
                //水平居中
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                //垂直居中
                cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                //关键点 IndexedColors.AQUA.getIndex() 对应颜色
                //cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                //cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());
                //cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                //Cell cell = row.createCell((short) 1);
               // cell.setCellValue("A1");
                //cell.setCellStyle(cellStyle);

                HSSFFont font = (HSSFFont) wb.createFont();//创建字体对象
                //字体加粗
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                //字体颜色变红
                font.setColor(HSSFColor.RED.index);
                //如果font中存在设置后的字体，并放置到cellStyle对象中，此时该单元格中就具有了样式字体
                cellStyle.setFont(font);
                //row.setRowStyle(style);
            }

            String[] header = threadExcelUtils.getHearders();
            String[] fields = threadExcelUtils.getFields();
            // 设置标题
            for (int i = 0; i < header.length; i++) {
               // row.getCell(i).setCellStyle(style);
                row.createCell(i).setCellValue(header[i]);
                row.getCell(i).setCellStyle(cellStyle);


            }
            // 开始写入数据
            if (sheetList != null && sheetList.size() > 0) {
                int dataLength = sheetList.size();
                for (int i = 0; i < dataLength; i++) {
                    if (i == 3 || i == 8 || i == 15) {
                        sheet.setColumnWidth(i, 20 * 256);
                    } else {
                        sheet.setColumnWidth(i, 15 * 256);
                    }

                    Row row1 = sheet.createRow(i + 1);
                    Object obj = sheetList.get(i);
                    Map<String, Object> map = (obj instanceof Map) ? (Map<String, Object>) obj
                            : beanToMap(obj);
                    int length = fields.length;
                    for (int j = 0; j < length; j++) {
                        String key = fields[j];
                        Object value = map.get(key);
                        if (value != null) {
                            // 不晓得 此处为啥有线程安全问题
                            synchronized (ThreadExcelUtils.class) {
                                if (value instanceof Date) {
                                    SimpleDateFormat sdf = new SimpleDateFormat(
                                            "yyyy-MM-dd");
                                    String format = sdf.format(value);
                                    row1.createCell(j).setCellValue(format);
                                } else {
                                    try {
                                        row1.createCell(j).setCellValue(
                                                value.toString());
                                    } catch (Exception e) {
                                        e.printStackTrace();
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}