package com.zhongshu.card.server.core.util;

import com.github.microservice.core.util.net.apache.HttpClientUtil;
import com.github.microservice.core.util.net.apache.HttpModel;
import com.github.microservice.core.util.net.apache.MethodType;
import com.zhongshu.card.server.core.util.excel.CommonExeclParam;
import com.zhongshu.card.server.core.util.excel.CommonExeclTd;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import lombok.Cleanup;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Slf4j
public class ExcelUtils {

    @SneakyThrows
    public static List<List<Cell>> readExcelCell(String fileUrl) {
        return readExcelCell(fileUrl, null);
    }

    public static Cell getCellByRow(XSSFRow row, int index, XSSFCellStyle cellStyle, String val) {
        Cell cell = row.getCell(index);
        if (cell == null) {
            cell = row.createCell(index);
        }
        cell.setCellStyle(cellStyle);
        cell.setCellValue(val);
        return cell;
    }

    /**
     * 读取excel文件每一行的数据
     *
     * @param fileUrl
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    @SneakyThrows
    public static List<List<Cell>> readExcelCell(String fileUrl, Integer numberOfSheets) {
        // 工作表

//        URL url = new URL(fileUrl);
//
//        URLConnection urlConnection = url.openConnection();
//        InputStream fileInputStream = urlConnection.getInputStream();

        File file = downloadExcel(fileUrl);
        FileInputStream fileInputStream = new FileInputStream(file);

        Workbook workbook = WorkbookFactory.create(fileInputStream);

        if (numberOfSheets == null) {
            // 表个数。
            numberOfSheets = workbook.getNumberOfSheets();
        }
        //时间转换


        // 遍历表。
        List<List<Cell>> list = new ArrayList<>();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);

            // 行数。
            int rowNumbers = sheet.getLastRowNum() + 1;//表头第一行是标题

            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }

            //列数
            int cells = temp.getPhysicalNumberOfCells();

            // 读数据。
            for (int row = 1; row < rowNumbers; row++) {//第一行是标题
                Row r = sheet.getRow(row);
                //第row行数据
                List<Cell> cellList = new ArrayList<>();
                for (int m = 0; m < cells; m++) {
                    cellList.add(r.getCell(m));
                }
                list.add(cellList);
            }
        }
        return list;

    }

    @SneakyThrows
    private static File downloadExcel(String url) {
        File tmpFile = File.createTempFile("tmp_", ".xlsx");
        @Cleanup FileOutputStream fileOutputStream = new FileOutputStream(tmpFile);

        HttpModel httpModel = new HttpModel();
        httpModel.setUrl(URLDecoder.decode(url, "UTF-8"));
        httpModel.setMethod(MethodType.Get);
        httpModel.setTimeOut(600);

        int code = HttpClientUtil.request(httpModel, fileOutputStream, null);

        return tmpFile;
    }

//   public static void export(List<PriceModel> list){
//
//       Date startTime = new Date();
//       MemoryUsage startMemory = ManagementFactory.getMemoryMXBean().getHeapMemoryUsage();
//
//       // 创建工作薄
//       XSSFWorkbook workbook = new XSSFWorkbook();
//       // 创建工作表
//       XSSFSheet sheet = workbook.createSheet("sheet1");
//
//       //设置数据
//       int row = 0;
//       for (PriceModel priceModel : list) {
//           XSSFRow sheetRow = sheet.createRow(row);
//           sheetRow.createCell(0).setCellValue(priceModel.getName());
//           sheetRow.createCell(1).setCellValue(priceModel.getModel());
//           sheetRow.createCell(2).setCellValue(priceModel.getUnit());
//           sheetRow.createCell(3).setCellValue(priceModel.getTaxPrice());
//           sheetRow.createCell(4).setCellValue(priceModel.getNotaxPrice());
//           sheetRow.createCell(5).setCellValue(priceModel.getCode());
//           sheetRow.createCell(6).setCellValue(priceModel.getMatType());
//           sheetRow.createCell(7).setCellValue(priceModel.getRemark());
//
//           sheetRow.createCell(8).setCellValue(priceModel.getFamily());
//           sheetRow.createCell(9).setCellValue(priceModel.getTrunkDiameter());
//           sheetRow.createCell(10).setCellValue(priceModel.getTopDiameter());
//           sheetRow.createCell(11).setCellValue(priceModel.getBranchHeight());
//           sheetRow.createCell(12).setCellValue(priceModel.getHeight());
//           row++;
//       }
//
//       //写入文件
//       try {
//           workbook.write(new FileOutputStream(new File("C:\\Users\\zcyk\\Desktop\\材料\\园林绿化工程材料.xlsx")));
//           workbook.close();
//       } catch (Exception ex) {
//           System.out.println(ex.getMessage());
//       }
//       Date endTime = new Date();
//       MemoryUsage endMemory = ManagementFactory.getMemoryMXBean().getHeapMemoryUsage();
//       System.out.println("Cost time(ms): " + (endTime.getTime() - startTime.getTime()));
//       System.out.println("Cost memory(): " + (endMemory.getUsed() - startMemory.getUsed()));
//
//       System.out.println( "Hello World!" );
//   }


    public static void main(String[] args) throws IOException {
        read();
    }

    private static void read() throws IOException {
        // 工作表
        File file = new File("C:\\Users\\zcyk\\Desktop\\打卡数据.xlsx");
//        URL url = new URL("https://zcykenterprisetest.oss-cn-hangzhou.aliyuncs.com/613ff7ed820d240791814f6b/60f6465d76d72e5480b37ad8zgFile/bb54259b1e004513a92fec020c2673bb.xlsx");

//        URLConnection urlConnection = url.openConnection();
//        InputStream fileInputStream = urlConnection.getInputStream();
        InputStream fileInputStream = new FileInputStream(file);

        Workbook workbook = WorkbookFactory.create(fileInputStream);

        // 表个数。
        int numberOfSheets = workbook.getNumberOfSheets();
        //时间转换


        // 遍历表。
        List<List<Cell>> list = new ArrayList<>();
        for (int i = 0; i < numberOfSheets - 1; i++) {
            Sheet sheet = workbook.getSheetAt(i);

            // 行数。
            int rowNumbers = sheet.getLastRowNum() + 1;//表头第一行是标题

            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }

            //列数
            int cells = temp.getPhysicalNumberOfCells();

            // 读数据。
            for (int row = 1; row < rowNumbers; row++) {//第一行是标题
                Row r = sheet.getRow(row);
                //第row行数据
                List<Cell> cellList = new ArrayList<>();
                for (int m = 0; m < cells; m++) {
                    cellList.add(r.getCell(m));
                    System.out.println("第" + row + "行，第" + m + "列的值：" + r.getCell(m).getCellType());
                }
                list.add(cellList);
            }
        }

        list.forEach(System.out::println);

    }

    public static String encodeFileName(HttpServletRequest request, String fileName) throws UnsupportedEncodingException {

        String new_filename = URLEncoder.encode(fileName, "UTF8").replaceAll("\\+", "%20");

        String agent = request.getHeader("USER-AGENT").toLowerCase();
        if (null != agent && -1 != agent.indexOf("msie")) {
            /**
             * IE浏览器，只能采用URLEncoder编码
             */
            return "filename=\"" + new_filename + "\"";
        } else if (null != agent && -1 != agent.indexOf("applewebkit")) {
            /**
             * Chrome浏览器，只能采用ISO编码的中文输出
             */
            return "filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + "\"";
        } else if (null != agent && -1 != agent.indexOf("opera")) {
            /**
             * Opera浏览器只可以使用filename*的中文输出
             * RFC2231规定的标准
             */
            return "filename*=" + new_filename;
        } else if (null != agent && -1 != agent.indexOf("safari")) {
            /**
             * Safani浏览器，只能采用iso编码的中文输出
             */
            return "filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + "\"";
        } else if (null != agent && -1 != agent.indexOf("firefox")) {
            /**
             * Firfox浏览器，可以使用filename*的中文输出
             * RFC2231规定的标准
             */
            return "filename*=" + new_filename;
        } else {
            return "filename=\"" + new_filename + "\"";
        }
    }

    /**
     * 通用导出execl
     *
     * @param request
     * @param response
     * @param param
     */
    public static void commonExecuteExcel(HttpServletRequest request, HttpServletResponse response, CommonExeclParam param) {
        String downFileName = param.getTitle();
        List<CommonExeclTd> tds = param.getTds();

        XSSFWorkbook wb = null;
        boolean isCreate = true;
        if (wb == null) {
            isCreate = true;
            wb = new XSSFWorkbook();
            log.info("{} 模版方式3", downFileName);
        }

        XSSFSheet newSheet = null;
        if (isCreate) {
            newSheet = wb.createSheet(downFileName);
        } else {
            newSheet = wb.getSheetAt(0);
        }

        XSSFCellStyle cellStyleTop = wb.createCellStyle();
        Font fontTop = wb.createFont();
        fontTop.setFontName("宋体");
        fontTop.setFontHeightInPoints((short) 14);
        fontTop.setBold(true);
        cellStyleTop.setFont(fontTop);
        cellStyleTop.setAlignment(HorizontalAlignment.CENTER);
        cellStyleTop.setVerticalAlignment(VerticalAlignment.CENTER);
        if (isCreate) {
            // 大标题
            XSSFRow row = newSheet.getRow(0);
            if (row == null) {
                row = newSheet.createRow(0);
            }
            row.setHeight((short) 600);
            ExcelUtils.getCellByRow(row, 0, cellStyleTop, downFileName);
            if (ObjectUtils.isNotEmpty(tds) && tds.size() > 1) {
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, tds.size() - 1);
                newSheet.addMergedRegion(region);
            }
        }

        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);
//        cellStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
//        //必须设置 否则背景色不生效
//        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        if (ObjectUtils.isNotEmpty(tds)) {
            // 数据表头
            int titleRow = param.getTitleRow();
            XSSFRow row = newSheet.getRow(titleRow);
            if (row == null) {
                row = newSheet.createRow(titleRow);
            }
            row.setHeight((short) 400);
            for (int i = 0; i < tds.size(); i++) {
                CommonExeclTd td = tds.get(i);
                ExcelUtils.getCellByRow(row, i, cellStyle, td.getTitle());
            }
        }

        XSSFCellStyle cellStyleRow = wb.createCellStyle();
        cellStyleRow.setAlignment(HorizontalAlignment.CENTER);
        cellStyleRow.setVerticalAlignment(VerticalAlignment.CENTER);
        Font fontRow = wb.createFont();
        fontRow.setFontName("宋体");
        fontRow.setFontHeightInPoints((short) 11);
        cellStyleRow.setFont(fontRow);
        List<Map<String, Object>> datas = param.getDatas();
        if (ObjectUtils.isNotEmpty(datas)) {
            int rowIndex = param.getStartRow();
            for (Map<String, Object> map : datas) {
                XSSFRow row = newSheet.getRow(rowIndex);
                if (row == null) {
                    row = newSheet.createRow(rowIndex);
                }
                for (int i = 0; i < tds.size(); i++) {
                    CommonExeclTd td = tds.get(i);
                    String key = td.getKey();
                    ExcelUtils.getCellByRow(row, i, cellStyleRow, getMapValue(key, map));
                }
                rowIndex++;
            }
        }
        for (int i = 0; i < tds.size(); i++) {
            int tdWidth = tds.get(i).getTdWidth();
            if (tdWidth <= 0) {
                newSheet.autoSizeColumn(i);
            } else {
                newSheet.setColumnWidth(i, tdWidth * 512);
            }
        }

        try {
            OutputStream outputStream = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel;charset=GBK");// application/x-download
            response.setHeader("Content-Disposition", "attachment; " + ExcelUtils.encodeFileName(request, downFileName));
            response.setCharacterEncoding("GBK");
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String getMapValue(String key, Map<String, Object> map) {
        String str = "";
        if (StringUtils.isNotEmpty(key) && map != null && map.containsKey(key)) {
            Object obj = map.get(key);
            if (obj != null) {
                if (obj instanceof String) {
                    str = (String) obj;
                } else if (obj instanceof BigDecimal) {
                    BigDecimal bigDecimal = (BigDecimal) obj;
                    str = bigDecimal.toPlainString();
                } else if (obj instanceof Long) {
                    Long num = (Long) obj;
                    str = num.toString();
                }
            }
        }
        return str;
    }

    public static void setColumnWidth(XSSFSheet newSheet, int columnIndex, int length) {
        if (newSheet != null) {
            newSheet.setColumnWidth(columnIndex, length * 256);
        }
    }

}
