Multi sheet Excel output

[ramandeep1980]

Hi Sherman and everyone,

Is it possible to get multi- sheet output in Excel (with TABS at the bottom) and every TAB havng different

information.

Please do share your view.

Regards,

Raman

 

[newnetzeouser]

Hi Raman,

I hope the following code will help you.

Before trying the code download POI jar file.

• poi-3.2-FINAL-20081019.jar
• poi-contrib-3.2-FINAL-20081019.jar
• poi-scratchpad-3.2-FINAL-20081019.jar

 And incude them.

Regards,
Naga

Code:

package s;import java.io.*;import java.net.*;import javax.servlet.*;import javax.servlet.http.*;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.*;import java.io.*;import java.util.*;import java.sql.*;public class fullexcel extends HttpServlet {   Statement st,st1,st2;   ResultSet rs,rs1,rs2;    protected void processRequest(HttpServletRequest request, HttpServletResponse response)    throws ServletException, IOException {        response.setContentType("text/html;charset=UTF-8");          try {                    //First Connect to DB//Creating a New Book Value        HSSFWorkbook hwb = new HSSFWorkbook();//Requesting the paramaters                String bn = request.getParameter("an");        String an = request.getParameter("an");                int bid = 000;        int aptid = Integer.parseInt(request.getParameter("selectapt"));         String disname= "";         int disid=0;         String an = "";         int aid = 0;         double rate = 0.0;//Sql cmd to fetch id;st = con.createStatement();rs = st.executeQuery("select bid from bnc where bn='"+ bn +"'");if(rs.first()){//assin valuebid = Integer.parseInt(rs.getString("bid"));}int x = 0;st = con.createStatement();//fetch data from disrs = st.executeQuery("select * from dis order by disname");while(rs.next())//While one{        x++;        disname = rs.getString("disname");        disid = Integer.parseInt(rs.getString("disid"));//Creating a sheet in the work book        HSSFSheet sheet = hwb.createSheet(x  + "=" + disname);//Assin the width of the coloum        sheet.setColumnWidth(1, 10000);        sheet.setColumnWidth(2, 10000);//Ignore the next command if u dont want to protect ur sheet        sheet.protectSheet("passowrd");//Creating a rowhead        HSSFRow rowhead = sheet.createRow((short)0);//Assing the row head with the values        rowhead.createCell((short) 0).setCellValue("SNo");        rowhead.createCell((short) 1).setCellValue("Arean");        rowhead.createCell((short) 2).setCellValue("Fare From " + an + " apt");        int index=1;        int sno=0;  //Select the data for the particular sheet              st = con.createStatement();        rs1 = st.executeQuery("select a.an,b.ratevalue from area a,rate b where a.disid = " + disid +" and b.disid = " + disid +" and b.aid = a.aid and b.aptid = " + aptid +" and b.bid =" +bid +" order by a.an");          while(rs1.next())//While two            {                sno++;              //Placing the values one by one in the sheet.                HSSFRow row = sheet.createRow((short)index);                row.createCell((short) 0).setCellValue(sno);                row.createCell((short) 1).setCellValue(rs1.getString(1));                double ratev = Double.parseDouble(rs1.getString(2).trim());                 row.createCell((short) 2).setCellValue(Double.valueOf(ratev));              //The following line increase or move the cursor to next line	  index++;            }}//Assing a name to the sheet with .xlsString filename = "CompleteRate for-"+bn+".xls";//Make the created sheet avaliable to downloadresponse.setContentType("application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename="+ filename +"");ServletOutputStream out1 = response.getOutputStream();hwb.write(out1);out1.write(hwb.getBytes());out1.close();con.close();        }         catch(Exception exp)        {            PrintWriter out = response.getWriter();            out.print("Contact Admin : Error at Complete download : " + exp);        }    } 

[ramandeep1980]

Can some one give more details regarding this topic. That how we should be able to able to get excel with multi TAB report.

 

Raman