Copying styles from one document to another

[Dabraleli]

Hi everyone. I'm trying to imeplement reports generation from template. The problem I encountered is I try to do the following:

auto cell = wks.cell(j, i);
newWks.cell(j, i).value() = cell.value();
newWks.cell(j, i).setCellFormat(cell.cellFormat());

And I get excel corruption error and no styles at all. Template contains only bold/normal text and cell's borders.

Is there any simple way to just copy styles from one document to another?
Also I don't see a possibility to somehow copy merged cell. Is it intended?

Thank you

[aral-matrix]

Hi @Dabraleli! This is actually a use case I hadn't considered yet. The problem is that each style has an index internal to one Excel document, and when you use cellFormat / setCellFormat, you are actually just assigning an index that is only what you want it to be in the context of another document.

What you could use is (in the new document) XLCellFormats::create, which takes as an argument a style to copy. This should also accept a style from another document. However, the problematic style indexes "cascade", as you have to then handle for the new document style:

        setNumberFormatId
        setFontIndex
        setFillIndex
        setBorderIndex
        setXfId

because all of those would be pointing to potentially invalid, but definitely different, styles in the new document.

I could see a use for supporting a "recursive" copy operation of a style from another document, but you would still have to be careful then and first copy the style, then assign it to all cells that shall have the same style - because if you invoke the "copy style" operation each time, you will create as many different styles (and all their sub-styles) as you would assign cell formats.

For now, the comprehensive option would be to:

• check which unique cell formats (from the source document) you want to copy - i.e. create a unique list of IDs
• for each of those cell formats:
  • create a cell format in the new document via XLCellFormats::create ( sourceDocCellFormat ) and assign the old cell format ID -> new cell format ID to a lookup table
• create a unique list of all sub-formatting elements (number formats, fonts, fills, borders, "xf" (For the moment I forgot where that was coming from - it was some weird duplication of cell formats, you might not need to copy this / could potentially set it to 0)
• for each of those sub-formatting elements, use the ::create( sourceDocFormat ) to copy the style and again, create a lookup table old ID -> new ID
• check in XLStyles.hpp whether any of those elements have even more sub-elements (I am afraid there might be some) and do the same
• then for all lookup lists, in the new document style elements, replace the old IDs with the new IDs
• finally, point cell formats in the new document to the new ID of a cell format from the old document, based on the lookup table

Considering the complexity and uncertainty of this operation, I would recommend to

• manually create a list of desired styles (with default settings), assigning each new ID to one of the old styles in a lookup table
• loop over the old document styles
  • manually retrieve and set in the new style all formatting elements that you are interested in
• point cell formats in the new document to the new ID of a cell format by lookup table

[Dabraleli]

Hi @aral-matrix!
Thank you for such explanative response!
I'm gonna now try another approach with moving cells in template.
My template have many different fields and tables that I have to fill. It makes me insert rows. I don't see an insert possibility, but is there some method for massive "move down" of rows? No formulas, so the task is a little bit easier

Thank you in advance

[aral-matrix]

It makes me insert rows. I don't see an insert possibility, but is there some method for massive "move down" of rows?

OpenXLSX works on the OOXML format, and that format has no concept of "inserting rows" - Office applications do that for you "under the hood".

OpenXLSX also does not track if any formulas break by "moving" a row somewhere else, so if you reference e.g. cell "C5" in a formula somewhere, and then insert a row at the top of the document, office applications automatically change the reference to "C6". Automating this would not only be quite some effort, it would also be very computation intensive as it would have to be done every time a cell "moves".

But as you say, you have no formulas - so the best solution with OpenXLSX would be to decide how many rows you need to insert, and then copy row by row (or cell by cell) from the back to a destination that is the desired amount of rows further down - then overwrite the rows at the top.

I am sorry I am in the middle of a code review so I can not give you detailed support right now, but you might want to have a Examples/Demo6.cpp how you can read a whole row at once into a vector of XLCellValues, and then I would recommend you use an XLRowIterator to iterate over the range of source and destination rows.

[Dabraleli]

Demo6.cpp is the thing I need. Thank you

[Dabraleli]

Sadly XLRowIterator is not bidirectional. No decrement implemented. Thought it would be easier to just start from the end. Will investigate it further.

[aral-matrix]

Doh. I forgot about that, I had exactly that use case in mind. I might implement a reverse iterator for you if that's doable with reasonable effort. Give me a few days though :)

[Dabraleli]

I think I fixed it just right now. Will send you an pull request tomorrow if won't forget

[aral-matrix]

You mean you added a decrement capability to an iterator?
To be honest, I am not a big fan of pull requests because most of the time I have to modify them to fit the library, and with my limited understanding of git that means there is an intermediate commit in the history which has a "bug" that I already know about when merging. Somehow I avoided that in the past because it was often (for larger commits) more work than implementing it myself.

[aral-matrix]

Doh. I forgot about that, I had exactly that use case in mind. I might implement a reverse iterator for you if that's doable with reasonable effort. Give me a few days though :)

Here you go: e67ec4d (development-aral branch) - let me know if that works fine for you (or if it doesn't)

[aral-matrix]

@Dabraleli would you have a chance to try the reverse iterator? I would be happy for some feedback.