How to write a list of data in excel in column wise using Java?

Question

I have a list of data in ArrayList which contains the heading as well as the values associated with it. Like below,

Fruits 
Apple 
Orange 
******
Vegetables 
Beans
Carrot 
Brinjal
Cucumber 
******
Colors
Orange 
Blue
Red
Green
Yellow

Now, I want to write the values in `excel` with each heading and the associated values in column-wise. Like below,

Fruits   Vegetable   Colors
Apple    Beans       Orange 
Orange   Carrot      Blue
         Brinjal     Red
         Cucumber    Green
                     Yellow

I'm writing my code in java and using apache.poi.xssf.usermodel library, to handing the excel manipulations. The issue what I'm facing here is, when I'm writing the 2nd heading and the associated values, the first heading and its values are getting cleared due to the Row creation code

XSSFRow row = outputSheet.createRow(rowNumValue);

Can anyone help me get this done?

Split, get the first String and make it the cat and after that loop the items and put it into a String Builder. — SamHoque
– SamHoque, Commented Oct 12, 2018 at 12:34

Emmanuel Rosa · Accepted Answer · 2018-10-12 12:42:49Z

2

What you need to do is use createRow() the first time to... create the row, and getRow() for all subsequent access. That way, you'll get the row already in the worksheet object instead of creating a new one. getRow() returns null if the row doesn't exist, so you can do the equivalent of this:

XSSFRow row = outputSheet.getRow(rowNumValue) ? outputSheet.getRow(rowNumValue) : outputSheet.createRow(rowNumValue);

answered Oct 12, 2018 at 12:42

Emmanuel Rosa

9,8752 gold badges17 silver badges21 bronze badges

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

Comments

Joop Eggen · Accepted Answer · 2018-10-12 12:53:14Z

One could operate immediately on the single list, but it is easier to use a sensible data structure. As every column can have a different number of rows, make a list of columns, every column a list of rows.

List<List<String>> makeColumns(List<String> linearList) {
    List<List<String>> columns = new ArrayList<>();
    int column = 0;
    for (String s : linearList) {
        if (s.startsWith("***")) {
            ++column;
        } else {
            while (column >= columns.size()) {
                columns.add(new ArrayList<>());
            }
            columns.get(column).add(s);
        }
    }
}

Now one can iterate over the rows to fill the sheet.

List<List<String>> columns = makeColumns(...);
int rows = columns.stream().mapToInt(List::size).max().orElse(0);

for (int rowi = 0; rowi < rows; ++rowi) {
    XSSFRow row = outputSheet.createRow(rowi + 1);
    for (int coli = 0; coli < columns.size(); ++coli) {
        if (rowi < columns.get(coli).size()) {
            String value = columns.get(coli).get(rowi);
            row.createCell(coli);
            ...
        }
    }
}

Collectives™ on Stack Overflow

How to write a list of data in excel in column wise using Java?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related