Important points to note.
- Use excel data in a single sheet.
- The excel file format should match the table schema.
Excel format:
| Id | Name | Description | CreatedAt | CreatedBy | ModifiedAt | ModifiedBy | Enabled | Deleted |
.aspx page
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Import" CssClass="btn btn-warning" OnClick="ImportExcel" />
Code Behind File
[sourcecode language="csharp"]
protected void ImportExcel(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
// SQL Server Connection String
string sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["flexi_stocky"].ConnectionString;
// Bulk Copy to SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
try
{
string path = string.Concat(Server.MapPath("~/uploads/" + FileUpload1.FileName));
FileUpload1.SaveAs(path);
// Connection String to Excel Workbook
string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = excelConnectionString;
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
DbDataReader dr = command.ExecuteReader();
bulkInsert.DestinationTableName = "your_sqlTableName";
bulkInsert.WriteToServer(dr);
bulkInsert.Close();
//Show success
}
catch (Exception ex)
{
bulkInsert.Close();
//Show error
}
}
else
{
//File not set
}
}
[/sourcecode]
