Implementing .NET external component - help please

[alanvl]

Hi, we are looking at using a excel .NET component for providing access to xlsx files. I have not implemented anything in .NET and am having difficulty in finding my way around.

I have found the following VB example just for opening the workbook, but am even having difficulty in translating that to Jade
Dim excel As Application = New Application
Dim w As Workbook = excel.Workbooks.Open("C:\file.xls")

Having imported the component, creating the dot net object for the ApplicationClass I am not sure where to go next. "workbooks" is a reference attribute on the class but is of type JadeDotNetType - there is a Workbook (not Workbooks) subclass of JadeDotNetType ...

What I am after is a pointer to some documentation (had a look for a white paper - if it exists I missed it) that would provide guidance on how to develop Jade code that implements .NET interfaces. OR if someone could provide an example of how to open files via excel and access cell contents that would also be a good start.

We are currently using Jade 6.3.07 and Office 2010

[torrie]

Hi Alan

We do a lot of excel work but we use the active X automation not .Net. We imported the Excel Active X Automation library. We can then use these classes to open / drive excel.

In order to work out the code for Excel, we record a Macro in Excel of what we need to do and then look at the code of the Macro to work out what code is needed in Jade.

For example, the macro to create a new workbook is

Code: Select all

Workbooks.Add

In Jade this translates to (First two lines are to open Excel, last line to get the active worksheet.)

Code: Select all

// Create the application create zMyXLApplication; zMyXLApplication.createAutomationObject(); // Add a new workbook zMyActiveWorkbook := zMyXLApplication.workbooks.add( null ); // Get the active Sheet zMyActiveWorksheet := zMyActiveWorkbook.activeSheet.getInterface( I_Worksheet ).I_Worksheet;

Notice how the last line uses the getInterface method to get the worksheet as a I_Worksheet object. This is the method to use when type casting. I'm not sure but you may just need to type cast the .Net object returned to the correct type.

All the help is available on the MSDN site (or just use google.)

Note: Each time you call an excel function that creates an excel object, the relevant object is created in Jade. These need to be cleaned up. For example a method that gets a cell and formats it as bold, the range returned needs to be deleted.

Code: Select all

setRange_Bold( piRow : Integer; pcColumn : String; piRow_2 : Integer; pcColumn_2 : String ); vars oRange : Range; begin oRange := zMyActiveWorksheet.range( pcColumn & piRow.String, pcColumn_2 & piRow_2.String ); oRange.font.putBold( true ); epilog delete oRange; end;

Secondly, each call to Excel is a round trip between the application server and the thin client. However once you've worked out the process, you could code a .Net DLL or similar that would do all the excel maniuplation in the dll. That way Jade only makes one call to the dll, not 20 or more calls to Excel.

If you are populating a data table into Excel, it is much more efficient to extract it as a CSV file and then import that file into Excel. We've tried setting the value of each cell, but it just too slow with large data sets.

Cheers

Torrie

[BeeJay]

If you are populating a data table into Excel, it is much more efficient to extract it as a CSV file and then import that file into Excel. We've tried setting the value of each cell, but it just too slow with large data sets.

Another option is to build the data into a string with Tab separators between the columns and CrLf between the rows. Then you can copy this string to the clipboard and do a paste into an appropriate range selection. This is much quicker than individually setting each cell as per the following "quasi Jade code" where the data is already in a local variable called reportData:

Code: Select all

app.copyStringToClipboard( reportData ); range := sheet.range( 'A1', null ); sheet.paste( range, null );

Cheers,
BeeJay.