Reading Excel Data into DataTable Excel Addin

Reading Excel Data into DataTable Excel Addin

Objective

We will create Excel VSTO Addin having excel file and having two columns data (1st column holds string values and 2nd column holds integer type values). we dynamically make selection to the range in 2×2 matrix and read it on a click into a DataTable.

Creating Excel file

Creating Excel VSTO Addin C#

Adding Ribbon Designer

Make Ribbon to Custom to make it visible in Excel Ribbon

Adding a Class “BuilkDataOperations.cs” and Add following reference in it and make class scope to public:

using excel=Microsoft.Office.Interop.Excel;
using System.Data;

Add following methods

public class BulkDataOperation
{
	public object[,] ReadExcelData(excel.Range oRange)
	{
		try
		{
			object[,] valObjects = oRange.get_Value(Type.Missing);
			return valObjects;
		}
		catch (Exception)
		{
			return null;
		}
	}

	public DataTable LoaDataTable(object[,] objRange)
	{
		//Create new DataTable
		DataTable dataList=new DataTable("LoadRange");
		DataColumn dtColumn;
		DataRow dtRow;
		//Create first column
		dtColumn=new DataColumn();
		dtColumn.DataType = System.Type.GetType("System.String");
		dtColumn.ColumnName = "nameId";
		dtColumn.Caption = "Name";
		//add to data table
		dataList.Columns.Add(dtColumn);
		//Create Second column
		dtColumn = new DataColumn();
		dtColumn.DataType = System.Type.GetType("System.Int32");
		dtColumn.ColumnName = "numberId";
		dtColumn.Caption = "Numbers";
		//add to data table
		dataList.Columns.Add(dtColumn);

		//Iterate object to load values
		for (int i = 1; i <= objRange.Length/2; i++)
		{
			//create new row
			dtRow = dataList.NewRow();
			dtRow["nameId"] = objRange[i,1].ToString();
			dtRow["numberId"] =Convert.ToInt32(objRange[i, 2]);
			dataList.Rows.Add(dtRow);
		}
	return dataList;
	}
}

Description

Method 1ReadExcelData takes one argument type Excel.Range as input which user selects in the Excel and returns that range by transforming into a two dimensional object[,] array.

Method 2LoaDataTable takes one argument as object[,] array and returns a data table having 2 columns and the number of rows supplied in the object.

Calling Method from Ribbon

On Ribbon put a button and double click, it generates click event for that button and type following code:

private void btnAction_Click(object sender, RibbonControlEventArgs e)
{
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;
	BulkDataOperation bkd=new BulkDataOperation();
	//Read Excel 2x2 matrix in a Object[,]
	object[,] objData = bkd.ReadExcelData(oRange);
	//Loading Object[,] into a datatable to represent
	DataTable myDataTable = bkd.LoaDataTable(objData);
}

Hit F5 from keyboard and open your Excel file where you have your data stored. Now select the data range and hit ribbon button and you can see your datatable is filled as shown in debug mode below:

Next>> Write Object Array in Excel Range VSTO C#

Leave a Reply

Your email address will not be published.