Read in a text file and upload it to excel using Java

Question

I am reading in a text file, and then performing some validations on the file after which I am trying to put that file into an excel file. But I am able to add only the last row into the excel document and not every row. Any help is appreciated. Thank You. Here is what I have so far:

LinkedList<String[]> llist = new LinkedList<>();

String[] data;

File temp = new File("file.txt");
BufferedWriter bw = new BufferedWriter(new FileWriter(temp));
for (int i = 0; i < llist.size(); i++) {
    if(i==0){
        bw.newLine();
    }
    else{
        data = llist.get(i);

    String empid1 = data[0];
    String fname = data[1];
    String ccode1 = data[2];

    if(data[2].equals("IND")) {
        replace = data[2].replaceAll("IND", "IN");
        ccode1 = replace;
    }
    else if(data[2].equals("USA")) {
        replace = data[2].replaceAll("USA", "US");
        ccode1 = replace;
    }

    else {
        ccode1 = data[2];
    }

    String newData=empid1+","+fname+","+ccode1;

    XSSFWorkbook workBook = new XSSFWorkbook();
    FileOutputStream outstream=new FileOutputStream("data.xls");
    XSSFSheet spreadSheet = workBook.createSheet("Clean");
    int row_num = 0;
    for(String[] str : llist) {
        XSSFRow row = spreadSheet.createRow(row_num++);
        int cell_num = 0;
        for(String value : str) {
            XSSFCell cell = row.createCell(cell_num++);
            cell.setCellValue(value);
        }
    }

    workBook.write(outstream);
}
}

bw.close();

}

}

looks like you are re-writing the excel document for each line of the original file...Isn't the second loop inside the first one? — agugglez
– agugglez, Commented Sep 19, 2018 at 22:59
Yes, second loop is inside the first loop, and I am writing the data into the excel file with any validation changes that happens in the data. — user2529660
– user2529660, Commented Sep 19, 2018 at 23:02
In that way it looks like you are trying to do read, validate and write all at the same time. I suggest you structure your code better (take the second loop out) and it'll be easier to identify the bug that way. — agugglez
– agugglez, Commented Sep 19, 2018 at 23:21
With this code, I am able to get all the data, but this data has not gone through data validation rules, and that is the problem that I am facing. I tried to enter in the newData into my column, but it is entering everything into one cell, instead of separate cells. — user2529660
– user2529660, Commented Sep 19, 2018 at 23:31
Yes, you are writing to excel document directly from llist variable. You are not using your validated data. — agugglez
– agugglez, Commented Sep 19, 2018 at 23:39

agugglez · Accepted Answer · 2018-09-20 01:31:01Z

1

Rewrite your code like this:

LinkedList < String[] > llist = new LinkedList < > ();

String[] data;

File temp = new File("file.txt");
BufferedWriter bw = new BufferedWriter(new FileWriter(temp));

XSSFWorkbook workBook = new XSSFWorkbook();
FileOutputStream outstream = new FileOutputStream("data.xls");
XSSFSheet spreadSheet = workBook.createSheet("Clean");

for (int i = 0; i < llist.size(); i++) {
 if (i == 0) {
  bw.newLine();
 } else {
  data = llist.get(i);

  String empid1 = data[0];
  String fname = data[1];
  String ccode1 = data[2];

  if (data[2].equals("IND")) {
   replace = data[2].replaceAll("IND", "IN");
   ccode1 = replace;
  } else if (data[2].equals("USA")) {
   replace = data[2].replaceAll("USA", "US");
   ccode1 = replace;
  } else {
   ccode1 = data[2];
  }

  //String newData=empid1+","+fname+","+ccode1;

  XSSFRow row = spreadSheet.createRow(i);

  XSSFCell cell = row.createCell(0);
  cell.setCellValue(empid1);

  cell = row.createCell(1);
  cell.setCellValue(fname);

  cell = row.createCell(2);
  cell.setCellValue(ccode1);
 }
}

workBook.write(outstream);
bw.close();

edited Sep 20, 2018 at 1:31

answered Sep 20, 2018 at 0:01

agugglez

1721 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user2529660 Over a year ago

But if I did not know the number of rows in my file, then how do I implement that?

agugglez Over a year ago

You keep the loop through llist, just not the one through str.

user2529660 Over a year ago

Can you show that as well in your answer? Also, llist does not contain the validated data.

agugglez Over a year ago

Just did. It doesn't really matter that llist contains validated data as long as it has the same number of rows.

user2529660 Over a year ago

Just one more question, is there a way to loop through newData, instead of writing to set each cell value?

|

Collectives™ on Stack Overflow

Read in a text file and upload it to excel using Java

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related