Export data to Excel

[Former Member]

Hello Experts,

I'm trying to develop an application to export content from an htmlb TableView to an Excel file. I've already implemented everything (including the export button). The thing is that I'm not working in a web dynpro project, so I can't use the IWDCachedWebResource object. I'm working in a enterprise portal project, and my question is, after I get the string that has all the information needed to export (instead of an xml file, i used an html table), how do i create the object to export? Including the URL and the link to that URL.

Thanks in advance

[Former Member]

Hallo Mauro,

I am also trying to develope a new Application by using which i have to Export  all  the data Table from XML to Excel. Can you please help me out with the code.

[Former Member]

I debugged this piece of code, to see where it terminates:

public void onClick(Event e)

{

ProxyDynPage p = this.getProxy();

IPortalComponentRequest request = p.getPortalComponentRequest();

IPortalComponentResponse response = p.getPortalComponentResponse();

try{

HttpServletResponse res = request.getServletResponse(true);

res.setContentType("application/vnd.ms-excel");

res.setHeader("Content-Disposition","attachment; filename=OrderStatusReport.xls");

OutputStream out = res.getOutputStream();

WritableWorkbook workbook = Workbook.createWorkbook(out);

workbook.write();

workbook.close();

}

catch(Exception exc){

}

}

My res variable isn't created, so it terminates when i try to instantiate workbook. Anybody has a clue why it doesn't get created?

[Former Member]

I've tried with both options (using JExcel and Poi) but still can't get it to go. When using Poi I can't instantiate my HSSFWorkbook variable (HSSFWorbook workbook = new HSSFWorkbook() gives an error). Same thing happens when using JExcel(WritableWorkbook workbook = Workbook.createWorkbook(res.getOutputStream());).

Edited by: babonhas8 on Aug 8, 2011 1:27 PM

[Former Member]

I have one question about the above method to solve my problem: Where do you get request?

[Former Member]

request is the instance of IPortalComponentRequest.

Lets say if it is an abstract portal component, this is available from the doContent() method..

public class <name> extends AbstractPortalComponent
{
    public void doContent(IPortalComponentRequest request, IPortalComponentResponse response)
    {

[Former Member]

Haven't tried it yet but I believe it fits exacty with what I need. My question is this "The API can also be used to send the workbook directly to an output stream eg. from a web server to the user's browser. If the HTTP header is set correctly, then this will launch Excel and display the generated spreadsheet." where do I change this HTTP header, in order that after the user clicks the export button, the browser asks if he wants to open or download the file?

[Former Member]

Hi,

Here are the steps using [Apache POI|http://poi.apache.org/download.html] library.

1. Make sure you have the servlet-2.3.jar and POI jars in your class path.

2. Write the following code to generate the Excel sheet:

		//Create a workbook
		HSSFWorkbook wb = new HSSFWorkbook();
		//Create an sheet attached to the workbook
		HSSFSheet  sheet = wb.createSheet("<name>");
		//create header
		HSSFRow row = sheet.createRow((short) 0);
		// Create all cells and put all value in it.
		row.createCell((short) 0).setCellValue(<column header>);

		//Create the rows
			row = sheet.createRow((short) 1);
			row.createCell((short) 0).setCellValue(<cell value>);

		HttpServletResponse response1 = request.getServletResponse(true);
		try {
			response1.setContentType("application/vnd.ms-excel");
			response1.setHeader("Content-Disposition",
				 "attachment; filename=<name>.xls");			
			wb.write(response1.getOutputStream());
		} catch (Exception e) {
			e.printStackTrace();
		}

Hope this helps.

[piyush_kumar6]

Hi ,

You can use JExcel Api for this , In sriptlet you can use java code

for details check this link : http://jexcelapi.sourceforge.net/

Regards,

Piyush