If you are stuck using Interop, then the code below may help. One issue in your current code is the way the code closes and releases the Excel COM objects. Granted you do have this code in a try/catch block, however, if the code crashes BEFORE it gets to the close and release code… then those COM objects do NOT get closed or released and end up being a leaking resource.
I suggest you put the close and release code in the finally portion of the try/catch/finally statement. This way the COM objects “should” technically get released even if the code crashes while working with the Excel file.
It appears you have defined a DataTable called dt at the start of your code… but it is never used. So, lets use it… I have renamed it to NewDT.
First step is to create the DataTable columns. We will assume the first row in the Excel file is a header row. We will use the first row for the column names. We will also assume that all the column “types” will be of a string type. You could easily convert the strings to the proper type if needed in this code. However, you would need to know before hand what those “types” are and “which” columns they belong to. In this example they are all strings to simplify the code.
After we create the columns for the DataTable then all that is left is to loop through the Excel cells and grab the cells string value and add it to the table. This sounds straight forward and simple enough, however, there is one caveat you want to keep in mind… Looping through Excel Ranges can be expensive… and I would avoid it if possible.
To avoid this, we can use a two dimensional object array and grab the WHOLE UsedRange and read it into this array… something like…
excelRange = excelWorksheet.UsedRange;
object[,] data = (object[,])excelRange.Cells.Value;
This is the array we will loop through to get the cell values. This will speed things up if the Excel file is large. SPECIAL NOTE: Since we will be looping through this object array data you should be aware that it will have its starting index at ONE (1) and NOT zero (0). This is coming from Excel and we just need to be aware of this.
The first row in the array contains the column names. So, we want to use this row to define the column names in the NewDT DataTable. This simple loop would look something like…
for (int i = 1; i <= colCount; i++) {
if (data[1, i] != null) {
NewDT.Columns.Add(data[1, i].ToString(), typeof(string));
}
else {
NewDT.Columns.Add("", typeof(string));
}
}
NOTE: It may be wise to check for "duplicate" columns names here to avoid the duplicate names exception. I will leave this for you to do.
Next, we want to loop through each row in the array and collect the column/cell values and add that row to the NewDT DataTable. This may look something like…
DataRow curRow = null;
for (int i = 2; i <= rowCount; i++) {
curRow = NewDT.NewRow();
for (int j = 1; j <= colCount; j++) {
if (data[i, j] != null) {
curRow[j - 1] = data[i, j].ToString();
}
}
NewDT.Rows.Add(curRow);
}
NOTE the curRow’s column/cell index is “J – 1” since the DataTable column indexes start at zero (0).
That should pretty much do it. Now you can add the DataTable as a DataSource to the grid… dataGridView1.DataSource = NewDT; …
The complete updated code is below…
string ExcelFolderPath = @"PathToYourFile";
private void Form1_Load(object sender, EventArgs e) {
OpenExcelFile();
}
private void OpenExcelFile() {
DataTable NewDT;
foreach (var file in Directory.GetFiles(this.ExcelFolderPath).Where(p => p.Contains("Contacts"))) {
Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;
Microsoft.Office.Interop.Excel._Worksheet excelWorksheet = null;
Microsoft.Office.Interop.Excel.Range excelRange = null;
try {
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelWorkbook = excelApp.Workbooks.Open(file);
excelWorksheet = excelWorkbook.Sheets[1];
excelRange = excelWorksheet.UsedRange;
int rowCount = excelRange.Rows.Count;
int colCount = excelRange.Columns.Count;
object[,] data = (object[,])excelRange.Cells.Value;
NewDT = new DataTable();
for (int i = 1; i <= colCount; i++) {
if (data[1, i] != null) {
NewDT.Columns.Add(data[1, i].ToString(), typeof(string));
}
else {
NewDT.Columns.Add("", typeof(string));
}
}
DataRow curRow = null;
for (int i = 2; i <= rowCount; i++) {
curRow = NewDT.NewRow();
for (int j = 1; j <= colCount; j++) {
if (data[i, j] != null) {
curRow[j - 1] = data[i, j].ToString();
}
}
NewDT.Rows.Add(curRow);
}
dataGridView1.DataSource = NewDT;
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
finally {
//GC.Collect();
//GC.WaitForPendingFinalizers();
if (excelRange != null) {
Marshal.ReleaseComObject(excelRange);
}
if (excelWorksheet != null) {
Marshal.ReleaseComObject(excelWorksheet);
}
if (excelWorkbook != null) {
excelWorkbook.Close();
Marshal.ReleaseComObject(excelWorkbook);
}
if (excelApp != null) {
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);
}
}
}
}
I hope this makes sense and helps.
