TODO:

On the way to the impossible we might find something eminently doable

========================================================================================================================

Tables

sheet

* add <tableParts>

sheet.rels

* add ref to ../../tables/table1.xml

table1.xml

* define the table

Content_Types

* add table1

Notes:

* in table1.xml, the tableColumns can define a dataDxfId (rel to styles.xml)

that specifies default format of new data

* in table1.xml, the tableColumn name property must match the value in the

cell where the column header goes

Table Properties:

* ref: Top Left of table

* name table.name

* displayName: table.displayName

* range - range of data cells (not including headers/footers)

=> table.ref includes headers/footers

=> autoFilter.ref includes headers only

* headerRow - table.headerRowCount

* default: true, show/hide the headers

* totalsRow - table.totalsRowShown="0" or table.totalsRowCount="1"

* default: false show/hide totals rows

* columns - tableColumns[n]

* id - sequential number

* name - must correspond to top cell's value

* style -> dataDxfId

* inserts inline style into dxfs in styles.xml

* filterButton - autoFilter[n].hiddenButton="1"

* shows or hides the filter (default: true)

* totalsRowLabel - for column1

* totalsRowFunction - name of function, one of [none, count, etc]

* totalsRowFormula - if function is 'custom', a formula

* totalsRowResult - the value to show

* style

* name - tableStyleInfo.name

* showFirstColumn - tableStyleInfo.showFirstColumn

* showLastColumn - tableStyleInfo.showLastColumn

* showRowStripes - tableStyleInfo.showRowStripes

* showColumnStripes - tableStyleInfo.showColumnStripes

========================================================================================================================

Page Setup

Themes:

Not supported

Margins

Inches?

<pageMargins left="0.25" right="0.25" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>

========================================================================================================================

Page Setup

<pageSetup paperSize="9" orientation="portrait" horizontalDpi="4294967295" verticalDpi="4294967295" r:id="rId1"/>

<pageSetup paperSize="119" pageOrder="overThenDown" orientation="portrait" blackAndWhite="1"

draft="1" cellComments="atEnd" errors="dash" horizontalDpi="4294967295" verticalDpi="4294967295" r:id="rId1"/>

<pageSetup paperSize="119" orientation="portrait" blackAndWhite="1" draft="1" cellComments="asDisplayed"

errors="blank" horizontalDpi="4294967295" verticalDpi="4294967295" r:id="rId1"/>

<pageSetup paperSize="119" orientation="portrait" errors="NA" horizontalDpi="4294967295" verticalDpi="4294967295" r:id="rId1"/>

<pageSetup paperSize="119" scale="95" fitToWidth="2" fitToHeight="3" orientation="landscape" r:id="rId1"/>

orientation: portrait, landscape

pageOrder: overThenDown, downThenOver(*)

blackAndWhite: true, false(*)

draft: true, false(*)

cellComments: atEnd, asDisplayed, (None)(*)

errors: dash, blank, NA, displayed(*)

scale: #

fitToWidth: num pages wide

fitToHeight: num pages high

horizontalDpi: 4294967295 (-1?)

verticalDpi: 4294967295

How to determine between scale and fit to page(s)

<sheetPr>

<pageSetUpPr fitToPage="1"/>

</sheetPr>

paperSize:

| Letter (*) | - |

| Legal | 5 |

| Executive | 7 |

| A4 | 9 |

| A5 | 11 |

| B5 (JIS) | 13 |

| Envelope #10 | 20 |

| Envelope DL | 27 |

| Envelope C5 | 28 |

| Envelope B5 | 34 |

| Envelope Monarch | 37 |

| Double Japan Postcard Rotated | 82 |

| 16K 197x273 mm | 119 |

========================================================================================================================

Print Options:

<printOptions headings="1" gridLines="1"/>

<printOptions horizontalCentered="1" verticalCentered="1"/>

headings: true, false(*)

gridLines: true, false(*)

horizontalCentered: true, false(*)

verticalCentered: true, false(*)

========================================================================================================================

Print Area

In workbook.xml

<definedNames>

<definedName name="_xlnm.Print_Area" localSheetId="0">Sheet1!$A$1:$C$11</definedName>

</definedNames>

In app.xml ?

<TitlesOfParts>

<vt:vector size="2" baseType="lpstr">

<vt:lpstr>Sheet1</vt:lpstr>

<vt:lpstr>Sheet1!Print_Area</vt:lpstr>

</vt:vector>

</TitlesOfParts>

Manual Page Breaks

In sheet.xml

<rowBreaks count="2" manualBreakCount="2">

<brk id="5" max="16383" man="1"/> // id="5" means underneath row 5 max & man??? is that a spelling error?

<brk id="7" max="16383" man="1"/>

</rowBreaks>

<colBreaks count="3" manualBreakCount="3">

<brk id="3" max="1048575" man="1"/>

<brk id="6" max="1048575" man="1"/>

<brk id="9" max="1048575" man="1"/>

</colBreaks>

Background Image

In sheet.xml

<picture r:id="rId2"/>

In sheet.xml.rels

<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image2.png"/>

Print Titles (rows and columns repeated on each page)

In workbook.xml

<definedName name="_xlnm.Print_Titles" localSheetId="0">Sheet1!$A:$B,Sheet1!$1:$2</definedName>

========================================================================================================================

Headers and Footers

<headerFooter>

<oddHeader>Page &amp;P</oddHeader>

<oddFooter>Page &amp;P</oddFooter>

</headerFooter>

<headerFooter>

<oddHeader>Page &amp;P of &amp;N</oddHeader>

<oddFooter>&amp;A</oddFooter>

</headerFooter>

<headerFooter differentOddEven="1" differentFirst="1">

<oddHeader>&amp;L&amp;B Confidential&amp;B&amp;C&amp;D&amp;RPage &amp;P</oddHeader>

<oddFooter>&amp;F</oddFooter>

</headerFooter>

<headerFooter>

<oddHeader>&amp;Z&amp;F</oddHeader>

<oddFooter>Prepared by Guyon Roche &amp;D&amp;RPage &amp;P</oddFooter>

</headerFooter>

<headerFooter differentOddEven="1" differentFirst="1">

<oddHeader>&amp;L&amp;"Algerian,Italic"&amp;14&amp;S&amp;U&amp;X&amp;K05 000Formatted&amp;C&amp;Z&amp;F</oddHeader>

<oddFooter>Prepared by Guyon Roche &amp;D&amp;RPage &amp;P</oddFooter>

<evenHeader>&amp;C&amp;T</evenHeader>

<firstHeader>&amp;L&amp;P of &amp;N&amp;C&amp;D &amp;T

&amp;Z&amp;F

&amp;F

&amp;A</firstHeader>

</headerFooter>

<headerFooter differentOddEven="1" differentFirst="1">

<oddHeader>&amp;L&amp;"Algerian,Italic"&amp;14&amp;S&amp;U&amp;X&amp;K05 000Formatted&amp;C&amp;K008000Colour Wheel</oddHeader>

<oddFooter>Prepared by Guyon Roche &amp;D&amp;RPage &amp;P</oddFooter>

<evenHeader>&amp;C&amp;T</evenHeader>

<firstHeader>&amp;L&amp;KAFD680RGB&amp;C&amp;KFF0000Std Colour&amp;R&amp;K09 059Purple Accent 6 60%</firstHeader>

</headerFooter>

oddHeader is default for where there are no differences

evenHeader & firstHeader used with appropriate options

Codes

&P - &[Page] - Page#

&N - &[Pages] - Page Count

&A - &[Tab] - Sheet #

&D - &[Date] - Date

&F - &[File] - Filename

&Z - &[Path] - Path

&L - Align Left

&C - Align Centre

&R - Align Right

&B - Bold Toggle

&"Font Name"

&14 - font size

&S - strikethrough

&U - underline

&X - superscript

&K05 000 colour?

&KFF0000 RGB, no alpha

&[Page]&[Pages]&[Date]&[Time]&[Path]&[File]&[File]&[Tab]

&[Picture]

========================================================================================================================

sheet.pageSetup = {

// in sheet

margins: {

left: 0.25, right: 0.25, top: 0.75, bottom: 0.75, header: 0.3, footer: 0.3

},

orientation: ['portrait(*)', 'landscape'],

pageOrder: ['overThenDown', 'downThenOver(*)'],

blackAndWhite: [true, false(*)],

draft: [true, false(*)],

cellComments: ['atEnd', 'asDisplayed', 'None(*)'',

errors: ['dash', 'blank', 'NA', 'displayed(*)''],

scale: 90(*),

fitToWidth: 2,

fitToHeight: 3,

paperSize: 9,

showRowColHeaders: [true, false(*)],

showGridLines: [true, false(*)],

horizontalCentered: [true, false(*)],

verticalCentered: [true, false(*)],

rowBreaks: [5,7],

colBreaks: [3,6,9],

// phase 2

header: {

odd: {

left: '',

center: '&[Page] of &[Pages]',

right: '',

raw: '&C&P of &N' // raw supercedes the others

},

even: {}, first: {}

},

footer: {}, // as per header

// defined names - phase 3

printArea: 'A1:D11',

printTitles: {

rows: '1:2' || '$1:$2' || 1 || '$1',

columns: 'B:C' || '$B:$C' | 'A' || '$A'

}

}

========================================================================================================================

https://github.com/guyonroche/exceljs/pull/114

<si>

<t>(a japanese text in KANJI)</t>

<rPh sb="0" eb="1">

<t>(its pronounciation in KATAKANA)</t>

</rPh>

</si>

<si>

<t>役割</t> <!-- role in English -->

<rPh sb="0" eb="2">

<t>ヤクワリ</t> <!-- Japanese phonic symbol called Katakana of role -->

</rPh>

<phoneticPr fontId="1" />

</si>

parseSharedStrings: function (stream) {

return new Bluebird(function(resolve, reject) {

var parser = Sax.createStream(true, {});

var t = null;

var sharedStrings = [];

var rph = false;

parser.on('opentag', function (node) {

if (!rph && node.name === 't') {

t = '';

}

if (node.name === 'rPh') {

rph = true;

}

});

parser.on('closetag', function (name) {

if ((t !== null) && (name === 't')) {

sharedStrings.push(t);

t = null;

}

if (name === 'rPh') {

rph = false;

}

});

parser.on('text', function (text) {

if (t !== null) {

t = text;

}

});

parser.on('end', function () {

resolve(sharedStrings);

});

parser.on('error', function (error) {

reject(error);

});

stream.pipe(parser);

});

},

========================================================================================================================

Comments

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

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

<authors>

<author>Author</author>

</authors>

<commentList>

<comment ref="A1" authorId="0" shapeId="0">

<text>

<r>

<rPr>

<b/>

<sz val="9"/>

<color indexed="81"/>

<rFont val="Tahoma"/>

<charset val="1"/>

</rPr><t>Author:</t>

</r><r>

<rPr>

<sz val="9"/>

<color indexed="81"/>

<rFont val="Tahoma"/>

<charset val="1"/>

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

Is it me you're looking for</t>

</r>

</text>

</comment>

</commentList>

</comments>

========================================================================================================================

Charts

New contents: Chart as sheet

[Content_Types].xml

chartsheets, charts, drawings, etc

app.xml

HeadingPairs

Charts: 1

TitleOfParts

Chart sheet

/xl/charts/*

/xl/chartsheets/*

/xl/drawings/

========================================================================================================================

Conditional Formatting

cell.conditionalFormatting: {

ref: cell-range

rules: [

{

type: expression | cellIs | top10 | aboveAverage | dataBar | colorScale | iconSet | containsText | timePeriod | etc

dxfId: styleId

priority: a number - typically last first (and unique within sheet)

// expression

formula: 'the-formula'

// cellIs

operator: ''

formulae: [formula, ...]

// top10

percent: true | false(*)

bottom: true | false(*)

rank: 10 (or other number)

// aboveAverage (why is this not top10 50% ?)

aboveAverage: true(*) | false

// dataBar

cfvo: [CFVO, ...]

color: Colour

showValue: true(*) | false

gradient: true(*) | false

border: true | false(*)

borderColour: Colour

axisColor: Colour

negativeFillColor: Colour

negativeBorderColor: Colour

negativeBarColorSameAsPositive: true | false(*)

negativeBarBorderColorSameAsPositive: true | false(*)

minLength: 0(*)..maxLength

maxLength: minLength..100(*)

axisPosition: middle | none | undefined (auto)

direction: rightToLeft | undefined (lToR)

// colorScale

cfvo: [CFVO, ...] # 2 or 3

color: [Colour]

// iconSet

iconSet: IconSetEnum

cfvo: [CFVO, ...] # 2..5

reverse: true | false(*)

showValue: true(*) | false

custom: true | false(*)

cfIcon: [{iconSet: IconSetEnum, iconId: 0..N}, ...]

// containsText

operator: 'containsText'

text: 'the text'

formulae: [<an actual formula>]

}

]

}

Notes:

* Formulae based on cell values use the top-left addr as this gets translated

* Some iconSet types are 'natively' supported: (assumed)

* Some iconSet types require extLst:

* 3Triangles | 3Stars | 5Boxes

* extLst XML appears similar to native except that attributes are often shifted to element texts

// Conditional Formatting Value Object

type CFVO {

type: min | max | percentile | num | percent | formula | autoMin | autoMax

val: N

}

Types:

expression: the formula returns true or false

cellIs:

operator: greaterThan | lessThan | equal | between

formulae: [compare value(s)]

top10:

percent: 1 | undefined (false)

bottom: 1 | undefined (false)

rank: N

aboveAverage:

aboveAverage: 0 | undefined (true)

dataBar:

dataBar:

cfvo: [CFVO]

color: rgb="argb" | theme="n"

showValue: 0 | undefined (true)

borderColor: Colour

negativeFillColor: Colour

negativeBorderColor?: Colour

negativeBarColorSameAsPositive: 0 | undefined (true)

negativeBarBorderColorSameAsPositive: 0 | undefined (true)

axisColor: Colour

gradient: 0 | undefined (true)

border: 1 | undefined (false)

minLength: 0

maxLength: 100

axisPosition: middle | none | undefined (auto)

direction: rightToLeft | undefined (lToR)

# solid fill requires the exeLst bit

colorScale:

cfvo: [CFVO] # 2 or 3

color: [rgb="argb"]

iconSet:

iconSet

iconSet:

3Arrows | 4Arrows | 5Arrows | 3ArrowsGray | 4ArrowsGray | 5ArrowsGray | 3Triangles |

3TrafficLights (*) | 3Signs | 4RedToBlack | 3TrafficLights2 | 4TrafficLights

3Symbols3 | 3Flags | Symbols2 |

5Quarters | 4Rating | 5Rating | 3Stars | 5Boxes

cfvo: [CFVO] # 2, 3, 4, 5 cfvo points

reverse: 1 | undefined (false)

showValue: 0 | undefined (true)

custom: 1 | undefined (false) # meaning mixed icons

cfIcon: [{iconSet: "name", iconId: 0..N}]

# 3Stars is only in extLst because the stars are proportionately filled?

containsText:

type: containsText | containsBlanks | notContainsBlanks | containsErrors | notContainsErrors

operator: containsText

text: "the text"

formulae: An actual true/false formula

timePeriod:

timePeriod: thisWeek | lastWeek | nextWeek | yesterday | today | tomorrow | last7Days | lastMonth | nextMonth

formulae: An actual true/false formula