create excel spreadsheet in .net web site

On an .aspx page in a web site I need to retrieve some data from a sql server database and, instead of displaying it in a Gridview, I need to put the data in a spreadsheet and present the user with a Save As dialog.

I'd prefer to do it without using the Office / Excel / Interop com object - so, from what I've read so far, I can do this by returning XML data from SQL Server?

Normally I get data from SQL Server and populate a dataset or datareader with it - and then use this as the Datasource of a GridView.

If I run this in SQL Server ...

Code:

---

SELECT UserID, UserName FROM tblUsers FOR XML Auto, Root('Users')

---

it produces this ...

Code:

---

<Users>
  <tblUsers UserID="1" UserName="Steve" />
  <tblUsers UserID="2" UserName="Andrea" />
  <tblUsers UserID="3" UserName="Zebedee" />
</Users>

---

So, first question - how do I get the data from SQL Server into my .aspx page? (All data access is done with stored procedures - so, from my data access class normally I create, for example, a SQLDataReader and populate it by calling a stored procedure. What object would I populate with the results of a stored procedure that contains a select statement with 'FOR XML Auto'

Then, assuming I get the XML into the .aspx page - how do I get it into a spreadsheet so that there are two columns called UserID and UserName and 3 rows with 1, 2 and 3 in the first cell and Steve, Andrea and Zebedee in teh second cell?

Thanks for any help. I've looked at loads of articles and am getting nowhere fast.

You can have a SQL Statement in ASP.NET that just executes a stored procedure, e.g.

MySQLCommand.CommandText = "EXEC mystoredproc"

and then use that to open a datareader or load a dataset.

As to output, what format do you need. If you just wrote that XML above to a file it would open in Excel and show the correct data. Probably with a prompt about schema.

Quote:

---

Originally Posted by SeanGrebey

You can have a SQL Statement in ASP.NET that just executes a stored procedure, e.g.

MySQLCommand.CommandText = "EXEC mystoredproc"

and then use that to open a datareader or load a dataset.

As to output, what format do you need. If you just wrote that XML above to a file it would open in Excel and show the correct data. Probably with a prompt about schema.

---

Thanks for your reply.

But is what is being returned from the database a recordset? Can it be used as the datasource of a sqldatareader (I will try now).

When you say ... if you just wrote that XML above to a file ... that, really, is my question - how do you do that?

Executing a stored proc that calls a select will return the same type of results as if you executed the select from your code itself. As to writing the XML to a file, you can use a streamwriter to write a temp file, and then use a Response.WriteFile and you'll get a Open/Save dialog. Here's an example of one I did with a .csv file:

Code:

---

        Response.Clear()
        Response.AddHeader("content-disposition", "attachment; filename=Schedule.csv")
        Response.ContentType = "application/csv"
        Response.WriteFile(strFileName)
        Response.End()

---

Where strFileName is the name of the file I created with my StreamWriter.

Quote:

---

Originally Posted by SeanGrebey

Executing a stored proc that calls a select will return the same type of results as if you executed the select from your code itself. As to writing the XML to a file, you can use a streamwriter to write a temp file, and then use a Response.WriteFile and you'll get a Open/Save dialog. Here's an example of one I did with a .csv file:

Code:

---

        Response.Clear()
        Response.AddHeader("content-disposition", "attachment; filename=Schedule.csv")
        Response.ContentType = "application/csv"
        Response.WriteFile(strFileName)
        Response.End()

---

Where strFileName is the name of the file I created with my StreamWriter.

---

I have done this ...

Code:

---

Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment;filename=excelfile1.xml");
        Response.ContentType = "application/excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        dg.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();

---

... and it works in the sense that it creates an excel spreadsheet okay - but the data is not correct. The XML is not constructed in such a way that I get a spreadsheet that looks like:
UserID....UserName
1...........Steve
2...........Andrea
3...........Zebedee

I was under the impression that doing this in SQL Server

SELECT UserID, UserName FROM tblUsers FOR XML Auto

would return the data with XML tags - formatted correctly. But, apparently not. I'm wondering what extra I need to do to get the data out of SQL Server as XML such that it will create a correct excel file.

Thanks again for your help.

What does the output look like offhand if you just open the XML file?

Quote:

---

Originally Posted by SeanGrebey

What does the output look like offhand if you just open the XML file?

---

Column1

1
2
3


So, five rows but only one cell. Top Row has 'Column1' in it - next row is empty, then '1', '2', '3' the next three rows.

I'll be honest I've never used that HTMLTextWriter before, so can't give you too much advice on why it is mangling your output. Maybe Gep will be along and has some suggestions.

Hello,

I have just had a quick read over this thread, and I am a little confused by what you are trying to achieve.

Is it possible that you can show all the code that you are using, rather than just snippets?

Gary

Quote:

---

Originally Posted by gep13

Hello,

I have just had a quick read over this thread, and I am a little confused by what you are trying to achieve.

Is it possible that you can show all the code that you are using, rather than just snippets?

Gary

---

What I am trying to achieve is to get data out of SQL Server as XML and use that data, in a .net web application to create an Excel spreasheet. So far I have managed to do it easily enough using 'normal' Sql Server output.

Let's say I have stored procedure called GetUsers and, within the stored procedure I have this select statement.

Code:

---

SELECT UserID, UserName FROM tblUsers WHERE OrganisationID = @OrganisationID

---

and I call that Stored Procedure from . net application and use it to populate a SqlDataReader like

Code:

---

DataAccess da = new DataAccess() //instance of my DataAccess class
SqlDataReader dr = da.GetUsers(OrganisationID);
dr.DataBind();
dr.NextResult; //this particular SqlDataReader has a few recordsets (not relevant))
DataGrid dg = new DataGrid();
dg.DataSource = dr;
dg.DataBind();

---

Having populated a DataGrid with the recordset that contains the Users for that organisation - I then do this ...

Code:

---

Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment;filename=excelfile1.xml");
        Response.ContentType = "application/excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        dg.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();

---

The second bit of that code I learnt here - and that is working fine - a box pops up asking if you want to save the Excel file and if you save and open the file it contains the details of the users - two columns called UserID and UserName and 3 rows with the relevant UserIDs and Usernames in there.

Now, really, my question is - how can I get the data out of SQL Server as XML and create an Excel spreadsheet because, I am hoping that doing it like that will not cause a warning box to pop up saying 'this file is not in the correct format - only open it if you trust it' - as I am on an intranet I don't want this box popping up.