package com.qianhe.common.utils;

import org.apache.commons.compress.utils.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.util.*;

public class MtExcelUtil {
    private static MtExcelUtil instance = new MtExcelUtil();

    public MtExcelUtil() {
    }

    public static MtExcelUtil getInstance() {
        return instance;
    }

    /**
     * 将 List<Map<String,Object>> 类型的数据导出为 Excel
     * 默认 Excel 文件的输出路径为 项目根目录下
     * 文件名为 filename + 时间戳 + .xlsx
     *
     * @param mapList  数据源(通常为数据库查询数据)
     * @param filename 文件名前缀, 实际文件名后会加上日期
     * @param title    表格首行标题
     */
    public void createExcel(HttpServletResponse response, List<Map<String, Object>> mapList, String filename, String title) {
        // 获取数据源的 key, 用于获取列数及设置标题
        Map<String, Object> map = mapList.get(0);
        Set<String> stringSet = map.keySet();
        List<String> headList = new ArrayList<>(stringSet);
        Collections.sort(headList);  // 表头排序

        // 定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建一个Sheet页
        XSSFSheet sheet = wb.createSheet();

        // 设置标题样式
        XSSFCellStyle headWriteCellStyle = getTitleStyle(wb);
        // 设置标题
        setTitle(sheet, headList, headWriteCellStyle);

        // 设置内容样式
        XSSFCellStyle contentWriteCellStyle = getContentStyle(wb);
        // 设置内容
        setContent(sheet, headList, mapList, contentWriteCellStyle);

        // 导出
        export(response, wb, filename);
    }

    public void setTitle(XSSFSheet sheet, List<String> headList, XSSFCellStyle headWriteCellStyle) {
        // 设置标题
        XSSFRow row = sheet.createRow(0);
        sheet.autoSizeColumn(0);
        for (int i = 0; i < headList.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(headList.get(i));
            cell.setCellStyle(headWriteCellStyle);
        }
    }

    public XSSFCellStyle getTitleStyle(XSSFWorkbook wb) {
        // 单元格样式类
        XSSFCellStyle headWriteCellStyle = wb.createCellStyle();
        // 设置单元格背景颜色
        headWriteCellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 128, 128)));
        headWriteCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 文字水平垂直居中
        headWriteCellStyle.setAlignment(HorizontalAlignment.CENTER);  // 水平居中
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
        // 设置字体
        XSSFFont headWriteFont = wb.createFont();
        headWriteFont.setFontName("Arial");  // 字体样式
        headWriteFont.setFontHeightInPoints((short) 10);  // 字体大小
        headWriteFont.setBold(true);  // 加粗
        headWriteFont.setColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setFont(headWriteFont);
        return headWriteCellStyle;
    }

    public void setContent(XSSFSheet sheet, List<String> headList, List<Map<String, Object>> mapList, XSSFCellStyle contentWriteCellStyle) {
        XSSFRow rows;
        XSSFCell cells;
        // 循环拿到的数据给所有行每一列设置对应的值
        for (int i = 0; i < mapList.size(); i++) {
            //在这个sheet页里创建一行
            rows = sheet.createRow(i + 1);
            sheet.autoSizeColumn(i + 1);
            //给该行数据赋值
            for (int j = 0; j < headList.size(); j++) {
                Map<String, Object> rowMap = mapList.get(i);
                String head = headList.get(j);
                String value = rowMap.get(head).toString();
                cells = rows.createCell(j);
                cells.setCellValue(value);
                cells.setCellStyle(contentWriteCellStyle);
            }
        }
    }

    public XSSFCellStyle getContentStyle(XSSFWorkbook wb) {
        // 单元格样式类
        XSSFCellStyle contentWriteCellStyle = wb.createCellStyle();
        // 设置单元格背景颜色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 文字水平垂直居中
        contentWriteCellStyle.setAlignment(HorizontalAlignment.CENTER);  // 水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
        // 设置边框
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        // 设置字体
        XSSFFont headWriteFont = wb.createFont();
        headWriteFont.setFontName("Arial");  // 字体样式
        headWriteFont.setFontHeightInPoints((short) 10);  // 字体大小
        contentWriteCellStyle.setFont(headWriteFont);
        return contentWriteCellStyle;
    }

    public void export(HttpServletResponse response, XSSFWorkbook wb, String filename) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try {
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            System.out.printf("导出Excel异常{}", e.getMessage());
        } finally {
            IOUtils.closeQuietly(wb);
        }
    }
}
