Write to EXCEL from FORTRAN use COM Interface

[Stephen_Sutcliffe1]

I have successfully managed to get a Fortran programs to write data to an Excel worksheet using a cell-by-cell approach and also by writing a row of similar data types (reals, integers or strings) in an array but I'm struggling to get the Fortan program to:

1) write columns of data

2) write columns or rows with various data types stored in an array of VARIANT's.

While writing cells individually works fine it is very slow compared to writing complete rows, columns or even 2 dimensional arrays.

A snippet of the source is shown below which writes row of intergers stored in the array vals. When this routine is called the WorkSheetID has already been established. The Exl_CellRef routines converts row,column numbers into Excel notation.

integer function Exl_PutRowVectorInt(row,col,n,vals)

!*******************************************************
! Put Integer array values into excel spreadsheet
!*******************************************************

implicit none

! Arguments
integer(K_OBJ),intent(in) :: row
integer(K_OBJ),intent(in) :: col
integer,intent(in)              :: n
integer,intent(in) :: vals(n)

! Local Variables
character*20 :: cell,endc
type(VARIANT) :: vBSTR1,vBSTR2
integer :: ier
integer :: status

! Initialise
ier = -1
if(n.eq.0) return

! Convert Row/Cell to Cell Reference
call Exl_CellRef(row,col,cell)
call Exl_CellRef(row,col+n-1,endc)
call VariantInit(vBSTR1)
vBSTR1%VT = VT_BSTR
bstr1 = ConvertStringToBSTR(cell)
vBSTR1%VU%PTR_VAL = bstr1
call VariantInit(vBSTR2)
vBSTR2%VT = VT_BSTR
bstr2 = ConvertStringToBSTR(endc)
vBSTR2%VU%PTR_VAL = bstr2
RangeID = $WorkSheet_GetRange(WorkSheetID,vBSTR1,vBSTR2,$STATUS=status)
ier = Exl_Status(status,"Error: Unable to get Real Range object "//trim(cell)//'-'//trim(endc))
if(ier.eq.0) then
  ier = AutoSetProperty(RangeID,'Value',vals)
endif

! Return Status
Exl_PutRowVectorInt = ier

return
end function

If Itry to use similar logic to write a column the first value of the array "vals" is written to every cell in the column.

When I try to change the type of variable vals to a VARIANT the compiler coplains of:- "There is no matching specific function for this generic function reference.   [AUTOSETPROPERTY]. This implies that you can't write a row (or column) of mixed variables in a single operation.

Has anyone got any hints or tips in solving this issue?

Any help will be much appreciated.

[Les_Neilson]

You could try looking at the code from Paul Curtis posted here If it doesn't do exactly what you want maybe it will provide some ideas.

Les

[andrew_4619]

From recall you can write rows but not columns, if you search this forum you will find some old threads on this subject.

[andrew_4619]

and to your other point, if you look in ifauto.f90 all the interafaces to write arrays of data are all 1d arrays of a common type ie all integer(4) or real(8) etc. not mixed so I infer mixed type array writes are not available,

[Stephen_Sutcliffe1]

Thanks,

I thought this might be the case but I don't see why these features aren't available as they would be very useful.

If we are limited to rows of similar data types and individual cells my only hope of speeding up the process of populatiing Excel is to turn off the excel features that might cause a speed reduction. I'm not sure what features are ON by default though.

[andrew_4619]

auto calculate and dispaly update are good features to turn off.

CALL $Application_SetVisible(excelapp, .FALSE._2,status)
CALL $Application_SetScreenUpdating (excelapp, .FALSE._2,status)

Writings CSV or TSV data to import into excel might be quicker I create csv data when my software cannot find a valid version of excel installed. 

[IanH]

I [think] you can write columns, or 2D arrays (well, I hope so, because it looks like I do).  Bear in mind that I look at this perhaps once per year (during which time the first law of programming applies - i.e. "num_features - num_bugs == constant") so my memory is flaky.  I go via the path of using a variant (that holds a 2D COM safe array - created using SafeArrayCreate, populate using lots of calls to SafeArrayPutElement) and then poking things through using Range_SetValue2.  The shape of the data that you are setting needs to matches the shape of the range (and the dimension order might swap between COM arrays and Fortran) - I know this is something that I frequently have muddled up in corner cases and what the OP describes sounds like the consequences of that sort of mismatch (i.e. their data has been tessellated and truncated rather than transposed).

I'm sure there's a bit of redundant copying of data going on, but you don't have the COM overhead associated with each call into Excel.  The result is snappy enough that I pull quite large arrays (for excel) in from a HDF5 file at speeds that are effectively instantaneous from the users' point of view.

Apparently I now need to torture myself by going for a five hour drive to the big smoke with two young children in the back seat, but I can post code when I return from this particular expedition in a day or two.

[Stephen_Sutcliffe1]

Thanks Ian, some code examples would be a great help if you get the chance to post them.

Thanks again

[IanH]

If you pop this little VBA driver into a macro enabled spreadsheet....

[plain]Option Explicit

Private Declare Sub GoGetMeAnArray Lib "C:\PathToWhereTheDLLIs\GoGetMeAnArray.DLL" _
  (ByVal rng As Range)

Sub Test()
  Call GoGetMeAnArray(Range("B3"))
End Sub[/plain]

and then marry the spreadsheet up with the DLL that results from compiling the attached Fortran to a DLL, then you might have an example that suits.  It transfers a 30 x 300 sized array of random numbers.  Things have been spliced together a bit to make this so it isn't very neat, plus some things are probably a little odd because I didn't quite understand what I was doing at the time (or now, for that matter...).  If you see something silly let me know.