In this example, we will fill some data into a datatable from excel application.
We created this DataTable example using with COM Object ( C# Read Excel File into DataTable without OleDb)
Berfore the creating codes, here is the examples screenshot
C# read Excel file
Step 1: For importing Excel to DataTable with Interop ,we have to references Excel namespace to the project.
Step 2: Given this the using alias of Excel.
|
1 2 3 |
using ExcelApp = Microsoft.Office.Interop.Excel; |
Step 3: Creating COM object referances and DataTable
|
1 2 3 4 5 6 |
//Create COM Objects. ExcelApp.Application excelApp = new ExcelApp.Application(); DataRow myNewRow; DataTable myTable; |
Step 4: … and checking Excel program is exists
|
1 2 3 4 5 6 7 |
if (excelApp == null) { Console.WriteLine("Excel is not installed!!"); return; } |
Step 5: Creating book, pages and range variables
|
1 2 3 4 5 6 |
//Notice: Change this path to your real excel file path ExcelApp.Workbook excelBook = excelApp.Workbooks.Open(@"E:\excelDatatable.xlsx"); ExcelApp._Worksheet excelSheet = excelBook.Sheets[1]; ExcelApp.Range excelRange = excelSheet.UsedRange; |
Step 6: Calculating rows and cols.
|
1 2 3 4 |
int rows = excelRange.Rows.Count; int cols = excelRange.Columns.Count; |
Converting Excel to DataTable in C#
Step 6: Define DataTable name and column names
|
1 2 3 4 5 6 7 |
//Set DataTable Name and Columns Name myTable = new DataTable("MyDataTable"); myTable.Columns.Add("FirstName", typeof(string)); myTable.Columns.Add("LastName", typeof(string)); myTable.Columns.Add("Age", typeof(int)); |
Step 7: Reading step by step cols and rows into DataTable.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
//first row using for heading, start second row for data for (int i = 2; i <= rows; i++) { myNewRow = myTable.NewRow(); myNewRow["FirstName"]= excelRange.Cells[i, 1].Value2.ToString(); //string myNewRow["LastName"] = excelRange.Cells[i, 2].Value2.ToString(); //string myNewRow["Age"] = Convert.ToInt32(excelRange.Cells[i, 3].Value2.ToString()); //integer myTable.Rows.Add(myNewRow); } |
Step 8: Reading data from DataTable
|
1 2 3 4 5 6 |
foreach (DataRow dr in myTable.Rows) { Console.WriteLine("First Name : {0}\t Last Name : {1} \t Age : {2} \t", dr[0], dr[1], dr[2]); } |
Step 9: After reading, relase the excel project.
|
1 2 3 4 5 6 |
//after reading, relaase the excel project excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); Console.ReadLine(); |
Here are the all codes:
|
1 2 3 4 5 6 7 8 9 10 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 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
class Program { static void Main(string[] args) { //Create COM Objects. ExcelApp.Application excelApp = new ExcelApp.Application(); DataRow myNewRow; DataTable myTable; if (excelApp == null) { Console.WriteLine("Excel is not installed!!"); return; } // ExcelApp.Workbook excelBook = excelApp.Workbooks.Open(@"E:\excelDatatable.xlsx"); ExcelApp._Worksheet excelSheet = excelBook.Sheets[1]; ExcelApp.Range excelRange = excelSheet.UsedRange; int rows = excelRange.Rows.Count; int cols = excelRange.Columns.Count; //Set DataTable Name and Columns Name myTable = new DataTable("MyDataTable"); myTable.Columns.Add("FirstName", typeof(string)); myTable.Columns.Add("LastName", typeof(string)); myTable.Columns.Add("Age", typeof(int)); //first row using for heading, start second row for data for (int i = 2; i <= rows; i++) { myNewRow = myTable.NewRow(); myNewRow["FirstName"]= excelRange.Cells[i, 1].Value2.ToString(); //string myNewRow["LastName"] = excelRange.Cells[i, 2].Value2.ToString(); //string myNewRow["Age"] = Convert.ToInt32(excelRange.Cells[i, 3].Value2.ToString()); //integer myTable.Rows.Add(myNewRow); } foreach (DataRow dr in myTable.Rows) { Console.WriteLine("First Name : {0}\t Last Name : {1} \t Age : {2} \t", dr[0], dr[1], dr[2]); } //after reading, relaase the excel project excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); Console.ReadLine(); } } |
Sources: I used to this articles for reading excel file and filling into datatable
Reading Excel file in C# Console Application
I’m very impressed with the way you explained all the code. Hope you can keep sharing information like this in the future. I’m starting with C# and I’m overwhelmed with the libraries part of the code.
Unfortunately the Microsoft Excel 15.0 Object Library is not available. I do have a office 16.0 Object Library. I Added it, but I keep having errors related with the excelApp namespace.
What is wrong?
Thank you.
TJ