CODEWITHSUNDEEP
javaexcelapache-poi
happy
happy

Reputation: 2628

Multiple tables data exported to multiple worksheets of the same excel sheet in java

Below program exports the data of the single table in excel file.My question is if I have two tables then how to export its data in two different worksheets of the same excel file?

Suppose tab1 data in worksheet1 and tab2 data in worksheet2 of same excel sheet

public class CreateExcelFile{
        public static void main(String[]args){
    try{
    String filename="c:/data.xls" ;
    HSSFWorkbook hwb=new HSSFWorkbook();
    HSSFSheet sheet =  hwb.createSheet("new sheet");

    HSSFRow rowhead=   sheet.createRow((short)0);
    rowhead.createCell((short) 0).setCellValue("SNo");
    rowhead.createCell((short) 1).setCellValue("Name");
    rowhead.createCell((short) 2).setCellValue("Address");
    rowhead.createCell((short) 3).setCellValue("Contact No");
    rowhead.createCell((short) 4).setCellValue("E-mail");

    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:456/test", "root", "root");
    Statement st=con.createStatement();
    ResultSet rs=st.executeQuery("Select * from employee");
    int i=1;
    while(rs.next()){
    HSSFRow row=   sheet.createRow((short)i);
    row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt("id")));
    row.createCell((short) 1).setCellValue(rs.getString("name"));
    row.createCell((short) 2).setCellValue(rs.getString("address"));
    row.createCell((short) 3).setCellValue(Integer.toString(rs.getInt("contactNo")));
    row.createCell((short) 4).setCellValue(rs.getString("email"));
    i++;
    }
    FileOutputStream fileOut =  new FileOutputStream(filename);
    hwb.write(fileOut);
    fileOut.close();
    System.out.println("Your excel file has been generated!");

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

    }
        }
    }

Upvotes: 0

Views: 8703

Answers (1)

Trace
Trace

Reputation: 18889

Not sure if I get the question right, and I am also not a Java programmer, but here's my theory which seems logical: 

HSSFWorkbook hwb=new HSSFWorkbook(); 
HSSFSheet sheet =  hwb.createSheet("new sheet");

Defines a new workbook / sheet object via the Java API provided (with "new sheet" being the name of the sheet). If you want to define a second sheet, then you should simply be able to: 

HSSFSheet sheet_2 =  hwb.createSheet("new_sheet_2");

which creates and defines a new Sheet Object.
Then when you: 

HSSFRow rowhead=   sheet.createRow((short)0);

Obviously, you need to use the second sheet object you created, Sheet_2. The same applies to the rest of the code and when building the table. When writing to the file, I'm quite positive that you should get the result you expect.

Upvotes: 1

Related Questions