Create spreadsheet using Open XML SDK .Net with Labview

[JF3M]

I see that it is easy to create spreadsheets and write data to such sheets using the Open XML SDK .Net assemblies.  There are examples out there for VB.Net and C#.  I thought I'd be able to reproduce this in Labview by using the .Net capabilities of Labview.

The steps in C# are...

            // Create a spreadsheet document by supplying the file name.
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                Create(fileName, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

I am stuck at the part of adding sheets to the workbook.  I cannot get Labview to pull up the Method of AddNewPart.  I checked the Open XML SDK with ILSpy to see what may be unique about this method as I can see all the other methods, but not AddNewPart.  I see in ILSpy that AddNewPart is a virtual method.  Is this why I can't see it in Labview and if so, is there a way around this?

I have it working where it creates the file and adds the WorkBookPart, but the file isn't quite valid yet until I can get past the next hurdle (the AddNewPart method).  For example, this...

Thanks!

[aputman]

It looks like you'll need to instantiate a workbookpart object, much like you did to get a spreadsheet object, to get at that function.  AddNewPart() is a function of the WorkbookPart object and you haven't created one of those yet.

[tyk007]

---

@JF3M wrote:

I see that it is easy to create spreadsheets and write data to such sheets using the Open XML SDK .Net assemblies.  There are examples out there for VB.Net and C#.  I thought I'd be able to reproduce this in Labview by using the .Net capabilities of Labview.

The steps in C# are...

            // Create a spreadsheet document by supplying the file name.
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                Create(fileName, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

I am stuck at the part of adding sheets to the workbook.  I cannot get Labview to pull up the Method of AddNewPart.  I checked the Open XML SDK with ILSpy to see what may be unique about this method as I can see all the other methods, but not AddNewPart.  I see in ILSpy that AddNewPart is a virtual method.  Is this why I can't see it in Labview and if so, is there a way around this?

I have it working where it creates the file and adds the WorkBookPart, but the file isn't quite valid yet until I can get past the next hurdle (the AddNewPart method).  For example, this...

Thanks!

---

LabVIEW doesn't support creating new generic types from the generic base type, which is what your code sample does:

WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();

See this: http://digital.ni.com/public.nsf/allkb/DC41DCDA972642CF8625787E00732DDD

To get around this the best way is to create a custom assembly (with the steps you have listed above) and then call this in LabVIEW.

[JF3M]

Thanks for the replies.

I did see this help document that tyk007 referred to, but didn't understand the terminology used, so didn't quite understand what they were telling me or the implications.

I didn't follow the workaround because I didn't understand how to do what they were telling me.

Does this mean I need (or have someone) go in to Visual Studio and create some code to do these steps and make an assembly that I'd call in Labview, then be able to use the Open XML SDK to do other things like add data to the spreadsheet?

If this is it, is there a guide or example I could follow to do this.  I am not a C# programmer.

Thanks!

[tyk007]

---

@JF3M wrote:

Thanks for the replies.

I did see this help document that tyk007 referred to, but didn't understand the terminology used, so didn't quite understand what they were telling me or the implications.

I didn't follow the workaround because I didn't understand how to do what they were telling me.

Does this mean I need (or have someone) go in to Visual Studio and create some code to do these steps and make an assembly that I'd call in Labview, then be able to use the Open XML SDK to do other things like add data to the spreadsheet?

If this is it, is there a guide or example I could follow to do this.  I am not a C# programmer.

Thanks!

---

Yes, you need to compile a new assembly. You could use Visual Studio or a number of tools to do so. I don't know much about the Open XML SDK but I'm guessing that there might be other generic classes involved to do what you are trying to accomplish so it is possible that the problem might not end there. Any time that you see a "new" instantiation and the angle brackets "<" or ">" around the type it indicates the creation fo a new type from a generic template, which is what you cannot do in LabVIEW. Without the experience this would be challenging to do. 

My advice is to ask a colleague that has C# or VB.NET experience to roll an assembly together that does the sequence of events you have right now and work from there. 

Or use the Reporting functions in LabVIEW to export data to Excel (see about half-way down http://www.ni.com/newsletter/51339/en/)

[Bob_Schor]

tyk007 wrote:

Or use the Reporting functions in LabVIEW to export data to Excel (see about half-way down http://www.ni.com/newsletter/51339/en/)

---

Or, if you are running LabVIEW 2014 or 2015, you can use the excellent Excel "hooks" in the Report Generation Toolkit to read and write Excel .xlsx Workbooks.

Bob Schor

[JF3M]

Thanks for the info!

One reason I was trying to do it with OpenXML SDK was I needed more than just basic data to Excel format.  I needed graphs with detailed control over how they are formatted.  I also liked the thought that Excel doesn't have to exist or run in the background as in ActiveX methods.  I liked the speed and writing in native .xlsx format.  I had tried some of things suggested in that NI link, but it didn't seem to give me enough control.

There are also some other .Net assemblies that do something similar.  I have tried EPPlus for example, which seems easier than the Open XML SDK, but it also didn't seem to give me enough control, but I didn't go far enough to confirm this.

I will explore these suggestions and see where I come out.  There are certainly some gotchas with Labview .Net.  On the surface it seems somewhat easy to do, but when running in to these detailed problems that you aren't familiar with, things get sticky.  

If anyone else has any further ideas, please post.

[EWiebe]

Here is my example with EPPlus (4.1.0.0)

1. I had to allow loading and trusting all .NET assemblies in LabView:

(like described in http://digital.ni.com/public.nsf/allkb/4742EB60B64BE22186257BCE0053B8FD)

Added LabView.exe.config with this content near to LabView.exe

<?xml version ="1.0"?>
<configuration>
  <runtime>
    <loadFromRemoteSources enabled="true" />
  </runtime>
</configuration>

2. Load EPPlus DLL in FGV

It works !

BR

EWiebe