How to create multiple sheets in a excel based on the values passed from output of query

Question

# My requirement is to generate an excel workbook with multiple sheets in it. with this condition that execute a query with specific date range and get the output and pass those values in to excel workbook with different sheets based on date eg: sheet 1 should contain only Date 1 values and sheet 2 should contain only date 2 values and so on till the given date range . #

## In the below code i have achieved only getting out of query for a selected date range and passed to excel workbook with only one sheet .Please help me out in how to go forward from here and achieve my requirment. ##

import java.sql.*;
import java.io.File;
import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; 

public class CreateExcelFile{
public static void main(String[]args){
try{
XSSFWorkbook wb=new XSSFWorkbook();
XSSFSheet sheet =  wb.createSheet("new sheet");

XSSFRow rowhead=   sheet.createRow((short)0);
rowhead.createCell((short) 0).setCellValue("EMPNO");
rowhead.createCell((short) 1).setCellValue("ENAME");
rowhead.createCell((short) 2).setCellValue("JOB");
rowhead.createCell((short) 3).setCellValue("MGR");
rowhead.createCell((short) 4).setCellValue("HIREDATE");

Class.forName("oracle.jdbc.driver.OracleDriver");
  Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","pass");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("SELECT * FROM emp
WHERE HIREDATE
BETWEEN TO_DATE ('1980/12/17', 'yyyy/mm/dd')AND TO_DATE ('1981/02/20','yyyy/mm/dd')");
int i=1;
while(rs.next()){
XSSFRow row=   sheet.createRow((short)i);
row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt("empno")));
row.createCell((short) 1).setCellValue(rs.getString("ename"));
row.createCell((short) 2).setCellValue(rs.getString("job"));
row.createCell((short) 3).setCellValue(rs.getString("mgr"));
row.createCell((short) 4).setCellValue(rs.getString("hiredate"));
i++;
}

FileOutputStream fileOut =  new FileOutputStream(new File("data.xlsx"));
wb.write(fileOut);
fileOut.close();
System.out.println("Your excel file has been generated!");


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

}
}
}

I can't understand your problem.You already able to create sheet and make entry. — Kick
– Kick, Commented Jan 9, 2014 at 10:17
I am able to create a excel workbook with single sheet which holds data's for entire date range which i used in my query. but what i need is to create separate sheets for each date and pass values in to it. — Thiyagarajan
– Thiyagarajan, Commented Jan 9, 2014 at 10:30
Fire the sql corresponding to single date and create the sheet with name same as date and if found any result then add all the data in that sheet. — Kick
– Kick, Commented Jan 9, 2014 at 10:35

svz · Accepted Answer · 2014-01-09 10:29:24Z

1

First of all, I'd change your app design by separating code to fetch data and to write an excel file into different classes.
Second, after you get your result set, store the data in a Map. E.g. HashMap<Date, List<DbRow>> where DbRow is a class with fields empname, ename and so on. This way you'll have your data split into lists by hiredate.
After that, go through map values and add data from each list to a new worksheet.

answered Jan 9, 2014 at 10:29

svz

4,59811 gold badges44 silver badges66 bronze badges

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

2 Comments

Thiyagarajan Over a year ago

can you please give me some examples how to use HasMap in to my code and please clarify your point ,its confusing @svz

Thiyagarajan Over a year ago

i did try to do use Hash map it worked. Thank you. @svz

hashbrown · Accepted Answer · 2014-01-09 10:30:27Z

1

sheet 1 should contain only Date 1 values and sheet 2 should contain only date 2 values and so on till the given date range

Apparently you are calling wb.createSheet("new sheet") only once. If your requirement is to create multiple sheets based on date range, you should be calling the method multiple times inside the while loop block.

The query,

SELECT * FROM emp WHERE HIREDATE BETWEEN TO_DATE ('1980/12/17', 'yyyy/mm/dd')AND TO_DATE ('1981/02/20','yyyy/mm/dd')

Is returning you number of rows with different hiredates. Check these dates and appropriately place them in different sheets.

answered Jan 9, 2014 at 10:30

hashbrown

3,5461 gold badge23 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to create multiple sheets in a excel based on the values passed from output of query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related