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?!?!
Thanks
-
@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?
-
@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.
-
@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. -
@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.
-
@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 asQXlsx 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? -
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