Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS -> Excel Formatting Issue Expand / Collapse
Author
Message
Posted Thursday, February 14, 2013 1:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:23 AM
Points: 116, Visits: 189
Hi everyone

What is the best way to keep the formatting in Excel? I have created a template with the font, column types, graphs, sums etc and removed all the data.

When I export the data into the columns, the formatting is gone, font size is different and the SUM/Graphs does't auto update as I thought it would have.

Is there any easy way around this?

Most of the posts Ive read about this, talks about using a Script Task to do further formatting?

Thanks
Post #1419858
Posted Tuesday, February 19, 2013 8:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 13, 2014 9:50 PM
Points: 70, Visits: 27
I had to do this recently and my workaround was to have 1 row of formatted data retained under the column headings in my template but hidden.

However, that was a simple table of data without graphs and formulae. It did cause the correct formatting and data types to be applied to all lines inserted so it may work for you.
Post #1421915
Posted Tuesday, February 19, 2013 8:41 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:14 AM
Points: 647, Visits: 1,312
In addition to nicks suggestion, you can use script task as per the below link

http://stackoverflow.com/questions/1954582/ssis-2008-excel-2007-formatting-excel-columns-properly-exporting-not-importin

I have not used this , but will keep this in mind so may be useful one day


-- Roshan Joe
*******************************************
There are two types of DBAs. Those who has skills and those who have permissions
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1421918
Posted Tuesday, February 19, 2013 11:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:23 AM
Points: 116, Visits: 189
Hi everyone

Just to let you know what I did.

I created a XLS template with the headers and a hidden/formatted row, this just formats the actual numbers etc. You still need to apply the font-size via a script. The formulas also seem to auto run.

So I created a C# Script file that adds all the formatting and formulas afterwards. Works really well and takes out human intervention.

Only problem is that, that was the easiest report of the 25+ I need to do...
Post #1421935
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse