Share via

How read excel file and save into database

Cenk 1,051 Reputation points
2021-11-07T16:43:33.453+00:00

Hello guys,

Is there any tutorial reading excel files and inserting them into a database with a .net core console application?

Thanks in advance.

Developer technologies | .NET | .NET Runtime
Developer technologies | .NET | .NET CLI

Answer accepted by question author
  1. Cenk 1,051 Reputation points
    2021-11-08T13:03:30.4+00:00

    Here is my working solution;

    class Program
    {
    static async Task Main(string[] args)
    {
    try
    {
    string s = null;
    var d = new DirectoryInfo(@"C:\Test");
    var files = d.GetFiles("*.xlsx");
    var usersList = new List<User>();

                foreach (var file in files)
                {
                    var fileName = file.FullName;
    
                    using var package = new ExcelPackage(file);
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                    var currentSheet = package.Workbook.Worksheets;
                    var workSheet = currentSheet.First();
                    var noOfCol = workSheet.Dimension.End.Column;
                    var noOfRow = workSheet.Dimension.End.Row;
                    for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
                    {
                        var user = new User
                        {
                            GameCode = workSheet.Cells[rowIterator, 1].Value?.ToString(),
                            Count = Convert.ToInt32(workSheet.Cells[rowIterator, 2].Value),
                            Email = workSheet.Cells[rowIterator, 3].Value?.ToString(),
                            Status = Convert.ToInt32(workSheet.Cells[rowIterator, 4].Value)
                        };
    
    
                        usersList.Add(user);
                    }
                }
    
                var conn = ConfigurationManager.ConnectionStrings["Development"].ConnectionString;
                await using var connString = new SqlConnection(conn);
                connString.Open();
                await BulkWriter.InsertAsync(usersList, "[Orders]", connString, CancellationToken.None);
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
    
        }
    
        private class BulkWriter
        {
            private static readonly ConcurrentDictionary<Type, SqlBulkCopyColumnMapping[]> ColumnMapping =
                new ConcurrentDictionary<Type, SqlBulkCopyColumnMapping[]>();
    
            public static async Task InsertAsync<T>(IEnumerable<T> items, string tableName, SqlConnection connection,
                CancellationToken cancellationToken)
            {
                using var bulk = new SqlBulkCopy(connection);
                await using var reader = ObjectReader.Create(items);
                bulk.DestinationTableName = tableName;
                foreach (var colMap in GetColumnMappings<T>())
                    bulk.ColumnMappings.Add(colMap);
                await bulk.WriteToServerAsync(reader, cancellationToken);
            }
    
            private static IEnumerable<SqlBulkCopyColumnMapping> GetColumnMappings<T>() =>
                ColumnMapping.GetOrAdd(typeof(T),
                    type =>
                        type.GetProperties()
                            .Select(p => new SqlBulkCopyColumnMapping(p.Name, p.Name)).ToArray());
        }
    }
    

1 additional answer

Sort by: Most helpful
  1. Castorix31 91,866 Reputation points
    2021-11-07T18:40:16.887+00:00

    You can use Office Interop to read Excel file (many samples on Google, like Read Excel File in C#)
    then SqlConnection, SqlCommand to insert data, by adding the package "System.Data.SqlClient"

    1 person found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.