question

FSOFSOTechnoSystems-3722 avatar image
0 Votes"
FSOFSOTechnoSystems-3722 asked HuiLiu-MSFT commented

importar datos de excel a data grid view en WPF

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

windows-wpf
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HuiLiu-MSFT avatar image
0 Votes"
HuiLiu-MSFT answered HuiLiu-MSFT commented

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:
201639-image.png
201702-image.png


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


image.png (21.5 KiB)
image.png (25.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@FSOFSOTechnoSystems-3722. Is there some update to the question? Did my answer solve your problem? If so, you could accept it as the answer. It's helpful for community members with similar questions.

0 Votes 0 ·