SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Export to Multi-Tab Excel Odd Formatting

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Export to Multi-Tab Excel Odd Formatting

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:

mychart.PNG

SUMMARY tab:

Summary.PNG

The format of the columns in SAS EG:

SAS.png

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?


Accepted Solutions
Solution
‎10-21-2016 10:34 AM
Occasional Contributor
Posts: 14

Re: Export to Multi-Tab Excel Odd Formatting

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.

 

Capture.PNG

 

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.

View solution in original post


All Replies
Valued Guide
Posts: 505

Re: Export to Multi-Tab Excel Odd Formatting

 

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"); ');

 

Occasional Contributor
Posts: 14

Re: Export to Multi-Tab Excel Odd Formatting

Posted in reply to rogerjdeangelis

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.

Solution
‎10-21-2016 10:34 AM
Occasional Contributor
Posts: 14

Re: Export to Multi-Tab Excel Odd Formatting

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.

 

Capture.PNG

 

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 394 views
  • 0 likes
  • 2 in conversation