package cn.ccb.odsbsx.common.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
/**
 * Excel文件转换成txt文件
 *
 *
 */
public class XlsToTxt {
    private static XlsToTxt instance = null;
    private static String EXCEL_LINE_DELIMITER = ",";
    private static String EXCEL_LINE = "\r\n";
       
    private XlsToTxt(){}
       
    /**
     * 生成实例对象
     * @return
     */
    public static XlsToTxt getInstance(){
        if (instance == null) {
            synchronized (UnZipApp.class) {
                if (instance == null)
                    instance = new XlsToTxt();
            }
        }
        return instance;
    }
    /**
     * @param args
     */
    public static void main(String[] args) {
        //window
        //String from = "e:\\fq\\test\\from\\";
        //String to = "e:\\fq\\test\\to\\";
        //linux
        //String from = "/home/ap/ods/tmp/excel/from/";
        //String to = "/home/ap/ods/tmp/excel/to/";
        //XlsToTxt xt = XlsToTxt.getInstance();
        //xt.excelToTxt(from, to);
    }
    /**
     * excel转换成txt
     *
     * @param from 源目录
     * @param to 目标目录
     */
    public synchronized void excelToTxt(String from, String to, String newFileName, String instNo, String uploadDate, String importAcctDate) {
        File[] files = getFiles(from);
        File newFile = null;
        int count = 0;
        InputStream is = null;
        FileOutputStream out = null;
        for (int i = 0; i < files.length; i++) {
            String filePath = files[i].getPath();
            String fileName = files[i].getName();
            String fileType = fileName.substring(fileName.indexOf('.') + 1);
            Workbook book = null;
            Sheet[] sheets = null;
            try {
                if (fileType.equals("xls")) {
                    if(count==0){
                        newFile = new File(to + newFileName);
                        out = new FileOutputStream(newFile);
                        headStr(out);
                    }
                    count++;
                    is = new FileInputStream(filePath);
                    book = Workbook.getWorkbook(is);
                    sheets = book.getSheets();
                    for (int j = 0; j < sheets.length; j++) {
                        readSheet(book.getSheet(j), out, instNo, uploadDate, importAcctDate, fileName);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (book != null)
                        book = null;
                    if (is != null)
                        is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        // 释放out资源
        try {
            if (out != null)
                out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
       
    private void headStr(FileOutputStream out){
        StringBuffer shead = new StringBuffer();
        shead.append("CUST_ACCT_NO,INST_NO,INST_NAME,CUST_NO,CUST_NAME,AREA_NO,ACCT_NAME,ACQU,PM_NO,");
        shead.append("CUST_OPEN_DT,FT_CLOS_DT,ACCT_DT,SLEEP_STS,UPLOAD_INST_NO,UPLOAD_FILE,UPLOAD_DT,ETL_DT" + EXCEL_LINE);
        try {
            out.write(shead.toString().getBytes());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 读Sheet写到txt文件
     *
     * @param sheet
     * @param out
     */
    private void readSheet(Sheet sheet, FileOutputStream out, String instNo, String uploadDate, String importAcctDate, String fileName) {
        int rows = sheet.getRows();
        int cols = sheet.getColumns();
        Cell[] row = new Cell[cols];
        StringBuffer sBuffer = null;
        if (rows > 0) {
            for (int iRow = 1; iRow < rows; iRow++) {
                // /读取一行数据
                row = sheet.getRow(iRow);
                // 撤销的的数据不再进行装载
                if (row[10].getContents().equals("撤销")) {
                    continue;
                }
                //一行数据
                sBuffer = new StringBuffer();
                sBuffer.append(row[0].getContents() != null ? row[0].getContents().trim() : "");//CUST_ACCT_NO
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append(row[1].getContents() != null ? row[1].getContents().trim() : "");//INST_NO
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append(row[2].getContents() != null ? row[2].getContents().trim() : "");//INST_NAME
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append("" + EXCEL_LINE_DELIMITER);//CUST_NO
                sBuffer.append(row[3].getContents() != null ? row[3].getContents().trim() : "");//CUST_NAME
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append(row[4].getContents() != null ? row[4].getContents().trim() : "");//AREA_NO
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append(row[5].getContents() != null ? row[5].getContents().trim() : "");//ACCT_NAME
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append(row[6].getContents() != null ? row[6].getContents().trim() : "");//ACQU
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append(row[7].getContents() != null ? row[7].getContents().trim() : "");//PM_NO
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append(row[8].getContents() != null ? row[8].getContents().trim() : "");//CUST_OPEN_DT
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append("" + EXCEL_LINE_DELIMITER);//FT_CLOS_DT
                sBuffer.append(row[9].getContents() != null ? row[9].getContents().trim() : "");//ACCT_DT
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append(row[10].getContents() != null ? row[10].getContents().trim() : "");//SLEEP_STS
                sBuffer.append(EXCEL_LINE_DELIMITER);
                sBuffer.append(instNo + EXCEL_LINE_DELIMITER);//UPLOAD_INST_NO
                sBuffer.append(fileName + EXCEL_LINE_DELIMITER);//UPLOAD_FILE
                sBuffer.append(uploadDate + EXCEL_LINE_DELIMITER);//UPLOAD_DT
                sBuffer.append(uploadDate + EXCEL_LINE);//ETL_DT
                   
                //读1行写到txt文件
                try {
                    out.write(sBuffer.toString().getBytes());
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    /**
     * 获取目录下的所有文件
     *
     * @param path
     * @return
     */
    private File[] getFiles(String path) {
        File file = new File(path);
        File[] array = file.listFiles();
        return array;
    }
}