Reading Excel file and putting the data in Database

Question

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.

Answer 1

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;
    }
}