Extracting XLS (Excel) file natively

General
1

An outside automation emails a .XLS file to our system, where we need to programatically open and ingest the data. What is the most straightforward way to ingest XLS data? This is a headless service app - no human interface.

2

If the file can be XLSX, it’s a pretty straightforward XML data structure.

1 Like
3

Thanks Eric - and I hope you don’t mind me asking a little more. Could it be as simple as passing the .XLSX into an XML constructor? XMLDocument — Xojo documentation

4

You can open it as if it were text. Xojo can read and manipulate text files. Once opened, you treat the entire text string as XML to determine where the information you’re looking for is.

Other option.

Xojo can open a file in Excel using OLE/COM automation. It works very well.

You can see the documentation:

The code sample:

Try
  Var excel As New ExcelApplication
  Var book As ExcelWorkbook
  Var sheet As ExcelWorksheet

  excel.Visible = True
  book = excel.Workbooks.Add
  excel.ActiveSheet.Name = "Expenses Report"
  For i As Integer = 0 To ProduceList.RowCount - 1
    excel.Range("A" + Str(i + 1), "A" + Str(i + 1)).Value = ProduceList.CellTextAt(i, 0)
    excel.Range("B" + Str(i + 1), "B" + Str(i + 1)).Value = ProduceList.CellTextAt(i, 1)
  Next
  excel.Range("A" + Str(ProduceList.RowCount + 1), "A"+ _
    Str(ProduceList.RowCount + 1)).Value = "Total"
  excel.Range("B1", "B" + Str(ProduceList.RowCount)).Style = "Currency"
  excel.Range("B" + Str(ProduceList.RowCount + 1), "B"+ _
    Str(ProduceList.RowCount + 1)).Value = "=SUM(B1:B" + _
    Str(ProduceList.RowCount) + ")"
Catch err As OLEException
    MessageBox(err.Message)
End Try
5

Isn’t OLE a Windows-only technology? Sorry, I mentioned it being “headless” - I should also have said WebApp.

6

Yes


Is Windows-only tech.

WebApp.

It’s better to use it as a file.

7

Yes, with the caveat that XLSX files are actually ZIP files – basically, a document bundle in Mac terms – and you’ll have to extract the XML file out of it. But once you do, you can throw it at XMLDocument and you’ll have all the data in memory. The next task will be to parse it to get at the data, which isn’t terribly difficult.

8

No. XLSX files are actually zip archives with XML files inside. You’d need to extract the XML files and then use the XMLDocument class to read them.

9

Well is there at least a standardized naming structure to get to ‘workbook1’ etc? :crossed_fingers:t2:

10

I just peeked inside one and it looks like /xl/worksheets/sheet1.xml

1 Like