Java實作将txt檔案轉成xls檔案

最近項目用到txt檔案和xls檔案的轉換，這裡記錄一下具體的思路。

下面利用java代碼實作txt轉xls，這裡要使用到jxl.jar包，這個包是通過java來操作Excel表格的工具類庫。

該jar包支援字型、數字、日期操作，能夠修飾單元格屬性，還能夠支援圖像和圖表，基本上已經滿足我們的日常操作，最主要的是這套API是純Java實作的，在Windows和Linux作業系統下，它都可以正确的處理Excel檔案。

具體實作代碼如下：

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 = ;
                while ((element = bufferedReader.readLine()) != null) {
                    //如果是此行為空，則跳過
                    if(element.trim().equals("")){
                        continue;
                    }
                    //第一行作為每列名稱
                    String[] value = element.trim().split(",");
                    if (index == ) {
                        c1Name = value[];
                        c2Name = value[];
                        c3Name = value[];
                        c4Name = value[];
                        c5Name = value[];
                        c6Name = value[];
                        c7Name = value[];
                        c8Name = value[];
                        index = ;
                        continue;
                    }
                    //從第二行開始讀取每行内容，以TxtFile形式存儲
                    TxtFile txtFile = new TxtFile(Integer.parseInt(value[]), Integer.parseInt(value[]), value[], value[], value[], Integer.parseInt(value[]), Integer.parseInt(value[]), Integer.parseInt(value[]));
                    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("商品資訊", );
            // 在Label對象為每一列添加列名，即每一列的第一行            
            Label label1 = new Label(, , c1Name);
            Label label2 = new Label(, , c2Name);
            Label label3 = new Label(, , c3Name);
            Label label4 = new Label(, , c4Name);
            Label label5 = new Label(, , c5Name);
            Label label6 = new Label(, , c6Name);
            Label label7 = new Label(, , c7Name);
            Label label8 = new Label(, , 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 = ; i < txtFileList.size(); i++) {
                TxtFile p = txtFileList.get(i); 
                jxl.write.Number item_code = new jxl.write.Number(, (i+), p.item_code);
                jxl.write.Number plu = new jxl.write.Number(, (i+), p.plu);
                Label commodity = new Label(, (i+), p.commodity);
                Label ingredient= new Label(, (i+), p.ingredient);
                Label special = new Label(, (i+), p.special);
                jxl.write.Number use_by_date = new jxl.write.Number(, (i+), p.use_by_date);
                jxl.write.Number use_by_date_print = new jxl.write.Number(, (i+), p.use_by_date_print);
                jxl.write.Number packge_by_date_print = new jxl.write.Number(, (i+), 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;
        }
    }