How to use Microsoft.Office.Interop.Excel in C#Script

[flokart]

I try to use c# to get access to Microsoft.Office.Interop.Excel but its not available how to activate this library so i can use it

using Microsoft.Office.Interop.Excel

Help is welcome

[flokart]

Ok after a lot of search in the www i found the assembly manager and Add the Microsoft.Office.Interop.Excel DLL inside.
When i try to get down to the worksheet i stuck and i dont know what this error means.
What works so far is.

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path);

But the sheet is not working.

Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1];

1. Error (CS0266): Cannot implicitly convert type ‘object’ to ‘Microsoft.Office.Interop.Excel.Worksheet’. An explicit conversion exists (are you missing a cast?) (line 60)

another try (prefered)

Excel.Worksheet xlWorksheet = xlWorkbook.Sheets("Liste");

1. Error (CS1955): Non-invocable member ‘Microsoft.Office.Interop.Excel._Workbook.Sheets’ cannot be used like a method. (line 60)

Any small hint is welcome

[Dancergraham (Graham Knapp)]

Hi,
Not sure but there seem to be a few online suggestions pointing to CType(Object, type) . Maybe something like :

Excel.Worksheet xlWorksheet = CType(xlWorkbook.Sheets[1], Microsoft.Office.Interop.Excel.Worksheet);

[flokart]

Morning Graham,

found already a solution for me…it seams the decleration…C# cant make Excel.Worksheet to an Object (I guess). So this line works. Will check your example also

Excel._Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet) xlWorkbook.Sheets["Liste"];

Your help is very welcome
How it comes that your code text have the right color format ?

[wim (Wim Dekeyser)]

@flokart

How it comes that your code text have the right color format ?

Check this post:

Category definition for Scripting

Topics related to RhinoScript and Python scripting. Syntax Highlighting: Format and highlight your script code by fencing your code block within three back ticks. If you name the language your script is in, you get syntax highlighting, too: Python: ```python # Your python code here print “Hello Pythonic World” ``` becomes #Your python code here print "Hello Pythonic World" VBScript: ```vbscript ’ Your VBScript code here print “Hello VBScript” ``` becomes ' Your VBScript code he…

[flokart]

Ok after a lot of try and error (first two days in C#) the first working script is there BUT ITS SUPER SLOW.
They mentioned to get the whole excel table (red dot in the picture) and extract cells from there instead to loop over the excel cells directly…BUT IT MAKES NO DIFFERENCE so i guess i made a super dump mistake with script updating.

Anyway maybe Interop is the wrong way to interchange data with excel.
Any tip to improve the script is alway welcome

[flokart]

I managed to create a excel reader and writer in C# with Microsoft.Office.Interop but it is super slow and make not fun to interact so tried another approche with System.Data.OleDb and this is fastest respons between exce.xlsx and grasshopper c#…hell on earth is this connection fast…i am so happy so here is the code if someone needs it.

using System.Data.OleDb;
using System.Data;


if (Execute)
    {
      OleDbConnection con = new OleDbConnection();
      con.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;"
        + "Data Source = "+PathOpen+";"
        + "Extended Properties=\"Excel 12.0 Xml;HDR=YES\"");
      OleDbCommand com = new OleDbCommand("select * from [" + Sheet + "$" + RowColRange + "]", con);
      System.Data.DataSet dSet = new System.Data.DataSet();
      OleDbDataAdapter dAd = new OleDbDataAdapter(com);
      dAd.Fill(dSet);

      DataTree<string> myTree = new DataTree<string>();
      int colCount = dSet.Tables[0].Columns.Count;
      for (int c = 0 ; c <= colCount - 1; c++)
      {
        foreach(DataRow row in dSet.Tables[0].Rows)
          myTree.Add(("" + row[c]), new GH_Path(c));
      }
      A = myTree;

    }

Hopfully this can be used to write and saveAs existing xlsx files

[flokart]

After two days of hard time to learn the c# basics and poi library IT WORKS…JIPPI and it is superfast compare to Interop in python so if someone needs a excel xls writer here is the C# code.

  if (Execute)
    {
      File.Copy(PathOpen, PathSave, true);
      HSSFWorkbook wb;
      string path = PathOpen;
      using (FileStream fs = new FileStream(PathSave, FileMode.Open, FileAccess.Read))
      {
        wb = new HSSFWorkbook(fs);

      }

      ISheet sheet = wb.GetSheet(Sheet);
      CellReference c = new CellReference(RowColStart);
      int columnC = (int) Table.Branches.Count;
      int rowC = (int) Table.Branch(0).Count;
      Print(c.Row.ToString());

      for (int i = c.Row  ; i <= c.Row + rowC - 1 ; i++)
      {
        IRow row = sheet.GetRow(i + 1);
        if (row == null)
        {
          IRow rowCr = sheet.CreateRow(i);
          for (int r = c.Col  ; r <= c.Col + columnC - 1  ; r++)
          {
            rowCr.CreateCell(r).SetCellValue((Table.Branch(r - c.Col)[i - c.Row]).ToString());
          }
        }
        else
        {
          for (int r = c.Col  ; r <= c.Col + columnC - 1 ; r++)
          {
            ICell cell = row.GetCell(r, MissingCellPolicy.RETURN_NULL_AND_BLANK);
            if (cell == null)
            {
              row.CreateCell(r).SetCellValue((Table.Branch(r - c.Col)[i - c.Row]).ToString());
            }
            else
            {
              cell.SetCellValue((Table.Branch(r - c.Col)[i - c.Row]).ToString());
            }
          }
        }
      }
      using (FileStream fs = new FileStream(PathSave, FileMode.Open, FileAccess.Write))
      {
        wb.Write(fs);
      }
    }

This was not easy for me…i didnt know that i have to analyse wether the row or cell is null before i can create or write to it…a lot of extra work.BUT I HAVE DONE IT YEA.

If someone needs information i can provide the assembly link

[flokart]

After some tests i realised that the Tree item format have to be analysed to so that excel formating it corectly
set to double or string than the workbook need to recalculate all formulas.
I set empty entries to 0 to avoid recalculated fourmales on empty …i dont know how to reclaculate only the specific cells.
here is the new code for the writer.

if (Execute)
{
HSSFWorkbook wb;
using (FileStream fs = new FileStream(PathOpen, FileMode.Open, FileAccess.Read))
{
wb = new HSSFWorkbook(fs);
}

  ISheet sheet = wb.GetSheet(Sheet);
  CellReference c = new CellReference(RowColStart);
  int columnC = (int) Table.Branches.Count;
  int rowC = (int) Table.Branch(0).Count;
  Print(c.Row.ToString());

  for (int i = c.Row   ; i <= c.Row + rowC - 1 ; i++)
  {
    IRow row = sheet.GetRow(i);
    if (row == null)
    {
      IRow rowCr = sheet.CreateRow(i);
      for (int r = c.Col  ; r <= c.Col + columnC - 1  ; r++)
      {
        var x = Table.Branch(r - c.Col)[i - c.Row];
        string B = Convert.ToString(x);
        if (string.IsNullOrEmpty(B))
        {
          rowCr.CreateCell(r).SetCellType(CellType.Blank);
        }
        else
        {
          double dob;
          if (double.TryParse(B, out dob))
          {
            rowCr.CreateCell(r).SetCellValue(dob);
          }
          else
          {
            rowCr.CreateCell(r).SetCellValue(0);
          }
        }
      }
    }
    else
    {
      for (int r = c.Col  ; r <= c.Col + columnC - 1 ; r++)
      {
        ICell cell = row.GetCell(r, MissingCellPolicy.RETURN_NULL_AND_BLANK);
        if (cell == null)
        {

          var x = Table.Branch(r - c.Col)[i - c.Row];
          string B = Convert.ToString(x);
          if (string.IsNullOrEmpty(B))
          {
            row.CreateCell(r).SetCellValue(0);
          }
          else
          {
            double dob;
            if (double.TryParse(B, out dob))
            {
              row.CreateCell(r).SetCellValue(dob);
            }
            else
            {
              row.CreateCell(r).SetCellValue(0);
            }
          }
        }
        else
        {
          var x = Table.Branch(r - c.Col)[i - c.Row];
          string B = Convert.ToString(x);
          if (string.IsNullOrEmpty(B))
          {
            cell.SetCellValue(0);
          }
          else
          {
            double dob;
            if (double.TryParse(B, out dob))
            {
              cell.SetCellValue(dob);
            }
            else
            {
              cell.SetCellValue(B);
            }
          }
        }
      }
    }
  }
  wb.ForceFormulaRecalculation = true;
  using (FileStream fs = new FileStream(PathSave, FileMode.Create, FileAccess.Write))
  {
    wb.Write(fs);
  }
  wb.Close();
}

I have to say that POI is not easy as interop but much faster

[su.lwpac]

Hey Flokart! thats amazing. I am working on a similar data transfer from grasshopper to Excel, even though there are so many excel plugins for Grasshopper they all have certain limitations when it comes to preserving the formatting and formulaes. I am trying to look for the POI library that you used. I did find the library but I dont know the right assembly to link to the C#. Do you mind sharing the files.

My Email is shaique.uddin@gmail.com

Thanks in advance!

[benedict (Ben)]

@flokart:

wow, thank you very much for sharing all this process in which I was about to through myself the next few nights, but you already did that and are cool enough to share!!!

Thank you!

[flokart]

Thanks man your welcome feel free to enrich this threat with your experience

[benedict (Ben)]

i will try to. Just got a very urgent very big work in, so probably it won’t be before a month
But thumb up for this open source spirit of yours!

[nils_d (Nils)]

Hi Flokart!
This is very good! Thanks for the sharing! Im trying to connect grasshopper and excel in a similar way but I dont get the POI library to work. Do you mind sharing the files with me aswell?

My Email is ndahlman1@gmail.com

Thanks in advance!

[flokart]

Hi,
the zip folder include all dll. files that you need to reference with the assembly manager from the c# component.
The gh file includes the xls writer.
Be aware that the component check what type is in the open file and write it to the save file( string to string int to int…string to int dont work)

xls_writer.gh (6.8 KB)
Poi_assamblies.zip (1.4 MB)

[mark_flayd (Medico Family)]

You just simply use zetexcel to Create an “excel sheet” through C# script. Just download from ZetExcel.com work very quickly.

[benedict (Ben)]

I completely forgot about this thread and developed a simple c# script that writes values to excel. As there are some problems occured lately with existing ToExcel components, I thought I’d share the code here, so people can choose between all these nice solutions

here’s my code:

if(!stream) return;
    //Thread.Sleep(500);
    try
    {
      Excel.Application xlsA = null;
      try
      {
        xlsA = (Excel.Application) System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
      }
      catch
      {
        xlsA = new Microsoft.Office.Interop.Excel.Application();
      }
      xlsA.Visible = true;
      xlsA.DisplayAlerts = false;
      if(!File.Exists(path))
      {
        xlsA.Workbooks.Add();
        Excel.Workbook tmp = xlsA.ActiveWorkbook;
        tmp.SaveAs(path);
        //Marshal.ReleaseComObject(tmp);
        tmp.Close();
      }

      Excel.Workbook book = xlsA.Workbooks.Open(path);
      Excel._Worksheet xlWs = (Excel.Worksheet) xlsA.ActiveSheet;

      List<int> spos = PositionParse(start);
      for(int i = 0;i < values.BranchCount;i++)
      {
        int cposx = i + spos[0];
        for(int j = 0;j < values.Branch(i).Count;j++)
        {
          int cposy = j + spos[1];
          xlWs.Cells[cposy, cposx] = values.Branch(i)[j];
        }
      }
    }
    finally
    {

    }

  public List<int> PositionParse(string str)
  {
    List<int> outL = new List<int>();
    char[] delimiters = {','};
    string[] vals = str.Split(delimiters);
    for(int i = 0;i < vals.Length;i++)
    {
      outL.Add(Convert.ToInt32(vals[i]));
    }
    return outL;
  }

working with interop.excel.dll:

Microsoft.Office.Interop.Excel.rar (221.1 KB)