Write Array to Excel Range

Question

I m currently trying to write data from an array of objects to a range in Excel using the following code  where objData is just an array of strings   private object m   System Type Missing  object   objData   getDataIWantToWrite     Range rn Temp  rn Temp    Range XlApp get Range RangeName  m   rn Temp   rn Temp get Resize objData GetUpperBound    1   rn Temp value2   objData    This very nearly works  the problem being that the range gets filled but every cell gets the value of the first item in the objData   The inverse works  i e   private object m   System Type Missing  object   objData   new object x y   Range rn Temp  rn Temp    Range XlApp get Range RangeName  m   rn Temp   rn Temp get Resize objData GetUpperBound    1   objData    object   rn Temp value2    would return an array containing all of the values from the worksheet  so I m not sure why reading and assignment work differently   Has anyone ever done this successfully  I m currently writing the array cell by cell  but it needs to cope with lots   50 000  of rows and this is therefore very time consuming

User · Answer

Thanks for the pointers guys - the Value vs Value2 argument got me a different set of search results which helped me realise what the answer is. Incidentally, the Value property is a parametrized property, which must be accessed through an accessor in C#. These are called get_Value and set_Value, and take an optional enum value. If anyone's interested, this explains it nicely.

It's possible to make the assignment via the Value2 property however, which is preferable as the interop documentation recommends against the use use of the get_Value and set_Value methods, for reasons beyond my understanding.

The key seems to be the dimension of the array of objects. For the call to work the array must be declared as two-dimensional, even if you're only assigning one-dimensional data.

I declared my data array as an object[NumberofRows,1] and the assignment call worked.

User · Answer

The kind of array definition seems the key  In my case it is a one dimension array of 17 items which have to convert to a two dimension array  Defintion for columns  object    Array   new object 17  1    Defintion for rows object    Array  new object 1 17    The code for value2 is in both cases the same             Excel Range cell   activeWorksheet get Range Range               cell Value2   Array   LG Georg

User · Answer

when you want to write a 1D Array in a Excel sheet you have to transpose it and you don t have to create a 2D array with 1 column   n  1   as I read above  Here is a example of code     wSheet Cells RowIndex  colIndex  Resize RowsCount    Value    excel Application transpose My1DArray    Have a good day  Gilles

User · Answer

In my case  the program queries the database which returns a DataGridView   I then copy that to an array   I get the size of the just created array and then write the array to an Excel spreadsheet   This code outputs over 5000 lines of data in about two seconds      private System Windows Forms DataGridView dgvResults  dgvResults DataSource   DB getReport     Microsoft Office Interop Excel Application oXL  Microsoft Office Interop Excel  Workbook oWB  Microsoft Office Interop Excel  Worksheet oSheet  try         Start Excel and get Application object      oXL   new Microsoft Office Interop Excel Application        oXL Visible   true       oWB    Microsoft Office Interop Excel  Workbook  oXL Workbooks Add           oSheet    Microsoft Office Interop Excel  Worksheet oWB ActiveSheet       var dgArray   new object dgvResults RowCount  dgvResults ColumnCount 1       foreach  DataGridViewRow i in dgvResults Rows                if  i IsNewRow  continue          foreach  DataGridViewCell j in i Cells                        dgArray j RowIndex  j ColumnIndex    j Value ToString                         Microsoft Office Interop Excel Range chartRange       int rowCount   dgArray GetLength 0       int columnCount   dgArray GetLength 1       chartRange    Microsoft Office Interop Excel Range oSheet Cells 2  1     I have header info on row 1  so start row 2     chartRange   chartRange get Resize rowCount  columnCount       chartRange set Value Microsoft Office Interop Excel XlRangeValueDataType xlRangeValueDefault  dgArray         oXL Visible   false      oXL UserControl   false      string outputFile    Output     DateTime Now ToString  yyyyMMddHHmmss       xlsx        oWB SaveAs  c   temp    outputFile  Microsoft Office Interop Excel XlFileFormat xlWorkbookDefault  Type Missing  Type Missing          false  false  Microsoft Office Interop Excel XlSaveAsAccessMode xlNoChange          Type Missing  Type Missing  Type Missing  Type Missing  Type Missing        oWB Close      catch  Exception ex

User · Answer

For some reason  converting to a 2 dimensional array didn t work for me   But the following approach did   public void SetRow Range range  string   data        range get Resize 1  data Length  Value2   data

User · Answer

This is an excerpt from method of mine  which converts a DataTable  the dt variable  into an array and then writes the array into a Range on a worksheet  wsh var   You can also change the topRow variable to whatever row you want the array of strings to be placed at   object    arr   new object dt Rows Count  dt Columns Count   for  int r   0  r  lt  dt Rows Count  r          DataRow dr   dt Rows r       for  int c   0  c  lt  dt Columns Count  c                  arr r  c    dr c           Excel Range c1    Excel Range wsh Cells topRow  1   Excel Range c2    Excel Range wsh Cells topRow   dt Rows Count - 1  dt Columns Count   Excel Range range   wsh get Range c1  c2   range Value   arr    Of course you do not need to use an intermediate DataTable like I did  the code excerpt is just to demonstrate how an array can be written to worksheet in single call

User · Answer

add ExcelUtility class to your project and enjoy it   ExcelUtility cs File content   using System  using Microsoft Office Interop Excel   static class ExcelUtility       public static void WriteArray lt T gt  this  Worksheet sheet  int startRow  int startColumn  T    array                var row   array GetLength 0           var col   array GetLength 1           Range c1    Range  sheet Cells startRow  startColumn           Range c2    Range  sheet Cells startRow   row - 1  startColumn   col - 1           Range range   sheet Range c1  c2           range Value   array             public static bool SaveToExcel lt T gt  T    data  string path                try                         Start Excel and get Application object              var oXl   new Application  Visible   false                  Get a new workbook              var oWb     Workbook   oXl Workbooks Add                   var oSheet     Worksheet  oWb ActiveSheet                oSheet WriteArray 1  1  bufferData1                oSheet WriteArray 1  1  data                oXl Visible   false              oXl UserControl   false              oWb SaveAs path  XlFileFormat xlWorkbookDefault  Type Missing                  Type Missing  false  false  XlSaveAsAccessMode xlNoChange                  Type Missing  Type Missing  Type Missing  Type Missing  Type Missing               oWb Close false               oXl Quit                      catch  Exception e                        return false                     return true            usage    var data   new           11  12  13  14  15  16  17  18  19  20        21  22  23  24  25  26  27  28  29  30        31  32  33  34  35  36  37  38  39  40      ExcelUtility SaveToExcel data   test xlsx      Best Regards

User · Answer

You could put your data into a recordset and use Excel s CopyFromRecordset Method - it s much faster than populating cell-by-cell   You can create a recordset from a dataset using this code  You will have to do some trials to see if using this method is faster than what you are currently doing

[c#] Write Array to Excel Range

Examples related to c#

Examples related to excel

Examples related to interop