package com.utils;

import com.jfinal.kit.PathKit;
import com.jfinal.plugin.activerecord.Record;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author     : WH
 * @group      : tgb8
 * @Date       : 2014-1-2 下午9:13:21
 * @Comments   : 导入导出Excel工具类
 * @Version    : 1.0.0
 */

public class ExcelExportUtil_template {

    public static void exportExcel(String templateFileName,HttpServletResponse response,String header, List list, String fields[], int rowIndex){
        //File newFile = createNewFile();
        File templateFile = new File(PathKit.getWebRootPath()+"/template/"+templateFileName);
        File newFile = new File(PathKit.getWebRootPath()+"/upload/"+System.currentTimeMillis()+".xls");

        //新文件写入数据，并下载*****************************************************
        InputStream is = null;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;
        try {
            FileUtils.copyFile(templateFile,newFile);
            is = new FileInputStream(newFile);
            workbook = new HSSFWorkbook(is);
            //获取第一个sheet
            sheet = workbook.getSheetAt(0);
            //写数据
            FileOutputStream fos = new FileOutputStream(newFile);
            HSSFRow row = sheet.getRow(0);
            HSSFCell cell = row.getCell(0);
            cell.setCellValue(header);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
            cellStyle.setWrapText(true);
            //放上数据
            for(int a = 0;a<list.size();a++){
                Object m = list.get(a);

                HSSFRow r = sheet.createRow(rowIndex);
                for (int i = 0; i < fields.length; i++) {
                    String key = fields[i];
                    HSSFCell c = r.createCell(i);
                    c.setCellStyle(cellStyle);
                    if(m instanceof Record) {
                        if (((Record)m).get(key) == null) {
                            c.setCellValue("");
                        } else {
                            c.setCellValue(((Record)m).get(key) + "");
                        }
                    }else if(m instanceof Map) {
                        if (((Map)m).get(key) == null) {
                            c.setCellValue("");
                        } else {
                            c.setCellValue(((Map)m).get(key) + "");
                        }
                    }
                }
                rowIndex++;
            }

            workbook.write(fos);
            fos.flush();
            fos.close();
            //下载
            InputStream fis = new BufferedInputStream(new FileInputStream(newFile));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            response.reset();
            response.setContentType("text/html;charset=UTF-8");
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/x-msdownload");
            String newName = URLEncoder.encode(templateFileName, "UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=\""+ newName + "\"");
            response.addHeader("Content-Length", "" + newFile.length());
            toClient.write(buffer);
            toClient.flush();
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (null != is) {
                    is.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        //删除创建的新文件
        //this.deleteFile(newFile);
    }
    public static void exportExcel_2007(String templateFileName,HttpServletResponse response,String header, List list, String fields[], int rowIndex){
        //File newFile = createNewFile();
        File templateFile = new File(PathKit.getWebRootPath()+"/template/"+templateFileName);
        File newFile = new File(PathKit.getWebRootPath()+"/upload/"+System.currentTimeMillis()+".xlsx");

        //新文件写入数据，并下载*****************************************************
        InputStream is = null;
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        try {
            FileUtils.copyFile(templateFile,newFile);
            is = new FileInputStream(newFile);
            workbook = new XSSFWorkbook(is);
            //获取第一个sheet
            sheet = workbook.getSheetAt(0);
            //写数据
            FileOutputStream fos = new FileOutputStream(newFile);
            XSSFRow row = sheet.getRow(0);
            XSSFCell cell = row.getCell(0);
            cell.setCellValue(header);
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
            cellStyle.setWrapText(true);
            //放上数据
            for(int a = 0;a<list.size();a++){
                Object m = list.get(a);

                XSSFRow r = sheet.createRow(rowIndex);
                for (int i = 0; i < fields.length; i++) {
                    String key = fields[i];
                    XSSFCell c = r.createCell(i);
                    c.setCellStyle(cellStyle);
                    if(m instanceof Record) {
                        if (((Record)m).get(key) == null) {
                            c.setCellValue("");
                        } else {
                            c.setCellValue(((Record)m).get(key) + "");
                        }
                    }else if(m instanceof Map) {
                        if (((Map)m).get(key) == null) {
                            c.setCellValue("");
                        } else {
                            c.setCellValue(((Map)m).get(key) + "");
                        }
                    }
                }
                rowIndex++;
            }

            workbook.write(fos);
            fos.flush();
            fos.close();
            //下载
            InputStream fis = new BufferedInputStream(new FileInputStream(newFile));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            response.reset();
            response.setContentType("text/html;charset=UTF-8");
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/x-msdownload");
            String newName = URLEncoder.encode(templateFileName, "UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=\""+ newName + "\"");
            response.addHeader("Content-Length", "" + newFile.length());
            toClient.write(buffer);
            toClient.flush();
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (null != is) {
                    is.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        //删除创建的新文件
        //this.deleteFile(newFile);
    }
    /**
     * 复制文件
     *
     * @param s
     * 源文件
     * @param t
     * 复制到的新文件
     */

    public void fileChannelCopy(File s, File t) {
        try {
            InputStream in = null;
            OutputStream out = null;
            try {
                in = new BufferedInputStream(new FileInputStream(s),1024);
                out = new BufferedOutputStream(new FileOutputStream(t),1024);
                byte[] buffer = new byte[1024];
                int len;
                while ((len=in.read(buffer))!=-1) {
                    out.write(buffer,0,len);
                }
            } finally {
                if (null != in) {
                    in.close();
                }
                if (null != out) {
                    out.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取excel模板，并复制到新文件中供写入和下载
     * @return
     */
    public File createNewFile(){
        //读取模板，并赋值到新文件************************************************************
        //文件模板路径
        String path = PathKit.getWebRootPath();
        String fileName="purchaseContract.xls";
        File file=new File(path+"/"+fileName);

        //保存文件的路径
        String realPath = PathKit.getRootClassPath();
        //新的文件名
        String newFileName = "采购合同"+System.currentTimeMillis() + ".xls";
        //判断路径是否存在
        File dir = new File(realPath);
        if(!dir.exists()){
            dir.mkdirs();
        }
        //写入到新的excel
        File newFile = new File(realPath, newFileName);
        try {
            newFile.createNewFile();
            //复制模板到新文件
            fileChannelCopy(file, newFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return newFile;
    }

    /**
     * 下载成功后删除
     *
     * @param files
     */
    private void deleteFile(File... files) {
        for (File file : files) {
            if (file.exists()) {
                file.delete();
            }
        }
    }

}
