Ever felt the need to modify the content of Excel XLSX files?

Well thanks to Keisuke opening up the intricate content of a Microsoft OPC file (docx;xlsx;pptx;etc…) we can now easily edit the content of the Excel spreadsheets fiddling around with XML.

Or rather, it could be easy if Microsoft had devised their open document formats in a straightforward way, but being Microsoft and having written the decisive chapters of the industry standard cookbook obfuscating code for sociopaths  they have made it as complicated as possible.

Well I can at least help alleviate the pain a bit with a little component you can download here:

Download AC_XlsxEdit from Dropbox

You will need to download the OPC Plugin from Keisukes Github repository:

Keisukes OPC Plugin on Github

The component is V14 but should work with all versions of 4D.

It does what it says, thus you can:

• get a list of worksheets
• get a cell value
• set a cell value
• save the modified worksheet
• some useful other stuff, just see for yourself

Its not very sophisticated, you will have to clean up the XML after you are done with it using DOM CLOSE XML, failing to do so will make your application hemorrhage memory as if that stuff was going out of fashion.

There is a method called _documentation, containing, you guessed it, example code!
All methods are documented in code.

Also calling the error handling rudimentary might qualify for a medal at the annual Euphemism World Cup.

Also I obviously decline any and all responsibility for what you do with it, gift horse and all…

That’s it folks!

Enjoy

and for the impatient here is a quite silly code example that sets the value of row 17, column 3 in the worksheet called “Revenue” to some text and returns the old value:

If (False)
// ----------------------------------------------------
// Parameter Definition
  C_TEXT($0)
// ----------------------------------------------------
// Local Variable Definition
  ARRAY TEXT($at_Sheets;0)
  C_LONGINT($l_Cols)
  C_LONGINT($l_Count)
  C_LONGINT($l_Rows)
  C_LONGINT($l_Sheet)
  C_TEXT($t_Cell)
  C_TEXT($t_Doc)
  C_TEXT($t_Value)
  C_TEXT($t_ValueOld)
  C_TEXT($tx_SheetXmlRef)
// ----------------------------------------------------
End if 
//select a document
$t_Doc:=Select document("";"xlsx";"Choose Your Spreadsheet";0)
If (OK=1)
$t_Doc:=document
//load the list of sheets
$l_Count:=XLSX_WorkSheetList ($t_Doc;->$at_Sheets)
$l_Sheet:=Find in array($at_Sheets;"Revenue")
  If ($l_Sheet<1)
    ALERT("No sheet named REVENUE found")
  Else 
//load the sheet
$tx_SheetXmlRef:=XLSX_WorkSheetLoad ($t_Doc;$l_Sheet)
//get the dimensions (not needed if you know you worksheet)
$l_Cols:=XLSX_WorkSheetColums ($tx_SheetXmlRef)
$l_Rows:=XLSX_WorkSheetRows ($tx_SheetXmlRef)
    Case of 
    : ($l_Cols<3)
      ALERT("not enough columns")
    : ($l_Rows<17)
      ALERT("not enough rows")
    Else 
//calculate the cell coordinates
$t_Cell:=XLSX_CellCode (3;17)
//now get the current value
XLSX_CellGetValue ($t_Doc;$tx_SheetXmlRef;$t_Cell;->$t_ValueOld)
//now set the new value
$t_Value:="My Test Value"
     XLSX_CellPutValue ($tx_SheetXmlRef;$t_Cell;->$t_Value)
//last: save the sheet back to the document
     XLSX_WorkSheetSave ($t_Doc;$l_Sheet;$tx_SheetXmlRef)
//voila!
   End case 
 End if 
  DOM CLOSE XML($tx_SheetXmlRef)
End if 
$0:=$t_ValueOld