Link to home
Get AccessLog in
Avatar of travishaberman
travishaberman

asked on

How do I populate an excel template with a SQL Server 2005 dataset?

Hi Experts!

I need to populate an exel file with lots of data using SQL Server 2005 and C#.  What's more, formatting of the file is an issue.  My guess is that you can build a visual template excell file, and then fill it with data.  Can someone provide me some code snipits or suedo code on how to do that?  If there is a website that expliclity addresses this, then that will work also.  Thanks Experts!  :)
Avatar of nutsch
nutsch
Flag of United States of America image

Check this question, https://www.experts-exchange.com/questions/23535385/Create-VBA-Code-inside-Excel-using-VBA.html?anchorAnswerId=22075772#a22075772. It could have most of what you need. This is a straight copy from a recordset to an excel template.

Also, when you say formatting is essential, is that straight formatting or do some parts of the recordset go someplace, while others go someplace else? In which case, you could transfer the recordset to an array and work from there in vba.

Thomas
Avatar of travishaberman
travishaberman

ASKER

I have a data view that will be copied column for column onto the exel file.  The formatting is some colors and some additional meta fields in the first, second, and third rows of the excel files.  That is, the recordset should come out the same, but have some additional information padded accross the top, and have some useful colors to distinguish stuff.  My thought is that I want a template, and then just have it filled with a dataview.  
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Oh, and just so you know, an insert statement will add to the bottom of the "known" rows...

e.g.

insert myexcel...Order_Worksheet$ (row,item,description,price,qty,discount)
values ('D10','Product3', 'Product 3 Description', 11.34, 12, 0 )

adds to row 20 in the above example...
Thank you!  :)