cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Export to Excel from SAP Table

Former Member

on ‎05-02-2008 8:01 AM

0 Kudos

Hello Experts,

I am using MS Visual Studio 2005 and PDK 2.5 for .net.

I would like to know to how to export data from SAP Table to Excel format.

I am already refer this link.

Even I also able save the data in Excel.

But How to force user to for Open/Save dialog box, so he/she can decide further action.

Any help is appreciated and points will be awarded.

Regards

Sunil Pawar

Edited by: Sunil Pawar

Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (1)

Answers (1)

Dorian
Participant
‎05-02-2008 5:26 PM
0 Kudos

Hi Sunil:

In a nutshell, here's what you need to do.....

On the SAP side, see if there is an existing RFC-enabled function module that will read & write data from the table you want to access. If not, you need to create one. Then, for each function module, create a web service.

On the Visual Studio side, you need to install VSTO (Visual Studio Tools for Office).

Now you can create an Excel Spreadsheet project in Visual Studio. Within this project you create Web References to consume the Web Services you have created in SAP. The VSTO API's allow you to open spreadsheets, etc - anything a user can do can be programmed within VSTO.

My company actually has a product about to be announced that does exactly what you want to do. Here's a link to the product information:

http://www.soa-connect.com/product.html

Regards,

D.

Show replies
Show replies
Former Member
‎05-05-2008 6:39 AM
0 Kudos

Hi Dorian Salmon,

Thanks for your reply.

As you suggested your company product is dealing with exporting data from SAP Module to Excel.

But I am expecting to export data from SAP Enterprise Portal to MS Excel.

The same thing we can do with data grid which is very simple.Following is code snap......

Response.ContentType = "application/vnd.ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

DataGrid1.RenderControl(htw);

Response.Write(sw.ToString());

I would like to Export data from SAP Table(control in in PDK.NET suite ) to Excel not from data grid.

Please let me know if you have any further information.

Thanks

Sunil Pawar

Edited by: Sunil Pawar on May 5, 2008 11:34 AM

rima-sirich
Advisor
Advisor
‎05-05-2008 3:41 PM
0 Kudos

Hi Sunil,

I didn't test the proposed solution but it might work.

I think that you can use your own code to generate SAP Table html presentation:


if (dsDataSet.Tables.Count > ZERO)
{ 

string sTableStart = @"<HTML><BODY><TABLE Border=1>";
string sTableEnd = @"</TABLE></BODY></HTML>";
string sTHead = "<TR>";
StringBuilder sTableData = new StringBuilder();
foreach (DataColumn col in dsDataSet.Tables[0].Columns)
{
sTHead += @"<TH>" + col.ColumnName + @"</TH>";
}
sTHead += @"</TR>";
foreach (DataRow row in dsDataSet.Tables[0].Rows)
{
sTableData.Append(@"<TR>");
for (int i = 0; i < dsDataSet.Tables[0].Columns.Count; i++)
{
sTableData.Append(@"<TD>" + row.ToString() + @"</TD>");
}
sTableData.Append(@"</TR>");
}
string sTable = sTableStart + sTHead + sTableData.ToString() + sTableEnd;

and after that just write it to the response:


Response.ContentType = "application/vnd.ms-excel";
Response.Write(sTable);

If there are more than 1 iView in the Page, change it's isolation level from EMBEDDED to URL.

Regards,

Rima.

Former Member
‎05-06-2008 5:42 AM
0 Kudos

Hi Rima,

Thanks for your response.

Your code snip is correct if I use it in ASP.NET application.

But let me tell you in PDK for .NET Response.ContentType = "application/vnd.ms-excel"; is not seems to be work.

This only show your HTML created table as plan text and not prompt for Open/Save dialog box of Microsoft.

Following is sample code I am using in button click event ,you can copy and pate it to see the result .

/*  ******************************************************************************  */
protected void BtnExport_Action(object sender, AbstractButton.ActionEventArgs e)
{

DataTable dt = new DataTable();
DataColumn PropertiesCol = new DataColumn("Properties");
DataColumn ValueCol = new DataColumn("Value");
dt.Columns.AddRange(new DataColumn[] { PropertiesCol, ValueCol });
for (int i = 0; i < 20; i++)
 {
  dt.Rows.Add(new object[] { "TestName" + i.ToString(), "TestValue" + i.ToString() });
   }
string sTableStart = @"<HTML><BODY><TABLE Border=1>";
string sTableEnd = @"</TABLE></BODY></HTML>";
string sTHead = "<TR>";
StringBuilder sTableData = new StringBuilder();
foreach (DataColumn col in dt.Columns)
 {
  sTHead += @"<TH>" + col.ColumnName + @"</TH>";
 }
sTHead += @"</TR>";
 foreach (DataRow row in dt.Rows)
  {
   sTableData.Append(@"<TR>");
    for (int i = 0; i < dt.Columns.Count; i++)
     {
       sTableData.Append(@"<TD>" + row<i>.ToString() + @"</TD>");
     }
      sTableData.Append(@"</TR>");
   }
StringBuilder sTable=new StringBuilder(sTableStart+sTHead+sTableData.ToString()+sTableEnd);
       
//first let's clean up the response.object
Response.Clear();
//Set the type and filename   
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
//set the response mime type for excel
Response.ContentType = "application/x-msdownload";
//create a string writer
System.IO.StringWriter stringWrite = new System.IO.StringWriter(sTable);
//create an htmltextwriter which uses the stringwriter
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
//all that's left is to output the html 
Response.Write(stringWrite.ToString());            
Response.End();

}
 /*  ************************************************************************* *  */

Please let me know if you have any further information.

Thanks

Sunil Pawar

Edited by: Sunil Pawar on May 6, 2008 10:51 AM

Ask a Question