hi kranti
ur forming data coming according to criteria by firing queries.
To get this data into excel sheet.
use of plugin called POI -jakarta apache project.
I have less time to explain coz of delivery time at my end .... but u would
need to
1. import jar files in ur lib which u get from the POI package.Go to the
download on jakarta site.
2.Create a class which inputs ur data for ex an arraylist and out puts a
file.
READ THIS VERY CAREFULLY
//----------------------------------------------------------------CALL THIS
IN ACTION CLASS-------------------------------------------------
String strFileName = null;
strFileName = "Report_" + sReportName + ".xls";
new ReportTransformer().tranformDataToExcel(opFilePath + "/"
+ strFileName, arrMainArray);
response.setHeader("Content-type", "application/xls");
response.setHeader("Content-disposition",
"inline;filename=\"" + strFileName + "\"");
OutputStream oStream = response.getOutputStream();
int iChar;
FileInputStream fsStream = new FileInputStream(opFilePath +
"/" + strFileName);
while ((iChar = fsStream.read()) != -1)
{
oStream.write(iChar);
}
fsStream.close();
oStream.close();
File downloadedFile = new File(opFilePath + "/" +
strFileName);
downloadedFile.delete();
///-------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------
EXTRACT THE IMP STUFF---- PART OF CODE ----- INSTANCE OF THIS CLASS WHICH
GOES LIKE THIS
public class ReportTransformer {
String sXslFilePath = "";
public void tranformDataToExcel(String fileName,ArrayList arrMainArray)
{
FileOutputStream out;
HSSFWorkbook wb;
HSSFSheet excelSheet;
HSSFRow sheetRow;
HSSFCell rowCell;
HSSFCellStyle dataCellStyle;
HSSFFont dataFont;
HSSFCellStyle headerCellStyle;
HSSFFont headerFont;
HSSFCellStyle mainHeaderCellStyle;
HSSFFont mainHeaderFont;
int iRowCtr = 0;
short iHeaderCount = 0;
try
{
out = new FileOutputStream(fileName);
wb = new HSSFWorkbook();
excelSheet = wb.createSheet("Report");
// Data Style Definition
dataCellStyle = wb.createCellStyle();
dataFont = wb.createFont();
// Header Style Definition
headerCellStyle = wb.createCellStyle();
headerFont = wb.createFont();
headerCellStyle.setFont(headerFont);
// Main Header Style Definition
mainHeaderCellStyle = wb.createCellStyle();
mainHeaderFont = wb.createFont();
sheetRow = null;
rowCell = null;
//header info start
sheetRow = excelSheet.createRow(++iRowCtr);
rowCell = sheetRow.createCell(iHeaderCount++);
rowCell.setCellValue("S.No");
rowCell.setCellStyle(mainHeaderCellStyle);
rowCell = sheetRow.createCell(iHeaderCount++);
rowCell.setCellValue("Account Name");
rowCell.setCellStyle(mainHeaderCellStyle);
for(int iCount=0;iCount<arrMainArray.size();iCount++ )
{
sheetRow = excelSheet.createRow(++iRowCtr);
short iColCtr=0;
AdminBean objbean =
(AdminBean)arrMainArray.get(iCount);
rowCell = sheetRow.createCell(iColCtr++);
rowCell.setCellValue(iCount+1);
rowCell.setCellStyle(dataCellStyle);
rowCell = sheetRow.createCell(iColCtr++);
rowCell.setCellValue(objbean.getCompanyMemberId());
rowCell.setCellStyle(dataCellStyle);
iRowCtr++;
}
wb.write(out);
out.close();
}catch (Exception e){e.printStackTrace();}
}
/////-----------------------------------------------------------------------
---------------
I am sure u will get an idea what needs to be done
.so start now
SHILPA
-----Original Message-----
From: Kranti Parisa [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 28, 2004 2:18 PM
To: [EMAIL PROTECTED]
Subject: extracting data from oracle to excel
Hi Everybody,
need some information .....
the prob goes like this...
we have a jsp page with some select boxes n all...its a search criteria....
the contects of the select boxes should be dynamically changed....
after user clicks on go button... the report has to be generated with
that search criteria...
actually the prob is...
there will be a "export to excel" button...if user clicks that button ...
the generated report, based on the search criteria, should be exported
as an excel file at the server end and rendered to the user..
the generated excel file should be freezed one.. means the first
column should remains constant irrespective of the no of records
fetched...
so that the user can see the report properly...
can u tell me some way to think for the solution .....
waiting for the reply
--
--
Kranti Kiran Kumar Parisa
Software Engineer [ e-Biz ],
Patni Computer Systems Ltd.,
India
Mobile: +91 98504 45977
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
"This e-mail message may contain confidential, proprietary or legally privileged
information. It
should not be used by anyone who is not the original intended recipient. If you have
erroneously
received this message, please delete it immediately and notify the sender. The
recipient
acknowledges that ICICI Bank or its subsidiaries and associated companies,
(collectively "ICICI
Group"), are unable to exercise control or ensure or guarantee the integrity of/over
the contents of the information contained in e-mail transmissions and further
acknowledges that any views
expressed in this message are those of the individual sender and no binding nature of
the message shall be implied or assumed unless the sender does so expressly with due
authority of ICICI Group.Before opening any attachments please check them for viruses
and defects."
