Import an Excel File data to Datagrid in WPF

Question

I am trying to import an excel file to a datagrid in WPF. What I've found around the Internet won't rally do the trick.

I have a code that opens and reads excelfiles and outputs the data cells to a messagebox. I want to do so but to DataGrid instead. Here follows the code that needs to be changed:

private void ReadFromFile_Click(object sender, RoutedEventArgs e) { //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Temp\vitoshacademy.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; //iterate over the rows and columns and print to the console as it appears in the file //excel is not zero based!! for (int i = 1; i <= rowCount; i++) { for (int j = 1; j <= colCount; j++) { //new line if (j == 1) MessageBox.Show("\r\n"); //write the value to the console if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null) MessageBox.Show(xlRange.Cells[i, j].Value2.ToString() + "\t"); } } //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); }

You can query an excel sheet using OleDB. connectionstrings.com/excel — Cleptus
– Cleptus, Commented Sep 7, 2018 at 8:40
I know that but I am required to use using Microsoft.Office.Interop.Excel; instead. — CodeNinjah
– CodeNinjah, Commented Sep 7, 2018 at 8:44
You might consider using open xml sdk, that way you don't have to depend on the users having excel installed for your program to work. learn.microsoft.com/en-us/office/open-xml/open-xml-sdk — Kevin Cook
– Kevin Cook, Commented Sep 7, 2018 at 12:05

Vityata · Accepted Answer · 2021-06-09 07:54:05Z

To Import the Excel file into DataGrid using C# in WPF

 Using Microsoft.Office.Interop.Excel; private async void btnImport_Click(object sender, RoutedEventArgs e) { OpenFileDialog choofdlog = new OpenFileDialog(); choofdlog.Filter = "All Files (*.*)|*.*"; if (choofdlog.ShowDialog() == DialogResult.OK) { string sFileName = choofdlog.FileName; string path = System.IO.Path.GetFullPath(choofdlog.FileName); Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); DataSet ds = new DataSet(); Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(path); foreach (Microsoft.Office.Interop.Excel.Worksheet ws in wb.Worksheets) { System.Data.DataTable td = new System.Data.DataTable(); td = await Task.Run(() => formofDataTable(ws)); ds.Tables.Add(td);//This will give the DataTable from Excel file in Dataset } Datagrid.ItemsSource = ds.Tables[0].DefaultView; wb.Close(); } } public System.Data.DataTable formofDataTable(Microsoft.Office.Interop.Excel.Worksheet ws) { System.Data.DataTable dt = new System.Data.DataTable(); string worksheetName = ws.Name; dt.TableName = worksheetName; Microsoft.Office.Interop.Excel.Range xlRange = ws.UsedRange; object[,] valueArray = (object[,])xlRange.get_Value(XlRangeValueDataType.xlRangeValueDefault); for (int k = 1; k <= valueArray.GetLength(1); k++) { dt.Columns.Add((string)valueArray[1, k]); //add columns to the data table. } object[] singleDValue = new object[valueArray.GetLength(1)]; //value array first row contains column names. so loop starts from 2 instead of 1 for (int i = 2; i <= valueArray.GetLength(0); i++) { for (int j = 0; j < valueArray.GetLength(1); j++) { if (valueArray[i, j + 1] != null) { singleDValue[j] = valueArray[i, j + 1].ToString(); } else { singleDValue[j] = valueArray[i, j + 1]; } } dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges); } return dt; }

This will give you the Importing the Excel into DataTable, and that can be bind in DataGrid

Collectives™ on Stack Overflow

Import an Excel File data to Datagrid in WPF

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related