Looking for example to export data from a DynPro table to Excel file

[Former Member]

Hello,

I have a certain content in a table.

I would like to export this content to Excel file and let the user save it on his PC. I tried to use the Office Controller but with no success.

Have someone already made something like this and can show me an example?

[Former Member · Accepted solution]

Hi

I already explained this in another post. Anyway i will cut and paste the same for you.Here you go.Sample way to create a Excel file and then download it to a local pc using a FileUI Download element.

We are developing a generic component for the same requirement which will download data to a excel from any table.

Anyway here is something which will give you some kind of idea as to how you can write the data from WebDynpro to a excel file.

For running this application

Ensure that the requisite JAR files are set for the Excel API's. See the blog link below.

Steps :

All your files in the machine running the WAS will be created in the default location

Alias : webdynpro/resources

Path : <Drive>:/usr/sap/J2E/JC00/j2ee/cluster/server0/temp/webdynpro/web

You can open your Visual Administrator and go to HTTP provider service and then create your own Alias. say

Alias : myAlias

Path : <Drive>:/mydirectory/myfiles

Once this is done restart your WebAS.

The code explained will perform the following.

There is a button UI element and a Download UI element. On click of the Action associated with the button ui element

The code below will create a excel file in the location mentioned.

On click of the download UI element you can view the excel file that was created.

Now create a New project in your NWDS

Follow the blog for importing the necessary jar files for Excel

/people/perumal.kanthan/blog/2005/03/21/reading-excel-data-from-java-using-hssf-api

Once that is done you can create say a button in your Webdynpro and associate it with a action.

Then you can write some code like the one below.

//Declare this in the end between the Begin others block.

private FileOutputStream out = null;

private HSSFWorkbook workBook = null;

private HSSFSheet hsSheet = null;

private HSSFRow row = null;

private HSSFCell cell = null;

private HSSFCellStyle cs = null;

private HSSFCellStyle cs1 = null;

private HSSFCellStyle cs2 = null;

private HSSFDataFormat dataFormat = null;

private HSSFFont f = null;

private HSSFFont f1 = null;

//Code to create the Excel.

public void onActionExportToExcel(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )

{

//@@begin onActionExportToExcel(ServerEvent)

try

{

out = new FileOutputStream("C:/mydirectory/myfiles/testexcel.xls");

workBook = new HSSFWorkbook();

hsSheet = workBook.createSheet("My Sheet");

cs = workBook.createCellStyle();

cs1 = workBook.createCellStyle();

cs2 = workBook.createCellStyle();

dataFormat = workBook.createDataFormat();

f = workBook.createFont();

f1 = workBook.createFont();

f.setFontHeightInPoints((short) 12);

// make it blue

f.setColor( (short)HSSFFont.COLOR_NORMAL );

// make it bold

// arial is the default font

f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// set font 2 to 10 point type

f1.setFontHeightInPoints((short) 10);

// make it red

f1.setColor( (short)HSSFFont.COLOR_RED );

// make it bold

f1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

f1.setStrikeout(true);

cs.setFont(f);

cs.setDataFormat(dataFormat.getFormat("#,##0.0"));

// set a thick border

cs2.setBorderBottom(cs2.BORDER_THICK);

// fill w fg fill color

cs2.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND);

cs2.setFillBackgroundColor((short)HSSFCellStyle.SOLID_FOREGROUND);

// set the cell format to text see HSSFDataFormat for a full list

cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

cs2.setFont(f1);

cs2.setLocked(true);

cs2.setWrapText(true);

row = hsSheet.createRow(0);

hsSheet.createFreezePane(0,1,1,1);

for(int i=1; i<10;i++)

{

cell = row.createCell((short)i);

cell.setCellValue("Excel Column "+i);

cell.setCellStyle(cs2);

}

workBook.write(out);

out.close();

//Read the file that was created.

FileInputStream fin = new FileInputStream("C:/mydirectory/myfiles/testexcel.xls");

byte b[] = new byte[fin.available()];

fin.read(b,0,b.length);

fin.close();

wdContext.currentContextElement().setDataContent(b);

}

catch(Exception e)

{

wdComponentAPI.getComponent().getMessageManager().reportException("Exception while reading file "+e,true);

}

//@@end

}

You can now add a Download UI Element to the layout.

Create a Context Attribute say "dataContent" and this should be of Type Binary.

The Data property of the File UI download element should be mapped to dataContent.

In the init method write code like

This is done for the download element.

IWDAttributeInfo attInfo = wdContext.getNodeInfo().getAttribute("dataContent");

IWDModifiableBinaryType binaryType = (IWDModifiableBinaryType) attInfo.getModifiableSimpleType();

//In addition the FileDownload UI element needs for defined resource types

binaryType.setFileName("testexcel.xls");

binaryType.setMimeType(WDWebResourceType.XLS);

Hope that helps you. Anyway its a sample and it should be helpful for you to see how you can modify it and of course maintain some standards in coding unlike the test sample given above :).

Let me know if you require further clarifications

regards

ravi

[Former Member · Accepted solution]

10Q very much for both of you. I'll go over this content and tell you in case I will encounter problems...

[Former Member · Accepted solution]

One question though: Where at the DynPro project I should put these jar files?

Roy

[Former Member · Accepted solution]

hi,

Right click on the project.. go to properties -> javabuildpath -> libraries -> add external jars

then add these jar files..

Now it will be available for reference for ur webdynpro project..

Regards

Bharathwaj

[Former Member · Accepted solution]

I'm sorry but my question wasn't clear enough: This procedure will cause the jars to be available locally for development. I would like to "ship" it to the server as well on deployment...

[Former Member · Accepted solution]

Hi,

Select a J2EE server component project and create a library project.Double click on server/provider xml file. Then say add jars and add your jar.go to the project and right click on it and say build library archive. This will give u a .sda file. Right click on the .sda file and deploy it to the J2EE server

Add one Library reference in the properties of the webdynpro project.

Library reference should be like this : sap.com/<Your Library project>

REGARDS

Bharathwaj

[Former Member · Accepted solution]

Hey Bharathwaj,

10X for the detailed answer.

One question though: In order to add the jars I must select them from an already different existing project. If I delete these jars from the other project, the library project can't reference these jars anymore. Why can't I add this jars to the library project as a "standalone" and don't let them be dependent on another project?

Regards,

Roy

[Former Member · Accepted solution]

Hey Ravi,

10X it is working gr8. One problem though: Let's say there are many people who are using this feature. Each "export" will override the other, now if 2 people will do it at the same time the data will not synchronized... I tried setting the export method as synchronized but it didn't help.

Any idea how to solve this?

Roy

[Former Member · Accepted solution]

Hi,

Eventhough we are selecting the jar file from some other project a copy of it is created in the .sda file.

Once you deploy this library project even if u undeploy the referred project there should not be any problem.

I am not sure why they have provided the feature of taking jars only from some other project ..Have to check with some SAP people..

Regards

Bharathwaj

Message was edited by: Bharathwaj R

[Former Member · Accepted solution]

Hey Bharathwaj,

Regarding the jar import: try to delete from your workspace the project you took the jars from and you'll see the Library project is having errors since it can't find the jars anymore. My point was, why creating another project just that the lirary project culd reference it. But as you said, this need to be checked with some SAP people...

And one more question: Do you know how can I reference this library project from non-DynPro projects, meaning, Portal application project and J2EE project...?

Regards,

Roy

[Former Member · Accepted solution]

Hi roy,

Another alternate method for your problem for shiping JARs with your webdynpro application.

What you can do is,

Go to the nevigator view and there choose your Webdynpro application. Now, In your application choose lib folder. Right click on it and choose import files. Here, choose your all required jars.

This will import all JARs in yor lib folder of your webdynpro project.

Now, add reference to these JARs in your project properties under Libraries tab.

So, when you rebuid and deploy the application these JARs go along with your application on the server.

Let me know the status,

Regards,

Bhavik

[Former Member · Accepted solution]

Hey Bhavik,

I've aleadr accomplished this mission thanx to Ravi and Bharathwaj help. I've decided to go with Bharathwaj's advice since I would like these jars to be available to other portal projects, but thanx for the tip, it's good to know...

Roy

[BeGanz]

Hi Roy,

next week we will publish a new Web Dynpro Tutorial which deals with exporting context data into Excel 2003 using the Web Dynpro Binary Cache service.

It has the following abstract:

<i>In many Web Dynpro application scenarios, dynamic data, which is not available at design time, must be accessible as a Web resource via a specific URL. In some cases, this URL points to an external Web resource, such as a dynamically created image stored externally in a MIME server. But how can we store and address a dynamic Web resource within the Web Dynpro runtime? In other words, is there a Web Dynpro caching and URL generation service for temporarily storing and addressing Web resources via URLs? The solution is provided by the Web Dynpro Binary Cache service.

Based on a simple Excel 2003 export scenario, we will demonstrate how to easily export context data into a generated XML Web resource, which is stored in the Web Dynpro Binary Cache. The user can then click a LinkToURL UI element to open this cached Web resource via an automatically generated Web resource URL.

The application architecture is based on a reusable Web Dynpro component encapsulating the complete Excel export functionality. It is embedded in the existing Web Dynpro table tutorial project.</i>

Regards, Bertram

[former_member318735]

Hi,

If the client uses ms excel whose version is below 2003 (e.g. 2000, 98), file opens as a xml file in ms excel. Is there a solution for this?

[Former Member]

hello i know this topic is old but i am having a very difficult time finding the proper jar files on that jakarta website. Can anyone direct me to those please? thanks

[Former Member]

Hi Roy,

Check the following blog "Create an excel file from JAVA using HSSF api"

/people/prakash.singh4/blog/2005/03/16/create-an-excel-file-from-java-using-hssf-api

In this blog Prakash Singh described how to create a excel file from JAVA.

Write a method to parse through the table node and read values of the necesary value attributes and populate the excel using the HSSF api.

Hope this help to solve your problem.

Regards,

Santhosh.C