How to write console output into Excel file in Java

Question

hi guys i m new in java. In my program, i can select text files and read them. I need to write these read files into excel file without using database. I wrote these codes.

I inserted data using Object Array but i dont need to write one by one whole content of text file.

Can anybody help me...Thanks in advance.

Program GUI

my code

public class FirstExample {

public static void main(String[] args) throws FileNotFoundException, IOException, BiffException, WriteException {


    String excelFilePath="C:\\Users\\Morlot\\Desktop\\Dosy1.xlsx";


    try{
        FileInputStream inpStr=new FileInputStream(new File(excelFilePath));

        org.apache.poi.ss.usermodel.Workbook wbook= org.apache.poi.ss.usermodel.WorkbookFactory.create(inpStr);
        org.apache.poi.ss.usermodel.Sheet sheet=wbook.getSheetAt(0);

        Object[][] bookData= {{

" 20.938 "," 0.2670 "," 304.66 "," 0.6196 "," 0.0000 "," 0.0000 "," -20.971 "," 0.1377 "," -1.5959 "," -0.4619 "," -0.4605 "," -299.15 "," 100.03 "," -0.0007 "," 0.0030 "," 0.2314 "," 0.0031 "," -299.51 ","14:51:06\n" },{
" 20.938 "," 0.2670 "," 304.66 "," 0.6249 "," 0.0000 "," 0.0000 "," -20.971 "," 0.1252 "," -1.6774 "," -0.4240 "," -0.4213 "," -297.19 "," 100.03 "," -0.0006 "," 0.0029 "," 0.2279 "," 0.0031 "," -299.51 ","14:51:09\n" },{
" 20.938 "," 0.2670 "," 304.67 "," 0.6286 "," 0.0000 "," 0.0000 "," -20.971 "," 0.1000 "," -1.7182 "," -0.3423 "," -0.3412 "," -293.18 "," 99.934 "," 0.0013 "," 0.0027 "," 0.2288 "," 0.0031 "," -299.51 ","14:51:12\n" },{
" 20.937 "," 0.2670 "," 304.67 "," 0.6342 "," 0.0000 "," 0.1249 "," -20.846 "," 0.0964 "," -1.7998 "," -0.3332 "," -0.3310 "," -291.19 "," 99.979 "," 0.0004 "," 0.0026 "," 0.2316 "," 0.0031 "," -299.51 ","14:51:15\n" }};

        int rowCount=sheet.getFirstRowNum();

        for(Object[] dataBook : bookData){
            Row row=sheet.getRow(++rowCount);

              int columnCount=0;

            org.apache.poi.ss.usermodel.Cell cell= row.createCell(columnCount);
            cell.setCellValue(rowCount);

            for(Object field : dataBook){
                cell= row.createCell(++columnCount);
                if(field instanceof String){
                    cell.setCellValue((String) field);
                }else if(field instanceof Integer){
                    cell.setCellValue((Integer) field);
                }

            }
        }
        inpStr.close();

        FileOutputStream opStr= new FileOutputStream("C:\\Users\\Morlot\\Desktop\\Dosy1.xlsx");
        System.out.println(excelFilePath + "data2 is written succesfully");
                    //10 kasım init text verisini excele yazdırma komutu 

        wbook.write(opStr);
        wbook.close();




    }catch(IOException | EncryptedDocumentException 
            | InvalidFormatException ex){
        ex.printStackTrace();
    }

}

}

excel file

3

Answer 1

Simpler way to solve that, save as a *.csv file. Its a text file with some rules as the link explain:

https://en.wikipedia.org/wiki/Comma-separated_values

It can be readed by any excel

Answer 2

You need to export your data into a *.csv. I had a similar issue once and this post was very helpful: https://www.mkyong.com/java/how-to-export-data-to-csv-file-java/

Here is the relevant part:

CSVUtils.java

package com.mkyong.utils;

import java.io.IOException;
import java.io.Writer;
import java.util.List;

public class CSVUtils {

    private static final char DEFAULT_SEPARATOR = ',';

    public static void writeLine(Writer w, List<String> values) throws IOException {
        writeLine(w, values, DEFAULT_SEPARATOR, ' ');
    }

    public static void writeLine(Writer w, List<String> values, char separators) throws IOException {
        writeLine(w, values, separators, ' ');
    }

    //https://tools.ietf.org/html/rfc4180
    private static String followCVSformat(String value) {

        String result = value;
        if (result.contains("\"")) {
            result = result.replace("\"", "\"\"");
        }
        return result;

    }

    public static void writeLine(Writer w, List<String> values, char separators, char customQuote) throws IOException {

        boolean first = true;

        //default customQuote is empty

        if (separators == ' ') {
            separators = DEFAULT_SEPARATOR;
        }

        StringBuilder sb = new StringBuilder();
        for (String value : values) {
            if (!first) {
                sb.append(separators);
            }
            if (customQuote == ' ') {
                sb.append(followCVSformat(value));
            } else {
                sb.append(customQuote).append(followCVSformat(value)).append(customQuote);
            }

            first = false;
        }
        sb.append("\n");
        w.append(sb.toString());


    }

}

CVSUtilExample.java

package com.mkyong.utils;

import java.io.FileWriter;
import java.util.Arrays;

public class CVSUtilExample {

    public static void main(String[] args) throws Exception {

        String csvFile = "/Users/mkyong/csv/abc.csv";
        FileWriter writer = new FileWriter(csvFile);

        CSVUtils.writeLine(writer, Arrays.asList("a", "b", "c", "d"));

        //custom separator + quote
        CSVUtils.writeLine(writer, Arrays.asList("aaa", "bb,b", "cc,c"), ',', '"');

        //custom separator + quote
        CSVUtils.writeLine(writer, Arrays.asList("aaa", "bbb", "cc,c"), '|', '\'');

        //double-quotes
        CSVUtils.writeLine(writer, Arrays.asList("aaa", "bbb", "cc\"c"));


        writer.flush();
        writer.close();

    }

}