package cn.palmte.work.utils.excel;

import cn.palmte.work.model.ProjectUserTime;
import cn.palmte.work.utils.DateKit;
import cn.palmte.work.utils.ObjectKit;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import top.jfunc.common.db.bean.Record;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by tracy on 2018/4/22.
 */
public class ExportUtils {
    private static final int ROW_ACCESS_WINDOW_SIZE = 1000;
    private SXSSFWorkbook workbook;
    private Sheet sheet;
    private CellStyle style2;
    private CellStyle        style;
    public ExportUtils(){}
    public ExportUtils(String[] headers){
        workbook = new SXSSFWorkbook(ROW_ACCESS_WINDOW_SIZE);
        sheet = workbook.createSheet();

        sheet.setDefaultColumnWidth((short)20);
        style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        Font font = workbook.createFont();
        font.setBold(false);
        // 把字体应用到当前的样式
        style.setFont(font);

        style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        style2.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.LEFT);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        Font font2 = workbook.createFont();
        font2.setBold(false);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        // 产生表格标题行
        Row row = sheet.createRow(0);
        for(short i = 0; i < headers.length; i++){
            Cell cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(headers[i]);
        }
    }

    /**
     * 单个工作薄流 excel文件输出
     *
     * @param dataset
     *            需要显示的数据集合，集合中一定要放置符合JavaBean风格的类的对象。
     * @param pattern
     *            如果有时间数据，设定输出格式。默认为"yyyy-MM-dd"
     * @param rowIndex
     *
     * @throws IOException
     */
    public void exportExcel(String[] columns, List<?> dataset, String pattern, int rowIndex) throws IOException{
        // 遍历集合数据，产生数据行
        int index = rowIndex;
        Font font3 = workbook.createFont();
        font3.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        for(int i = 0; i < dataset.size(); i++){
            Row row = sheet.createRow(index++);
            Object data = dataset.get(i);
            for(short col = 0; col < columns.length; col++){
                Cell cell = row.createCell(col);
                cell.setCellStyle(style2);
                Object value = getValue(columns, data, col);
                cell.setCellType(CellType.STRING);
                if(value == null){
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue("");
                }else{
                    if(value instanceof Integer){
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue((int)value);
                    } else if(value instanceof Long){
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue((long)value);
                    } else if(value instanceof Double){
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue((double)value);
                    } else if(value instanceof BigDecimal){
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue(((BigDecimal)value).doubleValue());
                    } else if(value instanceof Date){
                        cell.setCellType(CellType.STRING);
                        String date_str = DateKit.toStr((Date) value,pattern);
                        cell.setCellValue(date_str);
                    } else{
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(value.toString());
                    }
                }
            }
            if(i != 0 && i % ROW_ACCESS_WINDOW_SIZE == 0){
                ((SXSSFSheet)sheet).flushRows();
            }
        }
        ((SXSSFSheet)sheet).flushRows();
    }

    private Object getValue(String[] columns, Object data, short col){
        Object value = null;

        if(data instanceof Map){
            value = ((Map<String,Object>)data).get(columns[col]);
        }else if(data instanceof Object[]){
            value = ((Object[])data)[col];
        }else if(data instanceof Record){
            value = ObjectKit.getRecordValue(data,columns[col]);
        }else {//JavaBean风格
            value = ObjectKit.get(data,columns[col]);
        }
        return value;
    }

    public static void write(HttpServletResponse response,SXSSFWorkbook workbook,String fileName){
        fileName = fileName+"_"+DateKit.toStr(new Date(),"yyyyMMddHHmmss")+".xls";
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("UTF-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + java.net.URLEncoder.encode(fileName,"UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        ServletOutputStream sos = null;
        try {
            sos = response.getOutputStream();
            workbook.write(sos);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (sos != null){
                try {
                    sos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * @see ExportUtils#write(HttpServletResponse, SXSSFWorkbook, String)
     * 避免引入不必要的POI的API，SXSSFWorkbook
     */
    public void write(HttpServletResponse response , String fileName){
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("UTF-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + java.net.URLEncoder.encode(fileName,"UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        try {
            ServletOutputStream outputStream = response.getOutputStream();
            write(outputStream);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void write(OutputStream out){
        try{
            workbook.write(out);
            out.flush();
            workbook.close();
            out.close();
        }
        catch(IOException e){
            e.printStackTrace();
        }
    }

    public SXSSFWorkbook getSXSSFWorkbook(){
        return workbook;
    }

    /**
     * 分页查询数据导出到Excel的工具类
     * @param pageNumber 从那一页开始
     * @param appendMore 获取每页数据的接口
     */
    public static <T> ExportUtils  exportToExcel(String[] headers , String[] columns , int pageNumber , int pageSize , OutputStream outputStream , AppendMore<T> appendMore) throws IOException{
        ExportUtils exportUtils = new ExportUtils(headers);
        int rowIndex = 1;
        List<T> list = appendMore.getList(pageNumber , pageSize);
        while(null != list && list.size() >0 ){
            exportUtils.exportExcel(columns,list,"yyyy-MM-dd HH:mm:ss",rowIndex);
            rowIndex += list.size();
            list = appendMore.getList(++pageNumber , pageSize);
        }
        if( null!= outputStream) {
            exportUtils.write(outputStream);
        }
        return exportUtils;
    }

    /**
     * 人力成本管理导入模板专用
     *
     * @param dataset
     *            需要显示的数据集合，集合中一定要放置符合JavaBean风格的类的对象。
     * @param pattern
     *            如果有时间数据，设定输出格式。默认为"yyyy-MM-dd"
     * @param rowIndex
     *
     * @throws IOException
     */
    public void  exportTemplate(String[] columns, List<?> dataset, String pattern, int rowIndex) throws IOException{
        // 遍历集合数据，产生数据行
        int index = rowIndex;
        Font font3 = workbook.createFont();
        font3.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        for(int i = 0; i < dataset.size(); i++){
            Row row = sheet.createRow(index++);
            Object data = dataset.get(i);
            for(short col = 0; col < columns.length; col++){
                Cell cell = row.createCell(col);
                cell.setCellStyle(style2);
                Object value = data;
                cell.setCellType(CellType.STRING);
                if(value == null){
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue("");
                }else{
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(value.toString());
                }
            }
            if(i != 0 && i % ROW_ACCESS_WINDOW_SIZE == 0){
                ((SXSSFSheet)sheet).flushRows();
            }
        }
        ((SXSSFSheet)sheet).flushRows();
    }

    /**
     * 人力成本管理导出专用
     *
     * @throws IOException
     */
    public void exportAllData(String[] columns, String[] headers, List<ProjectUserTime> staff, List<ProjectUserTime> project, Map<String, BigDecimal> map, int rowIndex) throws IOException{
        // 遍历集合数据，产生数据行
        int index = rowIndex;
        Font font3 = workbook.createFont();
        font3.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        Row row = sheet.createRow(index++);
        Cell first = row.createCell(0);
        first.setCellStyle(style2);
        first.setCellType(CellType.STRING);
        first.setCellValue(project.get(rowIndex - 1).getProjectName());
        Cell second = row.createCell(1);
        second.setCellStyle(style2);
        second.setCellType(CellType.NUMERIC);
        second.setCellValue(project.get(rowIndex - 1).getCostSum().doubleValue());
        Cell third = row.createCell(2);
        third.setCellStyle(style2);
        third.setCellType(CellType.NUMERIC);
        third.setCellValue(project.get(rowIndex - 1).getSalarySum().doubleValue());
        for(short col = 3; col < headers.length; col++){
            Cell cell = row.createCell(col);
            cell.setCellStyle(style2);
            Object value = map.getOrDefault(project.get(rowIndex - 1).getProjectName() + staff.get(col - 3).getUserId(), new BigDecimal(0));
            cell.setCellType(CellType.STRING);
            if(value == null){
                cell.setCellType(CellType.STRING);
                cell.setCellValue("");
            } else if(value instanceof BigDecimal){
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(((BigDecimal)value).doubleValue());
            } else{
                cell.setCellType(CellType.STRING);
                cell.setCellValue(value.toString());
            }
        }
        ((SXSSFSheet)sheet).flushRows();
    }

    /**
     * 分页查询数据导出到Excel的工具类
     */
    public static <T> ExportUtils  exportHumanCost(String[] headers , String[] columns , List<ProjectUserTime> staff, List<ProjectUserTime> project,  OutputStream outputStream , Map<String, BigDecimal> map) throws IOException{
        ExportUtils exportUtils = new ExportUtils(headers);
        int rowIndex = 1;
        while(rowIndex <= columns.length){
            exportUtils.exportAllData(columns, headers, staff, project, map, rowIndex);
            rowIndex++;
        }
        if( null!= outputStream) {
            exportUtils.write(outputStream);
        }
        return exportUtils;
    }
}
