package yangtz.cs.liu.campus.util;

import com.core.utils.HolidayDateUtil;
import com.ruoyi.common.utils.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;

public class TemplateExcelUtils {
    protected static final Logger logger = LoggerFactory.getLogger(TemplateExcelUtils.class);
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    private static final String SPLIT = ".";

    /**
     * 根据模板导出数据
     * @param fileName
     * @param sourcePath resource/template文件夹下路径
     * @param beanParams
     * @param response
     * @throws Exception
     */
    public static void downLoadExcel(String fileName, String sourcePath, Map<String, Object> beanParams, HttpServletResponse response)
            throws Exception {
        try{
            //返回前端流形式
            OutputStream os = getOutputStream(fileName,response);
            //读取模板
            InputStream is = TemplateExcelUtils.class.getClassLoader().getResourceAsStream("template/"+sourcePath);
            /*XLSTransformer transformer = new XLSTransformer();
            //向模板中写入内容
            Workbook workbook = transformer.transformXLS(is, beanParams);*/
            //写入成功后转化为输出流
            /*workbook.write(os);*/
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
    }

    /**
     * 导出文件时为Writer生成OutputStream.
     * @param fileName 文件名
     * @param response response
     * @return ""
     */
    private static OutputStream getOutputStream(String fileName,
                                                HttpServletResponse response) throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }

    public static String convertTemplatePath(String path) {
        // 如果是windows 则直接返回
        // if (System.getProperties().getProperty("os.name").contains("Windows")) {
        // return path;
        // }

        Resource resource = new ClassPathResource(path);
        FileOutputStream fileOutputStream = null;
        // 将模版文件写入到 tomcat临时目录
        String folder = System.getProperty("catalina.home");
        File tempFile = new File(folder + File.separator + path);
        // System.out.println("文件路径：" + tempFile.getPath());
        // 文件存在时 不再写入
        if (tempFile.exists()) {
            return tempFile.getPath();
        }
        File parentFile = tempFile.getParentFile();
        // 判断父文件夹是否存在
        if (!parentFile.exists()) {
            parentFile.mkdirs();
        }
        try {
            BufferedInputStream bufferedInputStream = new BufferedInputStream(resource.getInputStream());
            fileOutputStream = new FileOutputStream(tempFile);
            byte[] buffer = new byte[10240];
            int len = 0;
            while ((len = bufferedInputStream.read(buffer)) != -1) {
                fileOutputStream.write(buffer, 0, len);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return tempFile.getPath();
    }

    public static Workbook getWorkbook(MultipartFile file) {
        Workbook workbook=null;
        try {
            // 获取Excel后缀名
            String fileName = file.getOriginalFilename();
            if (StringUtils.isEmpty(fileName) || fileName.lastIndexOf(SPLIT) < 0) {
                logger.warn("解析Excel失败，因为获取到的Excel文件名非法！");
                return null;
            }
            String fileType = fileName.substring(fileName.lastIndexOf(SPLIT) + 1, fileName.length());
            // 获取Excel工作簿
            if (fileType.equalsIgnoreCase(XLS)) {
                workbook = new HSSFWorkbook(file.getInputStream());
            } else if (fileType.equalsIgnoreCase(XLSX)) {
                workbook = new XSSFWorkbook(file.getInputStream());
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    public static List<String> getSheetTitles(Workbook workbook) {
        //拿第一个sheet表
        Sheet sheet = workbook.getSheetAt(0);
        // 校验sheet是否合法
        if (Objects.isNull(sheet)) {
            return null;
        }
        // 获取第一行数据（假如第一行就是列名）
        Row sheetTitleRow = sheet.getRow(sheet.getFirstRowNum());
        // 取出最后一列
        short lastCellNum = sheetTitleRow.getLastCellNum();
        List<String> sheetTitleList = new LinkedList<>();
        for (int i = 0; i < lastCellNum; i++) {
            //取出每一列的名
            String cellValue = sheetTitleRow.getCell(i).getStringCellValue();
            sheetTitleList.add(cellValue);
        }
        return sheetTitleList;
    }

    public static String getCellValue(Cell cell, Workbook workbook){
        if(cell==null){
            return "";
        }
        String cellValue =  "" ;

        if(cell.getCellType().equals(CellType.STRING)){
            //字符串
            cellValue = cell.getStringCellValue().trim();
            cellValue = StringUtils.isEmpty(cellValue) ?  ""  : cellValue;
            //return cell.getRichStringCellValue().getString();
        }else if(cell.getCellType().equals(CellType.NUMERIC)){//数值
            if (DateUtil.isCellDateFormatted(cell)) {
                //日期类型
                cellValue = HolidayDateUtil.format1.format(cell.getDateCellValue());
                //return cell.getDateCellValue();
            } else {
                //数值类型
                cellValue =  new DecimalFormat( "#.######" ).format(cell.getNumericCellValue());
                //return cell.getNumericCellValue();
            }
        }else if(cell.getCellType().equals(CellType.FORMULA)){//公式类型
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            cellValue = evaluator.evaluate(cell).getNumberValue()+"";
            //return evaluator.evaluate(cell).getNumberValue()+"";
        }else if(cell.equals(CellType.BOOLEAN)){//布尔类型
             cellValue = cell.getBooleanCellValue()? "TRUE" : "FALSE";
            //return cell.getBooleanCellValue()? "TRUE" : "FALSE";
        }else{

        }
        return cellValue;
    }

}
