Problem transfering datagrid to Excel

[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

 

[Alvin Bruney [MVP - ASP.NET]]

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

 

[nkunkov]

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

 

[Alvin Bruney [MVP - ASP.NET]]

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