package com.yeejoin.amos.latentdanger.business.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.yeejoin.amos.latentdanger.exception.YeeException;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hslf.usermodel.HSLFSlideShow;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.nio.ByteBuffer;
import java.nio.channels.Channel;
import java.nio.channels.FileChannel;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 *
 * 类描述: 包括功能、用途、现存BUG，以及其它别人可能感兴趣的介绍。
 *
 * @see 需要参见的其它类（可选）
 * @version 1.0
 * @date 2016年7月27日
 * @author <a href="mailto:Ydm@nationsky.com">Ydm</a>
 * @since JDK 1.7
 */
public class FileHelper {

  private static final Logger log = LoggerFactory.getLogger(FileHelper.class);

  /**
   *
   * @param file
   * @return
   */
//  public static boolean isExcel2003(File file) {
//    InputStream is = null;
//    Workbook wb = null;
//    try {
//      is = new FileInputStream(file);
//      wb = XSSFWorkbookFactory.createWorkbook(is);
//      if (wb instanceof XSSFWorkbook) {
//        return false;
//      } else if (wb instanceof HSSFWorkbook) {
//        return true;
//      }
//    } catch (Exception e) {
//      return false;
//    } finally {
//      try {
//        if (null != is) {
//          is.close();
//        }
//        if (null != wb) {
//          wb.close();
//        }
//      } catch (IOException e) {
//        e.printStackTrace();
//      }
//    }
//    return true;
//  }

  /**
   *
   * @param file
   * @return
   */
  public static boolean isWord2003(File file) {
    try (InputStream is = new FileInputStream(file);
         HWPFDocument hwpfDocument = new HWPFDocument(is);) {
    } catch (Exception e) {
      return false;
    }
    return true;
  }

  public static boolean isWord2007(File file) {
    InputStream is = null;
    try {
      is = new FileInputStream(file);
      new XWPFDocument(is).close();
    } catch (Exception e) {
      return false;
    } finally {
      try {
        if (null != is) {
          is.close();
        }
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
    return true;
  }

  /**
   *
   * @param file
   * @return
   */
  public static boolean isPPT2003(File file) {
    try (InputStream is = new FileInputStream(file);
         HSLFSlideShow ppt = new HSLFSlideShow(is);) {
    } catch (Exception e) {
      return false;
    }
    return true;
  }

  /**
   *
   * @param path
   * @return
   */
  public static StringBuffer readFile(String path) {
    StringBuffer buffer = new StringBuffer();
    File file = new File(path);
    try (InputStream is = new FileInputStream(file);
         BufferedReader br = new BufferedReader(new InputStreamReader(is));) {
      if (file.exists()) {
        String content = br.readLine();
        while (null != content) {
          buffer.append(content);
          content = br.readLine();
        }
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return buffer;
  }

  /**
   * 读取文件，并按照指定的分割符保存文件
   * @param path 文件的路径
   * @param split 分割内容的标识
   * @return 按照传入的分割符，标识的字符串
   */
  public static StringBuffer readFile(String path, String split) {
    StringBuffer buffer = new StringBuffer();
    File file = new File(path);
    try (InputStream is = new FileInputStream(file);
         BufferedReader br = new BufferedReader(new InputStreamReader(is));) {
      if (file.exists()) {
        String content = br.readLine();
        while (null != content) {
          buffer.append(content).append(split);
          content = br.readLine();
        }
      }
    } catch (Exception exception) {
      exception.printStackTrace();
    }
    return buffer;
  }

  /**
   * 将传入的字符串写入到指定的路径的文件下
   * @param content 将要写入文件的内容
   * @param path 写入内容的文件路径
   */
  public static void writeFile(String content, String path) {
    File file = new File(path);
    try (OutputStream fos = new FileOutputStream(file);
         BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(fos, StandardCharsets.UTF_8)); ) {
      if (!file.getParentFile().exists()) {
        file.getParentFile().mkdirs();
      }
      bw.write(content);
    } catch (FileNotFoundException fnfe) {
      fnfe.printStackTrace();
    } catch (IOException ioe) {
      ioe.printStackTrace();
    }
  }

  /**
   * 将图片写成html文件
   * @param size 图片数量
   * @param path 保存html文件全路径
   * @param fileName 图片路径
   */
  public static void writeHtmlFile(int size, String path, String fileName) {
    StringBuffer buffer = new StringBuffer();
    buffer.append("<!DOCTYPE html><html><head>");
    buffer.append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"/>");
    buffer.append("<meta name=\"viewport\" ");
    buffer.append("content=\"width=device-width,minimum-scale=1,maximum-scale=1,user-scalable=no,minimal-ui\"/>");
    buffer.append("<meta name=\"format-detection\" content=\"telephone=no\"/>");
    buffer.append("<meta http-equiv=\"Access-Control-Allow-Origin\" content=\"*\"/>");
    buffer.append("<title>touch</title>");
    buffer.append("<meta name=\"keywords\" content=\"\"/>");
    buffer.append("<meta name=\"description\" content=\"\"/>");
    buffer.append(
        "<style type=\"text/css\">body{width:100%;height:auto;position:relative;}img{max-width:100%;height:auto;margin:0 auto;}</style>");

    buffer.append("</head>");
    buffer.append("<body>");

    for (int offset = 0; offset < size; offset++) {
      buffer.append("<img src=\"" + fileName + "/" + (offset + 1) + ".png\" />");
      buffer.append("<br />");
    }
    buffer.append("</body></html>");
    // System.out.println(buffer.toString());
    writeFile(buffer.toString(), path + ".html");
  }

  public static void writeHtmlFile(String path, String fileName) {
    StringBuffer buffer = new StringBuffer();
    buffer.append("<!DOCTYPE html><html><head>");
    buffer.append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"/>");
    buffer.append(
        "<meta name=\"viewport\" content=\"width=device-width,minimum-scale=1,maximum-scale=1,user-scalable=no,minimal-ui\"/>");
    buffer.append("<meta name=\"format-detection\" content=\"telephone=no\"/>");
    buffer.append("<meta http-equiv=\"Access-Control-Allow-Origin\" content=\"*\"/>");
    buffer.append("<title>touch</title>");
    buffer.append("<meta name=\"keywords\" content=\"\"/>");
    buffer.append("<meta name=\"description\" content=\"\"/>");
    buffer.append(
        "<style type=\"text/css\">body{width:100%;height:auto;position:relative;}img{max-width:100%;height:auto;margin:0 auto;}</style>");

    buffer.append("</head>");
    buffer.append("<body>");

    buffer.append("<img src=\"" + fileName + "/" + fileName + ".png\" />");
    buffer.append("<br />");

    buffer.append("</body></html>");
    // System.out.println(buffer.toString());
    writeFile(buffer.toString(), path + ".html");
  }

  public static void write2Html(StringBuffer content, String path) {

    StringBuffer buffer = new StringBuffer();
    buffer.append(
        "<html><head><meta http-equiv=\"Access-Control-Allow-Origin\" content=\"*\"></head><body><div align=\"left\">");
    buffer.append("<p>" + content + "</p>");
    buffer.append("</div></body></html>");
    // System.out.println(buffer.toString());
    writeFile(buffer.toString(), path + ".html");
  }

  public static void mkdirFiles(String filePath, String fileType) {
    File file = new File(filePath + "/" + fileType);
    if (!file.exists()) {
      file.mkdirs();
    }
  }

  /**
   * 删除文件空行
   *
   * @param content
   * @param outPutFile
   * @throws IOException
   */
  public static void rmrBlankLines(String inputFile, String outPutFile) throws IOException {
    File htmFile = new File(inputFile);
    // 以GB2312读取文件
    try (BufferedReader br = new BufferedReader(new FileReader(htmFile));
         BufferedWriter bw = new BufferedWriter(new FileWriter(new File(outPutFile)));) {
      String result = null;
      while (null != (result = br.readLine())) {
        if (!"".equals(result.trim())) {
          bw.write(result + "\r\n");
        }
      }
    } catch (Exception e) {
      e.printStackTrace();
    }


  }

  /**
   * @param htmFilePath
   * @throws IOException
   */
  public static void parseH2(String htmFilePath) throws IOException {
    File htmFile = new File(htmFilePath);
    Document doc = Jsoup.parse(htmFile, "UTF-8");
    doc.getElementsByAttribute("h2");
    Elements content = doc.getElementsByTag("h2");
    for (Element meta : content) {
      meta.attr("style", "text-align:center");
    }
    FileUtils.writeStringToFile(htmFile, doc.html(), "UTF-8");
  }

  /**
   * @param htmFilePath
   * @throws IOException
   */
  public static void parseCharset(String htmFilePath) throws IOException {
    File htmFile = new File(htmFilePath);
    // 以GB2312读取文件
    Document doc = Jsoup.parse(htmFile, "utf-8");
    // 获取html节点
    Elements content = doc.getElementsByAttributeValueStarting("content", "text/html;");
    Elements brs = doc.getElementsByTag("<br>");

    for (Element br : brs) {
      br.before("<br />");
      br.remove();
    }


    for (Element meta : content) {
      // 获取content节点，修改charset属性
      meta.attr("content", "text/html; charset=utf-8");
      break;
    }
    // 转换成utf-8编码的文件写入
    System.out.println(doc.html());
    FileUtils.writeStringToFile(htmFile, doc.html(), "utf-8");
  }

  /**
   * @param htmFilePath
   * @throws IOException
   */
  public static void parse(String htmFilePath) throws IOException {
    File htmFile = new File(htmFilePath);
    // 以GB2312读取文件
    Document doc = Jsoup.parse(htmFile, "utf-8");
    String xmlns = doc.getElementsByTag("html").attr("xmlns");
    if (null == xmlns || "".equals(xmlns)) {
      return;
    }
    doc.getElementsByTag("html").removeAttr("xmlns");
    Element head = doc.head();
    /*
     * Elements headChildren = head.children(); for(Element children : headChildren) { Elements
     * metas = children.getElementsByTag("meta"); for(Element meta : metas) { meta.remove(); } }
     */
    head.appendElement("meta").attr("name", "viewport").attr("content",
        "width=device-width,initial-scale=1,minimum-scale=1,maximum-scale=1,user-scalable=no");

    // 获取html节点
    Element element = doc.body();
    Elements content = head.getElementsByAttributeValueStarting("name", "meta:page-count");
    for (Element meta : content) {
      String value = meta.attr("content");
      try {
        Integer count = Integer.valueOf(value);
        Elements ps = element.getElementsByTag("p");
        Iterator<Element> iterator = ps.iterator();
        while (iterator.hasNext()) {
          Element p = iterator.next();
          String text = p.text();
          if (text.equals("- " + count + " -")) {
            for (int offset = count; offset > 0; offset--) {
              p.remove();
              p = iterator.next();
              text = p.text();
            }
          }
          if (text.equals("")) {
            p.remove();
            p = iterator.next();
          }
          p.attr("align", "center");
          p.attr("style", "font-size:1.5rem;");
          break;
        }
      } catch (Exception e) {

      }
      // 获取content节点，修改charset属性
      // meta.attr("content", "text/html; charset=utf-8");
      break;
    }


    // 转换成utf-8编码的文件写入
    FileUtils.writeStringToFile(htmFile, "<!DOCTYPE html>" + doc.html(), "utf-8");
  }

  public static void checkHtmlEndTag(String htmFilePath) throws IOException {
    File htmFile = new File(htmFilePath);
    // 以GB2312读取文件
    Document doc = Jsoup.parse(htmFile, "utf-8");
    Elements all = doc.getElementsByTag("html");
    for (Element element : all) {
      parseElements(all, element);
    }
    FileUtils.writeStringToFile(htmFile, doc.html(), "utf-8");
  }

  public static void parseElements(Elements elements, Element element) {
    int childNodeSize = elements.size();
    if (0 < childNodeSize) {
      for (int offset = 0; offset < childNodeSize; offset++) {
        parseElements(elements.get(offset).children(), elements.get(offset));
      }
    } else {
      String tagName = element.tagName();
      String content = element.toString();
      if (tagName.length() + 3 > content.length()) {
        element.text("");
      } else {
        try {
          String endTag =
              content.substring(content.length() - tagName.length() - 3, content.length());
          if (!("</" + tagName + ">").equals(endTag)) {
            element.text("");
          }
        } catch (Exception w) {}
      }
    }
  }


  public static void changeImageType(String htmFilePath) throws IOException {
    File htmFile = new File(htmFilePath);
    // 以GB2312读取文件
    Document doc = Jsoup.parse(htmFile, "utf-8");

    Elements elements = doc.getElementsByTag("img");
    String imgPath = "";
    for (Element element : elements) {
      String src = element.attr("src");
      String[] sp = src.split("\\.");
      String newSrc = htmFile.getParent() + File.separator + sp[0] + ".jpg";
      imgPath = src;
      element.attr("src", newSrc);
    }
    FileUtils.writeStringToFile(htmFile, doc.html(), "utf-8");

    String name = htmFile.getName();

    htmFilePath = htmFilePath.substring(0, htmFilePath.length() - name.length()) + imgPath;


    File file = new File(htmFilePath);

    File[] files = file.getParentFile().listFiles();
    for (File file2 : files) {
      String filePath = file2.getPath();
      String[] sp = filePath.split("\\.");
      String newSrc = sp[0] + ".jpg";
      FileHelper.copyFile(filePath, newSrc, true);
    }

  }

  public static void nioTransferCopy(File source, File target) {
    FileChannel in = null;
    FileChannel out = null;
    FileInputStream inStream = null;
    FileOutputStream outStream = null;
    try {
      inStream = new FileInputStream(source);
      outStream = new FileOutputStream(target);
      in = inStream.getChannel();
      out = outStream.getChannel();
      in.transferTo(0, in.size(), out);
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      close(inStream);
      close(in);
      close(outStream);
      close(out);
    }
  }


  private static boolean nioBufferCopy(File source, File target) {
    FileChannel in = null;
    FileChannel out = null;
    FileInputStream inStream = null;
    FileOutputStream outStream = null;
    try {
      inStream = new FileInputStream(source);
      outStream = new FileOutputStream(target);
      in = inStream.getChannel();
      out = outStream.getChannel();
      ByteBuffer buffer = ByteBuffer.allocate(4096);
      while (in.read(buffer) != -1) {
        buffer.flip();
        out.write(buffer);
        buffer.clear();
      }
    } catch (IOException e) {
      e.printStackTrace();
      return false;
    } finally {
      close(inStream);
      close(in);
      close(outStream);
      close(out);
    }
    return true;
  }


  public static void customBufferStreamCopy(File source, File target) {
    InputStream fis = null;
    OutputStream fos = null;
    try {
      fis = new FileInputStream(source);
      fos = new FileOutputStream(target);
      byte[] buf = new byte[4096];
      int i;
      while ((i = fis.read(buf)) != -1) {
        fos.write(buf, 0, i);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      close(fis);
      close(fos);
    }
  }



  /**
   * 复制单个文件
   *
   * @param srcFileName 待复制的文件名
   * @param destFileName 目标文件名
   * @param overlay 如果目标文件存在，是否覆盖
   * @return 如果复制成功返回true，否则返回false
   */
  public static boolean copyFile(String srcFileName, String destFileName, boolean overlay) {
    File srcFile = new File(srcFileName);
    // 判断源文件是否存在
    if (!srcFile.exists()) {
      log.info("input file not null");
      return false;
    } else if (!srcFile.isFile()) {
      log.info("input file is not file");
      return false;
    }

    // 判断目标文件是否存在
    File destFile = new File(destFileName);
    if (destFile.exists()) {
      // 如果目标文件存在并允许覆盖
      if (overlay) {
        // 删除已经存在的目标文件，无论目标文件是目录还是单个文件
        new File(destFileName).delete();
      }
    } else {
      // 如果目标文件所在目录不存在，则创建目录
      if (!destFile.getParentFile().exists()) {
        // 目标文件所在目录不存在
        if (!destFile.getParentFile().mkdirs()) {
          // 复制文件失败：创建目标文件所在目录失败
          return false;
        }
      }
    }

    boolean result = nioBufferCopy(srcFile, destFile);

    return result;
  }

  /**
   * 复制整个目录的内容
   *
   * @param srcDirName 待复制目录的目录名
   * @param destDirName 目标目录名
   * @param overlay 如果目标目录存在，是否覆盖
   * @return 如果复制成功返回true，否则返回false
   */
  public static boolean copyDirectory(String srcDirName, String destDirName, boolean overlay) {
    // 判断源目录是否存在
    File srcDir = new File(srcDirName);
    if (!srcDir.exists()) {
      log.info("srcDir not found");
      return false;
    } else if (!srcDir.isDirectory()) {
      log.info("srcDir not Directory");
      return false;
    }

    // 如果目标目录名不是以文件分隔符结尾，则加上文件分隔符
    if (!destDirName.endsWith(File.separator)) {
      destDirName = destDirName + File.separator;
    }
    File destDir = new File(destDirName);
    // 如果目标文件夹存在
    if (destDir.exists()) {
      // 如果允许覆盖则删除已存在的目标目录
      if (overlay) {
        new File(destDirName).delete();
      } else {
        log.info("");
        return false;
      }
    } else {
      // 创建目的目录
      System.out.println("目的目录不存在，准备创建。。。");
      if (!destDir.mkdirs()) {
        System.out.println("复制目录失败：创建目的目录失败！");
        return false;
      }
    }

    boolean flag = true;
    File[] files = srcDir.listFiles();
    for (int i = 0; i < files.length; i++) {
      // 复制文件
      if (files[i].isFile()) {
        flag = FileHelper.copyFile(files[i].getAbsolutePath(), destDirName + files[i].getName(),
            overlay);
        if (!flag) {
          break;
        }
      } else if (files[i].isDirectory()) {
        flag = FileHelper.copyDirectory(files[i].getAbsolutePath(),
            destDirName + files[i].getName(), overlay);
        if (!flag) {
          break;
        }
      }
    }
    if (!flag) {
      log.info("copy Directory fail");
      return false;
    } else {
      return true;
    }
  }

  /**
   * 关闭资源
   * @param object 需要关闭的对象
   */
  public static void close(Object object) {
    if (null == object) {
      return;
    }
    try {
      if (object instanceof InputStream) {
        ((InputStream) object).close();
      } else if (object instanceof OutputStream) {
        ((OutputStream) object).close();
      } else if (object instanceof Channel) {
        ((Channel) object).close();
      }
    } catch (Exception exce) {
      System.err.println(exce.getMessage());
    }
  }



  /**
   * 合并excel表格的sheet
   *
   * @param htmFilePath html文件路径
   * @throws IOException 打开文件异常
   */
  public static void mergeTable(String htmFilePath) throws IOException {
    File htmFile = new File(htmFilePath);
    // 以GB2312读取文件
    Document doc = Jsoup.parse(htmFile, "utf-8");
    Integer tableMaxSize = getMaxTableSize(doc);
    Elements allTable = doc.getElementsByTag("tbody");
    Element max = null;
    for (Element table : allTable) {
      Elements elements = table.children();
      if (0 >= elements.size()) {
        table.parent().remove();
        continue;
      }
      int size = elements.first().children().size();
      if (size >= tableMaxSize) {
        max = table;
        continue;
      }
      for (Element tr : elements) {
        Elements td = tr.children();

        for (int offset = tableMaxSize; offset > td.size(); offset--) {
          Element tdd = doc.createElement("td");
          tr.appendChild(tdd);
        }

        max.appendChild(tr);
      }
      table.parent().remove();
    }
    FileUtils.writeStringToFile(htmFile, doc.html(), "utf-8");
  }

  private static Integer getMaxTableSize(Document doc) {
    Elements allTable = doc.getElementsByTag("tbody");
    TreeSet<Integer> tableSize = new TreeSet<Integer>();
    for (Element table : allTable) {
      Elements elements = table.children();
      int size = 0;
      try {
        size = elements.first().children().size();
      } catch (Exception e) {
        size = -1;
      }
      if (tableSize.contains(size)) {
        size--;
      }
      tableSize.add(size);
    }
    return tableSize.last();
  }

  /**
   * 获取文件css样式
   * @param src 文件
   * @return 文件css样式
   * @throws IOException 打开文件异常
   */
  public static final StringBuffer getHtmlCss(String src) throws IOException {
    File htmFile = new File(src);
    // 以GB2312读取文件
    Document doc = Jsoup.parse(htmFile, "utf-8");
    Elements styles = doc.getElementsByTag("style");
    StringBuffer csStringBuffer = new StringBuffer();
    for (Element style : styles) {
      csStringBuffer.append(style.toString().replace("<style>", "").replace("</style>", ""));
    }
    Elements links = doc.getElementsByTag("link");
    for (Element style : links) {
      String href = style.attr("href");
      String realPath = src + File.separator + href;
      StringBuffer link = FileHelper.readFile(realPath);
      csStringBuffer.append(link);
    }

    return csStringBuffer;

  }


  public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
      ExportParams exportParams = new ExportParams(title, sheetName);
      exportParams.setCreateHeadRows(isCreateHeader);
      defaultExport(list, pojoClass, fileName, response, exportParams);

  }
  public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
      defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
  }
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
      defaultExport(list, fileName, response);
  }

  private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
      Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
      if (workbook != null);
      downLoadExcel(fileName, response, workbook);
  }

  private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
      try {
          String name = new String(fileName.getBytes("UTF-8"), "ISO8859_1");
          response.setHeader("content-Type", "application/vnd.ms-excel");
          response.setHeader("Content-Disposition",
                  "attachment;filename=" + name);

          workbook.write(response.getOutputStream());
      } catch (IOException e) {
          throw new YeeException(e.getMessage());
      }
  }
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
      Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
      if (workbook != null);
      downLoadExcel(fileName, response, workbook);
  }

  public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
      if (ObjectUtils.isEmpty(filePath)){
          return null;
      }
      ImportParams params = new ImportParams();
      params.setTitleRows(titleRows);
      params.setHeadRows(headerRows);
      List<T> list = null;
      try {
          list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
      }catch (NoSuchElementException e){
          throw new YeeException("模板不能为空");
      } catch (Exception e) {
          e.printStackTrace();
          throw new YeeException(e.getMessage());
      }
      return list;
  }
  public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
      if (file == null){
          return null;
      }
      ImportParams params = new ImportParams();
      params.setTitleRows(titleRows);
      params.setHeadRows(headerRows);
      List<T> list = null;
      try {
          list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
      }catch (NoSuchElementException e){
          throw new YeeException("excel文件不能为空");
      } catch (Exception e) {
    	  e.printStackTrace();
          throw new YeeException(e.getMessage());
      }
      return list;
  }

	public static void exportZip(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
	   	try {
	   		response.setContentType("multipart/form-data");
	   		response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
	   	} catch (UnsupportedEncodingException e) {
	   		throw new YeeException("下载文件名编码时出现错误.");
	   	}
	   	OutputStream outputStream = null;
	   	ZipOutputStream zos = null;
	   	try {
	   		outputStream = response.getOutputStream();
	   		zos = new ZipOutputStream(outputStream);
	   		downloadZip(fileName.replace(".zip", ""), zos, list);
	   	} catch (IOException e) {
	   		throw new YeeException("下载文件名编码时出现错误.");
	   	}  finally {
	   		if(zos != null) {
	   			try {
	   				zos.close();
	   			} catch (Exception e2) {
	   				throw new YeeException("下载文件名编码时出现错误.");
	   			}
	   		}
	   		if(outputStream != null) {
	   			try {
	   				outputStream.close();
	   			} catch (Exception e2) {
	   				throw new YeeException("下载文件名编码时出现错误.");
	   			}
	   		}
	   	}
	}

	public static void downloadZip(String baseDir, ZipOutputStream zos, List<Map<String, Object>> list) {
		for (Map<String, Object> map : list) {
	   		String checkId = map.get("id").toString();
	   		//文件名称（带后缀）
	   		String fileName = map.get("photoData").toString();
	   		InputStream is = null;
	   		BufferedInputStream in = null;
	   		byte[] buffer = new byte[1024];
	   		int len;
	       	//创建zip实体（一个文件对应一个ZipEntry）
       		ZipEntry entry = new ZipEntry(baseDir + fileName.substring(fileName.lastIndexOf('/'), fileName.length()));
	       	try {
	       	       //获取需要下载的文件流
              URL url = new URL(fileName);
              HttpURLConnection conn = (HttpURLConnection)url.openConnection();
              //下载
              is = conn.getInputStream();

//              File file = new File(fileName);

//	       		is = new FileInputStream(file);

	   			in = new BufferedInputStream(is);
	       		zos.putNextEntry(entry);
	       		//文件流循环写入ZipOutputStream
	           	while ((len = in.read(buffer)) != -1 ) {
	           		zos.write(buffer, 0, len);
	           		}
	   		} catch (Exception e) {
	   			e.printStackTrace();
	   		}finally {
	   			if(entry != null) {
	   				try {
	   					zos.closeEntry();
	   				} catch (Exception e2) {

	   				}
	   			}
	   			if(in != null) {
	   				try {
	   					in.close();
	   				} catch (Exception e2) {

	   				}
	   			}
	   			if(is != null) {
	   				try {
	   					is.close();
	   				}catch (Exception e) {

	   				}
	   			}


	   		}
		}
	}

	/**
     * @Title: createExcelTemplate
     * @Description: 生成Excel导入模板
     * @param @param filePath  Excel文件路径
     * @param @param handers   Excel列标题(数组)
     * @param @param downData  下拉框数据(数组)
     * @param @param downRows  下拉列的序号(数组,序号从0开始)
     * @return void
     * @throws
     */
    public static void createExcelTemplate(String fileName, String[] handers,
            List<String[]> downData, String[] downRows,  HttpServletResponse response){

        HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄

        //表头样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //字体样式
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short)12);
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(fontStyle);

        //新建sheet
        HSSFSheet sheet1 = wb.createSheet("Sheet1");
        HSSFSheet sheet2 = wb.createSheet("Sheet2");
        HSSFSheet sheet3 = wb.createSheet("Sheet3");

        //生成sheet1内容
        HSSFRow rowFirst = sheet1.createRow(0);//第一个sheet的第一行为标题
        //写标题
        for(int i=0;i<handers.length;i++){
            HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格
            sheet1.setColumnWidth(i, 4000); //设置每列的列宽
            cell.setCellStyle(style); //加样式
            cell.setCellValue(handers[i]); //往单元格里写数据
        }

        //设置下拉框数据
        String[] arr = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};
        int index = 0;
        HSSFRow row = null;
        for(int r=0;r<downRows.length;r++){
            String[] dlData = downData.get(r);//获取下拉对象
            int rownum = Integer.parseInt(downRows[r]);

            if(dlData==null){
              continue;
            }
            if(dlData.length<5){ //255以内的下拉
                //255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
                sheet1.addValidationData(setDataValidation(sheet1, dlData, 1, 30000, rownum ,rownum)); //超过255个报错
            } else { //255以上的下拉，即下拉列表元素很多的情况

                //1、设置有效性
                   //String strFormula = "Sheet2!$A$1:$A$5000" ; //Sheet2第A1到A5000作为下拉列表来源数据
                String strFormula = "Sheet2!$"+arr[index]+"$1:$"+arr[index]+"$5000"; //Sheet2第A1到A5000作为下拉列表来源数据
                   sheet2.setColumnWidth(r, 4000); //设置每列的列宽
                   //设置数据有效性加载在哪个单元格上,参数分别是：从sheet2获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列
                      sheet1.addValidationData(SetDataValidation(strFormula, 1, 30000, rownum, rownum)); //下拉列表元素很多的情况

                      //2、生成sheet2内容
                      for(int j=0;j<dlData.length;j++){
                    if(index==0){ //第1个下拉选项，直接创建行、列
                        row = sheet2.createRow(j); //创建数据行
                        sheet2.setColumnWidth(j, 4000); //设置每列的列宽
                        row.createCell(0).setCellValue(dlData[j]); //设置对应单元格的值

                    } else { //非第1个下拉选项

                        int rowCount = sheet2.getLastRowNum();
                        //System.out.println("========== LastRowNum =========" + rowCount);
                        if(j<=rowCount){ //前面创建过的行，直接获取行，创建列
                            //获取行，创建列
                            sheet2.getRow(j).createCell(index).setCellValue(dlData[j]); //设置对应单元格的值

                        } else { //未创建过的行，直接创建行、创建列
                            sheet2.setColumnWidth(j, 4000); //设置每列的列宽
                            //创建行、创建列
                            sheet2.createRow(j).createCell(index).setCellValue(dlData[j]); //设置对应单元格的值
                        }
                    }
                }
                      index++;
            }
        }

        downLoadExcel(fileName, response, wb);
    }
    /**
     *
     * @Title: SetDataValidation
     * @Description: 下拉列表元素很多的情况 (255以上的下拉)
     * @param @param strFormula
     * @param @param firstRow   起始行
     * @param @param endRow     终止行
     * @param @param firstCol   起始列
     * @param @param endCol     终止列
     * @param @return
     * @return HSSFDataValidation
     * @throws
     */
    private static HSSFDataValidation SetDataValidation(String strFormula,
            int firstRow, int endRow, int firstCol, int endCol) {

        // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
        HSSFDataValidation dataValidation = new HSSFDataValidation(regions,constraint);

        dataValidation.createErrorBox("Error", "Error");
        dataValidation.createPromptBox("", null);

        return dataValidation;
    }


    /**
     *
     * @Title: setDataValidation
     * @Description: 下拉列表元素不多的情况(255以内的下拉)
     * @param @param sheet
     * @param @param textList
     * @param @param firstRow
     * @param @param endRow
     * @param @param firstCol
     * @param @param endCol
     * @param @return
     * @return DataValidation
     * @throws
     */
    private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {

        DataValidationHelper helper = sheet.getDataValidationHelper();
        //加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        //DVConstraint constraint = new DVConstraint();
        constraint.setExplicitListValues(textList);

        //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);

        //数据有效性对象
        DataValidation data_validation = helper.createValidation(constraint, regions);
        //DataValidation data_validation = new DataValidation(regions, constraint);

        return data_validation;
    }

    /**
     * @Title: getExcel
     * @Description: 下载指定路径的Excel文件
     * @param @param url 文件路径
     * @param @param fileName  文件名
     * @param @param response
     * @return void
     * @throws
     */
    public static void getExcel(String url, String fileName, HttpServletResponse response,HttpServletRequest request){
      //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
      response.setContentType("multipart/form-data");
      //2.设置文件头：最后一个参数是设置下载文件名
      try {
        response.setHeader("Content-disposition", "attachment; filename=\""
                + encodeChineseDownloadFileName(request, fileName+".xls") +"\"");
      } catch (UnsupportedEncodingException e) {
        e.printStackTrace();
      }
      try (FileInputStream in = new FileInputStream(new File(url));
              //3.通过response获取OutputStream对象(out)
              OutputStream out = new BufferedOutputStream(response.getOutputStream());) {
            int b = 0;
            byte[] buffer = new byte[2048];
            while ((b=in.read(buffer)) != -1){
                out.write(buffer,0,b); //4.写到输出流(out)中
            }
            out.flush();
        } catch (IOException e) {
            log.error("下载Excel模板异常", e);
        }
    }

/**
     *
     * @Title: encodeChineseDownloadFileName
     * @Description: TODO(这里用一句话描述这个方法的作用)
     * @param @param request
     * @param @param pFileName
     * @param @return
     * @param @throws UnsupportedEncodingException
     * @return String
     * @throws
     */
    private static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName)
            throws UnsupportedEncodingException {

        String filename = null;
        String agent = request.getHeader("USER-AGENT");
        //System.out.println("agent==========》"+agent);

        if (null != agent) {
            if (-1 != agent.indexOf("Firefox")) {//Firefox
                filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?=";
            } else if (-1 != agent.indexOf("Chrome")) {//Chrome
                filename = new String(pFileName.getBytes(), "ISO8859-1");
            } else {//IE7+
                filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
                filename = StringUtils.replace(filename, "+", "%20");//替换空格
            }
        } else {
            filename = pFileName;
        }

        return filename;
    }

    /**
     * @Title: delFile
     * @Description: 删除文件
     * @param @param filePath  文件路径
     * @return void
     * @throws
     */
    public static void delFile(String filePath) {
        java.io.File delFile = new java.io.File(filePath);
        delFile.delete();
    }
}

