Ada interface to Excel file

[AdaMagica]

I create Ada code from an Excel file. For this, I first manually export the file to csv format. The code generator works on the csv file.
I'd like to automate this first step by including the export into the code generator.

I guess there is a C interface for Excel. I only just need the export functionality, not a full interface.
However, being illiterate in C, I'd further welcome help on the way to define an Ada interface to this C code.

Can anyone help, please? Thanx a lot.

Christoph

[Jeffrey R.Carter]

G. de Montmollin has an Ada Excel writer, an Ada pkg for writing Excel files
(https://sourceforge.net/projects/excel-writer/). Presumably it could be
modified to read them.

--
Jeff Carter
"I spun around, and there I was, face to face with a
six-year-old kid. Well, I just threw my guns down and
walked away. Little bastard shot me in the ass."
Blazing Saddles
40

[Dmitry A. Kazakov]

AFAIK, Excel has an ODBC driver. So you can simply read/write an Excel
table directly from Ada using ODBC SQL statements.

--
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

[Gautier write-only address]

The simplest way by far is to generate the Ada sources
from within Excel by using VBA (a BASIC with a strong Ada flavour,
but still a BASIC) which is part of Excel.
From Excel, you activate VBA with the Alt-F11 shortcut.

You have "modules" which are just editor files and visible from
everywhere else (you have like implicit "with"'s and "use"'s); you
have functions called "Function" and procedures called "Sub".
You can associate a button in the Excel sheet to a Sub.
You declare each variable with "Dim x As Type".
You can also forget to declare variables, with funny outcomes.
The behavious of the type system around parameter passing is also funny.

You find below a few examples.
Now, if you already have your CSV-to-Ada generator, you can export a CSV; that's also easy with VBA.

G.

[somewhere (some module)]
Sub Generate_for_Production()
Dim anchor As String ' VBA String is Ada's Unbounded_String
' File handles
Dim fh_ada_spec As Integer
Dim fh_ada_body As Integer
...
Open ThisWorkbook.Path & "\src\" & pkg_name & _
".ads" For Output As #fh_ada_spec
...
For Each ws In Worksheets ' Scan all worksheets
For Each r In ws.UsedRange.Rows ' Scan all used rows
anchor = r.Cells(1, 1).Value
If anchor <> "" Then
...
End If
Next r
Next ws
...
Close #fh_ada_spec
End Sub

[somewhere else (perhaps another module)]

Print #fh, "with Ada.Calendar;"
Print #fh, "with Ada.Unchecked_Conversion;"
Print #fh, "with Interfaces;"
Print #fh,
Print #fh, "package " & name & " is"

[somewhere else]

If simple_record Then
Print #fh_ada_spec, " -- Simple record."
Print #fh_ada_spec,
Print #fh_ada_spec, " type Xyz is record -- " & paragraph
Else
Print #fh_ada_spec, " type Xyz is new " & parent_name & _
"Abc with record -- " & paragraph
End If

[somewhere else]

For i = min_row_offset To max_row_offset
' Convert name in cell to Ada name
field = Ada_Name(r.Cells(i, 3).Value)
If field = "" Then
Exit For
End If
amount = r.Cells(i, 6).Value
...
Print #fh_ada_body, " for index in 1 .. " & amount & " loop"
Print #fh_ada_body, " declare"

[Gautier write-only address]

> G. de Montmollin has an Ada Excel writer, an Ada pkg for writing Excel files
> (https://sourceforge.net/projects/excel-writer/). Presumably it could be
> modified to read them.

Actually, it is a completely different job. Note that it is the case for many formats (think of HTML or XML for instance).

Now, you find in the Excel Writer toolbox a program called biff_dump.adb that supports some early Excel formats and could be extended.
For the current format(s) (.xlsx), you can combine Zip-Ada for the container and XML-Ada for the contents.
Same for the .ods format.

[Björn Lundin]

On 2023-04-20 21:08, Gautier write-only address wrote:

> You can also forget to declare variables, with funny outcomes.

I set
option explicit
at the top of the module, which makes it complain if variable not decalared.

Or used to 25 years ago anyway.

option base 1
is good too, to make array index start at 1 instead of 0


VBA - long time ago - but some fond memories

--
/Björn

[Jeffrey R.Carter]

On 2023-04-20 21:24, Gautier write-only address wrote:
>> G. de Montmollin has an Ada Excel writer, an Ada pkg for writing Excel files
>> (https://sourceforge.net/projects/excel-writer/). Presumably it could be
>> modified to read them.
>
> Actually, it is a completely different job. Note that it is the case for many formats (think of HTML or XML for instance).

"Modified" was a poor choice of words. "Used to figure out how" is more what I
had in mind.

--
Jeff Carter
"If change threatens you, you become conservative
in self-defense. If it thrills you, you become
liberal in self-liberation. ... [T]he Threateneds
are frequently more successful in the short run,
because they always fight dirty. But in the long
run, they always lose, because Thrilled people
learn and thus accomplish more."
Variable Star
220

[AdaMagica]

Thank you all for your replies.
This doesn't look easy so I guess I just leave it as is. Thanx again.
Christoph