package com.casic.missiles.modular.system.util;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.beans.Encoder;
import java.io.IOException;
import java.util.*;

/**
 *
 */
@Slf4j
@SuppressWarnings("all")
public class ExportMapToExcel {


    /**
     *
     * @param title
     * @param dataset
     * @param pattern
     * @return
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String title, List<Map<String,String>> dataset) throws IOException {
        int rownum = 0;
        boolean flag = false;
        Workbook workbook = null;
        if (fileName.endsWith("xlsx")){
            workbook = new XSSFWorkbook();
        } else if (fileName.endsWith("xls")){
            workbook = new HSSFWorkbook();
        } else{
            try{
                throw new Exception("invalid file name, should be xls or xlsx");
            } catch (Exception e){
                log.info("必须是xls或者xlsx结尾的文件.");
                e.printStackTrace();
            }
        }
        Map<String,CellStyle> styles = createStyles(workbook);
        Sheet sheet = workbook.createSheet(title);
        CellStyle style = workbook.createCellStyle();

        if(dataset.isEmpty()){
            log.info("没有数据");
            return;
        }

        if (StringUtils.isNotBlank(title)){
            Row titleRow = sheet.createRow(rownum++);
            titleRow.setHeightInPoints(30);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellStyle(styles.get("title"));
            titleCell.setCellValue(title);
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
                    titleRow.getRowNum(), titleRow.getRowNum(), dataset.get(0).size()-1));
        }


        // 列名--表头
        Row row = sheet.createRow(rownum++);
        Map<String,String> headerMap = dataset.get(0);
        Integer number = 2;
        for(HashMap.Entry<String,String> entry : headerMap.entrySet()) {
            Cell cell = null;
            if(("statisticalDate").equals(entry.getKey())){//0
                cell = row.createCell(0);
                cell.setCellValue("自定义名称1");
            }else if(("transName").equals(entry.getKey())){//1
                cell = row.createCell(1);
                cell.setCellValue("自定义名称2");
            }else if(("carplate").equals(entry.getKey())){//1
                cell = row.createCell(1);
                cell.setCellValue("自定义名称2");
            }else if(("total").equals(entry.getKey())){//headerMap.size()-1
                cell = row.createCell(headerMap.size()-1);
                cell.setCellValue("自定义名称3");
            }else {
                cell = row.createCell(number++);
                cell.setCellValue(entry.getKey());
            }
            cell.setCellStyle(styles.get("header"));

        }
        //设置列宽
        for (int i = 0; i < headerMap.size(); i++) {
            //sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i,7000);
        }

        //数据
        Iterator<Map<String,String>> it = dataset.iterator();

        while (it.hasNext()){
            row = sheet.createRow(rownum++);

            Map<String,String> map = it.next();
            int num  = 2;
            for(HashMap.Entry<String,String> entry : map.entrySet()) {
                Cell cell = null;
                if(("statisticalDate").equals(entry.getKey())){//0
                    cell = row.createCell(0);
                }else if(("transName").equals(entry.getKey())){//1
                    cell = row.createCell(1);
                }else if(("carplate").equals(entry.getKey())){//1
                    cell = row.createCell(1);
                }else if(("total").equals(entry.getKey())){//headerMap.size()-1
                    cell = row.createCell(headerMap.size()-1);
                }else{
                    cell = row.createCell(num++);
                }
                cell.setCellStyle(styles.get("data2"));
                //cell.setCellValue(entry.getValue().toString());
                Object val = entry.getValue();
                if (val == null){
                    cell.setCellValue("");
                } else if (val instanceof String) {
                    cell.setCellValue((String) val);
                } else if (val instanceof Integer) {
                    cell.setCellValue((Integer) val);
                } else if (val instanceof Long) {
                    cell.setCellValue((Long) val);
                } else if (val instanceof Double) {
                    cell.setCellValue((Double) val);
                } else if (val instanceof Float) {
                    cell.setCellValue((Float) val);
                } else if (val instanceof Date) {
                    DataFormat format = workbook.createDataFormat();
                    style.setDataFormat(format.getFormat("yyyy-MM-dd"));
                    cell.setCellValue((Date) val);
                }

            }

        }
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename="+ java.net.URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());

    }


    /**
     * 创建表格样式
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        styles.put("data3", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
//		style.setWrapText(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        return styles;
    }

}
