C# Values from Array SpreadsheetGear API Sample

Excel-Compatible Samples for .NET

Description

SpreadsheetGear supports setting large, multi-cell range of cells using one line of code with a 2-dimensional array of objects. To do this set IRange.Value to an object[,] array of values, as demonstrated below.

To avoid possible exceptions, ensure your object[,] array size matches the size of the range being set. If you specify an IRange that is larger than the object[,] in either dimension, an IndexOutOfRangeException will be thrown. If you specify an IRange that is smaller than the object[,] array, the operation will succeed but any object[,] values located beyond the bounds of the IRange will be ignored.

Sample C# Source Code

// Create a new workbook and some local variables.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;

// Get a reference to a range of cells.
SpreadsheetGear.IRange range = cells["A1:J100"];

// Create an array of values matching the size of 
// the range which is 100 rows x 10 columns.
int rowCount = range.RowCount;
int colCount = range.ColumnCount;
object[,] values = new object[rowCount, colCount];
for (int row = 0; row < rowCount; row++)
{
    for (int col = 0; col < colCount; col++)
        values[row, col] = "Cell=" + range.WorkbookSet.GetAddress(row, col);
}

// Set the array values into the range.
range.Value = values;

// AutoFit the range.
range.Columns.AutoFit();