package thyyxxk.webserver.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jetbrains.annotations.NotNull;
import thyyxxk.webserver.entity.nationalmatch.SiCentralSuppliesMini;
import thyyxxk.webserver.entity.HeadInfo;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author dj
 */
@Slf4j
public class ExcelUtil {

    public static List<SiCentralSuppliesMini> startRead() {
        Workbook wb;
        Sheet sheet;
        Row row;
        List<SiCentralSuppliesMini> list = new ArrayList<>();
        String filePath = "D:\\副本.xlsx";
        wb = readExcel(filePath);
        if (wb != null) {
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rownum; i++) {
                row = sheet.getRow(i);
                if (null == row) {
                    break;
                }
                String code = getCellFormatValue(row.getCell(0));
                if (StringUtil.notBlank(code)) {
                    SiCentralSuppliesMini item = new SiCentralSuppliesMini();
                    item.setNationalCode(code);
                    item.setSiName(getCellFormatValue(row.getCell(4)));
                    item.setMaterial(getCellFormatValue(row.getCell(5)));
                    item.setNationalName(getCellFormatValue(row.getCell(8)));
                    item.setFactory(getCellFormatValue(row.getCell(9)));
                    list.add(item);
                }
            }
        }
        return list;
    }


    //读取excel
    public static Workbook readExcel(String filePath) {
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return new XSSFWorkbook(is);
            } else {
                return null;
            }

        } catch (IOException e) {
            log.error("读取EXCEL文件出错", e);
        }
        return null;
    }

    public static String getCellFormatValue(Cell cell) {
        if (cell != null) {
            CellType type = cell.getCellType();
            //判断cell类型
            switch (type) {
                case NUMERIC:
                    return String.valueOf(cell.getNumericCellValue());
                case FORMULA:
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        return thyyxxk.webserver.utils.DateUtil.formatDatetime(cell.getDateCellValue(),"yyyy-MM-dd");
                    } else {
                        //数字
                        return String.valueOf(cell.getNumericCellValue());
                    }
                case STRING:
                    return cell.getRichStringCellValue().getString();
                default:
                    return null;
            }
        }
        return null;
    }


    /**
     * 导出Excel
     */
    private static @NotNull HSSFWorkbook getHssfWorkbook(String @NotNull [] title, String[][] values) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        // 第二步，在workbook中添加一个sheet，对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("sheet1");

        // 第三步，在sheet中添加表头第0行，注意老版本poi对Excel行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);

        // 声明列对象
        HSSFCell cell;

        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        // 创建内容
        for (int i = 0; i < values.length; i++) {
            if (null == values[i]) {
                continue;
            }
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                // 将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }

    public static void exportExcel(@NotNull HttpServletResponse response,
                                   String[] title, String[][] values) {
        HSSFWorkbook wb = ExcelUtil.getHssfWorkbook(title, values);
        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.setHeader("Cache-Control", "no-cache");
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            wb.write(out);
        } catch (Exception e) {
            log.error("导出EXCEL文件出错", e);
        } finally {
            try {
                if (null != out) {
                    out.flush();
                    out.close();
                }
                wb.close();
            } catch (Exception e) {
                log.error("关闭导出EXCEL输出流出错", e);
            }
        }
    }

    public static void exportExcelReport(HttpServletResponse response, List<Map<String, Object>> recordList, List<HeadInfo> headlList, String report_name) {
        // Excel数据拼接
        HSSFWorkbook wb = new HSSFWorkbook();

        //设置总列数，下面要用到
        int cellCounts = -1 ;
        int rowIndex = 1;

        boolean isSecondaryMeter = false; // 不存在二级表头

        for (HeadInfo head : headlList) {
            if(!"操作".equals(head.getDisplay())){
                if(head.getColumns() != null){
                    List<HeadInfo>  columns = head.getColumns();
                    rowIndex = 2;
                    isSecondaryMeter = true; // 存在二级表头
                    cellCounts+=columns.size();
                }else{
                    cellCounts++;
                }
            }
        }
        // 设置样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet(report_name);
        HSSFRow row1 = sheet.createRow(0);
        // 创建单元格,参数为列的索引
        HSSFCell cell = row1.createCell(0);
        //表头样式
        HSSFCellStyle topicStyle = wb.createCellStyle();
        HSSFFont font =  wb.createFont();//创建字体对象
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short)20);
        topicStyle.setFont(font);
        topicStyle.setAlignment(HorizontalAlignment.CENTER);
        topicStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cell.setCellStyle(topicStyle);// 将样式应用于单元格

        cell.setCellValue(report_name);
        // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        CellRangeAddress cra1 = new CellRangeAddress(0, 0, 0, cellCounts);
        sheet.addMergedRegion(cra1);
        createCellRangeBorder(cra1, sheet);

        //创造第二 三行表头
        HSSFRow row2 = sheet.createRow(1);

        HSSFRow row3 = null;
        if(isSecondaryMeter) {
            row3 = sheet.createRow(rowIndex);
        }

        int index = 0 ;
        List<String> keyList = new ArrayList<String>();
        for (HeadInfo head : headlList) {
            if(!"操作".equals(head.getDisplay())){
                if(head.getColumns() != null){
                    List<HeadInfo>  columns = head.getColumns();
                    CellRangeAddress cra = new CellRangeAddress(1, 1, index, index+columns.size()-1);
                    sheet.addMergedRegion(cra);
                    createCellRangeBorder(cra, sheet);
                    createHeaderInfo(row2,cellStyle,index,head.getDisplay(), report_name);
                    for (HeadInfo column : columns) {
                        createHeader(row3,cellStyle,index,column.getDisplay());
                        keyList.add(column.getName());
                        index++;
                    }
                }else{
                    if(rowIndex>1) {
                        CellRangeAddress cra = new CellRangeAddress(1, rowIndex, index, index);
                        sheet.addMergedRegion(cra);
                        createCellRangeBorder(cra, sheet);
                    }
                    createHeaderInfo(row2,cellStyle,index,head.getDisplay(), report_name);
                    keyList.add(head.getName());
                    index++;
                }
            }
        }

        int index2 = rowIndex+1;
        for (Map<String, Object> map : recordList) {
            //第二行开始
            HSSFRow row = sheet.createRow(index2);

            for (int i = 0; i < keyList.size(); i++) {
                createHeaderInfo(row,cellStyle,i,(map.get(keyList.get(i))==null?"":map.get(keyList.get(i)).toString())+"", report_name);

            }
            index2++;
        }

        // 处理中文不能自动调整列宽的问题
        for (int i = 0; i < cellCounts+1; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            int colWidth=sheet.getColumnWidth(i) * 17 / 10;
            // sheet 最大单元格长度问题处理
            if(colWidth<255*256){
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            }else{
                sheet.setColumnWidth(i,255*256 );
            }
        }

        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.setHeader("Cache-Control", "no-cache");
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            wb.write(out);
        } catch (Exception e) {
            log.error("导出EXCEL文件出错", e);
        } finally {
            try {
                if (null != out) {
                    out.flush();
                    out.close();
                }
                wb.close();
            } catch (Exception e) {
                log.error("关闭导出EXCEL输出流出错", e);
            }
        }
    }

    // 补全合并单元格格子线条
    public static void createCellRangeBorder(CellRangeAddress cellRange, HSSFSheet sheet) {
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRange, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRange, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRange, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRange, sheet);
    }

    private static void createHeader(HSSFRow hfCell1, HSSFCellStyle cellStyle, int column, String value) {
        HSSFCell row = hfCell1.createCell(column);
        row.setCellStyle(cellStyle);// 将样式应用于单元格
        if(StringUtils.isNotEmpty(value) && !"null".equals(value)){
            value=processNumer(value);
            row.setCellValue(value);

        } else {
            row.setCellValue(value);
        }
    }

    private static void createHeaderInfo(HSSFRow hfCell1, HSSFCellStyle cellStyle, int column, String value, String title) {
        HSSFCell row = hfCell1.createCell(column);
        // 将样式应用于单元格
        row.setCellStyle(cellStyle);
        if(StringUtils.isNotEmpty(value) && !"null".equals(value)){
            value = processNumer(value);
            row.setCellValue(value);
        } else {
            row.setCellValue(value);
        }
    }

    public static String processNumer(String value){
        // 包含点，并且是整数和.
        String pattren="([0-9]\\d*\\.?\\d*)|((-)?[0-9]\\d*\\.?\\d*)";

        if(value.contains(".") && value.matches(pattren)){
            try{
                BigDecimal bigDecimal=new BigDecimal(value);
                bigDecimal=bigDecimal.setScale(2,BigDecimal.ROUND_HALF_UP);
                return bigDecimal.toString();
            }catch (Exception e){
                return value;
            }
        }
        return value;
    }

}
