Ada interface to Excel file

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

Re: Ada interface to Excel file

[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

Re: Ada interface to Excel file

[Dmitry A. Kazakov]

On 2023-04-19 19:36, AdaMagica wrote:
> 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.

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

Re: Ada interface to Excel file

[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"

Re: Ada interface to Excel file

[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.

Re: Ada interface to Excel file

[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

Re: Ada interface to Excel file

[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

Re: Ada interface to Excel file

[AdaMagica]

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