Export GridView or SQL query to Excel WITH formulas 2

[LittlBUGer]

Hello. I'm sorry if this has been asked before, but I'm looking for a way to export either GridView data or data from a SQL query to Excel WITH formulas. I've done exports to Excel many times just fine from both data sources above without formulas, but now has come the time where clients are saying that vast amounts of information available in nifty spreadsheets isn't any good anymore unless the formulas are there as well so they can edit it at will.

Well, I've found a page with barely any explanation of how to create an HTML page with XML coding that will make Excel import the data with formulas but it seems impossibly difficult to make it work in my situation. I know there's a few commercial tools available to do what I need, but I'm on a $0 budget. Does anyone have any really good instructions or suggestions or anything related to this?

Though I'm currently using ASP.NET 2.0 and VB.NET along with Visual Studio 2005, the web project this exporting will probably go in is older and only uses ASP.NET 1.1. Server is Windows 2003 Server with SQL Server 2000.

Thank you for your help.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[ca8msm]

You'll have to do most of the wwork by looping through the records and writing out the relevant XML/HTML/Excel code. It's not pretty, but here's a method I've followed in the past:

http://jasonhaley.com/blog/archive/2004/03/20/9583.aspx

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ca8msm]

Alternatively, if the web server has Excel installed you could use it's object library.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[LittlBUGer]

That link you posted was the exact article I was talking about! With my reports though, because things are so dynamic or random, I'm thinking it would be very hard to do that and take way too much time, which I also don't have.

As for Excel's object library, please elaborate what you mean. Thank you.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

 

[ca8msm]

The object library is what Excel exposes to developers. You can add a reference to the Excel DLL and then have full control over creating a spreadsheet via code.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ca8msm]

A similar, but free, method would also be Carlos Ag's Excel XML Writer.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[AlexCuse]

If you can go the XML Spreadsheet route (saves as .XML, deffinitely opens with Excel 2003 and later), I think you may find this tool useful:

http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx

I would not mess around with the COM objects if you can avoid it, they are painfully slow.

Hope this helps,

Alex




[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[ca8msm]

Copy me if you want Alex


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[AlexCuse]

That last one's a good idea Mark ;-)

In addition to the slowness, using the excel libraries can be problematic when versions change, etc...

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[AlexCuse]

Hey, I could pick worse people to copy!

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[ca8msm]

Let me know when you find them!


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[LittlBUGer]

Thank you for the helpful link to that XML writer. I will see what I can do.

http://downloads.littlbuger.info

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein