package com.tianbo.util.POI;


import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Iterator;
import java.util.List;

/**
 * @author: lucifer
 * @description: Excel导出工具类
 */
@Slf4j
public class ExportExcelUtil<T> {
    /**
     * excel ./xls后缀
     */
    public static final String EXTENSION_XLS = ".xls";


    /**
     * excel ./xlsx后缀
     */
    public static final String EXTENSION_XLSX = ".xlsx";

    /**
     * 导出文件夹路径
     */
    public static final String EXCEL_PATH = "./upload/";



    /**
     * 兼容.xls和.xlsx格式
     *
     * @param originalFilename
     * @return
     */
    private Workbook getWorkbook(String originalFilename) {
        Workbook workbook = null;
        if (originalFilename.endsWith(EXTENSION_XLS)) {
            workbook = new HSSFWorkbook();
        } else if (originalFilename.endsWith(EXTENSION_XLSX)) {
            workbook = new XSSFWorkbook();
        }
        return workbook;
    }


    /**
     * 导出
     *
     * @param originalFilename
     * @param sheetName
     * @param headers
     * @param columns
     * @param lists
     * @throws Exception
     */
    public void export(String originalFilename, String sheetName, String[] headers, String[] columns, List<T> lists,HttpServletResponse response) throws Exception {
        Workbook workbook = getWorkbook(originalFilename);
        Sheet sheet = workbook.createSheet(sheetName);
        sheet.setDefaultColumnWidth(15);
        //设置表头样式
        CellStyle style = setHeaderStyle(workbook);
        Row row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell headerCell = row.createCell(i);
            headerCell.setCellValue(headers[i]);
            headerCell.setCellStyle(style);
        }
        Iterator<T> it = lists.iterator();
        int rowIndex = 0;
        while (it.hasNext()) {
            rowIndex++;
            row = sheet.createRow(rowIndex);
            T t = it.next();
            Field[] fields = t.getClass()
                    .getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                String fieldName = field.getName();
                for (int j = 0; j < columns.length; j++) {
                    if (fieldName.equals(columns[j])) {
                        String getMethodName = "get" +
                                fieldName.substring(0, 1).
                                        toUpperCase() + fieldName.
                                substring(1);
                        Class cls = t.getClass();
                        Method getMethod = cls.getMethod(
                                getMethodName, new Class[]{});
                        Object val = getMethod.invoke(
                                t, new Object[]{});
                        String textVal;
                        if (null != val) {
                            textVal = val.toString();
                        } else {
                            textVal = "";
                        }
                        Cell cell = row.createCell(j);
                        cell.setCellValue(textVal);
//                        CellStyle cellStyle = setCellStyle(workbook, i);
                        cell.setCellStyle(style);
                        //单元格列宽设置
                        sheet.autoSizeColumn(j);
                        sheet.setColumnWidth(j, sheet.getColumnWidth(j)*17/10 > 255 * 256 ? 255 * 256 : sheet.getColumnWidth(j)*17/10);

                        log.info("i:===============" + i + ",j:================" + j + ",textVal:" + textVal);
                    }
                }
            }
        }
        String filename = sheetName + System.currentTimeMillis() + ".xls";
        //判断是否有文件夹，没有就创建
        File file = new File(EXCEL_PATH);
        if (!file.exists()) {
            file.mkdirs();
        }
        String filepath = EXCEL_PATH + filename;


        System.out.println("filepath:" + filepath);
        FileOutputStream out = new FileOutputStream(filepath);
        workbook.write(out);
        out.close();

        downloadExcel(filepath, response);
    }

    /**
     * 设置表头样式
     *
     * @param workbook
     * @return
     */
    private CellStyle setHeaderStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //设置字体
        Font cellFont = workbook.createFont();
        cellFont.setBold(true);
        cellStyle.setFont(cellFont);
        return cellStyle;
    }

    /**
     * 设置单元格样式
     */
    private CellStyle setCellStyle(Workbook workbook, int i) {
        CellStyle cellStyle = workbook.createCellStyle();
        //奇数列 左对齐
        if ((i & 1) != 1) {
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
        } else {
            //水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        }
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //设置字体
        Font cellFont = workbook.createFont();
        cellFont.setFontName("仿宋_GB2312");
        cellStyle.setFont(cellFont);
        return cellStyle;
    }


    /**
     * 下载
     */
    public static void downloadExcel(String filepath, HttpServletResponse response)
            throws IOException {
        File file = new File(filepath);
        String fileName = file.getName();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.addHeader("Content-Transfer-Encoding", "binary");
        response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
        response.setCharacterEncoding("utf-8");
        InputStream fis = new BufferedInputStream(new FileInputStream(file));
        byte[] b = new byte[fis.available()];
        fis.read(b);
        response.getOutputStream().write(b);
        fis.close();
        log.info("导出完毕");
    }

}