Exporting Datagrid to Excel Spreadsheet

[markknowsley]

I have used the following code to export the content of a datagrid to an Excel spreadsheet:

Response.Clear();
Response.AddHeader("content disposition", "attachment;filename=myexcelspreadsheet.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
dgAllHRUsers.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();

When I compile the code I get the standard Windows file download box, and an error message saying that
"Internet Explorer cannot download 'filename.aspx' from localhost"

The address of the .aspx page containing the datagrid is

http://localhost/sitename/filename.aspx

Any ideas?

 

[bassguy]

I am doing the same thing except I build a table instead of the datagrid. seems to work for me. Here is relevant the code

protected override void Render(HtmlTextWriter writer)
		{
 
			Response.ContentType="application/vnd.ms-excel"; 
			Table _table = new Table();
			DataTable _data=Data;
			TableRow _row;
			TableCell _cell;
			_row = new TableRow();
			TableHeaderCell _header ;

			string[] headers = new string[6] {
												 CommonText.currentTIRC,
												 CommonText.previousTIRC,
												 CommonText.TIRCVariance,
												 CommonText.currentACV,
												 CommonText.previousACV,
												 CommonText.ACVVariance
											 };
			for (int index = 0 ; index<headers.Length;index++)
			{
				_header=new TableHeaderCell();
				_header.Text=Translate(headers[index]);
				_row.Cells.Add(_header);
			}
		 	
			_table.Rows.Add(_row);
			for (int _rowNumber=0;_rowNumber<_data.Rows.Count;_rowNumber++)
			{
				_row = new TableRow();
				for (int index = 0 ; index<headers.Length;index++)
				{
						_cell=new TableCell();
						_cell.Text=_data.Rows[_rowNumber][headers[index]].ToString();
						_row.Cells.Add(_cell);
				}
		 	 	_table.Rows.Add(_row);
			}
			 _table.RenderControl(writer) ;
			Response.End();
		}

Hope that helps

 

[markknowsley]

Getting the following compile error:

The type or namespace name 'CommonText' could not be found (are you missing a using directive or an assembly reference?)

There's no intellisense for CommonText, am I missing a vital 'using' statement at the top?

 

[markknowsley]

Sorry, I'm being deliberately stupid, I guess that the headers are unique to your project so I've replaced them with my own.

The compiler is also falling over on the
header.Text = Translate(headers[index]);
line, it doesn't like Translate.

Any ideas?

 

[bassguy]

oh, I am sorry, i did not realize your were going to try and run it. Translate is my own method to get stuff from the resource manager and the CommonText is just an alias to my constants class for my strings


here is a more generic version:

protected override void Render(HtmlTextWriter writer)
        {
 
            Response.ContentType="application/vnd.ms-excel"; 
            Table _table = new Table();
            DataTable _data=Data;
            TableRow _row;
            TableCell _cell;
            _row = new TableRow();
            TableHeaderCell _header ;

            string[] headers = new string[6] {
                                                 
"Column 1",
"Column 2",
"Column 3",
"Column 4",
"Column 5",
"Column 6"
                                             };
            for (int index = 0 ; index<headers.Length;index++)
            {
                _header=new TableHeaderCell();
                _header.Text=headers[index];
                _row.Cells.Add(_header);
            }
             
            _table.Rows.Add(_row);
            for (int _rowNumber=0;_rowNumber<_data.Rows.Count;_rowNumber++)
            {
                _row = new TableRow();
                for (int index = 0 ; index<headers.Length;index++)
                {
                        _cell=new TableCell();
                        _cell.Text=_data.Rows[_rowNumber][headers[index]].ToString();
                        _row.Cells.Add(_cell);
                }
                  _table.Rows.Add(_row);
            }
             _table.RenderControl(writer) ;
            Response.End();
        }

that should be a bit nicer for you

bassguy