Excel and GH first step

Replies to This Discussion

Permalink Reply by raf2205 on August 6, 2011 at 7:57am

hi, still have some issues to output data on an excelsheet in C# here is my 2 differents codes:

in VB:




Dim oldCI As System.Globalization.CultureInfo = system.Threading.Thread.CurrentThread.CurrentCulture   

System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

Dim objExcel As Object

objExcel = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")

objExcel.Cells(1, 1).Value = "title1"

objExcel.Cells(1, 2).Value = "title2"

 

in C#:




System.Globalization.CultureInfo oldCI = new System.Globalization.CultureInfo("en-US");

Object objExcel;

objExcel = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

objExcel.Cells(2, 1).Value = "titleA";

objExcel.Cells(2, 2).Value = "titleB";

 

so the VB code is working good but the C# doesn't .

This seems to come from the Object type in C# which is not understood.

I tried many things without any good result if someone could help, it would be really great.

thx

Permalink Reply by Damien Alomar on August 6, 2011 at 4:44pm

http://www.grasshopper3d.com/forum/topics/excel-to-gh-new-service?p...

Permalink Reply by raf2205 on August 7, 2011 at 1:12am

thanks Damien, actually I red your post, which is really usefull. But not solve my issue. As you said "this assumes you already have gotten your workbook and worksheet references" . I don't know how to get my workbook and worksheet references in C#. I used a VB code that I found on the forum and try to wrote it in C#. But I have an issue with this line

Object objExcel;

objExcel = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")

I don't understand why but it seems Object is not working in C#..

when I will solved this be sure I will follow your recommandation to get and set value in excel ;)

 

 

Permalink Reply by Damien Alomar on August 7, 2011 at 8:38am

Do you happen to have Excel open?  That line is meant to retrieve a reference to a currently active instance of excel, so if you don't have one available, it will fail regardless of the syntax.

 

Assuming Excel is open, your syntax there will work, but its of very little use.  Dealing with things as objects is really only a good approach when you have absolutely no idea what things you might be dealing with, and more specifically, you don't care what kind of objects they are.  But since A) you do know what kind of object you need (an Excel.Application) and  B) you really need to do something with that object, your best bet is to cast that object as the specific type that you're looking for.  C# and VB.Net deal with type conversions a little bit differently, so this is likely where there's going to be some difference between how the two react.

 

So your statement above would become...

// fully qualified syntax is due to ambiguity between Excel and WinForms
Microsoft.Office.Interop.Excel.Application xlApp;
xlApp = (Microsoft.Office.Interop.Excel.Application) System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

 

Now its going to be very important to pay attention to the intellisense that pops up, because you need to make sure that you're getting the type back that you're looking for.  That Excel dll is VERY hit or miss when it comes to returning things as the type that is implied.

 

Now you can't just try and immediately retrieve the cell from the Excel object. At that point in time you're specifically working with the Excel application, not any specific excel file of sheet. In order to get down to the Cell level, you have to retrieve the workbook and the worksheet first. The two statements below will retrieve those for you, and again watch out for the types that are returned. These statements also assume that you have the file you'd like to work with open in Excel.

 

// returns a type of Workbook, so no cast needed
Workbook activeWorkbook = xlApp.ActiveWorkbook;
// returns a type of Object, so cast as a worksheet
Worksheet activeWorksheet = (Worksheet) activeWorkbook.ActiveSheet;

 

That should be able to bridge the gap between your issue and my previous post.  Let me know if you have any questions

Permalink Reply by raf2205 on August 8, 2011 at 1:11am

Hello Damien, thank  you really much for all these good explanations . Everything works fine now. You saved my day.

Thanks again !

raf

Permalink Reply by raf2205 on August 8, 2011 at 3:18am

sorry to come back to you soon but  have issue with adding a list to my excel sheet




Microsoft.Office.Interop.Excel.Application xlApp;
xlApp = (Microsoft.Office.Interop.Excel.Application) System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

// returns a type of Workbook, so no cast needed
Workbook activeWorkbook = xlApp.ActiveWorkbook;
// returns a type of Object, so cast as a worksheet
Worksheet activeWorksheet = (Worksheet) activeWorkbook.ActiveSheet;

for (int i = 0; i < myList.Count; i++) {        Microsoft.Office.Interop.Excel.Range cell = (Microsoft.Office.Interop.Excel.Range) activeWorksheet.Cells[i, 1];        cell.set_Value(Missing.Value, myList[i]);      }





but this returns me an error 

{0}0. error: Exception de HRESULT : 0x800A03EC (line: 0)

 

I googled it but do not really understand why..

I will continue my reseach but any inputs are welcome !

 

Permalink Reply by raf2205 on August 8, 2011 at 8:53am

got it ..

for those who are interested the loop has to start at 1 because no cell at [0,0] in excel..

 

for (int i = 1; i < myList.Count; i++) {        Microsoft.Office.Interop.Excel.Range cell = (Microsoft.Office.Interop.Excel.Range) activeWorksheet.Cells[i, 1];        cell.set_Value(Missing.Value, myList[i]);

Permalink Reply by Damien Alomar on August 8, 2011 at 3:21pm

haha... yea, completely forgot about cells not being zero based.  As you can tell programming with Excel is buckets of fun :)

Permalink Reply by raf2205 on August 8, 2011 at 9:03am

also a good link about excel class, and worksheet ,..

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.exce...