cancel
Showing results for 
Search instead for 
Did you mean: 

How to export the table data to the excel?

Former Member
0 Kudos

Hi,

I'm new to WebDynpro. im having a table 5rows and 6 columns, i need the data in the table to be exported to excel sheet by clicking on a button.

As i went through some of the blogs and tutorials i couldn't understand the procedure, so could any one help out with detailed procedure.

Thanks & Regards,

Suresh

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi

See Wiki Code Gallery article <a href="https://wiki.sdn.sap.com/wiki/x/0mQ">Exporting Table Data Using On-Demand Streams - SAP NW 7.0</a>

Any doubts get back ...

Regards

Akshaya

Message was edited by: Bertram Ganz

Akshaya, at a first glance I did not see any difference of your copied version of my Code Gallery article. In case you applied some changes, corrections, improvements to my solution please apply the given wiki editing, commenting functions. I see no reason for not refering to this wiki article but extracting its content to this foum thread without any reference instead.

Former Member
0 Kudos

HI! Suresh,

This is a littile bit complicated process.Here i am trying to expalin the procedure,

if you are having any problem then let me know.

Following are the steps:--

1.create two controls one of type linkToAction and other of type FileDownload and a location on your portal server say it's "/server0/temp/webdynpro/web/local/testexcel.xls"

2.create a method in your view or controller and call this method as downLoadToXls()

3.create a Value attribute of type binary in your context,say it's dataContent.

4.Bind your linkToAction Control to the method downLoadToXls() and control FileDownLoad data property to dataContent.

5.In the init() Hook methos write the following code:--


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);

6.in DownLoadToXls() method add the following code:--


try {
        out = new FileOutputStream(path);
        
       workBook = new HSSFWorkbook();
      hsSheet = workBook.createSheet("UserID");
     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(false);
   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);
    hsSheet.createFreezePane(0,1,1,1);
    row = hsSheet.createRow(0);
     cell = row.createCell((short)0 ,0);
      cell.setCellValue("User ID");
      cell.setCellStyle(cs2);
      cell = row.createCell((short)1 ,1);
      cell.setCellValue("Last Changed Password Date");
      cell.setCellStyle(cs2);
    int nodeSize = wdContext.nodeUserIDAndPass().size();
				 
    for(int jCount=0; jCount < nodeSize; jCount++) {
	row = hsSheet.createRow(jCount+1);
	  for(int count = 0; count < 2; count++) {
	 if(count == 0) {
	cell = row.createCell((short)0,count);			 
cell.setCellValue(wdContext.nodeUserIDAndPass().getUserIDAndPassElementAt(jCount).getCtx_UserID());
					 }   
	 if(count == 1) {
	   cell = row.createCell((short)1,count);
  cell.setCellValue(wdContext.nodeUserIDAndPass().getUserIDAndPassElementAt(jCount).getCtx_ChangedPasswd());
 }
      cell.setCellStyle(cs2); 					   
   }
					   
  }
         			       			  	      workBook.write(out);
 out.close();

//Read the file that was created.
        FileInputStream fin = new FileInputStream(path);
        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); 			   }

7. Add the follwoing constant at the end:--


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;
	private String path = "/server0/temp/webdynpro/web/local/testexcel.xls";

8.Add the follwowing jars in your classPath.

1)poi-3.0.1-FINAL-20070705.jar

2)jxls.jar

regards,

Mithileshwar

Former Member
0 Kudos

Hi Mithileshwar,

Thanks for your reply, but i have a doubt that where you are getting the data's of the table? And for what two buttons(link to action n file download) are used, is it not possible to keep a single button and by clicking on that the data should be exported.

Thanks & Regards,

Suresh

Former Member
0 Kudos

Hi! Suresh,

it's doesn't matter from where i am populating the data for the table UI Element.you can populate the data of Table UI Element from UME DataBase or from R/3.

Now our second question.

we Need two link beacuse the first link will sent your Table Data from your view to your Portal server and the second link will fetch the data from server to client machine and this is the way the DownLoad link works.

regards,

Mithileshwar

BeGanz
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hallo Suresh,

have You already read my corresponding Wiki code galery article <a href="https://wiki.sdn.sap.com/wiki/x/0mQ">Exporting Table Data Using On-Demand Streams - SAP NW 7.0</a>? If you need any help or if you have any questions on this solution send it back to me.

Regards, Bertram

Former Member
0 Kudos

Hi,

Here is the jxl javadocs link. You will find it useful to create the Excel file.

<a href="http://jexcelapi.sourceforge.net/resources/javadocs/2_6/docs/index.html">http://jexcelapi.sourceforge.net/resources/javadocs/2_6/docs/index.html</a>

Thanks,

Prasanna.

Former Member
0 Kudos

Hi,

Try this link.

<a href="/people/subramanian.venkateswaran2/blog/2006/08/16/exporting-table-data-to-ms-excel-sheetenhanced-web-dynpro-binary-cache:///people/subramanian.venkateswaran2/blog/2006/08/16/exporting-table-data-to-ms-excel-sheetenhanced-web-dynpro-binary-cache

former_member751941
Active Contributor
0 Kudos

Hi Suresh,

Check this links.

/people/anilkumar.vippagunta2/blog/2007/02/04/office-control-in-webdynpro-ii

Regards,

Mithu