How to print two columns in excel side by side after writing all rows from the first column?

Question

I have created code in which data is accessible through the database and then that data is written in the excel sheet.

Now data is written successfully. But the issue is when the data is get written in the excel sheet at the first column of the excel file writes all data properly.

But at the time of the second column, the rows are printed in the second column from where the last row is ended from the first column.

I want to print column side by side. please refer to this image link for more better understanding. Please, shed some light it will be helpful to answer my question. I have shortened up my code for simplicity.

    Integer col = 5;
    Double sl_counter = 0.0;
    Double coff_counter = 0.0;
    try {

                ConnectionClass cn = new ConnectionClass();
                Connection con = cn.connectDb();
            String filename = "E:\\export.csv" ;
                HSSFWorkbook workbook = new HSSFWorkbook();
                HSSFSheet sheet = workbook.createSheet("FirstSheet");
            HSSFRow leave_count = sheet.createRow((short)4);
                while(rs7.next()){
                     HSSFRow SL = sheet.createRow(col);
                     SL.createCell(17).setCellValue(rs7.getString("leave_Date"));
                     col++;
                     sl_counter++;
                }
                while(rs11.next()){
                       HSSFRow SL = sheet.createRow(col);
                       SL.createCell(17).setCellValue(rs11.getString("leave_Date")+"      (FIRST HALF DAY)");
                       col++;
                       sl_counter += 0.5;
                }
                while(rs15.next()){
                     HSSFRow SL= sheet.createRow(col);
                     SL.createCell(17).setCellValue(rs15.getString("leave_Date")+"      (SECOND HALF DAY)");
                     col++;
                     sl_counter += 0.5;
                }
                leave_count.createCell(17).setCellValue(sl_counter);

                //int cols = 5;   
                 while(rs8.next()){

                     HSSFRow leave_date = sheet.createRow(col);
                     leave_date.createCell(18).setCellValue(rs8.getString("leave_Date"));
                     col++;
                     coff_counter++;
                }
                while(rs12.next()){
                       HSSFRow leave_date = sheet.createRow(col);
                       leave_date.createCell(18).setCellValue(rs12.getString("leave_Date")+"      (FIRST HALF DAY)");
                       col++;
                       coff_counter += 0.5;
                }
                while(rs16.next()){
                     HSSFRow leave_date = sheet.createRow(col);
                     leave_date.createCell(18).setCellValue(rs16.getString("leave_Date")+"      (SECOND HALF DAY)");
                     col++;
                     coff_counter += 0.5;
                }

                leave_count.createCell(18).setCellValue(coff_counter);

                FileOutputStream fileOut = new FileOutputStream(filename);
                workbook.write(fileOut);
                fileOut.close();
    //            workbook.close();
                cl_counter = 0.0;
                col = 0;

            } catch ( Exception ex ) {
                System.out.println(ex);
            }

Do you have any requirement LIke Both data should be in a single sheet? Can you separate them? — Dushyant Tankariya, Jun 29 '19 at 07:32
not sure about single sheet but the main requirement is the data should be print in the same excel file. — steave jackson, Jun 29 '19 at 07:34
it's a basic question i think but i'm really new to servlets and java that's why don't have any knowledge of how to do that. i'm struggling with this problem from the last week but still did not find the answer. — steave jackson, Jun 29 '19 at 07:36
To Print your data in specific Cell you can use [this link](https://www.geeksforgeeks.org/creating-a-cell-at-specific-position-in-excel-file-using-java/) And to create New sheet you can use `createSheet("Array")` method of work book class. — Dushyant Tankariya, Jun 29 '19 at 07:39
i tried that but when i do like this then that time previous sheet data get erased and only new sheet data is printed. can you show me some basic code that how i print two columns side by side.? — steave jackson, Jun 29 '19 at 07:53
Have you used the same object to create a sheet or You've declared second? — Dushyant Tankariya, Jun 29 '19 at 08:15
I also tried with the row Iterator rowIterator = sheet.iterator(); but still not working or maybe i'm not using it properly. I really need help. — steave jackson, Jun 29 '19 at 09:20
anyone here know this answer if yes then please share your knowledge. — steave jackson, Jun 29 '19 at 11:38

Dushyant Tankariya · Answer 1 · 2019-07-02T09:28:48.073

0

In Order to create a new sheet, you need the second Object of HSSFSheet and need to initialize it.

I've added the poi jar from URL to Access HSSFSheet class.

I've already prepared a demo with HSSF library using List of temporary data so you can have a small idea about how to use this library to generate excel file with creating multiple sheets.

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class Excel {
    public static void main(String[] args) {
        int col = 1;
        try {

            String filename = "I:\\export.csv";
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("FirstSheet");
            List rs7 = new ArrayList();
            rs7.add("06/27/2019");

            List rs8 = new ArrayList();
            rs8.add("06/23/2019");

            List rs11 = new ArrayList();
            rs11.add("06/23/2019");

            List rs12 = new ArrayList();
            rs12.add("06/26/2019");

            List rs15 = new ArrayList();
            rs15.add("06/26/2019");

            List rs16 = new ArrayList();
            rs16.add("06/25/2019");

            int r7,r8, r11,r12, r15,r16;
            r7=r8=r11=r12=r15=r16=0;
            sheet.createRow(0).createCell(1).setCellValue("SL");
            while (r7<rs7.size()) {
                HSSFRow SL = sheet.createRow(col++);
                SL.createCell(1).setCellValue(""+rs7.get(r7++));
            }
            while (r11<rs11.size()) {
                HSSFRow SL = sheet.createRow(col++);
                SL.createCell(1).setCellValue(rs11.get(r11++) + "      (FIRST HALF DAY)");
            }
            while (r15<rs15.size()) {
                HSSFRow SL = sheet.createRow(col++);
                SL.createCell(1).setCellValue(rs15.get(r15++) + "      (SECOND HALF DAY)");
            }


            HSSFSheet sheet2 = workbook.createSheet("SecondSheet");
            sheet2.createRow(0).createCell(1).setCellValue("C-OFF");
            col=1;
            while (r8<rs8.size()) {

                HSSFRow leave_date = sheet2.createRow(col++);
                leave_date.createCell(1).setCellValue(""+rs8.get(r8++));
            }
            while (r12<rs12.size()) {
                HSSFRow leave_date = sheet2.createRow(col++);
                leave_date.createCell(1).setCellValue(rs12.get(r12++) + "      (FIRST HALF DAY)");
            }
            while (r16<rs16.size()) {
                HSSFRow leave_date = sheet2.createRow(col++);
                leave_date.createCell(1).setCellValue(rs16.get(r16++) + "      (SECOND HALF DAY)");
            }
            FileOutputStream fileOut = new FileOutputStream(filename);
            workbook.write(fileOut);
            fileOut.close();
            col = 0;

        } catch (Exception ex) {
            System.out.println(ex);
        }
    }
}

IF you don't need to create multiple sheet and need to display data in one data sheet only then you replace above second part with below code which helps you to understand the usage.

//HSSFSheet sheet2 = workbook.createSheet("SecondSheet");
            sheet.getRow(0).createCell(2).setCellValue("C-OFF");
//          sheet2.createRow(0).createCell(1).setCellValue("C-OFF");
            col=1;
            while (r8<rs8.size()) {
                sheet.getRow(col++).createCell(2).setCellValue(""+rs8.get(r8++));
                /*HSSFRow leave_date = sheet2.createRow(col++);
                leave_date.createCell(1).setCellValue(""+rs8.get(r8++));*/
            }
            while (r12<rs12.size()) {
                sheet.getRow(col++).createCell(2).setCellValue(""+rs12.get(r12++)+ "      (FIRST HALF DAY)");
                /*HSSFRow leave_date = sheet2.createRow(col++);
                leave_date.createCell(1).setCellValue(rs12.get(r12++) + "      (FIRST HALF DAY)");*/
            }
            while (r16<rs16.size()) {
                sheet.getRow(col++).createCell(2).setCellValue(""+rs16.get(r16++)+ "      (SECOND HALF DAY)");
                /*HSSFRow leave_date = sheet2.createRow(col++);
                leave_date.createCell(1).setCellValue(rs16.get(r16++) + "      (SECOND HALF DAY)");*/
            }
            FileOutputStream fileOut = new FileOutputStream(filename);
            workbook.write(fileOut);
            fileOut.close();
            col = 0;

edited Jul 02 '19 at 09:28

answered Jul 02 '19 at 04:53

Dushyant Tankariya

1,432
3
11
17

Thank you so much for the answer. Yesterday i solved this problem. I used sheet.createRow(col); everytime after once it's creation so that will make new row each time and showing blank. So instead of sheet.createRow(col); i have used sheet.getRow(col); – steave jackson Jul 02 '19 at 06:09
I will also try with your answer. :) Thank you once again. – steave jackson Jul 02 '19 at 06:10
Oh, sorry I'm late for the second answer. I've also prepared a demo with sheet.getRow(col++) but you have already solved It's good. – Dushyant Tankariya Jul 02 '19 at 06:11
yes i have done that but now testing that. and it only working one record not working properly for other records. now i'm trying your first answer. by creating arrays. – steave jackson Jul 02 '19 at 09:10
Doesn't need to create an array. You can directly print data from the result set in your program you need to specify only two variable on for `row` which increase the no of rows and the `col` which increase the no of the column. In between two different data/table, you need to start again with row 1 but you need to be stick with col. – Dushyant Tankariya Jul 02 '19 at 10:05
yes i have tried your answer but it returning only one column and actually i'm not assigning values statically. i'm retrieving that value from database and printing directly into excel cells. Go to this link i have uploaded my whole program so you'll get the clear picture what i have done exactly. https://jsfiddle.net/9L4rdmf1/ – steave jackson Jul 02 '19 at 10:40
Mate your second code is right for me but the program is dynamic so the main issue is some result sets are empty like example suppose consider there is no SL leave is present in result set so there no records will be displayed. Now here i don't no which results sets are empty and which are not so the problem is if i create rows in first while loops where first column is printing.and then used get rows in second while loops where second column is printing then that time second column is also prints nothing because of rows are not created and hence output get nothing. – steave jackson Jul 02 '19 at 11:16
is there anyway that i can create rows previously according to how much records are actually available in the database then store that all database records in array and just get that values in setCellValue and mention just a get row(number)? – steave jackson Jul 02 '19 at 11:22
So Do you know how to overcome this problem ? – steave jackson Jul 02 '19 at 12:25
Please update your answer with the database structure so I can figure out why you have 16 Resultset. Because for now, it seems like every result set has one row. – Dushyant Tankariya Jul 02 '19 at 12:41
yes take look of both the tables data and it's structure. First user_assign_leave table :- https://imgur.com/a/HHG2g4L and second apply_leave_detail table :- https://imgur.com/a/7QMmsk9 – steave jackson Jul 02 '19 at 12:58
So you have fields CL, PL, SL, C-OFF with the first and second half. And you want to print data which is by forget in 4 tables AS CL, PL, SL, C-OFF right? – Dushyant Tankariya Jul 02 '19 at 13:06
i want to print those data whose leaves are APPROVED in the status of specific user or user_email – steave jackson Jul 02 '19 at 13:09
and i have created so much result sets because the data is not accurate when i tried with two or more leaves. – steave jackson Jul 02 '19 at 13:11
Because here you don't need to create 17 result set you need to create only 1 resultset. update your code by writing a query in such a way that only approved leave data will return from database to resultset and then write your data to excel sheet with logical condition in while loop. – Dushyant Tankariya Jul 02 '19 at 13:14
Ex: ```Result set <= select assign_leave,balance_leave from user_assign_leave where user_email = "abc.xyz@stack.com" AND STATUS='APPROVED' if(result Set.getString(leave_Name).equals("CL")){ getRow(current row value).createCell(current Column value).setCellValue(resultSet.getString("assign_leave")); } ...``` – Dushyant Tankariya Jul 02 '19 at 13:21
No mate there lot more in the project i also need each individual leave counts so for that also need that all result set. – steave jackson Jul 02 '19 at 13:21
and also don't get confuse between this two tables from user_assign_leave table i'm only getting the values of assign_leave and balance_leave {values} and second from apply_leave_detail table i'm getting the leave_dates of specific user which leave is approved and take it individually because i need it's count for other calculation part. – steave jackson Jul 02 '19 at 13:25
i just need that how to actually create rows dynamically according to the leaves which are approved. but when i try to create rows for all it only display last column record. because of the overwriting the rows. – steave jackson Jul 02 '19 at 13:29
dynamically create rows can be achieved by creating two variables and maintain their values on every iteration. 1) row - For iterating horizontally, 2) col - For iterating vertically. That's it all from my side afterward you are on your own. – Dushyant Tankariya Jul 02 '19 at 13:31
i think there is a solution instead of creating rows there we can assign only row number and createCells and sets values accordingly only. So that way we can overcome this problem i think. Whats your opinion ? i'm trying on this if you have any idea then please shed some light – steave jackson Jul 02 '19 at 13:38

How to print two columns in excel side by side after writing all rows from the first column?

1 Answers1