CODEWITHSUNDEEP
javaexceloracle-databaseapache-poi
westman379
westman379

Reputation: 713

Java POI - inserting rows as columns

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?

Upvotes: 0

Views: 1614

Answers (1)

Jgrammer
Jgrammer

Reputation: 326

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

Upvotes: 2

Related Questions