locked
Import Excel File with multiple sheets to DataGrid in WPF Application RRS feed

  • Question

  • So far I have managed to import an Excel File with multiple sheets to a DataGridWiew in Windows Form.

    How can I do this in a WPF Application?

    I found some code on how to import just one sheet, but I need to import several sheets and would need to add them each to a DataGrid, where each Grid is a page in a TabControl.

    How can I adjust the following code to do as I decribed above?

    namespace ReadExcel_And_BindToDataGrid
    {
        /// <summary>
        /// Interaction logic for MainWindow.xaml
        /// </summary>
        public partial class MainWindow : Window
        {
            public MainWindow()
            {
                InitializeComponent();
            }
            
            private void btnOpen_Click(object sender, RoutedEventArgs e)
            {
                OpenFileDialog openfile = new OpenFileDialog();
                openfile.DefaultExt = ".xlsx";
                openfile.Filter = "(.xlsx)|*.xlsx";
                //openfile.ShowDialog();
    
                var browsefile = openfile.ShowDialog();
    
                if (browsefile == true)
                {
                    txtFilePath.Text = openfile.FileName;
    
                    Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                    //Static File From Base Path...........
                    //Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + "TestExcel.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                    //Dynamic File Using Uploader...........
                    Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath.Text.ToString(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                    Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1); ;
                    Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;
    
                    string strCellData = "";
                    double douCellData;
                    int rowCnt = 0;
                    int colCnt = 0;
    
                    DataTable dt = new DataTable();
                    for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
                    {
                        string strColumn = "";
                        strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                        dt.Columns.Add(strColumn, typeof(string));
                    }
    
    
                    for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
                    {
                        string strData = "";
                        for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
                        {
                            try
                            {
                                strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                                strData += strCellData + "|";
                            }
                            catch (Exception ex)
                            {
                                douCellData = (excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                                strData += douCellData.ToString() + "|";
                            }
                        }
                        strData = strData.Remove(strData.Length - 1, 1);
                        dt.Rows.Add(strData.Split('|'));
                    }
    
                    dtGrid.ItemsSource = dt.DefaultView;
    
                    excelBook.Close(true, null, null);
                    excelApp.Quit();
    
                }
            }
    
            private void btnClose_Click(object sender, RoutedEventArgs e)
            {
                this.Close();
            }
        }
    }

    • Moved by Kristin Xie Tuesday, March 1, 2016 7:52 AM Excel related
    Monday, February 29, 2016 11:18 PM

Answers

  • Hi Jonas,

    For several sheets, you could check Worksheets.Count, then base on count to iterate the worksheet, then do other things.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 2, 2016 6:21 AM

All replies

  • Hi Jonas,

    Based on your description, your case more related to Excel, I will help move your case to Excel for developers forum for better support.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 1, 2016 7:51 AM
  • Hi Jonas,

    For several sheets, you could check Worksheets.Count, then base on count to iterate the worksheet, then do other things.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 2, 2016 6:21 AM
  • Hi there,  

    As Starain suggested, you could iterate the worksheet by its count. Here is a straightforward method to import data from Excel worksheet to datatable in WPF, hope it can be an option for those who is looking for alternative solutions.

    Wednesday, March 23, 2016 3:47 AM