Auto populating data into an existing Excel template

I was given an Excel template and have to automate filling it.

It is supposed to be uploaded every month into the grand central DB of our company. Grand central accepts nothing other than an Excel file with that template.

I tried reproducing the same using SSRS but it looks like SSRS has no control over rendering the looks and data in Excel (it keeps hiding columns and rows which is not acceptable). I have created a SQL conne开发者_开发知识库ction on the Excel to get the stored procedure data into Excel.

Now I have to make sure the data goes into the template into appropriate fields. I think it needs groupings and page breaks also.

Please help..

You're on the right track with dumping SSRS as a way to export into the template. I've also found that the Excel rendering extension in SSRS does a lot of hiding and merging cells with certain report designs.

You will probably end up needing to write a script in your language of choice to load the data into appropriate cells on the template.

Here are some Powershell articles on Excel automation:

http://blogs.technet.com/b/heyscriptingguy/archive/2006/09/08/how-can-i-use-windows-powershell-to-automate-microsoft-excel.aspx

http://winpowershell.blogspot.com/2007/03/powershell-and-excel-com-object.html

Also take a look at Perl and the Win32::OLE module:

https://metacpan.org/pod/Win32::OLE

http://www.perlmonks.org/?node_id=153486