[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Autosize all columns in all sheets
From:       Roger DeAngelis <rogerjdeangelis () GMAIL ! COM>
Date:       2017-02-27 20:50:51
Message-ID: 3763958050873887.WA.rogerjdeangelisgmail.com () listserv ! uga ! edu
[Download RAW message or body]

Autosize all columns in all sheets


inspired by
https://goo.gl/4uQciq
http://stackoverflow.com/questions/42488135/sas-ods-tagsets-excelxp-autofit-column-widths-calling-vbscript-within-sas



HAVE Excel workbook with two sheets (Tab1 is males and Tab2 is females)
====================================================================

SHEET(Males ist sheet) CLASS IN WORKBOOK D:/XLS/TWO_SEETS.XLSX


  +-----------------------------------------------------------------------------+
  |     A      |     B      |    C       |     D      |    E       |    F       |
  +-----------------------------------------------------------------------------+
1 | BIGNUM     |NAME        |SEX         |    AGE     |  HEIGHT    |  WEIGHT    |
  +------------+------------+------------+------------+------------+------------+
2 | 1.23E+09   | ALFRED     |    M       |    14      |    69      |  112.5     |
  +------------+------------+------------+------------+------------+------------+
   ...          ...
  +------------+------------+------------+------------+------------+------------+
N | 1.23E+09   | WILLIAM    |    M       |    15      |   66.5     |  112       |
  +------------+------------+------------+------------+------------+------------+

[males]

SHEET(Females 2nd sheet)

  +-----------------------------------------------------------------------------+
  |     A      |     A      |    B       |     C      |    D       |    E       |
  +-----------------------------------------------------------------------------+
1 | BIGNUM   |  NAME      |   SEX      |    AGE     |  HEIGHT    |  WEIGHT    |
  +------------+------------+------------+------------+------------+------------+
2 | 1.23E+09   | ALICE      |    F       |    14      |    69      |  112.5     |
  +------------+------------+------------+------------+------------+------------+
   ...          ...
  +------------+------------+------------+------------+------------+------------+
N | 1.23E+09   | BARBARA    |    F       |    15      |   66.5     |  112       |
  +------------+------------+------------+------------+------------+------------+

[females]

WANT  (autosize all columns)
===

HAVE Excel workbook with two sheets (Tab1 is males and Tab2 is females)
====================================================================

SHEET(Males ist sheet) CLASS IN WORKBOOK D:/XLS/TWO_SEETS.XLSX

SHEET(Females 2nd sheet)

  +----------------------------------------+
  |     A    |    A  | B | C |  D   |  E   |
  +----------------------------------------+
1 |  BIGNUM  | NAME  |SEX|AGE|HEIGHT|WEIGHT|
  +----------+-------+---+---+------+------+
2 |1234567890|AFRED  | M |14 |  69  |112.5 |
  +----------+-------+---+---+------+------+
   ...        ..
  +----------+-------+---+---+------+------+
N |1234567890|WILLIAM| M |15 | 66.5 | 112  |
  +----------+-------+---+---+------+------+


[males]

SHEET(Females 2nd sheet)

  +-----------------------------------------+
  |     A    |     A  | B | C |  D   |  E   |
  +-----------------------------------------+
1 |  BIGNUM  |  NAME  |SEX|AGE|HEIGHT|WEIGHT|
  +----------+--------+---+---+------+------+
2 |1234567890| ALICE  | F |14 |  69  |112.5 |
  +----------+--------+---+---+------+------+
   ...        ...
  +----------+--------+---+---+------+------+
N |1234567890| BARBARA| F |15 | 66.5 | 112  |
  +----------+--------+---+---+------+------+

[females]


WORKING CODE
============

setColumnWidth(wb, sheet = "males",column=1:6,  width = -1);
setColumnWidth(wb, sheet = "females",column=1:6,  width = -1);

FULL SOLUTION
=============

* create the workbook with two sheets;
%let fyl=d:/xls/mf.xlsx;
%utlfkil(&fyl);
libname xel "&fyl";
data xel.males xel.females;
  retain bignum 1234567890;
  set sashelp.class;
  if sex='M' then output xel.males;
  else output xel.females;

run;quit;
libname xel clear;

* autosize all colmns in the two sheets;
%utl_submit_r64('
library(XLConnect);
wb <- loadWorkbook("d:/xls/mf.xlsx", create = FALSE);
setColumnWidth(wb, sheet = "males",column=1:6,  width = -1);
setColumnWidth(wb, sheet = "females",column=1:6,  width = -1);
saveWorkbook(wb);
');


[prev in list] [next in list] [prev in thread] [next in thread]