Moving Data From Excel to SQL Server in any web application without Microsoft Office installed.

XsExcel SDK not only facilitates its users to import data to worksheets from external data sources but also allow them to export their worksheet data to Microsoft SQL Server database. Exporting data from spreedsheets to data tables, and saving these data tables to sql database.

This guide page provides C# users with how to export data from XLSX and XLS workbook to sql database with XsExcel control in C#.NET.


public static void ExportSheetToSqlTable()
{
    string filename = "DataTableSample.xlsx";
    int sheetId = 0;

    // Load workbook from local excel file
    Workbook workbook = new Workbook(filename);

    // Export data table from worksheet
    DataTable dt = workbook.ConvertSheetToDataTable(sheetId);

    // Save data to sql database
    insertTableToSqlServer(dt);
}

private static void insertTableToSqlServer(DataTable dt)
{
    string connString = @"Data Source=.;Initial Catalog=database name;Integrated Security=True";
    string query = @"INSERT INTO tableName (paramColum) VALUES (@paramName)";

    using (SqlConnection sqlConn = new SqlConnection(connString))
    {
        using (SqlCommand sqlComm = new SqlCommand(query, sqlConn))
        {
            sqlComm.Parameters.Add("@paramName", SqlDbType.Text);
            sqlConn.Open();

            foreach (DataRow dr in dt.Rows)
            {
                string paramSource = dr[0].ToString();
                sqlComm.Parameters["@paramName"].Value = paramSource;
                //...

                sqlComm.ExecuteNonQuery();
            }
        }
    }           
}

Notice - If you used the trial version of XsExcel SDK, can only edit, insert or get the first 300 row data!

More Excel tutorial