Reading Excel file and putting the data in Database

Anjali Agarwal 1,386 Reputation points
2023-10-06T00:16:07.1233333+00:00

I have a console application. In that, I want to read the data from the Excel spreadsheet and put the data in the table in the database. This is how my Excel spreadsheet looks like:

EmpId Name DeptID

There are 15 columns in the spreadsheet, but I need to read the first three columns and put them in SQL database table.

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,246 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,401 questions
0 comments No comments
{count} votes

Accepted answer
  1. KOZ6.0 5,295 Reputation points
    2023-10-06T03:22:55.74+00:00

    Get ClosedXML from nuget and try the code below.

    using ClosedXML.Excel;
    using System.Data;
    using System.Diagnostics;
    using System.Data.SqlClient;
    
    internal class Program
    {
        const string filename = @"RegistData.xlsx";
        const string connectionString = @"DataSource=....;";
    
        static void Main(string[] args) {
            using (var con = new SqlConnection(connectionString)) {
                con.Open();
                using (var ds = ExcelToDataSet(filename, true)) {
                    foreach (DataTable dt in ds.Tables) {
                        using (var bulkCopy = new SqlBulkCopy(con)) {
                            bulkCopy.DestinationTableName = "(tableName)";
                            bulkCopy.WriteToServer(dt);
                        }
                    }
                }
            }
        }
    
        static DataSet ExcelToDataSet(string excelFileName, bool useHeader) {
            var ds = new DataSet();
            using (var workbook = new XLWorkbook(excelFileName)) {
                foreach (var worksheet in workbook.Worksheets) {
                    var first = true;
                    var dt = ds.Tables.Add(worksheet.Name);
                    dt.BeginInit();
                    dt.Columns.Add("EmpId", typeof(object));
                    dt.Columns.Add("Name", typeof(object));
                    dt.Columns.Add("DeptID", typeof(object));
                    dt.EndInit();
                    dt.BeginLoadData();
                    foreach (var row in worksheet.Rows()) {
                        if (useHeader && first) {
                            first = false;
                        } else {
                            object[] values = {
                                row.Cell(1).Value,
                                row.Cell(2).Value,
                                row.Cell(3).Value
                            };
                            dt.Rows.Add(values);
                        }
                    }
                    dt.EndLoadData();
                    dt.AcceptChanges();
                }
            }
            return ds;
        }
    }
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful