package com.fdkankan.platform.common;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    private static Logger log = LoggerFactory.getLogger("programLog");

    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    public static  List<Map<String, String>> readExcelForManageAgent(String finalXlsxPath) {
        OutputStream out = null;
        try {
            List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
            // 读取Excel文档
            File finalXlsxFile = new File(finalXlsxPath);
            if (!finalXlsxFile.exists()) {
                return null;
            }
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);

            int rowNumber = sheet.getLastRowNum();
            log.info("原始数据总行数，除属性列：" + rowNumber);
            for (int i = 1; i <= rowNumber; i++) {
                Row row = sheet.getRow(i);
                Map<String, String> dataMap = new HashMap<String, String>();
                for (int j = 0; j < 5; ++j) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        break;
                    }
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String str = cell.getStringCellValue();
                    dataMap.put(String.valueOf(j), str);
                }
                dataList.add(dataMap);
            }
            return dataList;
        } catch (Exception e) {
            StringWriter trace = new StringWriter();
            e.printStackTrace(new PrintWriter(trace));
            log.error(trace.toString());
            return null;
        }
    }


    /**
     * 判断Excel的版本,获取Workbook
     */
    public static Workbook getWorkbok(File file) throws IOException {
        try {
            Workbook wb = null;
            InputStream in = new FileInputStream(file);

            if (!in.markSupported()) {
                in = new PushbackInputStream(in, 8);
            }

            if (file.getName().endsWith(EXCEL_XLS)) {
                wb = new HSSFWorkbook(in);
            } else if (file.getName().endsWith(EXCEL_XLSX)) {
                wb = new XSSFWorkbook(in);
            } else {
                log.error("你的excel版本目前poi解析不了");
            }
            return wb;
        } catch (Exception e) {
            StringWriter trace = new StringWriter();
            e.printStackTrace(new PrintWriter(trace));
            log.error(trace.toString());
            return null;
        }
    }

    public static Workbook writeExcel(List<Map> dataList, int columnCount, String xlsxPath, int type) {
        OutputStream out = null;
        try {
            // 获取总列数
            int columnNumCount = columnCount;
            // 读取Excel文档
            File xlsxFile = new File(xlsxPath);
            HSSFWorkbook workBook = new HSSFWorkbook();
            // sheet 对应一个工作页
            HSSFSheet sheet = workBook.createSheet();
            HSSFCellStyle cellstyle = workBook.createCellStyle();
            HSSFRow hssfRow = sheet.createRow((short) 0);
            sheet.createFreezePane(0, 1);
            if (type == 1) {
                for (int i = 0; i < ConstantExcel.ORDEREXCELCOLENNAME.length; ++i) {
                    HSSFCell cell = hssfRow.createCell((short)i);
                    cell.setCellValue(ConstantExcel.ORDEREXCELCOLENNAME[i]);
                    cell.setCellStyle(cellstyle);
                }
            } else if (type == 2) {
                for (int i = 0; i < ConstantExcel.SUMMARYEXCELCOLENNAME.length; ++i) {
                    HSSFCell cell = hssfRow.createCell((short)i);
                    cell.setCellValue(ConstantExcel.SUMMARYEXCELCOLENNAME[i]);
                    cell.setCellStyle(cellstyle);
                }
            } else if (type == 3) {
                for (int i = 0; i < ConstantExcel.RECHARGEEXCELCOLENNAME.length; ++i) {
                    HSSFCell cell = hssfRow.createCell((short)i);
                    cell.setCellValue(ConstantExcel.RECHARGEEXCELCOLENNAME[i]);
                    cell.setCellStyle(cellstyle);
                }
            } else if (type == 4) {
                for (int i = 0; i < ConstantExcel.CONSUMEEXCELCOLENNAME.length; ++i) {
                    HSSFCell cell = hssfRow.createCell((short)i);
                    cell.setCellValue(ConstantExcel.CONSUMEEXCELCOLENNAME[i]);
                    cell.setCellStyle(cellstyle);
                }
            } else if (type == 5) {
                for (int i = 0; i < ConstantExcel.INVOICEEXCELCOLENNAME.length; ++i) {
                    HSSFCell cell = hssfRow.createCell((short)i);
                    cell.setCellValue(ConstantExcel.INVOICEEXCELCOLENNAME[i]);
                    cell.setCellStyle(cellstyle);
                }
            }else if (type == 6) {
                for (int i = 0; i < ConstantExcel.INVOICEEXCELCOLENNAME2.length; ++i) {
                    HSSFCell cell = hssfRow.createCell((short)i);
                    cell.setCellValue(ConstantExcel.INVOICEEXCELCOLENNAME2[i]);
                    cell.setCellStyle(cellstyle);
                }
            }
            // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out = new FileOutputStream(xlsxPath);
            workBook.write(out);
            /**
             * 往Excel中写新数据
             */
            if (type == 1) {
                for (int j = 0; j < dataList.size(); j++) {
                    // 创建一行：从第二行开始，跳过属性列
                    Row row = sheet.createRow(j + 1);
                    // 得到要插入的每一条记录
                    Map<String, String> dataMap = dataList.get(j);

                    for (int i = 0; i < ConstantExcel.ORDEREXCELCOLENNAME.length; ++i) {
                        String value = dataMap.get(ConstantExcel.ORDEREXCELCOLENNAME[i]);
                        Cell cell = row.createCell(i);
                        cell.setCellValue(value);
                    }
                }
            } else if (type == 2) {
                for (int j = 0; j < dataList.size(); j++) {
                    // 创建一行：从第二行开始，跳过属性列
                    Row row = sheet.createRow(j + 1);
                    // 得到要插入的每一条记录
                    Map<String, String> dataMap = dataList.get(j);

                    for (int i = 0; i < ConstantExcel.SUMMARYEXCELCOLENNAME.length; ++i) {
                        String value = dataMap.get(ConstantExcel.SUMMARYEXCELCOLENNAME[i]);
                        Cell cell = row.createCell(i);
                        cell.setCellValue(value);
                    }
                }
            } else if (type == 3) {
                for (int j = 0; j < dataList.size(); j++) {
                    // 创建一行：从第二行开始，跳过属性列
                    Row row = sheet.createRow(j + 1);
                    // 得到要插入的每一条记录
                    Map<String, String> dataMap = dataList.get(j);

                    for (int i = 0; i < ConstantExcel.RECHARGEEXCELCOLENNAME.length; ++i) {
                        String value = dataMap.get(ConstantExcel.RECHARGEEXCELCOLENNAME[i]);
                        Cell cell = row.createCell(i);
                        cell.setCellValue(value);
                    }
                }
            } else if (type == 4) {
                for (int j = 0; j < dataList.size(); j++) {
                    // 创建一行：从第二行开始，跳过属性列
                    Row row = sheet.createRow(j + 1);
                    // 得到要插入的每一条记录
                    Map<String, String> dataMap = dataList.get(j);

                    for (int i = 0; i < ConstantExcel.CONSUMEEXCELCOLENNAME.length; ++i) {
                        String value = dataMap.get(ConstantExcel.CONSUMEEXCELCOLENNAME[i]);
                        Cell cell = row.createCell(i);
                        cell.setCellValue(value);
                    }
                }
            } else if (type == 5) {
                for (int j = 0; j < dataList.size(); j++) {
                    // 创建一行：从第二行开始，跳过属性列
                    Row row = sheet.createRow(j + 1);
                    // 得到要插入的每一条记录
                    Map<String, String> dataMap = dataList.get(j);

                    for (int i = 0; i < ConstantExcel.INVOICEEXCELCOLENNAME.length; ++i) {
                        String value = dataMap.get(ConstantExcel.INVOICEEXCELCOLENNAME[i]);
                        Cell cell = row.createCell(i);
                        cell.setCellValue(value);
                    }
                }
            }else if (type == 6) {
                for (int j = 0; j < dataList.size(); j++) {
                    // 创建一行：从第二行开始，跳过属性列
                    Row row = sheet.createRow(j + 1);
                    // 得到要插入的每一条记录
                    Map<String, String> dataMap = dataList.get(j);

                    for (int i = 0; i < ConstantExcel.INVOICEEXCELCOLENNAME2.length; ++i) {
                        String value = dataMap.get(ConstantExcel.INVOICEEXCELCOLENNAME2[i]);
                        Cell cell = row.createCell(i);
                        cell.setCellValue(value);
                    }
                }
            } else {
                log.error("writeExcel出错：" + type);
            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out = new FileOutputStream(xlsxPath);
            workBook.write(out);
            log.info("数据导出成功");
            return workBook;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void output(HttpServletResponse resp, File file) {
        OutputStream os = null;
        BufferedInputStream bis = null;
        byte[] buff = new byte[1024];
        try {
            os = resp.getOutputStream();
            bis = new BufferedInputStream(new FileInputStream(file));
            int i = 0;
            while ((i = bis.read(buff)) != -1) {
                os.write(buff, 0, i);
                os.flush();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
