Object[,]:
Represents a two dimensional memory object which can hold any type of values in C#. Since a Range in excel is unique combination of Row and Column hence Object[,] array is the best fit to perform data transformation. In this article we will see how you can convert your Range into an Object array.
Writing data into excel by transposing into object[,] array dynamically: Create a method which will read your existing data range from excel to object array and returns as object[,]:
Prerequisites:
- Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
- Create Excel Addin in C# code style (Visual Studio Tools for Office)
- Create a ribbon designer and put button
Add a class and name it “BulkDataOperation.cs”
public object[,] ReadExcelData(excel.Range oRange)
{
try
{
object[,] valObjects = oRange.get_Value(Type.Missing);
return valObjects;
}
catch (Exception)
{
return null;
}
}
Once you get the data into object by using above code now its time to write data back into Excel:
BulkDataOperation bkd=new BulkDataOperation();
//Read existing data from excel selection
excel.Range oRange = Globals.ThisAddIn.Application.Selection;
//Read range into object[,]
object[,] objData = bkd.ReadExcelData(oRange);
//Write object[,] data into specific location in excel
excel.Worksheet xlSheet = Globals.ThisAddIn.Application.ActiveSheet;
//Here you can build range using cells or any other way in which you want to transpose/write data
excel.Range oRangeToWrite = xlSheet.get_Range("C"+ oRange.Row + ":D" + oRange.Rows.Count);
//Populate value back into excel
oRangeToWrite.set_Value(excel.XlRangeValueDataType.xlRangeValueDefault,objData);
Run the code and you see no looping is required to read individual cell by cell while reading or writing data into or from excel sheet.
