Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct
Insert Column into Excel Spreadsheet
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?!?!
JonB last edited by
Hi and welcome.
It would help for a question like this if you provided a link/reference to, say, the documentation of
Documentyou 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?
@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 last edited by JonB
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.
@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 last edited by
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
QXlsxdoesn't even know about formulae. It allows the construction of a
.xlsxfile 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
QAxObjectto 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 last edited by
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