Hey! As WWDC is a time for developers to share problems and solutions, I figured it might be time to ask here about something we’ve been struggling with over the last few months.
Five years ago, we started transitioning from VB.Net development toward Obj-C (and eventually Swift), and really hit it off in the last year or so, moving our apps which connected to PGSQL databases toward macOS. But the snag we now hit was this: the support to COM Objects, mainly excel files. One of the ordeal we are faced with is connecting to Excel files, which we want to edit, and have the various cells with linked formulas to have their values updated.
Many solutions seem to go the way of editing the XML file in the xlsx “archive”, though we wouldn’t have access to an updated value. As an example: Place a value in “A1” (for example, 2), and check the value in “B1”, which has the formula equal to “A1 * 2” (so it would return "4")
I don't know if any of you have worked a combination to allow this, and could give me some directions. Thanks in advance.
Five years ago, we started transitioning from VB.Net development toward Obj-C (and eventually Swift), and really hit it off in the last year or so, moving our apps which connected to PGSQL databases toward macOS. But the snag we now hit was this: the support to COM Objects, mainly excel files. One of the ordeal we are faced with is connecting to Excel files, which we want to edit, and have the various cells with linked formulas to have their values updated.
Many solutions seem to go the way of editing the XML file in the xlsx “archive”, though we wouldn’t have access to an updated value. As an example: Place a value in “A1” (for example, 2), and check the value in “B1”, which has the formula equal to “A1 * 2” (so it would return "4")
I don't know if any of you have worked a combination to allow this, and could give me some directions. Thanks in advance.
In the end, we figured a roundabout way of doing things:
A) using Podfiles, we installed XlsxReaderWriter library from GitHub (which turns the excel content into a dictionary structure)
B) we modify the cell values where we wanted
C) we tweaked the output, to remove the "Value" field of cells with formulas. (as well as correct the order of attributes required in the xml files, to allow the file to be read in Excel)
D) opening the file with Excel recalculates the cell having formulas but without values, and then saving it
E) reopening the file with the swift app now displays the update values we were interested in.
A) using Podfiles, we installed XlsxReaderWriter library from GitHub (which turns the excel content into a dictionary structure)
B) we modify the cell values where we wanted
C) we tweaked the output, to remove the "Value" field of cells with formulas. (as well as correct the order of attributes required in the xml files, to allow the file to be read in Excel)
D) opening the file with Excel recalculates the cell having formulas but without values, and then saving it
E) reopening the file with the swift app now displays the update values we were interested in.
