Problem transfering datagrid to Excel

  • Thread starter Thread starter nkunkov
  • Start date Start date
N

nkunkov

Hi,
I have read a lot of articles in this newsgroup about how to solve this
problem but found no solution. I'm trying to export a C# datagrid to
Excel file.
Here is my code that I have also found on google:

MyDataGrid.EnableViewState = false;
MyPage.Response.Clear();
MyPage.Response.Buffer = true;
MyPage.Response.AddHeader( "Content-disposition",
"filename="+reportName);
MyPage.Response.ContentType="application/vnd.ms-excel";
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
MyDataGrid.RenderControl(htmlWriter);
MyPage.Response.Write(stringWriter.ToString());
MyPage.Response.End();

The above works great when my datagrid has a few rows.
When I get a few hundred rows, Excel wouldn't open and I get page can't
be displayed error. It looks like it's a known problem and there are a
lot of questions like that in this newsgroup. The problem is that I
couldn't find an answer. Did anyone solve this?
I'm new to C# and .Net so bear with me if it's a stupid question.
Please, let me know if I can work around this somehow.
Thanks in advance.
NK
 
why the X-post? You are running into an excel limitation. About the easiest
thing you can do is page the datagrid so that it renders 20 rows and caches
the rest.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc
 
Alvin, thanks for you answer.
I will try to look at caching, I'm not sure yet how to implement it.
If you can point me to an example I'd greatly appreciate it.
I also worked out a different solution that lets me generate a file
while I generate a page.
I was wondering if you could take a look at the code below and let me
know if I have some glaring problems. The code works, I just want to
know if it's ok to use it or if has potential to cause problems for the
application.


StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
MyDataGrid.RenderControl(htmlWriter);
string FileName = "myfilename" + ".xls";
string temp_html_folder = ConfigurationSettings.AppSettings.Get
("temp_folder_path");
string sFullPath = temp_html_folder + FileName;
FileInfo file = new FileInfo(sFullPath);
if (file.Exists==true)
file.Delete();
TextWriter sWriter = File.CreateText(sFullPath);
sWriter.WriteLine(stringWriter.ToString()) ;
tmlWriter.Close();
sWriter.Flush();
sWriter.Close();


Is this a right way to output contents of the datagrid to a file?
Any potential problems with this code?

Your help is greatly appreciated.
Thanks
NK
why the X-post? You are running into an excel limitation. About the easiest
thing you can do is page the datagrid so that it renders 20 rows and caches
the rest.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc
Hi,
I have read a lot of articles in this newsgroup about how to solve this
problem but found no solution. I'm trying to export a C# datagrid to
Excel file.
Here is my code that I have also found on google:

MyDataGrid.EnableViewState = false;
MyPage.Response.Clear();
MyPage.Response.Buffer = true;
MyPage.Response.AddHeader( "Content-disposition",
"filename="+reportName);
MyPage.Response.ContentType="application/vnd.ms-excel";
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
MyDataGrid.RenderControl(htmlWriter);
MyPage.Response.Write(stringWriter.ToString());
MyPage.Response.End();

The above works great when my datagrid has a few rows.
When I get a few hundred rows, Excel wouldn't open and I get page can't
be displayed error. It looks like it's a known problem and there are a
lot of questions like that in this newsgroup. The problem is that I
couldn't find an answer. Did anyone solve this?
I'm new to C# and .Net so bear with me if it's a stupid question.
Please, let me know if I can work around this somehow.
Thanks in advance.
NK
Click to expand...
Click to expand...
 
you may want to wrap the delete call in an exception block. Deleting files
that are in use, locked or inaccessible will throw an exception causing a
memory leak or a crashed app. that's all i see for now.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc
Alvin, thanks for you answer.
I will try to look at caching, I'm not sure yet how to implement it.
If you can point me to an example I'd greatly appreciate it.
I also worked out a different solution that lets me generate a file
while I generate a page.
I was wondering if you could take a look at the code below and let me
know if I have some glaring problems. The code works, I just want to
know if it's ok to use it or if has potential to cause problems for the
application.


StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
MyDataGrid.RenderControl(htmlWriter);
string FileName = "myfilename" + ".xls";
string temp_html_folder = ConfigurationSettings.AppSettings.Get
("temp_folder_path");
string sFullPath = temp_html_folder + FileName;
FileInfo file = new FileInfo(sFullPath);
if (file.Exists==true)
file.Delete();
TextWriter sWriter = File.CreateText(sFullPath);
sWriter.WriteLine(stringWriter.ToString()) ;
tmlWriter.Close();
sWriter.Flush();
sWriter.Close();


Is this a right way to output contents of the datagrid to a file?
Any potential problems with this code?

Your help is greatly appreciated.
Thanks
NK
why the X-post? You are running into an excel limitation. About the
easiest
thing you can do is page the datagrid so that it renders 20 rows and
caches
the rest.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc
Hi,
I have read a lot of articles in this newsgroup about how to solve this
problem but found no solution. I'm trying to export a C# datagrid to
Excel file.
Here is my code that I have also found on google:

MyDataGrid.EnableViewState = false;
MyPage.Response.Clear();
MyPage.Response.Buffer = true;
MyPage.Response.AddHeader( "Content-disposition",
"filename="+reportName);
MyPage.Response.ContentType="application/vnd.ms-excel";
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
MyDataGrid.RenderControl(htmlWriter);
MyPage.Response.Write(stringWriter.ToString());
MyPage.Response.End();

The above works great when my datagrid has a few rows.
When I get a few hundred rows, Excel wouldn't open and I get page can't
be displayed error. It looks like it's a known problem and there are a
lot of questions like that in this newsgroup. The problem is that I
couldn't find an answer. Did anyone solve this?
I'm new to C# and .Net so bear with me if it's a stupid question.
Please, let me know if I can work around this somehow.
Thanks in advance.
NK
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Datagrid -> Excel? 2
export to excel 1
datagrid data to an Excel file 6
Export datagrid to Excel with a lot of rows 4
Export datagrid to Excel with a lot of rows 2
export dataset to excel 5
IE unable to open Excel stream 1
Internet Explorer unable to read Excel stream 1

Back
Top