package com.xhpc.activity.utils;

import com.aliyun.oss.OSS;
import com.aliyun.oss.OSSClientBuilder;

import com.aliyun.oss.model.PutObjectResult;
import com.aliyuncs.exceptions.ClientException;
import com.xhpc.activity.config.AliOSSProperties;
import com.xhpc.activity.vo.XDTTradebillVo;
import com.xhpc.common.util.DateUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 导出工具
 *
 * @author yuyang
 * @Date 2024-09-12 17:02
 */
public class ExcelUtilYu {


    /**
     * 创建Excel表
     * @param sheetName sheet名称
     * @param title 标题
     * @param keys 标题对应 map中的key值
     * @param dataList 内容
     * @return
     */
    private static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String []keys, List<Map<String,Object>> dataList){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = wb.createFont();
        font.setFontName("DengXian");
        font.setFontHeightInPoints((short)12);
        style.setFont(font);

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<dataList.size();i++){
            row = sheet.createRow(i + 1);
            Map<String, Object> map = dataList.get(i);
            //序号
            HSSFCell cell1 = row.createCell(0);
            cell1.setCellValue(i+1);
            cell1.setCellStyle(style);
            for (int j = 0; j < keys.length; j++) {
                String key = keys[j];
                //将内容对应的列对象
                if(map.get(key)==null){
                    HSSFCell cell2 = row.createCell(j + 1);
                    cell2.setCellValue("");
                    cell2.setCellStyle(style);
                }else{
                    HSSFCell cell2 = row.createCell(j + 1);
                    cell2.setCellValue(map.get(key).toString());
                    cell2.setCellStyle(style);
                }
            }
        }
        return wb;
    }

    /**
     *  发送响应流方法
     * @param response 响应
     * @param fileName 文件名
     * @param wb 表格对象
     */
    private static void setResponseHeader(HttpServletResponse response, HttpServletRequest requset, String fileName, HSSFWorkbook wb, XSSFWorkbook sb) {
        if(wb==null && sb ==null){
            return;
        }
        try {
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/msword");
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            if (requset.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); // firefox浏览器
            } else if (requset.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0 || requset.getHeader("User-Agent").contains("Trident")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");// IE浏览器
            }else if (requset.getHeader("User-Agent").toUpperCase().indexOf("CHROME") > 0) {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");// 谷歌
            }
            response.setHeader("Content-disposition", String.format("attachment;filename=\"%s\"",fileName));
            OutputStream os = response.getOutputStream();
            if(wb!=null){
                wb.write(os);
            }else{
                sb.write(os);
            }
            os.flush();
            os.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     *   自定义导出表格
     * @param response 响应
     * @param fileName 文件名
     * @param sheetName sheet名
     * @param title 每行的标题
     * @param keys 每个map值得 key
     * @param dataList 内容
     */
    public static void importExcel(HttpServletResponse response, HttpServletRequest requset, String fileName,String sheetName, String []title,String []keys, List<Map<String,Object>> dataList) throws ClientException {
        HSSFWorkbook workbook = getHSSFWorkbook(sheetName, title, keys, dataList);
        setResponseHeader(response,requset,fileName,workbook,null);
    }

    /**
     *  通过源文件导出表格
     * @param response 响应头
     * @param fileName 文件名字
     * @param path 源文件路径
     * @param keys 每个map值的key
     * @param dataList 内容
     * @param sheetNum Excel 左下角行号
     * @param sheetName Excel 左下角名称
     */
    public static void importExcelByOrigin(HttpServletResponse response, HttpServletRequest requset,String fileName,String path,String []keys, List<Map<String,Object>> dataList,int sheetNum,String value,String sheetName,int line){

        if(value.equals("visit")){
            XSSFWorkbook workbook = createByPathXLSX(path, keys, dataList,sheetNum,sheetName,line);
            setResponseHeaderVisit(response,requset,fileName,null,workbook);
        }else if(value.equals("yu")){
            XSSFWorkbook workbook = createByPathYu(path, keys, dataList,sheetNum,sheetName,line);
            setResponseHeader(response,requset,fileName,null,workbook);
        }else if(value.equals("xls")){
            HSSFWorkbook workbook = createByPath(path, keys, dataList,sheetNum,line);
            setResponseHeader(response,requset,fileName,workbook,null);
        }else{
            XSSFWorkbook workbook = createByPathXLSX(path, keys, dataList,sheetNum,sheetName,line);
            setResponseHeader(response,requset,fileName,null,workbook);
        }
    }

    /**
     *  通过源文件创建表格(xls)
     * @param path 源文件路径
     * @param keys map 中的key值
     * @param dataList 内容列表
     * @param sheetNum Excel 左下角行号
     * @return
     */
    private static HSSFWorkbook createByPath(String path,String []keys,List<Map<String,Object>> dataList,int sheetNum,int line){
        File newFile = new File(path);
        try {
            FileInputStream fileInputStream = new FileInputStream(newFile);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
            HSSFSheet sheet = hssfWorkbook.getSheetAt(sheetNum);
            //创建内容
            for(int i=0;i<dataList.size();i++){
                HSSFRow row = sheet.createRow(i + line);
                Map<String, Object> map = dataList.get(i);
                //序号
                HSSFCell cell1 = row.createCell(0);
                cell1.setCellValue(i+1);
                for (int j = 0; j < keys.length; j++) {
                    String key = keys[j];
                    //将内容对应的列对象
                    if(map.get(key)==null){
                        HSSFCell cell2 = row.createCell(j + 1);
                        cell2.setCellValue("");
                    }else{
                        HSSFCell cell2 = row.createCell(j + 1);
                        cell2.setCellValue(map.get(key).toString());
                    }
                }
            }
            return hssfWorkbook;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     *  通过源文件创建表格(xlsx)
     * @param path 源文件路径
     * @param keys map 中的key值
     * @param dataList 内容列表
     * @param sheetNum Excel 左下角行号
     * @param sheetName Excel 左下角名称
     * @param line Excel 第几行导入数据
     * @return
     */
    private static XSSFWorkbook createByPathXLSX(String path,String []keys,List<Map<String,Object>> dataList,int sheetNum,String sheetName,int line){
        File newFile = new File(path);
        try {
            FileInputStream fileInputStream = new FileInputStream(newFile);
            XSSFWorkbook hssfWorkbook = new XSSFWorkbook(fileInputStream);
            hssfWorkbook.setSheetName(sheetNum,sheetName);
            XSSFSheet sheet = hssfWorkbook.getSheetAt(sheetNum);
            //创建内容
            for(int i=0;i<dataList.size();i++){
                XSSFRow row = sheet.createRow(i + line);
                Map<String, Object> map = dataList.get(i);
                //序号
                //XSSFCell cell1 = row.createCell(0);
                //cell1.setCellValue(i+1);
                for (int j = 0; j < keys.length; j++) {
                    String key = keys[j];
                    //将内容对应的列对象
                    if(map.get(key)==null){
                        XSSFCell cell2 = row.createCell(j );
                        cell2.setCellValue("");
                    }else{
                        XSSFCell cell2 = row.createCell(j );
                        cell2.setCellValue(map.get(key).toString());
                    }
                }
            }

            return hssfWorkbook;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }




    /**
     * MultipartFile 转 File
     * @param file
     * @throws Exception
     */
    public static File inputStreamToFile(MultipartFile file)  throws  Exception{
        InputStream ins = file.getInputStream();
        File toFile  = new File(file.getOriginalFilename());

        try {
            OutputStream os = new FileOutputStream(toFile);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return toFile;
    }

    /**
     * 过滤空行
     */
    public static boolean isRowEmpty(XSSFRow row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            XSSFCell cell = row.getCell(c);
//            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK){
//                return false;
//            }
        }
        return true;
    }


    /**
     * 下载Excel
     * @param response
     * @param requset
     * @param url         路径
     * @param name       名称
     * @throws Exception
     */
    public static void getExcelDownload(HttpServletResponse response, HttpServletRequest requset,String url,String name)throws Exception{
        //String path = System.getProperty("user.dir");
        String path =url;
        //方法一：直接下载路径下的文件模板
        try {
            //获取要下载的模板名称
            String fileName = URLEncoder.encode(name,"UTF-8");
            //设置要下载的文件的名称
            if (requset.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); // firefox浏览器
            } else if (requset.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0 || requset.getHeader("User-Agent").contains("Trident")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");// IE浏览器
            }else if (requset.getHeader("User-Agent").toUpperCase().indexOf("CHROME") > 0) {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");// 谷歌
            }
            response.setHeader("Content-Type","application/msexcel");
            //response.setHeader("Content-disposition", String.format("attachment;filename=\"%s\"",fileName));
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName+".xlsx", "UTF-8"));
            //通知客服文件的MIME类型
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            //获取文件的路径
            FileInputStream input = new FileInputStream(path);
            OutputStream out = response.getOutputStream();
            byte[] b = new byte[2048];
            int len;
            while ((len = input.read(b)) != -1) {
                out.write(b, 0, len);
            }
            //修正 Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容？如果信任此工作簿的来源，请点击"是"
            response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
            input.close();
            //return Response.ok("应用导入模板下载完成");
        } catch (Exception ex) {
            //return Response.ok("应用导入模板下载失败！");
        }
    }


    private static XSSFWorkbook createByPathYu(String path,String []keys,List<Map<String,Object>> dataList,int sheetNum,String sheetName,int line){
        File newFile = new File(path);
        try {
            FileInputStream fileInputStream = new FileInputStream(newFile);
            XSSFWorkbook hssfWorkbook = new XSSFWorkbook(fileInputStream);
            hssfWorkbook.setSheetName(sheetNum,sheetName);
            XSSFSheet sheet = hssfWorkbook.getSheetAt(sheetNum);
            XSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
            cellStyle.setWrapText(true);

            //创建内容
            int sum=0; //开始第几行
            int count =0;//结束第几行
            Long repeatId =0L;
            for(int i=0;i<dataList.size();i++){
                XSSFRow row = sheet.createRow(i + line);
                Map<String, Object> map = dataList.get(i);
                int repeat = Integer.parseInt(map.get("number").toString());
                Long id = Long.valueOf(map.get("id").toString());
                String name = map.get("name").toString();
                if(!repeatId.equals(id) || "基础合计分".equals(name.substring(0,5))){
                    repeatId = id;
                    if(i==0){
                        sum = 2;
                        count = 2+repeat-1;
                        sheet.addMergedRegion(new CellRangeAddress(sum,count,0,0));
                        sheet.addMergedRegion(new CellRangeAddress(sum,count,1,1));
                        sheet.addMergedRegion(new CellRangeAddress(sum,count,2,2));
                        sum =sum+repeat;
                    }else if("基础合计分".equals(name.substring(0,5))){
                        count =sum;
                        sheet.addMergedRegion(new CellRangeAddress(sum,count,0,2));
                        sheet.addMergedRegion(new CellRangeAddress(sum,count,3,4));
                        sum =sum+1;
                    }else {
                        count = sum+repeat-1;
                        sheet.addMergedRegion(new CellRangeAddress(sum,count,0,0));
                        sheet.addMergedRegion(new CellRangeAddress(sum,count,1,1));
                        sheet.addMergedRegion(new CellRangeAddress(sum,count,2,2));
                        sum =sum+repeat;
                    }
                }
                //序号
                //XSSFCell cell1 = row.createCell(0);
                //cell1.setCellValue(i+1);
                for (int j = 0; j < keys.length; j++) {
                    String key = keys[j];
                    //将内容对应的列对象
                    if(map.get(key)==null){
                        XSSFCell cell2 = row.createCell(j );
                        cell2.setCellValue("");
                    }else{
                        XSSFCell cell2 = row.createCell(j );
                        if(j==1 && "基础".equals(map.get(key).toString().substring(0,2))){
                            XSSFCell cell3 = row.createCell(j-1 );
                            cell3.setCellValue("基础分合计");
                            XSSFCell cell4 = row.createCell(j+2 );
                            cell4.setCellValue(map.get(key).toString().substring(5));
                            break;
                        }else{
                            cell2.setCellValue(map.get(key).toString());
                        }
                    }
                }
            }
            return hssfWorkbook;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static XSSFWorkbook importExcelByOriginYu(HttpServletResponse response, HttpServletRequest requset,String fileName,String path, List<ExcelExp> mysheets,int sheetNum,String value,String sheetName,int line) throws ClientException, IOException {
        return createByPathYus(path,mysheets,sheetNum,sheetName,line);


    }


    private static XSSFWorkbook createByPathYus(String path,List<ExcelExp> mysheets,int sheetNum,String sheetName,int line){
        File newFile = new File(path);
        try {
            FileInputStream fileInputStream = new FileInputStream(newFile);
            XSSFWorkbook hssfWorkbook = new XSSFWorkbook(fileInputStream);
            List<ExcelExp> sheets = mysheets;
            for (int k = 0; k < sheets.size(); k++) {
                ExcelExp excel = sheets.get(k);
                XSSFCellStyle style = hssfWorkbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
                // 字体样式
                XSSFFont fontStyle = hssfWorkbook.createFont();
                fontStyle.setFontName("微软雅黑");
                fontStyle.setFontHeightInPoints((short) 12);
                style.setFont(fontStyle);
                style.setWrapText(true);
                XSSFSheet sheet = hssfWorkbook.createSheet(excel.getFileName());
                sheet.setColumnWidth(0,100);
                XSSFRow tableName = sheet.createRow(0);
                XSSFCell cellName = tableName.createCell(0);
                if(k==0){
                    sheet.addMergedRegion(new CellRangeAddress(0,0,0,15));
                }else if(k==1){
                    sheet.addMergedRegion(new CellRangeAddress(0,0,0,29));
                }else{
                    sheet.addMergedRegion(new CellRangeAddress(0,0,0,33));
                }

                sheet.autoSizeColumn(1,true);
                XSSFCellStyle titleStyle = hssfWorkbook.createCellStyle();
                XSSFFont titleFont = hssfWorkbook.createFont();
                titleFont.setFontHeightInPoints((short) 16); // 字
                titleStyle.setFont(titleFont);
                titleStyle.setAlignment(HorizontalAlignment.CENTER);
                cellName.setCellStyle(titleStyle);
                // 设置单元格内容
                cellName.setCellValue(excel.getTableName());
                XSSFRow rowFirst = sheet.createRow(1);
                String[] keys = excel.getHanders();

                for (int i = 0; i < keys.length; i++) {
                    // 获取第一行的每个单元格
                    XSSFCell cell = rowFirst.createCell(i);
                    // 往单元格里写数据
                    if("PowerAmount".equals(keys[i])){
                        cell.setCellValue("电费");
                    }else if("ServerAmount".equals(keys[i])){
                        cell.setCellValue("服务费");
                    }else if("totalAmount".equals(keys[i])){
                        cell.setCellValue("总金额");
                    }else if("ChargingDegree".equals(keys[i])){
                        cell.setCellValue("电量");
                    }else if("InternetCommission".equals(keys[i])){
                        cell.setCellValue("流里方总金额抽成");
                    }else if("InternetSvcCommission".equals(keys[i])){
                        cell.setCellValue("流里方服务费抽成");
                    }else if("InternetDegreeCommissione".equals(keys[i])){
                        cell.setCellValue("流里方电量抽成");
                    }else if("totalInternetAmount".equals(keys[i])){
                        cell.setCellValue("流里方总抽成的钱");
                    }else if("ActPowerPrice".equals(keys[i])){
                        cell.setCellValue("实收电费");
                    }else if("ActServicePrice".equals(keys[i])){
                        cell.setCellValue("实收服务费");
                    }else if("totalInternetSettlementAmount".equals(keys[i])){
                        cell.setCellValue("流里方结算平台金额");
                    }else if("totalPowerAmount".equals(keys[i])){
                        cell.setCellValue("电费");
                    }else if("totalServerAmount".equals(keys[i])){
                        cell.setCellValue("服务费");
                    }else if("InternetSettlementAmount".equals(keys[i])){
                        cell.setCellValue("总金额");
                    }else if("totalChargingDegree".equals(keys[i])){
                        cell.setCellValue("电量");
                    }else if("totalPayAmount".equals(keys[i])){
                        cell.setCellValue("流里方结算平台金额");
                    }else if("orderNo".equals(keys[i])){
                        cell.setCellValue("第三方订单号");
                    }else if("stationName".equals(keys[i])){
                        cell.setCellValue("场站");
                    }else if("city".equals(keys[i])){
                        cell.setCellValue("城市名称");
                    }else if("exceptionType".equals(keys[i])){
                        cell.setCellValue("异常类型");
                    }else if("transactionType".equals(keys[i])){
                        cell.setCellValue("交易类型");
                    }else if("paymentType".equals(keys[i])){
                        cell.setCellValue("付款类型");
                    }else if("settlementType".equals(keys[i])){
                        cell.setCellValue("结算状态");
                    }else if("settlementTime".equals(keys[i])){
                        cell.setCellValue("结算账单日");
                    }else if("orderAmount".equals(keys[i])){
                        cell.setCellValue("结算订单金额");
                    }else if("orderUserAmount".equals(keys[i])){
                        cell.setCellValue("结算用户实付金额");
                    }else if("offlineAmount".equals(keys[i])){
                        cell.setCellValue("线下支付金额");
                    }else if("userAmount".equals(keys[i])){
                        cell.setCellValue("用户待付金额");
                    }else if("badDebt".equals(keys[i])){
                        cell.setCellValue("充电坏账承担方");
                    }else if("placeholderBadDebt".equals(keys[i])){
                        cell.setCellValue("占位坏账承担方");
                    }else if("platformBadDebt".equals(keys[i])){
                        cell.setCellValue("平台承担坏账金额");
                    }else if("businessMoney".equals(keys[i])){
                        cell.setCellValue("商家支出");
                    }else if("platformMoney".equals(keys[i])){
                        cell.setCellValue("平台承担费用");
                    }else if("platformMoneyDifference".equals(keys[i])){
                        cell.setCellValue("平台费用总税差");
                    }else if("platformIncomeMoney".equals(keys[i])){
                        cell.setCellValue("平台收入金额");
                    }else if("platformProfitSharingMoney".equals(keys[i])){
                        cell.setCellValue("平台分润金额");
                    }else if("platformServerMoney".equals(keys[i])){
                        cell.setCellValue("平台服务费金额");
                    }else if("payAmount".equals(keys[i])){
                        cell.setCellValue("商家结算金额");
                    }else if("chargingDegree".equals(keys[i])){
                        cell.setCellValue("充电量");
                    }else if("powerAmount".equals(keys[i])){
                        cell.setCellValue("充电费");
                    }else if("serverAmount".equals(keys[i])){
                        cell.setCellValue("服务费");
                    }else if("surchargeAmount".equals(keys[i])){
                        cell.setCellValue("附加费");
                    }else if("settlementRules".equals(keys[i])){
                        cell.setCellValue("结算规则");
                    }else if("stationOrder".equals(keys[i])){
                        cell.setCellValue("占位订单号");
                    }else if("notes".equals(keys[i])){
                        cell.setCellValue("备注");
                    }else if("orderCreateTime".equals(keys[i])){
                        cell.setCellValue("订单生成时间");
                    }else if("orderType".equals(keys[i])){
                        cell.setCellValue("订单类型");
                    }else if("serialNumber".equals(keys[i])){
                        cell.setCellValue("订单号");
                    }else if("internetSerialNumber".equals(keys[i])){
                        cell.setCellValue("流量方订单号");
                    }else if("chargingStationName".equals(keys[i])){
                        cell.setCellValue("电站名称");
                    }else if("pileSerialNumber".equals(keys[i])){
                        cell.setCellValue("桩编号");
                    }else if("terminalName".equals(keys[i])){
                        cell.setCellValue("终端名称");
                    }else if("power".equals(keys[i])){
                        cell.setCellValue("功率");
                    }else if("startSoc".equals(keys[i])){
                        cell.setCellValue("开始soc");
                    }else if("endSoc".equals(keys[i])){
                        cell.setCellValue("结束soc");
                    }else if("powerPriceTotal".equals(keys[i])){
                        cell.setCellValue("总电费");
                    }else if("servicePriceTotal".equals(keys[i])){
                        cell.setCellValue("总服务费");
                    }else if("totalPrice".equals(keys[i])){
                        cell.setCellValue("订单总价");
                    }else if("activityPowerPriceTotal".equals(keys[i])){
                        cell.setCellValue("活动总电费");
                    }else if("activityServicePriceTotal".equals(keys[i])){
                        cell.setCellValue("活动总服务费");
                    }else if("activityTotalPrice".equals(keys[i])){
                        cell.setCellValue("活动总金额");
                    }else if("promotionDiscount".equals(keys[i])){
                        cell.setCellValue("电站活动抵扣总金额");
                    }else if("actPrice".equals(keys[i])){
                        cell.setCellValue("实际价格");
                    }else if("internetCommission".equals(keys[i])){
                        cell.setCellValue("流量方总金额抽成");
                    }else if("internetSvcCommission".equals(keys[i])){
                        cell.setCellValue("流量方服务费抽成");
                    }else if("internetDegreeCommission".equals(keys[i])){
                        cell.setCellValue("流量方电量抽成");
                    }else if("platformCommission".equals(keys[i])){
                        cell.setCellValue("平台总金额抽成");
                    }else if("platformSvcCommisssion".equals(keys[i])){
                        cell.setCellValue("平台服务费抽成");
                    }else if("operationCommission".equals(keys[i])){
                        cell.setCellValue("运维总抽成");
                    }else if("operationSvcCommission".equals(keys[i])){
                        cell.setCellValue("运维服务费抽成");
                    }else if("actPowerPrice".equals(keys[i])){
                        cell.setCellValue("实收电费-运营商电费");
                    }else if("actServicePrice".equals(keys[i])){
                        cell.setCellValue("实收电费-运营商服务费");
                    }else if("actSumPrice".equals(keys[i])){
                        cell.setCellValue("实收总费用-运营商总费用");
                    }else if("totalPrice".equals(keys[i])){
                        cell.setCellValue("订单总价");
                    }else if("actPrice".equals(keys[i])){
                        cell.setCellValue("实际价格-用户支付的钱");
                    }else if("chargingTime".equals(keys[i])){
                        cell.setCellValue("创建时间");
                    }else if("chargingDegree".equals(keys[i])){
                        cell.setCellValue("电量");
                    }else if("startTime".equals(keys[i])){
                        cell.setCellValue("订单开始时间");
                    }else if("endTime".equals(keys[i])){
                        cell.setCellValue("订单结束时间");
                    }else if("chargingModeName".equals(keys[i])){
                        cell.setCellValue("订单来源");
                    }
                    cell.setCellStyle(style); // 加样式
                    sheet.setColumnWidth(i, 7800); // 设置每列的列宽
                }

                if(k==1){
                    List<XDTTradebillVo> xdtTradebillVoList = excel.getXdtTradebillVoList();
                    for (int i = 0; i < xdtTradebillVoList.size(); i++) {
                        XSSFRow row = sheet.createRow(i + line);
                        XDTTradebillVo xdtTradebillVo = xdtTradebillVoList.get(i);
                        for (int j = 0; j < keys.length; j++) {
                            String key = keys[j];
                            if(j==0){
                                if(xdtTradebillVo.getOrderNo()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getOrderNo());
                                }
                            }else if(j==1){
                                if(xdtTradebillVo.getStationName()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getStationName());
                                }
                            }else if(j==2){
                                if(xdtTradebillVo.getCity()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getCity());
                                }
                            }else if(j==3){
                                if(xdtTradebillVo.getExceptionType()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getExceptionType());
                                }
                            }else if(j==4){
                                if(xdtTradebillVo.getTransactionType()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getTransactionType());
                                }
                            }else if(j==5){
                                if(xdtTradebillVo.getPaymentType()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getPaymentType());
                                }
                            }else if(j==6){
                                if(xdtTradebillVo.getSettlementType()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getSettlementType());
                                }
                            }else if(j==7){
                                if(xdtTradebillVo.getSettlementTime()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getSettlementTime());
                                }
                            }else if(j==8){
                                if(xdtTradebillVo.getOrderAmount()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getOrderAmount().toString());
                                }
                            }else if(j==9){
                                if(xdtTradebillVo.getOrderUserAmount()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getOrderUserAmount().toString());
                                }
                            }else if(j==10){
                                if(xdtTradebillVo.getOfflineAmount()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getOfflineAmount().toString());
                                }
                            }else if(j==11){
                                if(xdtTradebillVo.getUserAmount()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getUserAmount().toString());
                                }
                            }else if(j==12){
                                if(xdtTradebillVo.getBadDebt()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getBadDebt());
                                }
                            }else if(j==13){
                                if(xdtTradebillVo.getPlaceholderBadDebt()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getPlaceholderBadDebt());
                                }
                            }else if(j==14){
                                if(xdtTradebillVo.getPlatformBadDebt()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getPlatformBadDebt().toString());
                                }
                            }else if(j==15){
                                if(xdtTradebillVo.getBusinessMoney()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getBusinessMoney().toString());
                                }
                            }else if(j==16){
                                if(xdtTradebillVo.getPlatformMoney()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getPlatformMoney().toString());
                                }
                            }else if(j==17){
                                if(xdtTradebillVo.getPlatformMoneyDifference()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getPlatformMoneyDifference().toString());
                                }
                            }else if(j==18){
                                if(xdtTradebillVo.getPlatformIncomeMoney()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getPlatformIncomeMoney().toString());
                                }
                            }else if(j==19){
                                if(xdtTradebillVo.getPlatformProfitSharingMoney()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getPlatformProfitSharingMoney().toString());
                                }
                            }else if(j==20){
                                if(xdtTradebillVo.getPlatformServerMoney()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getPlatformServerMoney().toString());
                                }
                            }else if(j==21){
                                if(xdtTradebillVo.getPayAmount()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getPayAmount().toString());
                                }
                            }else if(j==22){
                                if(xdtTradebillVo.getChargingDegree()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getChargingDegree().toString());
                                }
                            }else if(j==23){
                                if(xdtTradebillVo.getPowerAmount()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getPowerAmount().toString());
                                }
                            }else if(j==24){
                                if(xdtTradebillVo.getServerAmount()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getServerAmount().toString());
                                }
                            }else if(j==25){
                                if(xdtTradebillVo.getSurchargeAmount()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getSurchargeAmount().toString());
                                }
                            }else if(j==25){
                                if(xdtTradebillVo.getSettlementRules()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getSettlementRules());
                                }
                            }else if(j==26){
                                if(xdtTradebillVo.getStationOrder()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getStationOrder());
                                }
                            }else if(j==27){
                                if(xdtTradebillVo.getNotes()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getNotes());
                                }
                            }else if(j==28){
                                if(xdtTradebillVo.getOrderCreateTime()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getOrderCreateTime());
                                }
                            }else if(j==29){
                                if(xdtTradebillVo.getOrderType()==null){
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue("");
                                }else{
                                    XSSFCell cell2 = row.createCell(j );
                                    cell2.setCellValue(xdtTradebillVo.getOrderType());
                                }
                            }
                        }
                    }
                }else{
                    List<Map<String, Object>> dataList = excel.getDataset();
                    for(int i=0;i<dataList.size();i++){
                        XSSFRow row = sheet.createRow(i + line);
                        Map<String, Object> map = dataList.get(i);
                        //序号
                        //XSSFCell cell1 = row.createCell(0);
                        //cell1.setCellValue(i+1);
                        for (int j = 0; j < keys.length; j++) {
                            String key = keys[j];
                            //将内容对应的列对象
                            if(map.get(key)==null){
                                XSSFCell cell2 = row.createCell(j );
                                cell2.setCellValue("");
                            }else{
                                XSSFCell cell2 = row.createCell(j );
                                cell2.setCellValue(map.get(key).toString());
                            }
                        }
                    }
                }


                setAutoColumnWidth(hssfWorkbook.getSheet(excel.getFileName()),3);
            }
            return hssfWorkbook;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


    private static void setAutoColumnWidth(XSSFSheet sheet,int maxColumnNum) {
        try{
            //获取当前列的宽度，然后对比本列的长度，取最大值
            for (int columnNum = 0; columnNum <= maxColumnNum; columnNum++)
            {
                int columnWidth = sheet.getColumnWidth(columnNum) / 256;
                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++)
                {
                    Row currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null)
                    {
                        currentRow = sheet.createRow(rowNum);
                    }
                    else
                    {
                        currentRow = sheet.getRow(rowNum);
                    }

                    if(currentRow.getCell(columnNum) != null)
                    {
                        Cell currentCell = currentRow.getCell(columnNum);
                        int length = currentCell.toString().getBytes("GBK").length;
                        if (columnWidth < length + 1)
                        {
                            columnWidth = length + 1;
                        }
                    }
                }
                sheet.setColumnWidth(columnNum, columnWidth * 300);
            }
        }catch (UnsupportedEncodingException e){
            e.printStackTrace();
        }
    }

    public static void importExcelByOriginVisit(HttpServletResponse response, HttpServletRequest requset,String fileName,String path, List<ExcelExp> mysheets,int sheetNum,String value,String sheetName,int line){
        XSSFWorkbook workbook = createByPathVisit(path,mysheets,sheetNum,sheetName,line);
        setResponseHeaderVisit(response,requset,fileName,null,workbook);
    }

    private static XSSFWorkbook createByPathVisit(String path,List<ExcelExp> mysheets,int sheetNum,String sheetName,int line){
        File newFile = new File(path);
        try {
            FileInputStream fileInputStream = new FileInputStream(newFile);
            XSSFWorkbook hssfWorkbook = new XSSFWorkbook(fileInputStream);
            List<ExcelExp> sheets = mysheets;
            for (int k = 0; k < sheets.size(); k++) {
                ExcelExp excel = sheets.get(k);
                XSSFCellStyle style = hssfWorkbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
                // 字体样式
                XSSFFont fontStyle = hssfWorkbook.createFont();
                fontStyle.setFontName("微软雅黑");
                fontStyle.setFontHeightInPoints((short) 12);

                style.setFont(fontStyle);
                style.setWrapText(true);

                XSSFSheet sheet = hssfWorkbook.createSheet(excel.getFileName());
                sheet.setColumnWidth(0,100);
                XSSFRow tableName = sheet.createRow(0);
                XSSFCell cellName = tableName.createCell(0);
                sheet.addMergedRegion(new CellRangeAddress(0,0,0,18));
                sheet.autoSizeColumn(1,true);
                XSSFCellStyle titleStyle = hssfWorkbook.createCellStyle();
                XSSFFont titleFont = hssfWorkbook.createFont();
                titleFont.setFontHeightInPoints((short) 16); // 字
                titleStyle.setFont(titleFont);
                titleStyle.setAlignment(HorizontalAlignment.CENTER);
                cellName.setCellStyle(titleStyle);
                // 设置单元格内容
                cellName.setCellValue(excel.getTableName());
                XSSFRow rowFirst = sheet.createRow(1);

                String[] keys = excel.getHanders();
                for (int i = 0; i < keys.length; i++) {
                    // 获取第一行的每个单元格
                    XSSFCell cell = rowFirst.createCell(i);
                    // 往单元格里写数据
                    if("visitData".equals(keys[i])){
                        cell.setCellValue("来访日期");
                    }else if("visitName".equals(keys[i])){
                        cell.setCellValue("来访问人姓名");
                    }else if("visitTypeName".equals(keys[i])){
                        cell.setCellValue("来访类别");
                    }else if("visitPeople".equals(keys[i])){
                        cell.setCellValue("来访人数");
                    }else if("visitSex".equals(keys[i])){
                        cell.setCellValue("性别");
                    }else if("visitIdNo".equals(keys[i])){
                        cell.setCellValue("身份证号码");
                    }else if("visitCode".equals(keys[i])){
                        cell.setCellValue("来访人地址");
                    }else if("visitAddress".equals(keys[i])){
                        cell.setCellValue("详细地址");
                    }else if("visitPhone".equals(keys[i])){
                        cell.setCellValue("联系电话");
                    }else if("visitInterviewName".equals(keys[i])){
                        cell.setCellValue("接访人姓名");
                    }else if("visitProblem".equals(keys[i])){
                        cell.setCellValue("来访反映的主要问题及要求");
                    }else if("appealOne".equals(keys[i])){
                        cell.setCellValue("是否涉诉处理结果1");
                    }else if("appealTwo".equals(keys[i])){
                        cell.setCellValue("是否涉诉处理结果2");
                    }else if("appealThree".equals(keys[i])){
                        cell.setCellValue("是否涉诉处理结果3");
                    }else if("visitHelp".equals(keys[i])){
                        cell.setCellValue("是否要求法律帮助/援助");
                    }else if("visitOpinion".equals(keys[i])){
                        cell.setCellValue("处理意见");
                    }else if("visitRemarks".equals(keys[i])){
                        cell.setCellValue("备注");
                    }else if("errorMassage".equals(keys[i])){
                        cell.setCellValue("错误信息");
                    }
                    cell.setCellStyle(style); // 加样式
                    sheet.setColumnWidth(i, 4000); // 设置每列的列宽
                }
                List<Map<String, Object>> dataList = excel.getDataset();
                //创建内容
                for(int i=0;i<dataList.size();i++){
                    XSSFRow row = sheet.createRow(i + 1);
                    Map<String, Object> map = dataList.get(i);
                    //序号
                    //XSSFCell cell1 = row.createCell(0);
                    //cell1.setCellValue(i+1);
                    for (int j = 0; j < keys.length; j++) {
                        String key = keys[j];
                        //将内容对应的列对象
                        if(map.get(key)==null){
                            XSSFCell cell2 = row.createCell(j );
                            cell2.setCellValue("");
                        }else{
                            XSSFCell cell2 = row.createCell(j );
                            cell2.setCellValue(map.get(key).toString());
                        }
                    }
                }
            }
            return hssfWorkbook;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     *  发送响应流方法
     * @param response 响应
     * @param fileName 文件名
     * @param wb 表格对象
     */
    private static void setResponseHeaderVisit(HttpServletResponse response, HttpServletRequest requset, String fileName, HSSFWorkbook wb, XSSFWorkbook sb) {
        if(wb==null && sb ==null){
            return;
        }
        try {
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/msword");
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            if (requset.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); // firefox浏览器
            } else if (requset.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0 || requset.getHeader("User-Agent").contains("Trident")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");// IE浏览器
            }else if (requset.getHeader("User-Agent").toUpperCase().indexOf("CHROME") > 0) {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");// 谷歌
            }
            response.setHeader("Content-disposition", String.format("attachment;filename=\"%s\"",fileName));
            OutputStream os = response.getOutputStream();
            if(wb!=null){
                wb.write(os);
            }else{
                //sb.write(os);
            }
            os.flush();
            os.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}
