Java implementation converts txt file to xls file

Keywords: Java Excel Windows Linux

;

Recently, the project uses the conversion of txt file and xls file. Here are the specific ideas.

Next, use java code to convert txt to xls. Here, use the jxl.jar Package, which is a tool class library for operating Excel tables through Java.
The jar package supports font, number and date operations, can modify cell attributes, and can support images and charts. It basically meets our daily operations. The most important thing is that this API is implemented in pure Java. Under Windows and Linux operating systems, it can correctly process Excel files.

The specific implementation code is as follows:

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 text path
        static String txtFilePath = "D:\\Super_PLU.txt";
        //xls path
        static String xlsFilePath = "D:\\Super_PLU.xls";
        //Column name of each column
        static String c1Name, c2Name, c3Name, c4Name, c5Name, c6Name, c7Name, c8Name;

        public static void main(String args[]) {
            // Parse the txt file and save it as a List
            ArrayList<TxtFile> xlsList = getTxtInfos();
            // Save List as xls
            TransToExcel(xlsList);
        }

        private static ArrayList<TxtFile> getTxtInfos() {
            ArrayList<TxtFile> txtFileList = new ArrayList<TxtFile>();
            BufferedReader bufferedReader = null;
            try {
                // Note the encoding format of the specified file here
                bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(txtFilePath), "gbk"));
                String element = null;
                int index = 0;
                while ((element = bufferedReader.readLine()) != null) {
                    //If this behavior is empty, skip
                    if(element.trim().equals("")){
                        continue;
                    }
                    //First row as column name
                    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;
                    }
                    //Read each line from the second line and store it as 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 {
            // Create an xls file
            book = Workbook.createWorkbook(new File(xlsFilePath));
            // Generate a worksheet named 'product information', where parameter 0 represents the first page
            WritableSheet sheet = book.createSheet("Commodity information", 0);
            // Add a column name to each column in the Label object, that is, the first row of each column            
            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); 
            // Add a defined column name to the worksheet
            sheet.addCell(label1);
            sheet.addCell(label2);
            sheet.addCell(label3);
            sheet.addCell(label4);
            sheet.addCell(label5);
            sheet.addCell(label6);
            sheet.addCell(label7);
            sheet.addCell(label8);

            /*
             * Traverse the List passed in, and add the contents of each row to the worksheet in order,
             * When generating numeric cells, you must use the full package path of 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 file model class
    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;
        }
    }

Posted by Harbinger on Wed, 03 Jun 2020 09:31:15 -0700