Java POI - inserting rows as columns

Question

I'm basically getting rows from my database and pasting them in my excel file as rows. It is kinda easy, because there isn't any data conversion. Here is my code (most important part of it):

XSSFSheet chart1_sheet = wb.createSheet("Chart1");

String strss_chart[] = {"dataa", "inn", "closed", "application"};

hed_row = chart1_sheet.createRow(0);
for_each_header(hed_row, strss_chart);

sql = "select dataa, inn, closed, application from Chart1";
statement =  conn.prepareStatement(sql);
resultSet = statement.executeQuery();  

row = 1;
while(resultSet.next()) 
{
    Row dataRow = chart1_sheet.createRow(row);
    for_each_row_func(dataRow, resultSet, strss_chart);
    row = row + 1;
}

And my two functions:

public static void for_each_row_func(Row dataRow, ResultSet resultSet, String [] strs) throws Exception
{
    int i = 0;
    for(String str : strs)
    {
         dataRow.createCell(i).setCellValue(resultSet.getString(str));
         i += 1;
    }
}


public static void for_each_header(Row dataRow, String [] strs) throws Exception
{
    int i = 0;
    for(String str : strs)
    {
         //dataRow.createCell(i).setCellValue(resultSet.getString(str));
         dataRow.createCell(i).setCellValue(str);
         i += 1;
    }
}

My final goal is to create chart from this data, but to do it I need data NOT like this:

Column1 Column2 Column3
Data1   Data2   Data3
Data4   Data5   Data6

But like this:

Column1 Data1 Data4
Column2 Data2 Data5
Column3 Data3 Data6

Is there any way to do that in Java Poi?

Jgrammer · Accepted Answer · 2017-02-10 10:28:12Z

2

As I remember in POI you need to create first rows, than columns as cells. So - in your case you need create 3 rows instances

Row row1 = chart1_sheet.createRow(1);
Row row2 = chart1_sheet.createRow(2);
Row row3 = chart1_sheet.createRow(3);

than fill headers

row1.createCell(1).setCellValue(str[0]);
row2.createCell(1).setCellValue(str[1]);
row3.createCell(1).setCellValue(str[2]);

And than n iteration fill data

rownum = 2;
while(resultSet.next()) 
{
    row1.createCell(rownum).setCellValue(resultSet.getString(1));
    row2.createCell(rownum).setCellValue(resultSet.getString(2));
    row3.createCell(rownum).setCellValue(resultSet.getString(3));
    rownum++;
}

You can put all rows into collection P.S. Sorry don't have any Java IDE on my PC - may be some mistakes in syntax. Tried to describe algorithm

edited Feb 10, 2017 at 10:28

answered Feb 10, 2017 at 9:34

Jgrammer

3262 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

Java POI - inserting rows as columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related