create excel spreadsheet in .net web site

[Webskater]

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 ...

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

it produces this ...

<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.

[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.

[Webskater]

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?

[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:

        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.

[Webskater]

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:

        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 ...

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.

[SeanGrebey]

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

[Webskater]

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.

[SeanGrebey]

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.

[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

[Webskater]

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.

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

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 ...

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.