[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Displaying ADO.NET DataSet in Excel through Browser

Posted on 2005-04-26
12
Medium Priority
?
1,918 Views
Last Modified: 2010-05-18
The following code is returning a blank excel spreadsheet.  If I change the ContentType to "text/plain" it shows me the data I want in table form, but I need it in Excel.  Can anyone help?

I've tried some things with xslt, but all the examples I've found have been C# and I can't get the syntax right with VB.NET.

'Now that you have the data; display it in excel format.
            If Not IsNothing(ds) Then
                Response.Clear()
                Response.Charset = ""
                Response.ContentType = "application/vnd.ms-excel"
                Dim stringWrite As New System.IO.StringWriter
                Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
                Dim dg As New DataGrid
                dg.DataSource = ds.Tables(0)
                dg.DataBind()
                dg.RenderControl(htmlWrite)
                Response.Write(stringWrite.ToString)
                Response.End()
            End If
0
Comment
Question by:jasonboetcher
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 800 total points
ID: 13872337
                 this.Response.Charset = "";
                  this.Response.ContentType = "application/vnd.ms-excel";
                  System.IO.StringWriter sw = new System.IO.StringWriter();
                  System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
                  DataGrid dg = this.Grid;
                  dg.GridLines = GridLines.None;
                  dg.HeaderStyle.Font.Bold = true;
                  dg.DataBind();
                  dg.RenderControl(htw);
                  this.Response.Write(sw.ToString());
                  this.Response.End();

I am doing this in C# and it works great seems rather similar ... gridlines.none I would hope not to cause this but ....

Greg
0
 
LVL 14

Assisted Solution

by:puranik_p
puranik_p earned 800 total points
ID: 13873330
I found this class and it works great. in VB.Net
To use it, just pass it a dataset and "response" object
eg.
Export.Convert(myData, Response)


'Class to convert a dataset to an html stream which can be used to display the dataset

'in MS Excel

'The Convert method is overloaded three times as follows

' 1) Default to first table in dataset

' 2) Pass an index to tell us which table in the dataset to use

' 3) Pass a table name to tell us which table in the dataset to use

Public Class DataSetToExcel

Public Shared Sub Convert(ByVal ds As DataSet, ByVal response As HttpResponse)

'first let's clean up the response.object

response.Clear()

response.Charset = ""

'set the response mime type for excel

response.ContentType = "application/vnd.ms-excel"

'create a string writer

Dim stringWrite As New System.IO.StringWriter()

'create an htmltextwriter which uses the stringwriter

Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

'instantiate a datagrid

Dim dg As New DataGrid()

'set the datagrid datasource to the dataset passed in

dg.DataSource = ds.Tables(0)

'bind the datagrid

dg.DataBind()

'tell the datagrid to render itself to our htmltextwriter

dg.RenderControl(htmlWrite)

'all that's left is to output the html

response.Write(stringWrite.ToString)

response.End()

End Sub

Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableIndex As Integer, ByVal response As HttpResponse)

'lets make sure a table actually exists at the passed in value

'if it is not call the base method

If TableIndex > ds.Tables.Count - 1 Then

Convert(ds, response)

End If

'we've got a good table so

'let's clean up the response.object

response.Clear()

response.Charset = ""

'set the response mime type for excel

response.ContentType = "application/vnd.ms-excel"

'create a string writer

Dim stringWrite As New System.IO.StringWriter()

'create an htmltextwriter which uses the stringwriter

Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

'instantiate a datagrid

Dim dg As New DataGrid()

'set the datagrid datasource to the dataset passed in

dg.DataSource = ds.Tables(TableIndex)

'bind the datagrid

dg.DataBind()

'tell the datagrid to render itself to our htmltextwriter

dg.RenderControl(htmlWrite)

'all that's left is to output the html

response.Write(stringWrite.ToString)

response.End()

End Sub

Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableName As String, ByVal response As HttpResponse)

'let's make sure the table name exists

'if it does not then call the default method

If ds.Tables(TableName) Is Nothing Then

Convert(ds, response)

End If

'we've got a good table so

'let's clean up the response.object

response.Clear()

response.Charset = ""

'set the response mime type for excel

response.ContentType = "application/vnd.ms-excel"

'create a string writer

Dim stringWrite As New System.IO.StringWriter()

'create an htmltextwriter which uses the stringwriter

Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

'instantiate a datagrid

Dim dg As New DataGrid()

'set the datagrid datasource to the dataset passed in

dg.DataSource = ds.Tables(TableName)

'bind the datagrid

dg.DataBind()

'tell the datagrid to render itself to our htmltextwriter

dg.RenderControl(htmlWrite)

'all that's left is to output the html

response.Write(stringWrite.ToString)

response.End()

End Sub

End Class
0
 

Author Comment

by:jasonboetcher
ID: 13874428
gregoryyoung,
I tried adding the line regarding gridlines and it had not affect; thanks for the suggestion though.

puranik_p,
My code is exactly the same as the first convert method that accepts a dataset and a response object.

What version of Excel are you guys using to get this to work?  I'm using Excel 2000.  When my code executes and Excel is launched I'm getting a blank worksheet; it doesn't even show any of the row/column lines; but I know the data is being passed correctly because when I change the type to "text/plain" the data displays in an html table fine.

Has anyone got this to work using a different method other than using a datagrid (and using VB.NET)?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 14

Expert Comment

by:puranik_p
ID: 13874481
Here's another approach..
You put the data in a datagrid which is then displayed in HTML table.
Then user clicks a button which opens up excel on his machine with data in the table.
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_21005946.html
Quite a long thread. search for 'This is purely client side script.' that's the code to be used.
0
 

Author Comment

by:jasonboetcher
ID: 13874629
puranik_p,

This looks like a reasonable approach but having the data return to a datagrid and then having them do an extra button click to bring up Excel is not acceptable for my requirements; I wish it was since it looks like a very good idea (I may be able to use this functionality in other parts of my system though).
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 13874722
okay. Now here's altogether another approach..
http://support.microsoft.com/default.aspx?scid=kb;en-us;319180

It works only with Excel 2002 (office xp) and above at client machine.
0
 

Author Comment

by:jasonboetcher
ID: 13874959
I'll see if I can upgrade to Excel 2002.

I'd like to keep this open to other suggestions; I'm still curious as to why the datagrid approach is not working for me.  Everything I have seen up to this point tells me I should be able to get that method to work.
0
 

Assisted Solution

by:MiddletownRI
MiddletownRI earned 400 total points
ID: 13877758
We have a series of reports that we provide to our users in ASP.NET and also offer an excel version of the same code using the following code in the Page_Load function:

Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False

The rest of our code is exactly how we would write it for ASP.NET, but we do not use Response Write to render the control:

'tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite)
'all that's left is to output the html
response.Write(stringWrite.ToString)
response.End()

We would instead declare the datagrid in the ASPX page with runat="server" and a Protected WithEvents declaration on the vb page, but you could do this a number of different ways.

DataGrid dg = this.Grid;
dg.GridLines = GridLines.None;
dg.HeaderStyle.Font.Bold = true;
dg.DataBind();
0
 

Author Comment

by:jasonboetcher
ID: 13896301
The datagrid approach seems to only work with Excel 2002 and higher.  It does not work with Excel 2000.  We've found this to not be acceptable so have decided to provide a csv formatted file which displays nicely in Excel.  Here is the code we are using:

            'Now that you have the data; display it in excel format by building csv.
            Dim sb As New StringBuilder
            sb.Append("")

            If Not IsNothing(ds) Then
                If ds.Tables(0).Rows.Count > 0 Then
                    'Create Column Headers Row
                    For Each col As DataColumn In ds.Tables(0).Rows(0).Table.Columns
                        sb.Append(col.ColumnName & ",")
                    Next
                    sb.Append(vbCrLf)

                    'Create Data Rows
                    For Each row As DataRow In ds.Tables(0).Rows
                        For i As Integer = 0 To row.ItemArray.Length - 1
                            sb.Append(row.Item(i) & ",")
                        Next
                        sb.Append(vbCrLf)
                    Next
                End If
            End If

            If sb.ToString.Length = 0 Then
                sb.Append("No Data")
            End If

            Response.Clear()
            Response.ContentType = "text/plain"
            Response.AppendHeader("content-disposition", "attachment; filename=" & _oReport.ReportName.Replace(" ", "") & ".csv")
            Response.Charset = ""
            Response.Write(sb.ToString)
            Response.End()
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 13896868
be careful with this ...

check DataColumn DataType to see if it is a string to handle things properly OR treat everything as a string otherwise string values with "," in them will make your routine break

here is an example that uses the all string method (also note the escaping of quotes

                  bool first = true;
                  DataSet ds = this.GetQueryData();
                  this.Response.Clear();
                  this.Response.ContentType = "application/txt";
                  this.Response.AddHeader("content-disposition", "filename=QueryOutput.csv");
                  System.IO.StreamWriter sw = new System.IO.StreamWriter(this.Response.OutputStream);
                  foreach(DataColumn dc in ds.Tables[0].Columns) {
                        //write header line
                        if(first) { first = false; } else { sw.Write(","); }
                        sw.Write("\"" + dc.ColumnName.Replace("\"", "\"\"") + "\"") ;
                  }
                  sw.Write("\n");
                  foreach(DataRow dr in ds.Tables[0].Rows) {
                        first = true;
                        foreach(DataColumn dc in ds.Tables[0].Columns) {
                              if(first) { first = false; } else { sw.Write(","); }
                              sw.Write("\"" + dr[dc].ToString().Replace("\"", "\"\"") + "\"") ;
                        }
                        sw.Write("\n");
                  }
                  this.Response.End();
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 13896886
also your code is creating an extra "," at the end of each line.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month19 days, 15 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question