submit a requirement - schedschd.ws/hosted_files/commons17/55/fast modern excel (xlsx) workb… ·...

SUBMIT A REQUIREMENT

• Be part of the voice to IBM – help enhance the IBM i and AIX

operating systems

• Submit feature requests and requirements to IBM

Submit online at

https://ibm.co/

2oLNF3v

or scan this

QR code

1

Submit online at

requirements.

common.org

or scan this

QR code

Submit COMMON Requirement

for IBM i on IBM RFE site:

Submit COMMON Requirement

for AIX on COMMON site:

Presented by :

Vern Hamberg Senior IT Developer

Pest Elimination NA

Ecolab

(651) 250-4581

[email protected]

Fast Modern Excel

(XLSX) Workbook

Creation Using RPG –

Advanced Topics

Session 170434

Turks 3, Turks Level

Tuesday, May 9, 2017

2:00 PM

Ø Easy EXCEL features you can use

Ø Can I use the _____ Excel feature?

Ø Grouping & outlining

Ø Shared strings

Ø Optimization

Ø Oh, no! The dreaded “unreadable content”!

Ø Tools

Ø Resources

Agenda

Easy EXCEL features you can use


Ø “Easy”?

• Include them in original or model workbook

o Set’m up in Excel and use’m in your report

• Can often be added or modified after template creation

Ø Catalog of features

• Active tab

• Active cell

• Merged cells

• Autofilter

• Frozen heading

• Ignored errors

• Conditional formatting


Ø Active tab

• Sheet user sees first when opening workbook

• Sheet that was open when saving workbook

• Specified in workbook part (workbook.xml)

o <workbookView> element

o Zero-based index – 2nd sheet has value of 1

o Default is 0 (1st sheet)

Zero-based index of

sheet displayed on open


Ø Active cell

• Cell currently selected in a sheet

• Displayed with heavy border around it

• Cell that was selected when saving workbook

• Specified in “header” of sheet part (sheet#.xml)

o <sheetView> element

o Default is A1 (or upper-left-most non-frozen cell)

Cell reference


Ø Merged cells

• Multiple cells combined and treated as one

• Keeps upper-left-most data (and style) only

• Useful for automatic centering of header lines

• Useful for complex layout

• Specified in “footer” of sheet part (sheet#.xml)

o <mergeCells> element

Cell reference

Different values

and styles One value and

same style


Ø Autofilter

• Lets you view specific rows based on cell contents

• Specified in “footer” of sheet

part (sheet#.xml)

o <autoFilter> element

Update lower-right

corner with row count

Cells range


Ø Frozen heading

• Lets you always see the heading when scrolling

• Nice visibility feature

• Specified in “header” of sheet part (sheet#.xml)

o <sheetView> element

1st cell BELOW frozen row

Heading always

visible

Ø Rows or columns or both can be frozen

• Might be easiest to let Excel set these up


Ø Ignored errors

• Certain errors are marked with green error indicator in upper

left corner of cell

• Select cell and hover over yellow warning sign to see error

• Many of these so-called errors really are just distractions

• Click on down-arrow next to yellow warning sign to see actions

Error

indicator

Error

description

“Ignore Error”

action

Ø Ignored errors (cont.)

• Common errors

o numberStoredAsText / formula / formulaRange

o Ignore for entire content

• Other errors

o evalError / twoDigitTextYear / unlockedFormula /

o emptyCellReference / listDataValidation / calculatedColumn


o <ignoredErrors> element


Range where errors

shall be ignored

List of errors

to ignore

Update lower-right

corner with row count


Ø Conditional formatting

• Change a cell’s appearance based on its value

• Quick way to visualize data

• Configure using “Conditional Formatting” button in Excel


o <conditionalFormatting> element


Ø Conditional formatting (cont.) Range where conditional

formatting is in effect

Rule consists of

1) kind of operator

2) parameters

3) priority of application

4) reference to style

• Styles specified in styles part (styles.xml)

o <dxf> elements

Can I use the _____ Excel feature?

Ø What we know

• XLSX files are a ZIP’d package of XML files

• Any XML file CAN be generated

o Does the benefit justify the effort?

Ø How to determine what is feasible

• Create a basic, default workbook in Excel

• Examine the contents of the default workbook

• Add a feature to the default workbook

• Compare the new content to the default

• Assess effort required to implement the feature


An expanding acquaintance with

the Office Open XML specification

will serve one well!

Ø Create a basic, default workbook in Excel

• Open Excel

• Immediately save the workbook

• Close Excel


Ø Examine the contents of the default workbook

• Use Windows Explorer or 7-Zip or equivalent to extract contents

to a folder


Ø Add feature to the default workbook

• Open workbook with Excel

• Add the feature in question (a logo)

• Save the workbook

• Close Excel


Ø Compare new content to default

• Extract contents to a different folder

than default

• Take note of new content


Image file of logo

Describes use

of image file

Relationship of

drawing part and sheet

Relationship of image

and drawing part

Ø Assess effort required to implement the feature

• Effort to add logo to all sheets could be minimal

o Copy sheet1.xml.rels file for each additional sheet

o Add items to [Content_Types].xml as needed

o Copy drawing part or ensure relationships be in place for each

additional sheet

• If number of sheets is static, this can be one-time effort


Grouping & outlining

Ø Used to group and summarize data

Ø Used to drill down to deeper detail in each level

Ø Up to 8 levels

Ø Similar to control levels

Ø Subtotals and totals are not accumulated

• Excel’s SUBTOTAL() function used instead

o Keep track of start and end of rows to subtotal/total


Keep Your Eye

on the Prize!

Ø Want to understand how grouping & outlining looks in XML

• Use “Subtotal” button to build groups


• Sort data to be grouped

• Run “Subtotal” from most to least selective

• Save workbook and open with 7-Zip

• Look for “outlineLevel” in attributes in the sheet part

o <sheetFormatPr> element, “outlineLevelRow” attribute

o <row> element, “outlineLevel” attribute


Ø Original data to be grouped

• Sorted by Region & State

Ø XML for original data

• No outline attributes

• Header row will not be grouped

• Data rows will be grouped


Ø Group & subtotal by Region (most selective – control level L2)


Ø Group & subtotal by State (less selective – control level L1)

Ø XML for grouped data

• Number of outline levels

• Data rows are grouped

o Deepest outline level (detail)

o Next higher outline level (control level L1) with subtotal

o Highest outline level (control level L2) with subtotal

o Grand total (LR)

Grouping & outlining Matches up with last 3

outline symbols

Range extends to previous row

Range does not include previous subtotal row

Range does not include previous 2 subtotal rows


Ø Workbook can open with some levels collapsed

• Set this up in Excel and save workbook to configure this

• Additional <row> attributes

o hidden

ü Outline levels 3 & 2 (Month & State)

o collapsed

ü Outline level 1 (Region)

o Both attributes might be used together in the same row

Shared strings

Ø Contains one occurrence of each unique string that occurs on

all worksheets in a workbook

• Only 1 Shared String Table part (sharedStrings.xml) in workbook

• Improves performance by reading each string just once

• Can optimize space utilization significantly

Shared String Table

Total # of strings

in workbook

Total # of unique

strings in Shared

String Table

Shared String Table

Ø How text content is specified in cells (<c> element)

• Data type attribute à t=“s”

• Value (<v> element) à 0-based index into shared strings array

Shared String Table

Shared String Table Worksheet data

Data type attribute

Ø Creating a Shared String Table

• Might seem daunting & impractical at first

• But after dreaming about it for a couple years, turns out it can

be done quite easily with a work file and *SRVPGM

o Shared strings work file (SHRSTRWK) keyed on string A R RLSHRSTRWK TEXT('XLSX shared string work file') A SHRDSTR 4096A B TEXT('Shared string value') A VARLEN(50) A SHRDIDX 15P 0B TEXT('Shared string index') A K SHRDSTR

o Shared strings work file LF (SHRSTRWKL1) keyed on index A R RLSHRSTRWK PFILE(SHRSTRWK) A K SHRDIDX

Shared String Table

Source code will be

available for download from

the conference schedule page

Ø Creating a Shared String Table (cont.)

• Shared strings *SRVPGM

o Exports

ü initializeSharedStrings DCL-PR initializeSharedStrings EXTPROC('initializeSharedStrings'); pFixedSharedStrings VARCHAR(100) CONST; pCount LIKE(stringCount); pUniqueCount LIKE(uniqueStringCount); END-PR ;

ü getSharedString DCL-PR getSharedString VARCHAR(15) EXTPROC('getSharedString'); pString LIKE(SSWds.SHRDSTR) CONST; pCount LIKE(stringCount); pUniqueCount LIKE(uniqueStringCount); END-PR ;

ü writeSharedStrings DCL-PR writeSharedStrings EXTPROC('writeSharedStrings'); pCount LIKE(stringCount); pUniqueCount LIKE(uniqueStringCount); pSharedStringsXML VARCHAR(100) CONST; END-PR ;

Shared String Table


• Shared strings *SRVPGM (cont.)

o Other

ü addSharedString DCL-PR addSharedString PACKED(15 : 0); pString VARCHAR(4096) CONST; pIndex PACKED(15 : 0); END-PR ;

• Prepare CGIDEV2 template for shared strings

o Use “unusual” numeric marker for variable text in model XLSX

ü Excel puts ALL text into the Shared Strings Table

ü We don’t want extra string items in the Table

ü Excel might still put numbers in the table, so check the results,

remove extra string items, and adjust <v> elements for shared-

string types, as needed

Shared String Table


• Prepare CGIDEV2 template for shared strings (cont.)

o Copy extracted sharedStrings.xml to Templates folder

ü Name it sharedStrings-fixed.xml – anything, so long as it’s an XML

ü Use that name in the pFixedSharedStrings parameter of the

initializeSharedStrings() procedure

ü The initializeSharedStrings() procedure needs only the count and

uniqueCount attributes from the fixed XML

Shared String Table

o Make template from sharedString.xml in Templates folder

ü Name it sharedStrings.xml.tpl

ü Add CGIDEV2 header section before line 1



ü Add CGIDEV2 variable for count & uniqueCount

count="“

uniqueCount=""

Shared String Table

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

<sst uniqueCount="4" count="4"

xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006

/main">

<si><t>Region</t></si>

<si><t>Month</t></si>

<si><t>Sales</t></si>

<si><t>State</t></si>

</sst>



o Make template from sharedString.xml in Templates folder (cont.)

ü Add CGIDEV2 body sections just before closing </sst> tag



<si><t></t></si>



<si><t xml:space="preserve"></t></si>

ü Add CGIDEV2 footer section just before closing </sst> tag



Shared String Table



Used for strings

without leading blanks



Used for strings with

leading blanks

o Make template from sharedString.xml in Templates folder (cont.)

Shared String Table



<?xml version="1.0" encoding="UTF-8" standalone="true"?>

<sst uniqueCount="" count=""

xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<si><t>Region</t></si>

<si><t>Month</t></si>

<si><t>Sales</t></si>

<si><t>State</t></si>






</sst>

Header

="

http://schemas.openxmlformats.org/spreadsheetml/2006/main

Unique string count

count=""

http://schemas.openxmlformats.org/spreadsheetml/2006/main

Unique string count





Body


Footer


• Using shared strings *SRVPGM

o Copy shared strings work file to QTEMP CPYF FROMFILE(SHRSTRWK) + TOFILE(QTEMP/SHRSTRWK) + MBROPT(*REPLACE) + CRTFILE(*YES) MONMSG MSGID(CPF0000) CLRPFM FILE(QTEMP/SHRSTRWK) MONMSG MSGID(CPF0000) CRTLF FILE(QTEMP/SHRSTRWKL1) MONMSG MSGID(CPF0000) OVRDBF FILE(SHRSTRWK) + TOFILE(QTEMP/SHRSTRWK) OVRDBF FILE(SHRSTRWKL1) + TOFILE(QTEMP/SHRSTRWKL1)

o Use shared strings /include member /include qcpysrc,shrdstrs_h

ü Has variables for count and uniqueCount

Shared String Table


• Using shared strings *SRVPGM

o Call initializeSharedStrings before writing report body initializeSharedStrings(frmXmlFile : stringCount : uniqueStringCount);

o Call getSharedString to set index in cell while writing body updHTMLvar('Brand' : getSharedString(%trimr(brandVar) : stringCount : uniqueStringCount));

o Call writeSharedStrings after writing report body writeSharedStrings(stringCount : uniqueStringCount : sharedStringsXML);

ü With this approach it is possible to use CGIDEV2 variables in the

Shared Strings Table – set those before calling writeSharedStrings, as

the procedure knows nothing about those variables

Shared String Table

Use one of

the %trim

BIFs as

appropriate

(%trimr(

Optimization

Ø Focus of optimization

• Performance

• Size

Optimization

f ti i ti

Start with best practices for designing

and creating Excel worksheets

Ø Performance

• Use shared strings

o Main technique available to us

o Repetitive information is read only once

Optimization

Ø Size

• Always a desirable goal

• External constraints

o Email attachment limitations

ü Maximum attachment size

ü Uncompressed size

Optimization

Ø Uncompressed size

• ScanMail

o “ScanMail sustains business and network integrity by

screening out spam messages and messages containing

undesirable or unwanted content." (from readme.txt)

o Blocks ZIP’d file when decompressed size of included files

exceeds specified size

ü Protect against DOS (denial of service)

ü Protect against “ZIP of Death” attack

ü Settings used against Microsoft OOXML packages

• Scenario

o Exchange server attachment size limit: 25MB

o ScanMail decompressed size limit: 200MB

o Possible to have 12MB XLSX with 280MB sheet1.xml

Optimization

Ø Uncompressed size

Optimization

Size Optimization (180,000 rows, 25 columns)

Object Before After

sheet1.xml 300MB 120MB

XLSX 24MB 7MB

• Size reduction techniques

o Make changes in CGIDEV2 template for sheet

o Change text cells from using formulas to use shared strings

o Remove attributes that can be omitted

o Remove row & cell reference attributes

o Remove newlines

Ø Uncompressed size (cont.)

• Change text cells from using formulas to use shared strings

o Cell <c> element can be quite a bit smaller with shared strings

• sharedStrings.xml contains only unique strings

o Obviously minimizes size of sharedStrings.xml

o Moving strings to sharedStrings.xml reduces size of sheets

o This may help with decompressed-size constraints

• Reduces each text cell by 13 characters average (YMMV)

o 1,439,955 cells è 24,479,235 characters reduction

Optimization

Shared string Formula


• Remove attributes that can be omitted

o Excel can figure things out without these

o Remove all xy14ac:dyDescent attributes

o Remove spans attributes

o Reduces each row by about 32 characters

ü 180,000 rows è 5,760,000 characters reduction

Optimization


• Remove row references

o Reduces each row by about 10 characters (include trailing space)

ü 180,000 rows è 1,800,000 characters reduction

o Remove cell references

o Reduces each cell by about 11 characters (include trailing space)

ü 180,000 rows & 25 columns è 49,500,000 characters reduction

o Cautions

ü If there are gaps in the rows or cells, the first row or cell after the

gap MUST have the reference

ü Troubleshooting will be difficult, as it will be hard to know where

you are without the references

Optimization

• Remove newlines (CRLFs) in each section of CGIDEV2 template

o XML does not require there be newlines

ü We put them in when creating the template, to make things easier

Optimization

 <row r=""><c r="A" s="12"><v></v></c>

becomes

o Reduces each cell by 6 characters

ü 180,000 rows & 25 columns è 27,000,000 characters reduction

o Caution

ü Keep a copy of template WITH newlines for later editing

Oh, no! The dreaded “unreadable content”!

Ø Troubleshooting XLSX workbooks

• This error may occur on many projects


• Click on Yes to see if there’s any helpful information


Ø Troubleshooting XLSX workbooks (cont.)

Not much help here!



• Not very helpful things!

Exactly the same information

as shown in the dialog!



• Helpful things

• How to analyze problem

o Open workbook with 7-Zip or similar application

o Extract affected part to desktop

o Open extracted file in text editor

o See what is happening at identified location in file

Removed Part: /xl/sharedStrings.xml part with XML error. (Strings) A

document must contain exactly one root element. Line 1, column 0.

Repaired Part: /xl/worksheets/sheet1.xml part

with XML error. Load error. Line 3, column 0.

Error: What?

Error: How? Error: Where?

document must contain exactly one root element. Line 1, column 0.document must contain exactly one root element. Line 1, column 0.

Removed Part: /xl/sharedStrings.xml part with XML error. (Strings) A

heet1.xml part

3, column 0. Do NOT save the

workbook when

you close it!



XML error. (Strings) A document must contain exactly one root element. Line

1, column 0.

Extract affected

part

No root element at all!

Check source code and

see why nothing was

written to affected part



XML error. Load error. Line 3, column 0.

Extract affected

part

affected

rt

**Missing Data** means

a CGIDEV2 variable was

not updated

Check source code



• When all else fails!

o Sometimes the error is not obvious

o Use binary “elimination” search to isolate error – also called

ü Half-interval search

ü Logarithmic search

o Extract sheet.xml from XLSX with 7-Zip or equivalent

o Binary “elimination” search

ü Remove bottom half of rows

• After each change, adjust ref attribute of <dimension> tag

• Save sheet.xml

• Copy back into 7-Zip to update XLSX

• Try to open it

ü If error, remove bottom half of what is left

ü If no error, restore rows of half count of what is left

ü If no error, restore rows of half count of what was just restored



• When all else fails!

o Binary “elimination” search example

ü 16 rows of data + 4 of heading

ü Remove last rows 13-20 – error

ü Remove rows 9-12 – no error

• Error has to be in rows 9-12 of sheet

ü Restore rows 9-10 – no error

ü Restore row 11 - error

o Error in row 11, at least, and maybe row 12 – visual

inspection to identify

Tools

Tools

Ø Text editors

• Rational Developer for i (Rdi) text editor

o Use the “Compare with each other” to merge changes

• Notepad++

o http://notepad-plus-plus.org/

• Other text editor of choice

Ø XML editors

• XML Notepad 2007

o https://www.microsoft.com/en-

us/download/details.aspx?id=7973

Ø Oh, no! The dreaded “…unreadable content…” message!

• Open XML SDK 2.5 for Microsoft Office Productivity Tool

o https://www.microsoft.com/en-

us/download/details.aspx?id=30425

Resources

Ø Office Open XML (OOXML)

• ECMA-376 specification, 4th Edition

o http://www.ecma-

international.org/publications/standards/Ecma-376.htm

• ECMA-376 overview

o http://www.ecma-

international.org/news/TC45_current_work/OpenXML%20White

%20Paper.pdf

Ø Websites

• http://officeopenxml.com/

• Google it!

Ø XML

• http://www.w3schools.com/xml/

Resources

Vern has worked on IBM midrange systems for over 25 years,

beginning with the System/38 and AS/400. At present he is a

Senior IT Developer in the Pest Elimination division of Ecolab. He

also serves on the COMMON Americas Advisory Council (CAAC).

He is degreed in both music and mathematics, which gives him a

distinct approach to the art and science of programming. In his

spare time he plays in a community band, sings in a pretty-good

a capella choral group, and can be found working with local

musical theater productions. In April, 2011, he wrote a special

report on IBM Rational Open Access: RPG Edition that was

published in System iNews.

Vern Hamberg

#COMMONS17

www.common.org

Don’t Forget Your Session Surveys

• Sign in to the Online Session Guide (www.common.org/sessions)

• Go to your personal schedule

• Click on the session that you attended

• Click on the Feedback Survey button located above the abstract

Completing session surveys helps us plan future programming and provides feedback used in speaker awards. Thank you for your participation.

submit a requirement - schedschd.ws/hosted_files/commons17/55/fast modern excel (xlsx) workb… ·...

Documents