Today, I will show you how to upload excel file, read Excel file data, save Excel file data & import into SQL Server using SQLBULK in ASP.NET.
First step, you must create the excel file. And then create a SQL table in database like the following picture:
Next step, add the code in "Default.aspx"
<asp:FileUpload ID="fupUpload" runat="server" />
<asp:Button ID="btnImport" Font-Bold="true" ForeColor="White"
BackColor="#136671" Height="23px" runat="server" Text="Import Excel Data"
onclick="btnImport_Click" />
Now, write the following code in "Default.aspx.cs"
Add these NameSpace
using System.IO;
using System.Data.OleDb;
using System.Data;
Write the code in Click Event of Import Button:
protected void btnImport_Click(object sender, EventArgs e)
{
string strFilepPath;
DataSet ds = new DataSet();
string strConnection = ConfigurationManager.ConnectionStrings
["connectionString"].ConnectionString;
if (fupUpload.HasFile)
{
try
{
FileInfo fi = new FileInfo(fupUpload.PostedFile.FileName);
string ext = fi.Extension;
if (ext == ".xls" || ext == ".xlsx")
{
string filename = Path.GetFullPath(fupUpload.PostedFile.FileName);
string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
strFilepPath = DirectoryPath + fupUpload.FileName;
Directory.CreateDirectory(DirectoryPath);
fupUpload.SaveAs(strFilepPath);
string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ strFilepPath + ";Extended Properties=\"Excel 12.0
Xml;HDR=YES;IMEX=1\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
DeleteExcelFile(fupUpload.FileName); // Delete File Log
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection,
SqlBulkCopyOptions.KeepIdentity);
sqlBulk.DestinationTableName = "Table_1";
sqlBulk.WriteToServer(ds.Tables[0]);
conn.Close();
sqlBulk.Close();
ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
"alert('Excel file successfully imported into DB');", true);
return;
}
else
{
ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
"alert('Please upload excel file only');", true);
return;
}
}
catch (Exception ex)
{
DeleteExcelFile(fupUpload.FileName);
ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
"alert('error occured: " + ex.Message.ToString() + "');", true);
return;
}
}
else
{
ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
"alert('Please upload excel file');", true);
return;
}
}
protected void DeleteExcelFile(string Name)
{
if (Directory.Exists(Request.PhysicalApplicationPath +
"UploadExcelFile\\"))
{
string[] logList = Directory.GetFiles(Request.PhysicalApplicationPath
+ "UploadExcelFile\\", "*.xls");
foreach (string log in logList)
{
FileInfo logInfo = new FileInfo(log);
string logInfoName = logInfo.Name.Substring(0,
logInfo.Name.LastIndexOf('.'));
if (logInfoName.Length >= Name.Length)
{
if (Name.Equals(logInfoName.Substring(0, Name.Length)))
{
logInfo.Delete();
}
}
}
}
}
I hope it works for you! Good luck.
HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.
