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

unhandled

#1

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


#2

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


(Graham) #3

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);

#4

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 Dekeyser) #5

Check this post:


#6

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


#7

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


#8

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


#9

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