An Idea Exchange for SAS software and services

Comments
by Valued Guide
on ‎03-16-2017 10:20 AM
/* T0100640 Prevent ods excel from wrapping column data

Hits #39: Prevent ods excel from wrapping column data

   1. Without setting a nowrap style on each column, a template or separate widths.
   2. Using just one global option
   3. Set global option but allow some columns to use different styles but maintain nowrap
   4. Using R/Python or Perl to programatically autofit all columns after setting nowrap


HAVE EXCEL SHEET WITH WRAPPING (Excel sheet created by ods with wrapped lines)
=====================================================

  Numeric        GLC  Und
   Rep. for    Country  efi

  Continent  ID Number  ned  GLC Country Name     ISO Name for Country       Region
         91        180  044  BAHAMAS              BAHAMAS                    AMR
         91        227  084  BELIZE               BELIZE                     AMR
         91        260  124  CANADA               CANADA                     AMR
         91        295  188  COSTA RICA           COSTA RICA                 AMR
         91        300  192  CUBA                 CUBA                       AMR
                             DOMINICAN            DOMINICAN
         91        320  214  REPUBLIC             REPUBLIC                   AMR
         91        320  214  DOMINICAN REPUBLIC   DOMINICAN REPUBLIC         AMR
         91        330  222  EL SALVADOR          EL SALVADOR                AMR
         91        415  320  GUATEMALA            GUATEMALA                  AMR
         91        420  332  HAITI                HAITI                      AMR
         91        430  340  HONDURAS             HONDURAS                   AMR
         91        487  388  JAMAICA              JAMAICA                    AMR
         91        595  484  MEXICO               MEXICO                     AMR
         91        665  558  NICARAGUA            NICARAGUA                  AMR
         91        710  591  PANAMA               PANAMA                     AMR
         91        763  659  SAINT KITTS/NEVIS    SAINT KITTS AND
                                                  SAINT KITTS                AMR
         91        763  659                       AND NEVIS                  AMR
         91        926  840  UNITED STATES        UNITED STATES              AMR
                                                  ANTIGUA AND BARBUDA
         92        149  028  ANTIGUA/BARBUDA      BARBUDA                    AMR
         92        150  032  ARGENTINA            ARGENTINA                  AMR
         92        184  052  BARBADOS             BARBADOS                   AMR
         92        205  068  BOLIVIA              BOLIVIA                    AMR
         92        220  076  BRAZIL               BRAZIL                     AMR
         92        275  152  CHILE                CHILE                      AMR
         92        285  170  COLOMBIA             COLOMBIA                   AMR
         92        318  212  DOMINICA             DOMINICA                   AMR
         92        325  218  ECUADOR              ECUADOR


WANT NO WRAPPING
================

                       900
                         +
   Numeric        GLC  Und
  Rep. for    Country  efi
 Continent  ID Number  ned  GLC Country Name      ISO Name for Country        Region

        91        180  044  BAHAMAS               BAHAMAS                     AMR
        91        227  084  BELIZE                BELIZE                      AMR
        91        260  124  CANADA                CANADA                      AMR
        91        295  188  COSTA RICA            COSTA RICA                  AMR
        91        300  192  CUBA                  CUBA                        AMR
        91        320  214  DOMINICAN REPUBLIC    DOMINICAN REPUBLIC          AMR
        91        330  222  EL SALVADOR           EL SALVADOR                 AMR
        91        415  320  GUATEMALA             GUATEMALA                   AMR
        91        420  332  HAITI                 HAITI                       AMR
        91        430  340  HONDURAS              HONDURAS                    AMR
        91        487  388  JAMAICA               JAMAICA                     AMR
        91        595  484  MEXICO                MEXICO                      AMR
        91        665  558  NICARAGUA             NICARAGUA                   AMR
        91        710  591  PANAMA                PANAMA                      AMR
        91        763  659  SAINT KITTS/NEVIS     SAINT KITTS AND NEVIS       AMR
        91        926  840  UNITED STATES         UNITED STATES               AMR
        92        149  028  ANTIGUA/BARBUDA       ANTIGUA AND BARBUDA         AMR
        92        150  032  ARGENTINA             ARGENTINA                   AMR
        92        184  052  BARBADOS              BARBADOS                    AMR
        92        205  068  BOLIVIA               BOLIVIA                     AMR
        92        220  076  BRAZIL                BRAZIL                      AMR
        92        275  152  CHILE                 CHILE                       AMR


SOLUTION WORKING CODE ( you have to click on the first border to resize (or use R to do it

proc report data=demographics style(column)={tagattr='wraptext:no' width=100%};

FULL SOLUTION

* create PROBLEM SHEET;
data demographics;
  set sashelp.demographics(keep=cont id iso isoname name region gni pop);
run;quit;

* THIS ONE WRAPS;
%let fyl=d:/xls/dem_wrap.xlsx;
%utlfkil(&fyl);  * delete if exist;
ods excel file="&fyl";
proc report data=demographics;
run;quit;
ods excel close;


%let fyl=d:/xls/dem_nowrap.xlsx;
%utlfkil(&fyl);  * delete if exist;
ods excel file="&fyl";
proc report data=demographics style(column)={tagattr='wraptext:no' width=100%};
run;quit;
ods excel close;

YOU CAN EVEN CENTER ONE COLUMN AND UNWRAP

%let fyl=d:/xls/dem_nowrap_center.xlsx;
%utlfkil(&fyl);  * delete if exist;
ods excel file="&fyl";
proc report data=demographics style(column)={tagattr='wraptext:no' width=100%};
define cont / diaplay style={just=center};
run;quit;
ods excel close;

PRGRAMTICALLY AUTO RESIZE THE COLUMS(This will not lead to wrapping)

If you want to autofit all of the columns after nowrap
programatically, then you will need to use the win32com interface as follows:


Here is untested python code to autofit all columns
(I believe you can do this in R and PERL)

http://stackoverflow.com/questions/33665865/adjust-cell-width-in-excel
import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(r'file.xlsx')
ws = wb.Worksheets("Sheet1")
ws.Columns.AutoFit()
wb.Save()
excel.Application.Quit()

This can easily be done after you closed the file using your current xlsxwriter code.
Note, you might need to supply a full path to your file.
by Regular Contributor
‎03-16-2017 10:32 AM - edited ‎03-16-2017 01:44 PM

Exactly my point -- it is truly insane.  Thanks for the support.  Please vote up.

Idea Statuses
Top Liked Authors