package test;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class txtToxls {
    //txt文本路徑
    static String txtFilePath = "D:\\Super_PLU.txt";
    //xls路徑
    static String xlsFilePath = "D:\\Super_PLU.xls";
    //每一列的列名
    static String c1Name, c2Name, c3Name, c4Name, c5Name, c6Name, c7Name, c8Name;

    public static void main(String args[]) {
      // 將txt文件進行解析，保存為List
      ArrayList<TxtFile> xlsList = getTxtInfos();
      // 將List以xls保存
      TransToExcel(xlsList);
    }

    private static ArrayList<TxtFile> getTxtInfos() {
      ArrayList<TxtFile> txtFileList = new ArrayList<TxtFile>();
      BufferedReader bufferedReader = null;
      try {
        // 這里注意指定文件的編碼格式
        bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(txtFilePath), "gbk"));
        String element = null;
        int index = 0;
        while ((element = bufferedReader.readLine()) != null) {
          //如果是此行為空，則跳過
          if(element.trim().equals("")){
            continue;
          }
          //第一行作為每列名稱
          String[] value = element.trim().split(",");
          if (index == 0) {
            c1Name = value[0];
            c2Name = value[1];
            c3Name = value[2];
            c4Name = value[3];
            c5Name = value[4];
            c6Name = value[5];
            c7Name = value[6];
            c8Name = value[7];
            index = 1;
            continue;
          }
          //從第二行開始讀取每行內容，以TxtFile形式存儲
          TxtFile txtFile = new TxtFile(Integer.parseInt(value[0]), Integer.parseInt(value[1]), value[2], value[3], value[4], Integer.parseInt(value[5]), Integer.parseInt(value[6]), Integer.parseInt(value[7]));
          txtFileList.add(txtFile);
        }
      } catch (Exception e) {
        e.printStackTrace();
      } finally {
        if (bufferedReader != null) {
          try {
            bufferedReader.close();
          } catch (IOException e) {
            e.printStackTrace();
          }
        }
      }
      return txtFileList;
    }

  private static void TransToExcel(ArrayList<TxtFile> txtFileList) {
    WritableWorkbook book = null;
    try {
      // 創建一個xls文件
      book = Workbook.createWorkbook(new File(xlsFilePath));
      // 生成名為'商品信息'的工作表，這里參數0表示第一頁
      WritableSheet sheet = book.createSheet("商品信息", 0);
      // 在Label對象為每一列添加列名，即每一列的第一行      
      Label label1 = new Label(0, 0, c1Name);
      Label label2 = new Label(1, 0, c2Name);
      Label label3 = new Label(2, 0, c3Name);
      Label label4 = new Label(3, 0, c4Name);
      Label label5 = new Label(4, 0, c5Name);
      Label label6 = new Label(5, 0, c6Name);
      Label label7 = new Label(6, 0, c7Name);
      Label label8 = new Label(7, 0, c8Name); 
      // 將定義好列名添加到工作表中
      sheet.addCell(label1);
      sheet.addCell(label2);
      sheet.addCell(label3);
      sheet.addCell(label4);
      sheet.addCell(label5);
      sheet.addCell(label6);
      sheet.addCell(label7);
      sheet.addCell(label8);

      /*
       * 遍歷傳進來的List，把每一行的內容再順序加入到工作表中，
       * 在生成數字單元格時， 必須使用Number的完整包路徑 
       */
      for (int i = 0; i < txtFileList.size(); i++) {
        TxtFile p = txtFileList.get(i); 
        jxl.write.Number item_code = new jxl.write.Number(0, (i+1), p.item_code);
        jxl.write.Number plu = new jxl.write.Number(1, (i+1), p.plu);
        Label commodity = new Label(2, (i+1), p.commodity);
        Label ingredient= new Label(3, (i+1), p.ingredient);
        Label special = new Label(4, (i+1), p.special);
        jxl.write.Number use_by_date = new jxl.write.Number(5, (i+1), p.use_by_date);
        jxl.write.Number use_by_date_print = new jxl.write.Number(6, (i+1), p.use_by_date_print);
        jxl.write.Number packge_by_date_print = new jxl.write.Number(7, (i+1), p.packge_by_date_print);

        sheet.addCell(item_code);
        sheet.addCell(plu);
        sheet.addCell(commodity);
        sheet.addCell(ingredient);
        sheet.addCell(special);
        sheet.addCell(use_by_date);
        sheet.addCell(use_by_date_print);
        sheet.addCell(packge_by_date_print);
      }
      book.write();
      book.close();
    } catch (Exception e) {
      e.printStackTrace();;
    }
  }
}
  // txt文件model類
  class TxtFile {
    int item_code;
    int plu;
    String commodity;
    String ingredient;
    String special;
    int use_by_date;
    int use_by_date_print;
    int packge_by_date_print;

    public TxtFile(int item_code, int plu, String commodity, String ingredient, String special,int use_by_date, int use_by_date_print, int packge_by_date_print) {
      this.item_code = item_code;
      this.plu = plu;
      this.commodity = commodity;
      this.ingredient = ingredient;
      this.special = special;
      this.use_by_date = use_by_date;
      this.use_by_date_print = use_by_date_print;
      this.packge_by_date_print = packge_by_date_print;
    }
  }