package com.cjs.cms.util.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.MessageFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class ExcelUtil {

    private ExcelConfig config;

    public ExcelUtil(ExcelConfig config) {
        this.config = config;
    }

    public void writeExcel() throws Exception {
        // Workbook wb = WorkbookFactory.create(new
        // File(config.getSourceTemplate()));
        // Sheet sheet =
        // wb.createSheet(WorkbookUtil.createSafeSheetName("表单1"));
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(config.getSourceTemplate()));
        SXSSFWorkbook swb = new SXSSFWorkbook(wb, 500);
        Sheet sheet = swb.getSheetAt(0);
        Row title = wb.getSheetAt(0).getRow(0);
        Row templateRow = wb.getSheetAt(0).getRow(1);
        String[] template = new String[templateRow.getLastCellNum()]; // 属性集合
        CellStyle[] cellStyles = new CellStyle[templateRow.getLastCellNum()];
        Integer rowNum = wb.getSheetAt(0).getLastRowNum(); // 模板已有行
        Integer pageSize = config.getPageSize();
        Integer pageCount = 0;
        Row row;
        Cell cell;
        List<?> list = config.getData();
        Map<String, BigDecimal> sumMap = new HashMap<String, BigDecimal>(); // 汇总列
        BigDecimal sum;
        Object value;

        if (pageSize != null) {
            pageCount = (int) Math.ceil(((float) list.size()) / pageSize);
        }
        if (config.getSumColumn() != null) {
            for (String prop : config.getSumColumn()) {
                sumMap.put(prop, new BigDecimal(0));
            }
        }

        // 获取模板行
        for (int i = 0; i < templateRow.getLastCellNum(); i++) {
            if (templateRow.getCell(i) == null) {
                template[i] = "";
                cellStyles[i] = null;
            } else {
                template[i] = ExcelUtil.getCellValue(templateRow.getCell(i));
                cellStyles[i] = templateRow.getCell(i).getCellStyle();
            }
        }

        // 写入内容
        for (int i = 0; i < list.size(); i++) {
            Object obj = list.get(i);

            // 标题
            if (pageSize != null) {
                if (i >= pageSize && i % pageSize == 0) {
                    row = sheet.createRow(rowNum++);
                    this.copy(title, row);
                    // 假设标题第一个单元格有页码信息
                    cell = row.getCell(0);
                    cell.setCellValue(MessageFormat.format(cell.getStringCellValue(),
                        i / pageSize + 1, pageCount));
                }
            }

            // 内容行
            row = sheet.createRow(rowNum++);
            for (int j = 0; j < template.length; j++) {
                cell = row.createCell(j);
                value = PropertyUtils.getProperty(obj, template[j]);
                if (value == null) {
                    value = template[j];
                }
                cell.setCellStyle(cellStyles[j]);
                this.setCellValue(cell, value);

                // 汇总
                if (value != null && sumMap.containsKey(template[j])) {
                    sum = sumMap.get(template[j]);
                    sum = sum.add(new BigDecimal(value.toString()));
                    sumMap.put(template[j], sum);
                }
            }
        }

        // 处理第一行标题的页码
        if (pageSize != null) {
            cell = title.getCell(0);
            cell.setCellValue(MessageFormat.format(cell.getStringCellValue(), 1, pageCount));
        }

        // 处理汇总
        if (config.getSumColumn() != null) {
            CellStyle style;
            //设置字体
            Font font = swb.createFont();
            font.setColor(IndexedColors.RED.getIndex());
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            row = sheet.createRow(rowNum++);
            for (int i = 0; i < template.length; i++) {
                //注意这里要从原有样式里clone，否则设置单元格样式后，会影响到整列。
                style = swb.createCellStyle();
                style.cloneStyleFrom(cellStyles[i]);
                //前景色
                style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                style.setFont(font);
                cell = row.createCell(i);
                cell.setCellStyle(style);
                if (sumMap.containsKey(template[i])) {
                    if (sumMap.get(template[i]).toString().indexOf(".") == -1) {
                        cell.setCellValue(sumMap.get(template[i]).longValue());
                    } else {
                        cell.setCellValue(sumMap.get(template[i]).doubleValue());
                    }
                }
            }
        }

        // 自动调整列
        if (config.getAutoSize()) {
            for (int i = 0; i < template.length; i++) {
                sheet.autoSizeColumn(i);
            }
        } else if (config.getAutoSizeColumn() != null) {
            for (int i = 0; i < template.length; i++) {
                for (String auto : config.getAutoSizeColumn()) {
                    if (StringUtils.equals(auto, template[i])) {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
        }

        writeFile(swb, config.getTargetFile());
    }

    public void writeSFT() throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(config.getSourceTemplate()));
        SXSSFWorkbook swb = new SXSSFWorkbook(wb, 500);
        Sheet sheet = swb.getSheetAt(0);
        wb.getSheetAt(0).getRow(1).getCell(2).setCellValue(config.getRemark1());
        Row title = wb.getSheetAt(0).getRow(2);
        Row templateRow = wb.getSheetAt(0).getRow(3);
        String[] template = new String[templateRow.getLastCellNum()]; // 属性集合
        CellStyle[] cellStyles = new CellStyle[templateRow.getLastCellNum()];
        Integer rowNum = wb.getSheetAt(0).getLastRowNum(); // 模板已有行
        Integer pageSize = config.getPageSize();
        Integer pageCount = 0;
        Row row;
        Cell cell;
        List<?> list = config.getData();
        Map<String, BigDecimal> sumMap = new HashMap<String, BigDecimal>(); // 汇总列
        BigDecimal sum;
        Object value;

        if (pageSize != null) {
            pageCount = (int) Math.ceil(((float) list.size()) / pageSize);
        }
        if (config.getSumColumn() != null) {
            for (String prop : config.getSumColumn()) {
                sumMap.put(prop, new BigDecimal(0));
            }
        }

        // 获取模板行
        for (int i = 0; i < templateRow.getLastCellNum(); i++) {
            if (templateRow.getCell(i) == null) {
                template[i] = "";
                cellStyles[i] = null;
            } else {
                template[i] = ExcelUtil.getCellValue(templateRow.getCell(i));
                cellStyles[i] = templateRow.getCell(i).getCellStyle();
            }
        }

        // 写入内容
        for (int i = 0; i < list.size(); i++) {
            Object obj = list.get(i);

            // 标题
            if (pageSize != null) {
                if (i >= pageSize && i % pageSize == 0) {
                    row = sheet.createRow(rowNum++);
                    this.copy(title, row);
                    // 假设标题第一个单元格有页码信息
                    cell = row.getCell(0);
                    cell.setCellValue(MessageFormat.format(cell.getStringCellValue(),
                        i / pageSize + 1, pageCount));
                }
            }

            // 内容行
            row = sheet.createRow(rowNum++);
            for (int j = 0; j < template.length; j++) {
                cell = row.createCell(j);
                value = PropertyUtils.getProperty(obj, template[j]);
                if (value == null) {
                    //                    value = template[j];
                    value = "";
                }
                cell.setCellStyle(cellStyles[j]);
                this.setCellValue(cell, value);

                // 汇总
                if (value != null && sumMap.containsKey(template[j])) {
                    sum = sumMap.get(template[j]);
                    sum = sum.add(new BigDecimal(value.toString()));
                    sumMap.put(template[j], sum);
                }
            }
        }

        // 处理第一行标题的页码
        if (pageSize != null) {
            cell = title.getCell(0);
            cell.setCellValue(MessageFormat.format(cell.getStringCellValue(), 1, pageCount));
        }

        // 处理汇总
        if (config.getSumColumn() != null) {
            CellStyle style;
            //设置字体
            Font font = swb.createFont();
            font.setColor(IndexedColors.RED.getIndex());
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            row = sheet.createRow(rowNum++);
            for (int i = 0; i < template.length; i++) {
                //注意这里要从原有样式里clone，否则设置单元格样式后，会影响到整列。
                style = swb.createCellStyle();
                style.cloneStyleFrom(cellStyles[i]);
                //前景色
                style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                style.setFont(font);
                cell = row.createCell(i);
                cell.setCellStyle(style);
                if (sumMap.containsKey(template[i])) {
                    if (sumMap.get(template[i]).toString().indexOf(".") == -1) {
                        cell.setCellValue(sumMap.get(template[i]).longValue());
                    } else {
                        cell.setCellValue(sumMap.get(template[i]).doubleValue());
                    }
                }
            }
        }

        // 自动调整列
        if (config.getAutoSize()) {
            for (int i = 0; i < template.length; i++) {
                sheet.autoSizeColumn(i);
            }
        } else if (config.getAutoSizeColumn() != null) {
            for (int i = 0; i < template.length; i++) {
                for (String auto : config.getAutoSizeColumn()) {
                    if (StringUtils.equals(auto, template[i])) {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
        }

        writeFile(swb, config.getTargetFile());
    }

    /**
     * 导出Excel
     * @param titles 标题
     * @param datas 数据集
     * @param targetFile 目标数据文件
     * @return
     * @throws IOException
     */
    public File createExcel(List<String> titles, List<List<Object>> datas,
                            File targetFile) throws IOException {
        SXSSFWorkbook wb = new SXSSFWorkbook(200);
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(0);
        Cell cell;
        //标题
        for (int i = 0; i < titles.size(); i++) {
            cell = row.createCell(i);
            this.setCellValue(cell, titles.get(i));
            sheet.autoSizeColumn(i);
        }
        //数据
        for (int i = 0; i < datas.size(); i++) {
            row = sheet.createRow(i + 1);
            List<Object> rowData = datas.get(i);
            for (int j = 0; j < rowData.size(); j++) {
                cell = row.createCell(j);
                this.setCellValue(cell, rowData.get(j));
            }
        }
        for (int i = 0; i < titles.size(); i++) {
            sheet.autoSizeColumn(i);
        }

        this.writeFile(wb, targetFile);
        return targetFile;
    }

    /** 写入目标文件 */
    private void writeFile(SXSSFWorkbook wb, File file) throws IOException {
        FileOutputStream out = null;
        try {
            file.getParentFile().mkdirs();
            out = new FileOutputStream(file);
            wb.write(out);
        } catch (IOException e) {
            throw e;
        } finally {
            if (out != null) {
                out.close();
            }
            wb.dispose();
        }
    }

    /** 获取单元格值 */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_NUMERIC:
                return new BigDecimal(cell.getNumericCellValue()).toString();
            case Cell.CELL_TYPE_BOOLEAN:
                return new Boolean(cell.getBooleanCellValue()).toString();
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
        }
        return cell.getStringCellValue();
    }

    /** 设置单元格值 */
    public void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
            return;
        }
        if (value instanceof Number) {
            cell.setCellValue(new BigDecimal(value.toString()).doubleValue());
        } else if (value instanceof Date) {
            if (config.getDateFormat() == null) {
                cell.setCellValue((Date) value);
            } else {
                cell.setCellValue(config.getDateFormat().format((Date) value));
            }
        } else {
            cell.setCellValue(value.toString());
        }
    }

    /** 将source复制到target */
    public Row copy(Row source, Row target) {
        target.setHeight(source.getHeight());
        Cell cell;
        for (int i = 0; i < source.getLastCellNum(); i++) {
            cell = target.createCell(i);
            cell.setCellValue(source.getCell(i).getStringCellValue());
            cell.setCellStyle(source.getCell(i).getCellStyle());
        }
        return target;
    }
}