CODEWITHSUNDEEP
javatype-conversiontext-filesxls
CosmicGiant
CosmicGiant

Reputation: 6439

How to convert a tab-separated ".txt" file into Excel ".xls" format using java?

I have 498 text files resulting from WebScraping results from a form on a website, and i want to convert it all to Excel standard (.xls) format using java.

I will have to do this conversion every month with the same amount of files, taking no less than 10 seconds per file on a hand-based conversion, summing a full 1h23m of inconvenience at best of optimism, so the method of loading each file and hitting "save_as", by hand or by robot, is forbidden.

Upvotes: 2

Views: 16279

Answers (3)

TikkaBhuna
TikkaBhuna

Reputation: 515

I would have an array looping over all the files you want. You can read in the .csv file as a standard text file storing each row in an Array and then looping over that you can create the excel sheet.

Now I've never created an excel workbook from scratch but I have edited them. I wrote up the following. This will loop through all the files in a directory and scan them in. Its will just place the data from each file one after another on the first sheet. It would have been nice to use iterators but I'll let you do that. :)

public void convertCSV(File dir) throws IOException {
    // Gets the CSV files.
    File[] csvFiles = dir.listFiles();
    // Sets up the Workbook and gets the 1st (0) sheet.
    File excelFile = new File("H:\\yourOutput.xls");
    InputStream input = new FileInputStream(excelFile);
    HSSFWorkbook workbook = new HSSFWorkbook(input);
    HSSFSheet sheet = workbook.getSheetAt(0);

    int rowNo = 0;
    int columnNo = 0;

    // Loop through the files.
    for(File file : csvFiles) {
        Scanner scanner = new Scanner(file);
        while(scanner.hasNextLine()) {
            String line = scanner.nextLine();
            // Gets the row and if it doesn't exist it will create it.
            Row tempRow = sheet.getRow(rowNo);
            if(tempRow == null)
                tempRow = sheet.createRow(rowNo);

            Scanner lineScanner = new Scanner(line);
            lineScanner.useDelimiter("\t");
            // While there is more text to get it will loop.
            while(lineScanner.hasNext()) {
                // Gets the cell in that row and if it is null then it will be created.
                Cell tempCell = tempRow.getCell(columnNo, Row.CREATE_NULL_AS_BLANK);
                String output = lineScanner.next();
                // Write the output to that cell.
                tempCell.setCellValue(new HSSFRichTextString(output));
                columnNo++;
            }
            // Resets the column count for the new row.
            columnNo = 0;
            rowNo++;
        }
    }
    // Writes the file and closes everything.
    FileOutputStream out = new FileOutputStream(excelFile);
    workbook.write(out);
    input.close();
    out.close();
}

Tested it and it works with having tab as a delimiter. You just need to specify the directory that your CSV files are in.

Upvotes: 1

Victor Sorokin
Victor Sorokin

Reputation: 12006

Excel can load them as csv files, just specify that field separator is Tab.

Upvotes: 2

jtahlborn
jtahlborn

Reputation: 53694

you can use the apache poi project to generate xls files in java.

Upvotes: 2

Related Questions