package com.fsinfo.common.utils;

import com.fsinfo.modules.enterprise.entity.EnterpriseRecordEntity;

import org.apache.commons.io.FileUtils;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.web.multipart.MultipartFile;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.Date;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.List;

    /**

     * @Author leijiaxuan
　　　* @Email 897953910@qq.com

     * @Date 9:50 2020/7/22

     **/

    public class ExcelInsert {

// private static Logger logger = Logger.getLogger(ExcelReader.class.getName()); // 日志打印类

        private static final String XLS = "xls";

        private static final String XLSX = "xlsx";

        /**

         * 根据文件后缀名类型获取对应的工作簿对象

         * @param inputStream 读取文件的输入流

         * @param fileType 文件后缀名类型（xls或xlsx）

         * @return 包含文件数据的工作簿对象

         * @throws IOException

         */

        public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {

            Workbook workbook = null;

            if (fileType.equalsIgnoreCase(XLS)) {

                workbook = new HSSFWorkbook(inputStream);

            } else if (fileType.equalsIgnoreCase(XLSX)) {

                workbook = new XSSFWorkbook(inputStream);

            }

            return workbook;

        }

        /**

         * 读取Excel文件内容

         * @param myfile 要读取的Excel文件流

         * @return 读取结果列表，读取失败时返回null

         */

        public static List<EnterpriseRecordEntity> readExcel(MultipartFile myfile) {

            Workbook workbook = null;

            FileInputStream inputStream = null;

            try {

                // 获取Excel后缀名

                String fileName=myfile.getOriginalFilename();

                String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());

                // // 获取Excel文件

                // File excelFile = new File(fileName);

                // if (!excelFile.exists()) {

                //// logger.warning("指定的Excel文件不存在！");

                // return null;

                // }

                File file = new File(myfile.getOriginalFilename());

                FileUtils.copyInputStreamToFile(myfile.getInputStream(), file);

                // 获取Excel工作簿

                inputStream = new FileInputStream(file);

                workbook = getWorkbook(inputStream, fileType);

                // 会在本地产生临时文件，用完后需要删除

                if (file.exists()) {

                    file.delete();

                }

                // 读取excel中的数据

                List<EnterpriseRecordEntity> resultDataList = parseExcel(workbook);

                return resultDataList;

            } catch (Exception e) {

                // logger.warning("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());

                return null;

            } finally {

                try {

                    if (null != workbook) {

                        workbook.close();

                    }

                    if (null != inputStream) {

                        inputStream.close();

                    }

                } catch (Exception e) {

                    // logger.warning("关闭数据流出错！错误信息：" + e.getMessage());

                    return null;

                }

            }

        }

        /**

         * 解析Excel数据

         * @param workbook Excel工作簿对象

         * @return 解析结果

         */

        private static List<EnterpriseRecordEntity> parseExcel(Workbook workbook) {

            List<EnterpriseRecordEntity> resultDataList = new ArrayList<>();

            // 解析sheet

            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {

                Sheet sheet = workbook.getSheetAt(sheetNum);

                // 校验sheet是否合法

                if (sheet == null) {

                    continue;

                }

                // 获取第一行数据

                int firstRowNum = sheet.getFirstRowNum();

                Row firstRow = sheet.getRow(firstRowNum);

                if (null == firstRow) {

                // logger.warning("解析Excel失败，在第一行没有读取到任何数据！");

                }

                // 解析每一行的数据，构造数据对象

                int rowStart = firstRowNum + 1;

                int rowEnd = sheet.getPhysicalNumberOfRows();

                for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {

                    Row row = sheet.getRow(rowNum);

                    if (null == row) {

                        continue;

                    }

                    EnterpriseRecordEntity resultData = convertRowToData(row);

                    if (null == resultData) {

                    // logger.warning("第 " + row.getRowNum() + "行数据不合法，已忽略！");

                        continue;

                    }

                    resultDataList.add(resultData);

                }

            }

            return resultDataList;

        }

        /**

         * 将单元格内容转换为字符串

         * @param cell

         * @return

         */

        private static String convertCellValueToString(Cell cell) {

            if(cell==null){

                return null;

            }

            String returnValue = null;

            switch (cell.getCellType()) {

                case 0: //数字

                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式

                        SimpleDateFormat sdf = null;

                        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat

                                .getBuiltinFormat("h:mm")) {

                            sdf = new SimpleDateFormat("HH:mm");

                        } else {// 日期

                            sdf = new SimpleDateFormat("yyyy-MM-dd");

                        }

                        Date date = cell.getDateCellValue();

                        returnValue = sdf.format(date);

                    } else if (cell.getCellStyle().getDataFormat() == 58) {

                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)

                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

                        double value = cell.getNumericCellValue();

                        Date date = org.apache.poi.ss.usermodel.DateUtil

                                .getJavaDate(value);

                        returnValue = sdf.format(date);

                    } else {

                        double value = cell.getNumericCellValue();

                        CellStyle style = cell.getCellStyle();

                        DecimalFormat format = new DecimalFormat();

                        String temp = style.getDataFormatString();

                        // 单元格设置成常规

                        if (temp.equals("General")) {

                            format.applyPattern("#");

                        }

                        returnValue = format.format(value);

                    }

                    break;

                case 1: //字符串

                    returnValue = cell.getStringCellValue();

                    break;

                case 4: //布尔

                    Boolean booleanValue = cell.getBooleanCellValue();

                    returnValue = booleanValue.toString();

                    break;

                case 2: // 空值

                    break;

                case 7: // 公式

                    returnValue = cell.getCellFormula();

                    break;

                case 64: // 故障

                    break;

                default:

                    break;

            }

            return returnValue;

        }

        /**

         * 提取每一行中需要的数据，构造成为一个结果数据对象

         *

         * 当该行中有单元格的数据为空或不合法时，忽略该行的数据

         *

         * @param row 行数据

         * @return 解析后的行数据对象，行数据错误时返回null

         */

        private static EnterpriseRecordEntity convertRowToData(Row row) {

            EnterpriseRecordEntity resultData = new EnterpriseRecordEntity();

            Cell cell;

            int cellNum = 0;

            // 获取记录日期

            cell = row.getCell(cellNum++);

            String recorddate = convertCellValueToString(cell);

            resultData.setRecorddate(java.sql.Date.valueOf(recorddate));

            // 获取企业名称

            cell = row.getCell(cellNum++);

            String enterpriseuuid = convertCellValueToString(cell);

            resultData.setEnterpriseuuid(enterpriseuuid);

                // 获取企业反映问题

            cell = row.getCell(cellNum++);

            String recordcontext = convertCellValueToString(cell);

            resultData.setRecordcontext(recordcontext);

            // 获取解决情况

            cell = row.getCell(cellNum++);

            String handlinfo = convertCellValueToString(cell);

            resultData.setHandlinfo(handlinfo);

            // 获取备注

            cell = row.getCell(cellNum++);

            String remarks = convertCellValueToString(cell);

            resultData.setRemarks(remarks);

            return resultData;

        }

    }