package com.ruoyi.common.utils.poi;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Excel导出工具类
 */
public class ExportExcelUtil {
    private static final int XLS_MAX_ROW = 65535; //0开始

    /**
     * 给传入的HSSFWorkbook创建sheet和sheet里的内容
     *
     * @param workbook     要包含这个表单的workbook
     * @param titlesCNName 首行标题
     * @param titlesENName 对应的实体类属性
     * @param dataList     实体类数据list
     * @param sheetIndex   表单index，从0开始
     * @param hidden       是否隐藏该表单
     * @param sheetName    表单名称
     */
    public static HSSFSheet createSheet(HSSFWorkbook workbook, String[] titlesCNName, String[] titlesENName, List dataList, int sheetIndex, boolean hidden, String sheetName) {
        //创建一个工作表sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        if (hidden) {
            workbook.setSheetHidden(sheetIndex, true);
        }
        //创建第一行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;
        //插入第一行数据，中文列名
        for (int i = 0; i < titlesCNName.length; i++) {
            cell = row.createCell(i);
            if (titlesCNName[i].contains("*")) {
//				将*设置为红色
                Font font = workbook.createFont();
                font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());//红色
                HSSFRichTextString ts = new HSSFRichTextString(titlesCNName[i]);//单元格内容
                ts.applyFont(0, 1, font);  //从零开始，长度1
                cell.setCellValue(ts);
            } else {
                cell.setCellValue(titlesCNName[i]);
            }

        }
        //追加数据  dataList.size()行数
        if (!CollectionUtils.isEmpty(dataList)) {
            for (int i = 0; i < dataList.size(); i++) {
                HSSFRow nextrow = sheet.createRow(i + 1);
                //向每一列填充数据
                Object obj = dataList.get(i);
                for (int j = 0; j < titlesENName.length; j++) {
                    HSSFCell hssfCell = nextrow.createCell(j);
                    String value = getFieldValue(titlesENName[j], obj);
                    //判断是否该字段有效
                    if (null != value) {
                        hssfCell.setCellValue(value);
                    }
                }
            }
        }

        return sheet;
    }

    /**
     * 生成下拉框及提示、验证
     *
     * @param formulaString 关联了数据的name
     * @param columnIndex
     * @return
     */
    public static DataValidation getDataValidationByFormula(String formulaString, int columnIndex) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(1, XLS_MAX_ROW, columnIndex, columnIndex);
        // 数据有效性对象
        DataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
        dataValidationList.createErrorBox("Error", "请选择或输入有效的选项，或下载最新模版重试！");
        dataValidationList.createPromptBox("", "请选择或输入有效项");
        dataValidationList.setShowErrorBox(true);
        return dataValidationList;
    }

    /**
     * 根据属性名称，获取pojo的值
     *
     * @param fieldName
     * @param obj
     * @return
     */
    public static String getFieldValue(String fieldName, Object obj) {
        String getter = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
        try {
            Method method = obj.getClass().getMethod(getter);
            Object value = method.invoke(obj);
            if (value != null && !value.equals("")) {
                return value.toString();
            }

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 设置response表头
     *
     * @param request
     * @param response
     * @param strFileName
     * @return
     * @throws UnsupportedEncodingException
     */
    public static HttpServletResponse setResponseHeader(HttpServletRequest request, HttpServletResponse response,
                                                        String strFileName) throws UnsupportedEncodingException {
        response.setContentType("application/octet-stream; charset=utf-8");
        if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + new String(strFileName.getBytes(StandardCharsets.UTF_8), "ISO8859-1"));// firefox浏览器
        } else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + URLEncoder.encode(strFileName, "UTF-8"));// IE浏览器
        } else {
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + new String(strFileName.getBytes("gb2312"), "ISO8859-1"));
        }
        return response;
    }

    /**
     * 将传入的workbook导出
     *
     * @param request
     * @param response
     * @return
     * @throws IOException
     */
    public static String downloadExcel(HttpServletRequest request, HttpServletResponse response, HSSFWorkbook workbook) throws IOException {
        // 设置响应和请求编码utf-8
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        String result = "导出失败";

        Date date = new Date();
        DateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");

        String strFileName;
        strFileName = format.format(date) + ".xls";// 默认当前时间为Excel名称
        response = setResponseHeader(request, response, strFileName);//设置表头
        response.flushBuffer();// 刷新缓冲
        try {
            workbook.write(response.getOutputStream());
            result = "导出成功";
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            result = "导出失败";
        }
        return result;
    }

}

