package com.persagy.dmp.common.utils;


import com.alibaba.fastjson.JSONArray;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;


/**
 * ExcelUtils
 * @author linhuili
 * @date 2021-09-30
 */
public class ExcelUtils {

    public static void main(String[] args) throws Exception {
        // 读测试
     /*   InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("excel/equipment.xlsx");
        List<List<String>> result = getExcelSheetInfo(in,"设备");*/

        // 写测试
        ExcelModel excelModel = new ExcelModel();

        JSONArray rowList = new JSONArray();
        JSONArray columnList = new JSONArray();
        columnList.add("1你好");
        columnList.add("2");
        columnList.add("3");
        rowList.add(columnList);
        ExcelModel sheet1 = new ExcelModel();
        sheet1.setSheetName("1");
        sheet1.setSheetLines(rowList);

        ExcelModel sheet2 = new ExcelModel();
        sheet2.setSheetName("2");
        sheet2.setSheetLines(rowList);
        excelModel.addSheet(sheet1);
        excelModel.addSheet(sheet2);
        writeInfoToExcel("temp.xls", excelModel);
    }

    @Data
    public static class ExcelModel {
        private List<ExcelModel> sheetDatas;
        private String sheetName;
        private JSONArray sheetLines;

        public void addSheet(ExcelModel sheet) {
            if (sheetDatas == null) {
                sheetDatas = new ArrayList<>();
            }
            sheetDatas.add(sheet);
        }
    }


    /**
     * description 读取excel模版 将数据写入excel
     *
     * @param filePath   生成的excel文件的path
     * @param excelModel excel数据
     * @return void
     * @author feng
     * @since 17:35 2018/7/11
     */
    public static void writeInfoToExcel(String filePath, ExcelModel excelModel) throws Exception {
        // 根据模板，创建工作簿
        Workbook workBook = null;
        // 输出流
        OutputStream out = null;
        try {
            out = new FileOutputStream(new File(filePath));
            int maxLines = 65535;
            if (filePath.endsWith("xls")) {
                workBook = new HSSFWorkbook();
            } else {
                maxLines = 1048575;
                workBook = new SXSSFWorkbook(10000);
            }
            for (ExcelModel sheetData : excelModel.getSheetDatas()) {
                JSONArray rowInfoList = sheetData.getSheetLines();
                String sheetName = sheetData.getSheetName();
                Sheet sheet = workBook.createSheet(sheetName);
                // 输出流
                for (int rowNumber = 0; rowNumber < rowInfoList.size(); rowNumber++) {
                    if (rowNumber == maxLines) {
                        break;
                    }
                    JSONArray lines = rowInfoList.getJSONArray(rowNumber);
                    for (int columnNumber = 0; columnNumber < lines.size(); columnNumber++) {
                        createCell(workBook, sheet, rowNumber, columnNumber, lines.getString(columnNumber));
                    }
                }
            }

            workBook.write(out);
        } finally {
            if (out != null) {
                out.flush();
                out.close();
            }
            if (workBook != null) {
                workBook.close();
            }
        }
    }

    /**
     * description 读取excel信息
     *
     * @param in        文件输入流
     * @param sheetName sheet名称
     * @return java.util.List<java.util.List < java.lang.String>> 行信息的集合
     * @author feng
     * @since 16:16 2018/7/11
     */
    public static List<List<String>> getExcelSheetInfo(InputStream in, String sheetName) throws Exception {
        Workbook workBook = WorkbookFactory.create(in);
        // 遍历excel中的sheet
        for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
            List<List<String>> resultInfo = new ArrayList<>();
            Sheet sheet = workBook.getSheetAt(numSheet);
            if (sheet == null || !sheetName.equals(sheet.getSheetName())) {
                continue;
            }

            int lastColumnNum = 0;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                if (lastColumnNum < row.getLastCellNum()) {
                    lastColumnNum = row.getLastCellNum();
                }
            }

            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                ArrayList<String> curarr = new ArrayList<>();
                if (row == null) {
                    for (int columnNum = 0; columnNum < lastColumnNum; columnNum++) {
                        curarr.add("");
                    }
                } else {
                    for (int columnNum = 0; columnNum < lastColumnNum; columnNum++) {
                        Cell cell = row.getCell(columnNum);
                        curarr.add(getCellStringValue(cell));
                    }
                }
                resultInfo.add(curarr);
            }
            return resultInfo;

        }
        return null;
    }

    /**
     * 创建excel
     * @param workbook
     * @param sheet
     * @param rowNumber
     * @param cellNumber
     * @param content
     */
    public static void createCell(Workbook workbook, Sheet sheet, int rowNumber, int cellNumber, String content) {

        Row row = sheet.getRow(rowNumber);
        if (row == null) {
            row = sheet.createRow(rowNumber);
        }

        Cell cell = row.getCell(cellNumber);
        if (cell == null) {
            cell = row.createCell(cellNumber);
        }

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        cell.setCellStyle(cellStyle);
        if (content == null) {
            content = "";
        }
        cell.setCellValue(content);
    }

    /**
     * 获取单元格值
     * @param cell
     * @return
     * @throws Exception
     */
    public static String getCellStringValue(Cell cell) throws Exception {
        if (cell == null) {
            return "";
        }
        String value;
        switch (cell.getCellTypeEnum()) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
                } else {
                    value = new BigDecimal(Double.toString(cell.getNumericCellValue())).longValue() + "";
                }
                break;
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                value = "";
                break;
            case ERROR:
                throw new Exception("can not resolve this cell! CELL_TYPE_ERROR! ");
            case FORMULA:
                value = cell.getCellFormula();
                break;
            default:
                value = cell.getStringCellValue();
                break;
        }

        return value;
    }

    /**
     * 删除单个文件
     *
     * @param sPath 被删除文件的文件名
     */
    public static void deleteFile(String sPath) {
        File file = new File(sPath);
        // 路径为文件且不为空则进行删除
        if (file.isFile() && file.exists()) {
            file.delete();
        }
    }


    /**
     * 生成html格式的数据
     * @param datas
     * @return
     */
    public static String html(JSONArray datas){
        StringBuilder content = new StringBuilder("<html><head><style>.table-d table {background: #000;margin-top: 50px;}.table-d table td {background: #FFF;" +
                "font-size: 11.0pt;font-weight: 400;font-style: normal;text-decoration: none;font-family: 宋体;white-space: nowrap}</style></head><body>");
        content.append("<div class=\"table-d\"><table width=\"auto\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\">");
        for (int i = 0; i < datas.size(); i++) {
            JSONArray data = datas.getJSONArray(i);
            content.append("<tr>");
            for (int i1 = 0; i1 < data.size(); i1++) {
                content.append("<td>" + data.getString(i1) + "</td>"); //第一列
            }
            content.append("</tr>");
        }
        content.append("</div></table>");
        content.append("</body></html>");
        return content.toString();
    }

}
