importar datos de excel a data grid view en WPF

Question

disculpen soy estudiante de programacion y tengo un proyecto en WPF en el que uso un libro de excel como base de datos y nesecito cargar los datos del libro de excel a un data grid view y no encuentro la forma de acerlo me podrian ayudar por favor

Answer 1

Welcome to Microsoft Q&A, this is an English forum, it is recommended that you use English.
Install EPPlus using NuGet.
App.config:

 <configuration>  
     ...  
     <appSettings>  
         <!--The license context used-->  
         <add key="EPPlus:ExcelPackage.LicenseContext" value="NonCommercial" />  
     </appSettings>  
 </configuration>  

MainWindow.xaml:

 <Window.Resources>  
        <local:DateTimeConverter x:Key="converter"/>  
        <local:DecimalConverter x:Key="decimalConverter"/>  
    </Window.Resources>  
    <Grid>  
        <DataGrid x:Name="dg" Width="700" Height="400" AutoGenerateColumns="False">  
            <DataGrid.Columns>  
                <DataGridTextColumn Header="Party" Width="100" Binding="{Binding Party}"></DataGridTextColumn>  
                <DataGridTextColumn Header="BillNo" Width="100" Binding="{Binding BillNo}"></DataGridTextColumn>  
                <DataGridTextColumn Header="BillDate" Width="100" Binding="{Binding BillDt,Converter={ StaticResource converter}, StringFormat=yyyy - MM - dd }"></DataGridTextColumn>  
                <DataGridTextColumn Header="Amount" Width="100" Binding="{Binding Amt , Converter={StaticResource decimalConverter},StringFormat=N2}"></DataGridTextColumn>  
                <DataGridTextColumn Header="DueDate" Width="100" Binding="{Binding DueDt,Converter={ StaticResource converter}, StringFormat=yyyy - MM - dd }"></DataGridTextColumn>  
                <DataGridTextColumn Header="PaidOn" Width="100" Binding="{Binding PaidOn,Converter={ StaticResource converter}, StringFormat=yyyy - MM - dd }"></DataGridTextColumn>  
            </DataGrid.Columns>  
        </DataGrid>  
    </Grid>  

MainWindow.xaml.cs:

using OfficeOpenXml;  
using System;  
using System.Collections.Generic;  
using System.Data;  
using System.Data.SqlClient;  
using System.Globalization;  
using System.IO;  
using System.Linq;  
using System.Windows;  
using System.Windows.Data;  

namespace SqlLiteDemo  
{  
  public partial class MainWindow : Window  
  {  
    public MainWindow()  
    {  
      InitializeComponent();  
      CreateTable();  
    }  
    string xlFile = @"C:\Users\...\Desktop\testing.xlsx";  
    public void CreateTable()  
    {  
      SqlConnection m_dbConnection = new   SqlConnection(@"constr");  
      m_dbConnection.Open();  
      DataTable dt = new DataTable();  
      FileInfo existingFile = new FileInfo(xlFile);  
      ExcelPackage package = new ExcelPackage(existingFile);  
      dt = ExcelPackageToDataTable(package);  
      try  
      {  
        SqlDataAdapter Adapter = new SqlDataAdapter("SELECT * FROM [dbo].[ExcelData]", m_dbConnection);  
        SqlCommandBuilder cb = new SqlCommandBuilder(Adapter);  
        Adapter.UpdateCommand = cb.GetUpdateCommand();  
        Adapter.Update(dt);  
      }  
      catch  
      {  
        MessageBox.Show("data exist");  
      }  
      SqlCommand sqlCom = new SqlCommand("Select * From [dbo].[ExcelData]", m_dbConnection);  
      SqlDataAdapter sda = new SqlDataAdapter(sqlCom);  
      DataSet ds = new DataSet();  
      sda.Fill(ds);  
      if (ds.Tables[0].Rows.Count > 0)  
      {  
        dg.ItemsSource = ds.Tables[0].DefaultView;  
      }  
      m_dbConnection.Close();  
    }  
    public static DataTable ExcelPackageToDataTable(ExcelPackage excelPackage)  
    {  
      DataTable dt = new DataTable();  
      ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();  
      if (worksheet.Dimension == null)  
      {  
        return dt;  
      }  
      List<string> columnNames = new List<string>();  

      int currentColumn = 1;  
      foreach (var cell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])  
      {  
        string columnName = cell.Text.Trim();  

        if (cell.Start.Column != currentColumn)  
        {  
          columnNames.Add("Header_" + currentColumn);  
          dt.Columns.Add("Header_" + currentColumn);  
          currentColumn++;  
        }  
        columnNames.Add(columnName);  
        int occurrences = columnNames.Count(x => x.Equals(columnName));  
        if (occurrences > 1)  
        {  
          columnName = columnName + "_" + occurrences;  
        }  
        dt.Columns.Add(columnName);  
        currentColumn++;  
      }  
      for (int i = 2; i <= worksheet.Dimension.End.Row; i++)  
      {  
        var row = worksheet.Cells[i, 1, i, worksheet.Dimension.End.Column];  
        DataRow newRow = dt.NewRow();  
        foreach (var cell in row)  
        {  
          newRow[cell.Start.Column - 1] = cell.Text;  
        }  
        dt.Rows.Add(newRow);  
      }  
      return dt;  
    }  
  }  
  [ValueConversion(typeof(DateTime), typeof(String))]  
  public class DateTimeConverter : IValueConverter  
  {  
    public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)  
    {  
      return DateTime.ParseExact(value.ToString(), "d-M-yyyy", CultureInfo.InvariantCulture);  
    }  
    public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)  
    {  
      throw new NotImplementedException();  
    }  
  }  
  [ValueConversion(typeof(double), typeof(String))]  
  public class DecimalConverter : IValueConverter  
  {  
    public object Convert(object value, Type targetType, object parameter, CultureInfo culture)  
    {  
      return double.Parse(value.ToString(), CultureInfo.InvariantCulture);  
    }  
    public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)  
    {  
      throw new NotImplementedException();  
    }  
  }  
}  

The result:

---

If the response is helpful, please click "Accept Answer" and upvote it.
 Note: Please follow the steps in our [documentation][5] to enable e-mail notifications if you want to receive the related email notification for this thread. 

[5]: https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html