Export to Multi-Tab Excel Odd Formatting

[titus]

I have an Excel template saved to our Unix server. The template has 4 tabs or sheets:
- Chart

- Valid

- Invalid

- Summary

When I export to Excel I'm overwriting the Valid, Invalid, and Summary tabs  with new data so that only the Chart tab remains from the original.

After export, I open the file and any numeric fields on the newly exported tabs seem to inherit the formatting from my Chart tab.

CHART tab:

SUMMARY tab:

The format of the columns in SAS EG:

And finally the code to export the files:

	%macro XLMultiTab(	sn= /*[Required] Sheet Name*/,
						fn= /*[Required] File Name*/,
						dn= /*[Required] Fully Qualified DataSet Name*/,
						fp=/users/apps/data/ /*[Optional] Default value is the docman_wfo directory*/);

	proc export
		data=&dn.
		outfile= "&fp.&fn..xlsx"
		dbms=xlsx replace;
		sheet="&sn.";
	run;

	%mend;

	/*CREATE EXCEL FILE*/
	%XLMultiTab(sn=Summary,	fn=ReindexReconciliation,	dn=Summary);
	%XLMultiTab(sn=Valid,	fn=ReindexReconciliation,	dn=VALID);
	%XLMultiTab(sn=Invalid,	fn=ReindexReconciliation,	dn=Invalid_DocIDs);

Any thoughts on why this is happening and how to resolve?

[rogerjdeangelis]

HAVE TWO FORMATATED TABS USING A TEMPLATE (SHEETS TABONE AND TABTWO)
(NOT SURE THIS HELPS, BUT I DONT HAVE ALL YOUR CODE)

TABONE (WEIGHT EXCEL FORMAT ##0)

Name Sex Age
Name      Sex      Age      Weight
Alfred      M      14.0      113
Alice       F      13.0      84
Barbara     F      13.0      98
Carol       F      14.0      103
Henry       M      14.0      103
James       M      12.0      83
Jane        F      12.0      85
Janet       F      15.0      113
Jeffrey     M      13.0      84
John        M      12.0      100
Joyce       F      11.0      51
Judy        F      14.0      90
Louise      F      12.0      77
Mary        F      15.0      112
Philip      M      16.0      150
Robert      M      12.0      128
Ronald      M      15.0      133
Thomas      M      11.0      85
William     M      15.0      112

TABONE

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


TABTWO  (WEIGHT EXCEL FORMAT ##0.#0 )

Name      Sex      Age      Weight
Alfred      M      14      112.50
Alice       F      13      84.0
Barbara     F      13      98.0
Carol       F      14      102.50
Henry       M      14      102.50
James       M      12      83.0
Jane        F      12      84.50
Janet       F      15      112.50
Jeffrey     M      13      84.0
John        M      12      99.50
Joyce       F      11      50.50
Judy        F      14      90.0
Louise      F      12      77.0
Mary        F      15      112.0
Philip      M      16      150.0
Robert      M      12      128.0
Ronald      M      15      133.0
Thomas      M      11      85.0
William     M      15      112.0


WANT ( UPDATE WEIGHT BUT KEEP ALL THE FORMATTING)

TABONE (SAME EXCEL FORMAT ##0)

Name Sex Age
Name      Sex      Age      Weight
Alfred      M      14.0      24
Alice       F      13.0      24
Barbara     F      13.0      24
Carol       F      14.0      24
Henry       M      14.0      24
James       M      12.0      24
Jane        F      12.0      24
Janet       F      15.0      24
Jeffrey     M      13.0      24
John        M      12.0      24
Joyce       F      11.0      24
Judy        F      14.0      24
Louise      F      12.0      24
Mary        F      15.0      24
Philip      M      16.0      24
Robert      M      12.0      24
Ronald      M      15.0      24
Thomas      M      11.0      24
William     M      15.0      24


TABTWO  (EXCEL FORMAT ##0.#0 )

Name      Sex      Age      Weight
Alfred      M      14      99.35
Alice       F      13      99.35
Barbara     F      13      99.35
Carol       F      14      99.35
Henry       M      14      99.35
James       M      12      99.35
Jane        F      12      99.35
Janet       F      15      99.35
Jeffrey     M      13      99.35
John        M      12      99.35
Joyce       F      11      99.35
Judy        F      14      99.35
Louise      F      12      99.35
Mary        F      15      99.35
Philip      M      16      99.35
Robert      M      12      99.35
Ronald      M      15      99.35
Thomas      M      11      99.35
William     M      15      99.35


SOLUTION

* CREATE HEAVILY FORMATTED WRKBOOK TO UPDATE WEIGHT)

%let excel=d:/xls/utl_twotab.xlsx;

%utlfkil(&excel);
ods listing close;
ods escapechar='^';
ods excel file="&excel";
ods excel options
 (
   start_at                   = "D3"
   tab_color                  = "yellow"
   autofilter                 = 'yes'
   orientation                = 'landscape'
   zoom                       = "100"
   suppress_bylines           = 'no'
   embedded_titles            = 'yes'
   embedded_footnotes         = 'yes'
   embed_titles_once          = 'yes'
   gridlines                  = 'yes'
   frozen_headers             = 'yes'
   absolute_column_width      =  "30pct,22pct,22pct,23pct"
   frozen_rowheaders          = 'yes'
  );
;run;quit;

ods excel options(sheet_name="tabone" sheet_interval="none");

title;footnote;
proc report data=sashelp.class nowd missing split='+'
    style(header)={font_size=13pt just=left font_face=Times}
    style(column)={font_size=11pt font_face=Times }
;
cols
  ( "Name Sex Age" name sex age weight );
define Name   /  'Name'   style={just=left};
define Sex    /  'Sex'    style={just=left};
define Age    /  'Age'    style={just=right tagattr='format:##0.#0'};
define Weight /  'Weight' style={just=right tagattr='format:##0'};
run;quit;

ods excel options
 (
   start_at                   = "F9"
   tab_color                  = "green"
   autofilter                 = 'no'
   orientation                = 'landscape'
   zoom                       = "100"
   suppress_bylines           = 'no'
   embedded_titles            = 'yes'
   embedded_footnotes         = 'yes'
   embed_titles_once          = 'yes'
   gridlines                  = 'no'
   frozen_headers             = 'no'
   absolute_column_width      =  "40pct,12pct,22pct,13pct"
   frozen_rowheaders          = 'yes'
  );
;run;quit;

ods excel options(sheet_name="tabtwo" sheet_interval="none");

title;footnote;
proc report data=sashelp.class nowd missing split='+'
    style(header)={font_size=13pt just=right font_face=Times}
    style(column)={font_size=11pt font_face=Times }
;
cols
  ( "Name Sex Age" name sex age weight );
define Name   /  'Name'   style={just=left};
define Sex    /  'Sex'    style={just=left};
define Age    /  'Age'    style={just=right tagattr='format:##0'};
define Weight /  'Weight' style={just=right tagattr='format:##0.#0'};
run;quit;

ods excel close;


libname sd1 "d:/sd1";
options validvarname=upcase;
data sd1.weight;
 set sashelp.class(keep=weight);
;run;quit;


* You can run this from IML;
%utl_submit_r64('
library(XLConnect);
library(haven);
weight<-read_sas("d:/sd1/weight.sas7bdat");
weight$WEIGHT<-23.546;
wb      <- loadWorkbook("d:/xls/utl_twotab.xlsx");
sheets <- getSheets(wb);
setStyleAction(wb,XLC$"STYLE_ACTION.NONE");
writeWorksheet ( wb,weight$WEIGHT,sheets[1],startRow=5,startCol=7,header = FALSE);
weight$WEIGHT<-99.346;
writeWorksheet ( wb,weight$WEIGHT,sheets[2],startRow=11,startCol=9,header = FALSE);
wb      <- saveWorkbook(wb,"d:/xls/utl_twotab.xlsx");
');

[titus]

Thanks for the response, Roger. However, as I understand the ODS method of creating an Excel file, it will always generate a new file instead of updating the existing file which will wipe out my Chart tab which needs to remain intact.

[titus · Accepted solution]

I played around with the files this morning and was unable to determine exactly which cell it was sourcing the numeric formatting from off of my Chart tab. I would determine it was one specific cell and would clear the formatting of that cell and then the next run the formatting would be back and sourcing from a different cell. It seemed to be choosing the cells at random. I even placed a dummy tab at the front of the workbook hoping that it would source the formatting from the dummy tab but it was still using the Chart tab formatting.

In my research, I discovered that an xlsx file is essentially just a ZIP file. So I changed my XLSX to a ZIP and then looked at the XMLs within. I'm not great with XML but I believe that what's happening is the XMLs store all the formats found within the workbook into defined formats for quick reference.

I believe that when SAS writes to the XLSX its defaulting the number formatting to one of these packaged formats. Perhaps someone with more XML knowledge would be able to modify the XML so that the desired numeric format is chosen as default, but that's not me.

Knowing that the format was coming from the XML, I changed my file format from XLSX to XLS and that has solved my formatting issue.