package com.yqzx.common.util;
// @formatter:off
import cn.jiguang.common.utils.StringUtils;
import lombok.val;import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;

import javax.servlet.http.HttpServletResponse;import java.net.URLEncoder;import java.util.Date;import java.util.List;import java.util.Locale;
import java.util.Map;

/**
 *                .-~~~~~~~~~-._       _.-~~~~~~~~~-.
 *            __.'  @Author     ~.   .~    代码无Bug   `.__
 *          .'//     liu.q        \./       (秘籍)      \\`.
 *        .'// [916000612@qq.com]  |   欲练神功   引刀自宫  \\`.
 *      .'// .-~"""""""~~~~-._     |    _,-~~~~"""""""~-.  \\`.
 *    .'//.-"  2019-07-17     `-.  |  .-'     16:54       "-.\\`.
 *  .'//______.============-..   \ | /   ..-============.______\\`.
 *.'______________________________\|/______________________________`.
 *
 * @Description :
 */
// @formatter:on

public class ExportExcel {
    public static void exportExcel(String fileName, List<String> titles, List<List<Object>> rows, HttpServletResponse response) throws Exception {

        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 18);

        //设置标题
        HSSFRow title = sheet.createRow(0);
        for (int i = 0; i < titles.size(); i++) {
            HSSFCell cell = title.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(titles.get(i));
            cell.setCellValue(text);
        }

        //设置数据
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        for(int i=0;i<rows.size();i++){
            HSSFRow row = sheet.createRow(i+1);
            List<Object> clo = rows.get(i);
            int index = 0;
            for(Object o :clo){
                HSSFCell cell = row.createCell(index);
                if(o instanceof Date){
                    HSSFDataFormat format = workbook.createDataFormat();
                    cellStyle.setDataFormat(format.getFormat("yyyy-mm-dd HH:mm:ss"));
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue((Date)o);
                }else if(o instanceof Double){
                    HSSFDataFormat format = workbook.createDataFormat();
                    cellStyle.setDataFormat(format.getFormat("#,##0.00"));
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(Double.valueOf(o.toString()));
                }else{
                    HSSFDataFormat format = workbook.createDataFormat();
                    cellStyle.setDataFormat(format.getFormat("@"));
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(o.toString());
                }
                index++;

            }
        }
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");

        //解决 苹果浏览器乱码问题
        val ua = CommonUtil.getRequest().getHeader("User-Agent").toLowerCase(Locale.ENGLISH);
        if (ua.indexOf("macintosh") > -1 && ua.indexOf("chrome") < 0) {
            fileName = new String(fileName.getBytes(), "iso8859-1");
        } else {
            fileName = URLEncoder.encode(fileName, "utf-8");
        }

        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }
    public static void exportExcel(String fileName, List<String> titles, List<List<Object>> rows, Map<String,String> option, HttpServletResponse response) throws Exception {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 18);

        //设置标题
        HSSFRow title = sheet.createRow(0);
        for (int i = 0; i < titles.size(); i++) {
            HSSFCell cell = title.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(titles.get(i));
            cell.setCellValue(text);
            if(option!=null && !option.isEmpty()){
                String op = option.get(titles.get(i));
                if(StringUtils.isNotEmpty(op)){
                    // 设置第一列的1-10行为下拉列表
                    CellRangeAddressList regions = new CellRangeAddressList(1, rows.size(), i, i);
                    // 创建下拉列表数据
                    DVConstraint constraint = DVConstraint.createExplicitListConstraint(op.split(","));
                    // 绑定
                    HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
                    sheet.addValidationData(dataValidation);
                }
            }

        }

        //设置数据
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        for(int i=0;i<rows.size();i++){
            HSSFRow row = sheet.createRow(i+1);
            List<Object> clo = rows.get(i);
            int index = 0;
            for(Object o :clo){
                HSSFCell cell = row.createCell(index);
                if(o instanceof Date){
                    HSSFDataFormat format = workbook.createDataFormat();
                    cellStyle.setDataFormat(format.getFormat("yyyy-mm-dd HH:mm:ss"));
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue((Date)o);
                }else if(o instanceof Double){
                    HSSFDataFormat format = workbook.createDataFormat();
                    cellStyle.setDataFormat(format.getFormat("#,##0.00"));
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(Double.valueOf(o.toString()));
                }else{
                    HSSFDataFormat format = workbook.createDataFormat();
                    cellStyle.setDataFormat(format.getFormat("@"));
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(o.toString());
                }
                index++;

            }
        }
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");

        //解决 苹果浏览器乱码问题
        val ua = CommonUtil.getRequest().getHeader("User-Agent").toLowerCase(Locale.ENGLISH);
        if (ua.indexOf("macintosh") > -1 && ua.indexOf("chrome") < 0) {
            fileName = new String(fileName.getBytes(), "iso8859-1");
        } else {
            fileName = URLEncoder.encode(fileName, "utf-8");
        }

        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }


    /**
     * @Description : Excel导出自适应宽度工具类
     * @Author : wh
     * @Date: 2021/10/20 - 11:43
     */
    public static void setSizeColumn2(Sheet sheet, int size) {
        for (int i = 0; i <= size; i++) {
            //int orgWidth = sheet.getColumnWidth(i);
            // sheet.autoSizeColumn(i, true);
            sheet.setColumnWidth(i, 5000);
        }
    }
}