xlsgen > overview > Page Setup

 


The Page Setup dialog from Excel

The Page Setup dialog defines a number of options used for printing purposes. Excel maintains a separate Page Setup dialog for each worksheet of a workbook. In Excel, this dialog is available from File / Page Setup.

xlsgen supports a number of those options in the IXlsPageSetup interface available from the worksheet :

 

Paper Sizes

  "LETTER",               /* Letter 8 1/2 x 11 in               */
  "LETTERSMALL",          /* Letter Small 8 1/2 x 11 in         */
  "TABLOID",              /* Tabloid 11 x 17 in                 */
  "LEDGER",               /* Ledger 17 x 11 in                  */
  "LEGAL",                /* Legal 8 1/2 x 14 in                */
  "STATEMENT",            /* Statement 5 1/2 x 8 1/2 in         */
  "EXECUTIVE",            /* Executive 7 1/4 x 10 1/2 in        */
  "A3",                   /* A3 297 x 420 mm                    */
  "A4",                   /* A4 210 x 297 mm                    */
  "A4SMALL",              /* A4 Small 210 x 297 mm              */
  "A5",                   /* A5 148 x 210 mm                    */
  "B4",                   /* B4 (JIS) 250 x 354                 */
  "B5",                   /* B5 (JIS) 182 x 257 mm              */
  "FOLIO",                /* Folio 8 1/2 x 13 in                */
  "QUARTO",               /* Quarto 215 x 275 mm                */
  "10X14",                /* 10x14 in                           */
  "11X17",                /* 11x17 in                           */
  "NOTE",                 /* Note 8 1/2 x 11 in                 */
  "ENV_9",                /* Envelope #9 3 7/8 x 8 7/8          */
  "ENV_10",               /* Envelope #10 4 1/8 x 9 1/2         */
  "ENV_11",               /* Envelope #11 4 1/2 x 10 3/8        */
  "ENV_12",               /* Envelope #12 4 \276 x 11           */
  "ENV_14",               /* Envelope #14 5 x 11 1/2            */
  "CSHEET",               /* C size sheet                       */
  "DSHEET",               /* D size sheet                       */
  "ESHEET",               /* E size sheet                       */
  "ENV_DL",               /* Envelope DL 110 x 220mm            */
  "ENV_C5",               /* Envelope C5 162 x 229 mm           */
  "ENV_C3",               /* Envelope C3  324 x 458 mm          */
  "ENV_C4",               /* Envelope C4  229 x 324 mm          */
  "ENV_C6",               /* Envelope C6  114 x 162 mm          */
  "ENV_C65",              /* Envelope C65 114 x 229 mm          */
  "ENV_B4",               /* Envelope B4  250 x 353 mm          */
  "ENV_B5",               /* Envelope B5  176 x 250 mm          */
  "ENV_B6",               /* Envelope B6  176 x 125 mm          */
  "ENV_ITALY",            /* Envelope 110 x 230 mm              */
  "ENV_MONARCH",          /* Envelope Monarch 3.875 x 7.5 in    */
  "ENV_PERSONAL",         /* 6 3/4 Envelope 3 5/8 x 6 1/2 in    */
  "FANFOLD_US",           /* US Std Fanfold 14 7/8 x 11 in      */
  "FANFOLD_STD_GERMAN",   /* German Std Fanfold 8 1/2 x 12 in   */
  "FANFOLD_LGL_GERMAN",   /* German Legal Fanfold 8 1/2 x 13 in */
  "ISO_B4",               /* B4 (ISO) 250 x 353 mm              */
  "JAPANESE_POSTCARD",    /* Japanese Postcard 100 x 148 mm     */
  "9X11",                 /* 9 x 11 in                          */
  "10X11",                /* 10 x 11 in                         */
  "15X11",                /* 15 x 11 in                         */
  "ENV_INVITE",           /* Envelope Invite 220 x 220 mm       */
  "RESERVED_48",          /* RESERVED--DO NOT USE               */
  "RESERVED_49",          /* RESERVED--DO NOT USE               */
  "LETTER_EXTRA",         /* Letter Extra 9 \275 x 12 in        */
  "LEGAL_EXTRA",          /* Legal Extra 9 \275 x 15 in         */
  "TABLOID_EXTRA",        /* Tabloid Extra 11.69 x 18 in        */
  "A4_EXTRA",             /* A4 Extra 9.27 x 12.69 in           */
  "LETTER_TRANSVERSE",    /* Letter Transverse 8 \275 x 11 in   */
  "A4_TRANSVERSE",        /* A4 Transverse 210 x 297 mm         */
  "LETTER_EXTRA_TRANSVERSE", /* Letter Extra Transverse 9\275 x 12 in */
  "A_PLUS",               /* SuperA/SuperA/A4 227 x 356 mm      */
  "B_PLUS",               /* SuperB/SuperB/A3 305 x 487 mm      */
  "LETTER_PLUS",          /* Letter Plus 8.5 x 12.69 in         */
  "A4_PLUS",              /* A4 Plus 210 x 330 mm               */
  "A5_TRANSVERSE",        /* A5 Transverse 148 x 210 mm         */
  "B5_TRANSVERSE",        /* B5 (JIS) Transverse 182 x 257 mm   */
  "A3_EXTRA",             /* A3 Extra 322 x 445 mm              */
  "A5_EXTRA",             /* A5 Extra 174 x 235 mm              */
  "B5_EXTRA",             /* B5 (ISO) Extra 201 x 276 mm        */
  "A2",                   /* A2 420 x 594 mm                    */
  "A3_TRANSVERSE",        /* A3 Transverse 297 x 420 mm         */
  "A3_EXTRA_TRANSVERSE",  /* A3 Extra Transverse 322 x 445 mm   */
  "DBL_JAPANESE_POSTCARD",/* Japanese Double Postcard 200 x 148 mm */
  "A6",                   /* A6 105 x 148 mm                 */
  "JENV_KAKU2",           /* Japanese Envelope Kaku #2       */
  "JENV_KAKU3",           /* Japanese Envelope Kaku #3       */
  "JENV_CHOU3",           /* Japanese Envelope Chou #3       */
  "JENV_CHOU4",           /* Japanese Envelope Chou #4       */
  "LETTER_ROTATED",       /* Letter Rotated 11 x 8 1/2 11 in */
  "A3_ROTATED",           /* A3 Rotated 420 x 297 mm         */
  "A4_ROTATED",           /* A4 Rotated 297 x 210 mm         */
  "A5_ROTATED",           /* A5 Rotated 210 x 148 mm         */
  "B4_JIS_ROTATED",       /* B4 (JIS) Rotated 364 x 257 mm   */
  "B5_JIS_ROTATED",       /* B5 (JIS) Rotated 257 x 182 mm   */
  "JAPANESE_POSTCARD_ROTATED", /* Japanese Postcard Rotated 148 x 100 mm */
  "DBL_JAPANESE_POSTCARD_ROTATED", /* Double Japanese Postcard Rotated 148 x 200 mm */
  "A6_ROTATED",           /* A6 Rotated 148 x 105 mm         */
  "JENV_KAKU2_ROTATED",   /* Japanese Envelope Kaku #2 Rotated */
  "JENV_KAKU3_ROTATED",   /* Japanese Envelope Kaku #3 Rotated */
  "JENV_CHOU3_ROTATED",   /* Japanese Envelope Chou #3 Rotated */
  "JENV_CHOU4_ROTATED",   /* Japanese Envelope Chou #4 Rotated */
  "B6_JIS",               /* B6 (JIS) 128 x 182 mm           */
  "B6_JIS_ROTATED",       /* B6 (JIS) Rotated 182 x 128 mm   */
  "12X11",                /* 12 x 11 in                      */
  "JENV_YOU4",            /* Japanese Envelope You #4        */
  "JENV_YOU4_ROTATED",    /* Japanese Envelope You #4 Rotated*/
  "P16K",                 /* PRC 16K 146 x 215 mm            */
  "P32K",                 /* PRC 32K 97 x 151 mm             */
  "P32KBIG",              /* PRC 32K(Big) 97 x 151 mm        */
  "PENV_1",               /* PRC Envelope #1 102 x 165 mm    */
  "PENV_2",               /* PRC Envelope #2 102 x 176 mm    */
  "PENV_3",               /* PRC Envelope #3 125 x 176 mm    */
  "PENV_4",               /* PRC Envelope #4 110 x 208 mm    */
  "PENV_5",               /* PRC Envelope #5 110 x 220 mm    */
  "PENV_6",               /* PRC Envelope #6 120 x 230 mm    */
  "PENV_7",               /* PRC Envelope #7 160 x 230 mm    */
  "PENV_8",               /* PRC Envelope #8 120 x 309 mm    */
  "PENV_9",               /* PRC Envelope #9 229 x 324 mm    */
  "PENV_10",              /* PRC Envelope #10 324 x 458 mm   */
  "P16K_ROTATED",         /* PRC 16K Rotated                 */
  "P32K_ROTATED",         /* PRC 32K Rotated                 */
  "P32KBIG_ROTATED",      /* PRC 32K(Big) Rotated            */
  "PENV_1_ROTATED",       /* PRC Envelope #1 Rotated 165 x 102 mm */
  "PENV_2_ROTATED",       /* PRC Envelope #2 Rotated 176 x 102 mm */
  "PENV_3_ROTATED",       /* PRC Envelope #3 Rotated 176 x 125 mm */
  "PENV_4_ROTATED",       /* PRC Envelope #4 Rotated 208 x 110 mm */
  "PENV_5_ROTATED",       /* PRC Envelope #5 Rotated 220 x 110 mm */
  "PENV_6_ROTATED",       /* PRC Envelope #6 Rotated 230 x 120 mm */
  "PENV_7_ROTATED",       /* PRC Envelope #7 Rotated 230 x 160 mm */
  "PENV_8_ROTATED",       /* PRC Envelope #8 Rotated 309 x 120 mm */
  "PENV_9_ROTATED",       /* PRC Envelope #9 Rotated 324 x 229 mm */
  "PENV_10_ROTATED",      /* PRC Envelope #10 Rotated 458 x 324 mm */

 

Here is an example of source code :

Java code

XlsWorksheet wksht = wbk.AddWorksheet( "sheet1" );
wksht.getPageSetup().putPageOrientation(false);
wksht.getPageSetup().putPaperSize("LETTER");
wksht.getPageSetup().putHeader("&Ccenter header");
wksht.getPageSetup().putFooter("&R&P");
VB code

Dim wksht As IXlsWorksheet
Set wksht = wbk.AddWorksheet( "sheet1" )

wksht.PageSetup.PageOrientation = False
wksht.PageSetup.PaperSize = "LETTER"
wksht.PageSetup.PageHeader = "&Ccenter header"
wksht.PageSetup.PageFooter = "&R&P"
C# code

IXlsWorksheet wksht = wbk.AddWorksheet( "sheet1" );
wksht.PageSetup.PageOrientation = false;
wksht.PageSetup.PaperSize = "LETTER";
wksht.PageSetup.PageHeader = "&Ccenter header";
wksht.PageSetup.PageFooter "&R&P";
C/C++ code

xlsgen::IXlsWorksheetPtr wksht;
wksht = wbk->AddWorksheet( L"sheet1" );
wksht->PageSetup->PageOrientation = FALSE;
wksht->PageSetup->PaperSize = L"LETTER";
wksht->PageSetup->PageHeader = L"&Ccenter header";
wksht->PageSetup->PageFooter = L"&R&P";

 

Header/Footer

Excel uses an internal encoding to represent elements in header or a footer of a worksheet. A header or a footer is divided in three portions, left, center and right. Each portion supports a number of predefined tokens and content. It works as follows :

EncodingDescription
&LWhat's after this token applies to the portion on the left
&CWhat's after this token applies to the portion on the center
&RWhat's after this token applies to the portion on the right
&TInserts the time (using the user's computer regional settings)
&DInserts the date (using the user's computer regional settings)
&PInserts the current page number (possibly offset by the FirstPageNumber property, whose default value is 1).
&P+nnInserts the current page number (possibly offset by the FirstPageNumber property, whose default value is 1), plus number nn.
&P-nnInserts the current page number (possibly offset by the FirstPageNumber property, whose default value is 1), minus number nn.
&NInserts the total page number
&FInserts the Excel file name
&ZInserts the directory path to the Excel file name
&AInserts the worksheet name
&GInserts a graphic. The token is to be followed by the actual filepath of the graphic enclosed in double quotes, i.e. &G"c:\tmp\myheader.png". Please note the backslashes in the filepath may have to be doubled depending on your programming language.
&"fontname"Sets the font name and optional style to use for any content after that token. Examples : "Arial", "Arial,Bold", "Arial,Bold Italic"
&numberSets the font size in pixels for any content after that token. Example : &08
&KrrggbbSets the font color using red, green and blue components. For instance &KFF0000 is red.
&KTT+-SSSSets the font color using a theme color index (greater than 1) as well as a shade percent. For instance, to set the font color as theme index 6 with a shade of 30%, just use &K06+030.
&BSets the font bold, to use for any content after that token, or until there is another &B.
&ISets the font italic, to use for any content after that token, or until there is another &I.
&USets the font underline, to use for any content after that token, or until there is another &U.
&ESets the font double underline, to use for any content after that token, or until there is another &E.
&SSets the font strike-through, to use for any content after that token, or until there is another &S.
&XSets the font superscript, to use for any content after that token, or until there is another &X.
&YSets the font subscript, to use for any content after that token, or until there is another &Y.
&OTurns outline printing on or off (Macintosh only)
&HTurns shadow printing on or off (Macintosh only).

In order to obtain a header like this :


A centered picture in the header

you can use the following piece of source code (C++) :

C/C++ code

xlsgen::IXlsWorksheetPtr wksht;
wksht = wbk->AddWorksheet( L"sheet1" );
wksht->PageSetup->PageHeader = L"&C&G\"c:\\tmp\\seasight.gif\"";

xlsgen provides an easy way to write rich headers and footers without having to learn those tokens. It's the automatic source code generator. Just start Excel and manually create the header/footer that you'd like to generate. Save this Excel file. Then start the automatic source code generator that is part of the xlsgen package (xlscodegen.exe), and open that file there. It will produce the corresponding source code in the programming language of your choice!

 

xlsgen documentation. © ARsT Design all rights reserved.