I need to output a query to an Excel file and send e-mail

[RicardoPereira]

I need to output a query to an Excel file and send this file by e-mail. Anyone could give me a tip to do this?

Thanks

 

[sedj]

You'll need the JavaMail API (

http://www.java.sun.com)

and the POI API (

http://jakarta.apache.org/poi/)

 

[RicardoPereira]

I think it's better i start with the txt files. It is possible generate and keep an txt file with the output of a form?
How can i save and put a name in a file? without showing in the screen?

Thanks
Ricardo

 

[venur]

Hi Ricardo,

I haven't tried , let's try saving the output to an EXCEL file with Apache POI.

In order to do this you need to have the POI jar file from Apache.

Step 1: Generate the Results from the DB and store them into an ArrayList (any Collection Object).

Step 2: Create the File
FileOutputStream fileOut = new FileOutputStream("sendOutPutAsEmail.xls"

Setp 3: Create a HSSFWorkBook
HSSFWorkbook workBook = new HSSFWorkBook();
HSSFSheet sheet1 = workBook.createSheet("TestOutPut"

Setp 4: Create the Headers row in the XLS file. Use ResultSetMetaData of the Resultset

HSSFRow row = sheet1.createRow((short)0);
ResultSetMetaData columnNames = rs.getColumnNames();
for (int i=0; i<columnNames.size()+ 1; i++)
{
row.createCell((short)i).setCellValue(rsmd.getColumnName(i));
}

Setp 5: Load the data into the cells

ArrayList data = resultSetList;
// resultSetList is generated from Step 1
row = sheet1.createRow((short)(i+1));
for (int j=0; j<data.size(); j++)
{
String columnData = (String)data.get(j);
Row.createCell((short)j).setCellvalue(columnData);
}


Setp 6: Save the File and close

workBook.write(fileOut);
fileOut.close();

Setp 7: Attach the file you have generted in step 2 and send an E-Mail out.


Other way around. If your DB has the SMTP or POP setup you can actually send the E-Mail out form the DB. Generate an Excel file with SQL Query and send the file out.

Cheers,
Venu

 

[venur]

Hi,

Sorry I have't answer your question fully. Run a Backgroud Processor which extends thread or runnable. All this processor has to do is Listening. Once you receive the request from the User invoke this method which does the job for you.

If you think that there will be no chance of receiving mulitiple requests at a time then you can just write a simple java file.

Cheers,
Venu

 

[RicardoPereira]

How do i generate an Excel file with SQL Query ?
I think create a procedure in sql server that is triggered in JSP would be the best solution, but how do i do that?

PS. Venu, thanks for the previous answer.

Thanks
Ricardo Pereira

 

[venur]

Hi,

If you are using Oracle then you need to spool the data to a output file. The output file can be any thing could be any flat file (CSV,TXT, etc).

Ex:

set term off;
set heading off;
set linesize 400;
set feedback off;
set pageszie 0;
spool fileName.csv; // Spool the output to a file named fileName.csv
select col1 ||','|| col2
from oracle_table; // Select each column from table delimiting by a comma
spool off;

the fileName.csv will have the exported data in CSV file format (Excel). For orther DB's it might be different. After that run the E-Mail Procedure to read that file and send it out.

What DB are you using ?

Cheers,
Venu

 

[RicardoPereira]

I am using SQL server, it is the same thing?

 

[venur]

Hi,

It is not same for SQL Server. In SQL Server you need to use the BCP utility in order to store the date into a formatted file. It is a command line utility. You can execute this from command line.

I am really not sure how this works but, here is the SQL Query.

bcp &quot;SELECT au Iname FROM pubs..authors&quot; QUERYOUT C:\temp\testOut.txt -Usa -P -S <yourservername> -c&quot;

usding this SP you can
Exec master.dbo.xp_cmdshell '<above statement>'

Hope this helps you. But I pefer to use the Apache POI which is lot more easy to do if its Oracle or MySQL then I would have gone for the DB option.

Cheers,
Venu