Write a 2D array of objects in range Excel C#

Question

I created a new user defined function in excel using c#, excelDna Add-In,what i want to get is when a user open an excel file, clic on a cell and write =myFunction() press enter, data should be displayed in the excel file. these data are retrieved from sql server database and stocked in a 2D array of object, my problem is when i try to display this array in excel range i got this exception Exception de HRESULT : 0x800A03EC Below is my code :

public static void LoadViewData()
  {
      var target = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
      var sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, target);
      var application = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
      var sheet = application.Sheets[Regex.Replace(sheetName, @"\[[^]]*\]", string.Empty)];
      object[,] result = LoadFromDbData();
      var startCell =sheet.Cells[target.RowFirst + 1, target.ColumnFirst];
      var endCell =sheet.Cells[target.RowFirst+ result.GetUpperBound(0) - result.GetLowerBound(0) + 1,
          target.ColumnFirst+ result.GetUpperBound(1) - result.GetLowerBound(1) + 1];
      var writeRange = sheet.Range[startCell, endCell];

      writeRange.Value2 = result;

  }

target returns the correct value of the cell where the user has written the formula (=myFunction()) sheetName returns the correct activeSheet in which the user writes the formula result contains the data retrieved from sql server, it is an array of object[854,8] startcell and endcell represents the range from which cell to which cell data will be displayed when debugging, all variables contain the correct values, the exception appears in this instruction :

writeRange.Value2 = result;

Anyone has already worked with this or can help please ? Thanks

It looks like you are trying to modify the Excel sheet from a user-defined function, called from a worksheet formula. Excel does not allow this (Range.Value = ... will always fail when called in a UDF function). You need to run the code from a ribbon button or something. In that case you have to change from using the "Caller" to getting the ActiveCell to decide where to update. — Govert
– Govert, Commented Apr 12, 2018 at 16:20

Cetin Basoz · Accepted Answer · 2018-04-12 15:55:11Z

I think your "LoadFromDbData()" is returning the data as typed. Try converting each value to a string. Here is a sample (and I can recreate that error code if I do not convert to string):

void Main()
{
  var tbl = new System.Data.DataTable();
  new SqlDataAdapter(@"
  WITH  tally ( OrderNo, UniqueId, RandNumber )
        AS (
             SELECT TOP 50000
                    ROW_NUMBER() OVER ( ORDER BY t1.object_id ), 
                    NEWID(),
                    CAST(CAST(CAST(NEWID() AS VARBINARY(4)) AS INT) AS DECIMAL) / 1000
             FROM   master.sys.all_columns t1
             CROSS JOIN master.sys.all_columns t2
           )
  SELECT  OrderNo, 
    DATEADD(DAY, -OrderNo, GETDATE()) as OrnekDate, 
    UniqueId, RandNumber, 
    abs(RandNumber)%100 / 100 as pct
  FROM [tally];", @"server=.\SQLExpress;Database=master;Trusted_Connection=yes;").Fill(tbl);

  object[,] arr = new object[tbl.Rows.Count + 1, tbl.Columns.Count];
  for (int i = 0; i < tbl.Columns.Count; i++)
  {
    arr[0, i] = tbl.Columns[i].Caption;
  }
  for (int i = 0; i < tbl.Rows.Count; i++)
  {
    for (int j = 0; j < tbl.Columns.Count; j++)
    {
      arr[i + 1, j] = tbl.Rows[i][j].ToString(); // without .ToString() you should have the error
    }
  }

  // Excel dosya yarat ve arrayi koy
  Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
  var workbook = xl.Workbooks.Add();
  xl.Visible = true;

 Worksheet sht = ((Worksheet)workbook.ActiveSheet);
 Range target = (Range)sht.Range[ (Range)sht.Cells[1,1], (Range)sht.Cells[arr.GetUpperBound(0)+1,arr.GetUpperBound(1)+1] ];
 target.Value2 = arr;

}

Note: As a side note, why would you transfer the data as a 2D array? That is one of the ways, but beware it is limited (I don't know a good value for the upper limit - try a high number like 200K rows). Getting data into excel is best done via QueryTables.Add or CopyFromRecordSet to my experience. Depending on your needs you might also directly use the Excel file itself as a data table and do inserts. There is also EPPlus library on Nuget but that would be a little bit slow + may not contain all the capabilities you need.

Collectives™ on Stack Overflow

Write a 2D array of objects in range Excel C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related