Insert Column into Excel Spreadsheet

aconnell · 27 Oct 2020, 22:13

There doesn't seem to be a way to insert a column (or row) into an excel spreadsheet using the QXLSX library. I feel like it should be simple, am I missing something? The Document class has methods for inserting images, charts, sheets, but not rows and columns?!?!

Thanks

JonB · 27 Oct 2020, 22:29

@aconnell
Hi and welcome.

It would help for a question like this if you provided a link/reference to, say, the documentation of Document you are asking about.

Anyway, I haven't used it, but why do you think one needs to "insert a column (or row)"? From a user perspective you can just put a value into any row/column without "inserting". Don't you just use bool Document::write(int row, int col, const QVariant & value, const Format & format = Format()) to just write values into any cells you want to?

aconnell · 28 Oct 2020, 07:41

@JonB I'm using this library https://github.com/QtExcel/QXlsx/tree/master/QXlsx and in particular this -- https://github.com/QtExcel/QXlsx/blob/master/QXlsx/header/xlsxdocument.h. You are correct about writing, I can read/write to cells, but I have a bunch of existing spreadsheets that need to be updated. Columns added and data entered based on existing data. I could write after the last filled out column, but the reason we are using spreadsheets and not a db is they need to be human readable so order/presentation matter.

JonB · wrote on 28 Oct 2020, 07:41

@aconnell
I think you're saying you have some existing data in a sheet, and you wish to insert a row/column into that, such that the existing subsequent data gets moved down/right for you. So the fact remains that it is your job to write a loop to copy the necessary around to accommodate the need for the gap, which you can then fill. So it's perfectly doable. I refer you to https://github.com/dbzhang800/QtXlsxWriter/issues/141, https://github.com/dbzhang800/QtXlsxWriter/issues/166, https://forum.qt.io/topic/87962/how-to-use-insert-row-in-qtxlsx-please-help-me.

aconnell · 28 Oct 2020, 12:30

@JonB Thanks for the reply but that doesn't really solve my issue, #141 and #166 just seem to reference each other without explaining anything. The other one is slightly helpful in showing how to copy cells, but that isn't really an insert. I have cells with formulas and formatting, simply copying that data to another cell doesn't update the references, so the calculations will be broken. So I assume there is not a way to insert a row or column and that Qt is not the right tool for this job.

JonB · wrote on 28 Oct 2020, 12:30

@aconnell said in Insert Column into Excel Spreadsheet:

I have cells with formulas and formatting, simply copying that data to another cell doesn't update the references

Indeed not! (Correct me if I'm wrong, but...) this QXlsx doesn't even know about formulae. It allows the construction of a .xlsx file legally, but doesn't know/care what you put in there. It describes itself as

QXlsx is excel file(*.xlsx) reader/writer library.

Not a "spreadsheet evaluator". What you sound like you want is, say, to use QAxObject to drive the Excel application via Automation. Have you seen e.g. https://forum.qt.io/topic/89536/qaxobject-and-excel ? But that will require Windows + Excel, it's not an off-line file writer. What is your usage case?

VRonin · wrote on 28 Oct 2020, 12:55

QXlsx doesn't even know about formulae.

It does, it just doesn't calculate them

The problem is not the insertion itself but it's updating the formulas after an insertion. You'd probably need to insert an engine that can at least read the formulas before even attempting at updating them. This was the stopper in implementing the functionality in the library