package com.jojubanking.boot.framework.excel.core.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.jojubanking.boot.framework.excel.CustomRowHeightHandler;
import com.jojubanking.boot.framework.excel.core.ExcelTitleHandler;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

/**
 * Excel 工具类
 *
 * @author TW
 */
public class ExcelUtils {

    /**
     * 生成有图片的excel
     * @param dataList 数据列表
     */
//    public static <T>  void generateImageExcel(HttpServletResponse response,List<T> dataList) throws Exception {
//
//        if (CollectionUtils.isEmpty(dataList)) return ;
//        String fileName = "数据列表";
//        String sheetName = "数据列表";
//
//        String picHeadName = "截图";
//
//        //图片列最大图片数
////        AtomicReference<Integer> maxImageSize = new AtomicReference<>(0);
////        dataList.forEach(item -> {
////            String picUrls = item.getReportPicUrl();
////            if (StringUtils.isNotEmpty(picUrls)){
////                if (picUrls.split(",").length > maxImageSize.get()) {
////                    maxImageSize.set(picUrls.split(",").length);
////                }
////            }
////        });
//
//        // 表头名称和列宽的映射
////        Map<String, Integer> stringIntegerMap = HandleObjDataUtil.headNameColumnWidthMap(ReportQuestionReplyExportDTO.class);
////        //设置列长度所用类
////        AutoColumnWidthStyleStrategy longWidth = new AutoColumnWidthStyleStrategy();
//
////        Integer partitionSize = dataList.size()/10;
////        // 对集合切割分区并发处理
////        List<List<ReportQuestionReplyExportDTO>> dataPartition = Lists.partition(dataList, partitionSize > 20? 20 : partitionSize);
//
////        dataPartition.parallelStream().forEach(objList -> {
////            objList.forEach(item -> {
//                WriteCellData<Void> writeCellData = new WriteCellData<>();
//                if (StringUtils.isNotEmpty(item.getReportPicUrl())) {
//                    //每张图片间距
//                    Integer splitWidth = -2;
//                    //每张图片的长度
//                    Integer imageWidth = 60;
//                    //图片列的最大长度
//                    Integer sumWidth = maxImageSize.get() * (imageWidth + splitWidth);
//
//                    List<ImageData> imageDataList = new ArrayList<>();
//
//                    List<String> imagePathList = Lists.newArrayList(item.getReportPicUrl().split(","));
//                    for (int i = 1; i <= imagePathList.size(); i++) {
//                        String path = imagePathList.get(i - 1);
//                        Integer left = imageWidth * (i - 1) + i * splitWidth;
//                        Integer right = sumWidth - imageWidth - left;
//                        ImageData imageData = new ImageData();
//
//                        byte[] bytes = null;
//                        InputStream inputStream = null;
//                        ByteArrayOutputStream outputStream = null;
//                        try {
//                            inputStream = new URL(path).openStream();
//                            outputStream = new ByteArrayOutputStream();
//                            // 对图片进行压缩
//                            Thumbnails.of(inputStream).scale(0.9f).outputQuality(0.3f).toOutputStream(outputStream);
//                            bytes = outputStream.toByteArray();
//                        } catch (IOException e) {
//                            e.printStackTrace();
//                            continue;
//                        } finally {
//                            if (inputStream != null){
//                                try {
//                                    inputStream.close();
//                                } catch (IOException e) {
//                                    e.printStackTrace();
//                                }
//                            }
//                        }
//                        if (bytes.length < 1) continue;
//
//                        imageData.setImage(bytes);
//                        imageData.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
//                        //距离单元格顶部距离
//                        imageData.setTop(1);
//                        //距离单元格底部距离
//                        imageData.setBottom(1);
//                        //距离单元格左边距离
//                        imageData.setLeft(left);
//                        //距离单元格右边距离
//                        imageData.setRight(right);
//                        imageData.setAnchorType(ClientAnchorData.AnchorType.MOVE_AND_RESIZE);
//                        imageDataList.add(imageData);
//                    }
//                    writeCellData.set .setImageDataList(imageDataList);
//
//
//                    Map<String, Integer> zdyColumnWidth = new HashMap<>();
//                    //图片列名称，对应导出对象的列名称，图片列长度
//                    zdyColumnWidth.put(picHeadName, sumWidth / 6);
//                    //设置列长度所用类
//                    longWidth.setZdyColumnWidth(zdyColumnWidth,stringIntegerMap);
//                }
//                item.setWriteCellDataFile(writeCellData);
////            });
////        });
//
//        ResponseDataUtils.setExcelRespProp(response,fileName);
//        EasyExcel.write(response.getOutputStream())
//                .registerWriteHandler(longWidth)
//                .head(ReportQuestionReplyExportDTO.class)
//                .excelType(ExcelTypeEnum.XLSX)
//                .sheet(sheetName)
//                .doWrite(dataList);
//    }

    /**
     * 将列表以 Excel 响应给前端
     *
     * @param response 响应
     * @param filename 文件名
     * @param sheetName Excel sheet 名
     * @param head Excel head 头
     * @param data 数据列表哦
     * @param <T> 泛型，保证 head 和 data 类型的一致性
     * @throws IOException 写入失败的情况
     */
    public static <T> void writexytj(HttpServletResponse response, String xyrwname, String filename, String sheetName,
                                 Class<T> head, List<T> data) throws IOException {

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setWrapped(true);
        // 设置内容字体大小
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 20); // 设置内容字体大小为10
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置头部标题居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

//        //创建ExcelWriter写入对象
//        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
//                .autoCloseStream(false)
//                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//
//                .build();
//        //创建Sheet对象
//        WriteSheet sheet = new WriteSheet();
//        //设置第N个Sheet
//        sheet.setSheetNo(1);
//        //设置Sheet名称
//        sheet.setSheetName(sheetName);
//        //创建表格对象
//        WriteTable table = new WriteTable();
//        //设置第N个表格
//        table.setTableNo(1);
//
//        xyrwname = "2024年第一批次";
//        //创建表头集合
//        List<List<String>> headList = new ArrayList<List<String>>();
//
//        //第N列的表头
//        List<String> headTitle1 = new ArrayList<>();
//        List<String> headTitle2 = new ArrayList<>();
//        List<String> headTitle3 = new ArrayList<>();
//        List<String> headTitle4 = new ArrayList<>();
//        List<String> headTitle5 = new ArrayList<>();
//        List<String> headTitle6 = new ArrayList<>();
//        List<String> headTitle7 = new ArrayList<>();
//        List<String> headTitle8 = new ArrayList<>();
//        List<String> headTitle9 = new ArrayList<>();
//        List<String> headTitle10 = new ArrayList<>();
//        List<String> headTitle11 = new ArrayList<>();
//        List<String> headTitle12 = new ArrayList<>();
//        headTitle1.add(xyrwname);
//        headTitle1.add("就诊ID");
//        headTitle2.add("xyrwname");
//        headTitle2.add("学校");
//        headTitle3.add("xyrwname");
//        headTitle3.add("班级");
//        headTitle4.add("");
//        headTitle4.add("姓名");
//        headTitle5.add("");
//        headTitle5.add("性别");
//        headTitle6.add("");
//        headTitle6.add("身份证号");
//        headTitle7.add("");
//        headTitle7.add("基础项目");
//        headTitle8.add("");
//        headTitle8.add("基础项目总额(元)");
//        headTitle9.add("");
//        headTitle9.add("可选项目");
//        headTitle10.add("");
//        headTitle10.add("可选项目总额(元)");
//        headTitle11.add("");
//        headTitle11.add("缴费总额(元)");
//        headTitle12.add("");
//        headTitle12.add("就诊ID条码");
//
//        headList.add(headTitle1);
//        headList.add(headTitle2);
//        headList.add(headTitle3);
//        headList.add(headTitle4);
//        headList.add(headTitle5);
//        headList.add(headTitle6);
//        headList.add(headTitle7);
//        headList.add(headTitle8);
//        headList.add(headTitle9);
//        headList.add(headTitle10);
//        headList.add(headTitle11);
//        headList.add(headTitle12);
//        table.setHead(headList);
//
//        List<List<Object>> list = new ArrayList<>();
//        excelWriter.write(list, sheet, table);
//        // 记得 释放资源
//        excelWriter.finish();

        //指定写出的excel文件
/*        String fileName = "C:\\Users\\admin\\Desktop\\demo.xlsx";
        //指定读取的图片文件
        String imagePath = "C:\\Users\\admin\\Desktop\\路飞.jpg";
        //如果插入多张
        List<ImageDemoData> list = new ArrayList<>();
        //图片对象
        ImageDemoData imageDemoData = new ImageDemoData();
        //加入到list
        list.add(imageDemoData);
        //这里只用了File，还可以用对象里的InputStream、String、byte[]、URL
        imageDemoData.setFile(new File(imagePath));
        EasyExcel.write(fileName, ImageDemoData.class).sheet().doWrite(list);*/

        int batchSize = 10; // 可以根据实际情况调整批次大小
        int totalSize = data.size();
        int batchCount = (totalSize + batchSize - 1) / batchSize;

        // 输出 Excel
        String dateHead = "";
//        EasyExcel.write(response.getOutputStream(), head)
//                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
//                .registerWriteHandler(new ExcelTitleHandler(xyrwname, dateHead))
//                //.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
//                .registerWriteHandler(horizontalCellStyleStrategy)
//                //.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
//                .sheet(sheetName).doWrite(data);
//        EasyExcel.write(filename, head)
//                .autoCloseStream(true) // 不要自动关闭，交给 Servlet 自己处理
//                .registerWriteHandler(new ExcelTitleHandler(xyrwname, dateHead))
//                //.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
//                .registerWriteHandler(horizontalCellStyleStrategy)
//                //.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
//                .sheet(sheetName).doWrite(data);

        // 创建 ExcelWriter
        ExcelWriter excelWriter = EasyExcel.write(filename, head)
                .autoCloseStream(true) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(new ExcelTitleHandler(xyrwname, dateHead))
                //.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                .registerWriteHandler(horizontalCellStyleStrategy)
                //.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
                .build();

        try {
            for (int i = 0; i < batchCount; i++) {
                // 计算当前批次的起始和结束位置
                int start = i * batchSize;
                int end = Math.min(start + batchSize, totalSize);
                List<T> subList = data.subList(start, end);

                // 创建 WriteSheet
                WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName + "_" + (i + 1)).build();

                // 写入数据
                excelWriter.write(subList, writeSheet);
            }
        } finally {
            // 关闭 ExcelWriter
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

        // 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
//        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
//        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    }


    /**
     * 将列表以 Excel 响应给前端
     *
     * @param response 响应
     * @param filename 文件名
     * @param sheetName Excel sheet 名
     * @param head Excel head 头
     * @param data 数据列表哦
     * @param <T> 泛型，保证 head 和 data 类型的一致性
     * @throws IOException 写入失败的情况
     */
    public static <T> void write(HttpServletResponse response, String filename, String sheetName,
                                 Class<T> head, List<T> data) throws IOException {

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setWrapped(true);

        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置头部标题居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        // 创建自定义行高处理器
        short rowHeight = 150 * 20;
        CustomRowHeightHandler rowHeightHandler = new CustomRowHeightHandler(rowHeight);
        // 输出 Excel
//        EasyExcel.write(filename, head)
//                .autoCloseStream(true) // 不要自动关闭，交给 Servlet 自己处理
//                .registerWriteHandler(horizontalCellStyleStrategy)
//                .registerWriteHandler(rowHeightHandler)
//                .sheet(sheetName).doWrite(data);

        //改成分批次写入excel，防止内存溢出
        // 每批次写入的数据量
        int batchSize = 100; // 可以根据实际情况调整批次大小
        int totalSize = data.size();
        int batchCount = (totalSize + batchSize - 1) / batchSize;

        // 创建 ExcelWriter
        ExcelWriter excelWriter = EasyExcel.write(filename, head)
                .autoCloseStream(true) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(rowHeightHandler)
                .build();

        try {
            for (int i = 0; i < batchCount; i++) {
                // 计算当前批次的起始和结束位置
                int start = i * batchSize;
                int end = Math.min(start + batchSize, totalSize);
                List<T> subList = data.subList(start, end);

                // 创建 WriteSheet
                WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName + "_" + (i + 1)).build();

                // 写入数据
                excelWriter.write(subList, writeSheet);
            }
        } finally {
            // 关闭 ExcelWriter
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

    }

    /**
     * 将列表以 Excel 响应给前端
     *
     * @param response 响应
     * @param filename 文件名
     * @param sheetName Excel sheet 名
     * @param head Excel head 头
     * @param data 数据列表哦
     * @param <T> 泛型，保证 head 和 data 类型的一致性
     * @throws IOException 写入失败的情况
     */
    public static <T> void writeorder(HttpServletResponse response, String filename, String sheetName,
                                 Class<T> head, List<T> data) throws IOException {

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setWrapped(true);

        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置头部标题居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        // 创建自定义行高处理器
//        short rowHeight = 150 * 20;
//        CustomRowHeightHandler rowHeightHandler = new CustomRowHeightHandler(rowHeight);

        //改成分批次写入excel，防止内存溢出
        // 每批次写入的数据量
        int batchSize = 500; // 可以根据实际情况调整批次大小
        int totalSize = data.size();
        int batchCount = (totalSize + batchSize - 1) / batchSize;

        // 创建 ExcelWriter
        ExcelWriter excelWriter = EasyExcel.write(filename, head)
                .autoCloseStream(true) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(horizontalCellStyleStrategy)
//                .registerWriteHandler(rowHeightHandler)
                .build();

        try {
            for (int i = 0; i < batchCount; i++) {
                // 计算当前批次的起始和结束位置
                int start = i * batchSize;
                int end = Math.min(start + batchSize, totalSize);
                List<T> subList = data.subList(start, end);

                // 创建 WriteSheet
                WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName + "_" + (i + 1)).build();

                // 写入数据
                excelWriter.write(subList, writeSheet);
            }
        } finally {
            // 关闭 ExcelWriter
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

    }

    public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
       return EasyExcel.read(file.getInputStream(), head, null)
                .autoCloseStream(false)  // 不要自动关闭，交给 Servlet 自己处理
                .doReadAllSync();
    }

}
