Thiyagarajan Thiyagarajan - 29 days ago 12
Java Question

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

# 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);

}
}
}

svz svz
Answer

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.