!rows array, page view & margins, image support

[Siguza]

Credit: !rows handling: paulish

View usage:

XLSX.write(wb,
{
    // ...
    view: 'pageLayout',
    zoomScale: 140,
    zoomScaleNormal: 100,
    zoomScalePageLayoutView: 140,
});

Margin usage:

XLSX.write(
{
    SheetNames: ['Example'],
    Sheets:
    {
        Example:
        {
            // ...
            '!pageMargins':
            {
                left: 0,
                right: 0,
                top: 0,
                bottom: 0,
                header: 0,
                footer: 0,
            },
        },
    },
}, opts);

Images usage:

XLSX.write(
{
    SheetNames: ['Example'],
    Sheets:
    {
        Example:
        {
            // ...
            '!drawing': 0, // Index in wb.Drawings
        },
    },
    Drawings:
    [ // Array of drawings
        [ // Drawing = array of images
            {
                image: 0, // Index in wb.Images
                from:
                {
                    col: 0,
                    row: 0,
                    colOff: 1335173,
                    rowOff: 107424,
                },
                to:
                {
                    col: 0,
                    row: 0,
                    colOff: 1729508,
                    rowOff: 243441,
                },
            },
            // ...
        ],
    ],
    Images:
    [
        {
            data: '...', // <base64 data without "data:..." header>
            type: 'png', // the only supported type for now
        },
        // ...
    ],
}, opts);

[seriousben]

@protobi if the images part of this PR were extracted and made into another PR. Would you accept it?

[seriousben]

@Siguza how functional were your changes before the conflict?

[Siguza]

Fully functional, I'd say. Limited, but functional.

For image support, using the dist from my fork you should be able to literally copy the last code block from my PR, replace the ... in data with some base64 encoded PNG and get an XLSX with an embedded image.

Everything else is fairly simple and only required a handful of XML tags or attributes to be added or set.

[seriousben]

Thanks a lot!

[eric-borland]

The image handling would be a great feature (It's sad, but our client uses images on excel files). Do you guys have any plan on fixing + merging this branch soon? Or to separate the image development in another PR?

Thank you all very much for this awesome module!

[adamtaylor13]

Is this project dead? I've got a pretty serious issue and it doesn't seem anyone is even replying to issues anymore. (https://github.com/protobi/js-xlsx/issues/39)

[Siguza]

Well I'm still around, but I only forked the repo and hacked image support together. I suppose I could try and write up a fix for that issue of yours though, if you can provide me with an xlsx both pre- and post-repair.

[adamtaylor13]

Well I wouldn't actually mind trying to write the fix myself if I could figure out what exactly is causing the issues. The only reason I'm hesitant to provide the before/after .xlsx is because it contains government records.

However, if you can get me a general idea of where the issue may be occuring, I'd love to try to fix it myself. I think I provided as much info as I could get under my issue (linked in the prior comment)

[Siguza]

The most likely source of a bug in worksheets/sheetX.xml would seem bits/67_wsxml.js. Without knowing the nature of the bug though (missing tag/attribute that is expected, XML syntax error, ...), I don't think I can tell more. But I suggest you create a before/after pair, unzip both of them, run both xl/worksheets/sheet1.xml through an XML beautifier and compare them. That would reveal the exact place and nature of the bug, which should in turn allow you to track it to the code that generates the XML portion in question.

[pietersv]

I'd love to support the project more. The challenge for me is that the base project is silent. There are tens of thousands of unit tests for the server and browser, and these catch a surprising number of issues with seemingly simple edits. So I'm hesitant to accept PRs without running the tests.

However, the base project combined XLSX with XLS, XLSB and XLSM into one project and some of these no longer work in Node 4+. Ideally, the base project would work with Node 4+, I pull those changes in and roll along. I've been hoping that would happen by now. Even better would be for the base project to handle styles, and we all support that.

I think the root issue here is the economics of open source which requires one small group with the skill and intensity to manage the core. I can make the business case to support the styles extension but not the XLS/XLSB or node migration part.

[adamtaylor13]

Yeah that makes sense to me. Unfortunately I don't feel like I have the expertise to get my hands really dirty with these types of migrations/updates. I really wish the base project wasn't dead. This seems like a very useful project that just kind of died.

[SheetJSDev]

@Siguza we were looking at an issue https://github.com/SheetJS/js-xlsx/pull/509 which appears to have plucked changes from your PR. Nice work!

We already pulled in the core of the changes from @paulish PR but have yet to finalize the row parameters.

With regards to the page margins, is there any particular reason for the defaults you chose? The UI for Excel 2007 and Excel 2016 both quote rounded decimals:

And for completeness sake, here are a few sample files (and they show the same numbers as the UI):

page_margins_2007.xlsx

page_margins_2016.xlsx

Since the file has no conception of "wide/narrow/normal", only the raw numbers, it makes sense to merely document the sizes in the README but default to the normal values.

[Siguza]

@SheetJSDev:
I got those values directly from some XML files I generated, though I'm not sure where they originally come from. They do correspond to rounded centimetre values though, so I probably just changed my defaults at some point (I didn't even realise the values in the XML were inches until today ^^).
Happy to see my work is useful after all. :)

[SheetJSDev]

@Siguza in Excel, if you go to page layout and hit the margins button, you'll see a dropdown. Can you read off what is written there? The margins according to your files are:

margin	size
left/right	18 mm
top/bottom	19 mm
header/footer	8 mm

[Siguza]

The values don't match my current ones.

margin	size
left/right	1.778 cm
top/bottom	1.905 cm
header/footer	0.762 cm

This is a fresh install, so my old values were most likely custom.

[SheetJSDev]

@Siguza we just pushed a change in the main repo to add page margin parsing for the main formats as well as page margin writing for XLSB: https://github.com/SheetJS/js-xlsx/commit/1587688aea57de985bb04e84aedbd992a73e177c#diff-d3b36db34e6b01d5a4720fd524e20279. If you can fork the main repo and add in the XLSX write stuff, we'd accept a PR. Also check your email :)

[gdoteof]

Wanted to add I worked a bit on images -- I needed to be able to add multiple and set some properties (offsets and sizes).

https://github.com/protobi/js-xlsx/pull/84