This article explains about the import and export MS-Excel file to SQL server. We could achieve this goal either by copy-pasting the excel rows directly to the destined database table in SQL Server Management Studio or by querying directly on the excel sheet from SQL Server Management Studio itself. SQL server has very easy Import/Export option and Excel itself provide easy options. But i done it through ASP.NET MVC using C#.
I've created this sample excel file to import/retrieve the data in SQL server, and is also the file I will be using in this article. Here is the screenshot for the same.
IMPORT:
Create a action method in Controller
public ActionResult ImportExcelFileToDatabase()
@using (Html.BeginForm("ImportExcelFileToDatabase", "ControllerName", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div>
<input type="file" id="FileUpload"
name="FileUpload" required data-val="true" data-val-required="please select a file"/>
<input type="submit" id="Submit" class="submit" value="Upload" title="Click to upload file"/>
</div>
}
The above code looks like below screenshot....
Then, create a Player-info model
public class Player-infoModel
{
public long ID { get; set; }
public string PlayerName { get; set; }
public string Country{ get; set; }
public int Matches{ get; set; }
public int Runs{ get; set; }
public string HS{ get; set; }
public int Hundreds{ get; set; }
public int Fifties{ get; set; }
}
Our requirement is to import data into SQL database from excel sheet using Asp.Net MVC. For this, we use OLEDB connection to read data from excel sheet in this post. If we have excel in .xls format then we use
Microsoft.Jet.OLEDB.4.0 and for .xlsx format then use Microsoft.ACE.OLEDB.12.0.
Then, Create a POST Action method to save the data into database. Follow the below code.
I've created this sample excel file to import/retrieve the data in SQL server, and is also the file I will be using in this article. Here is the screenshot for the same.
IMPORT:
Create a action method in Controller
public ActionResult ImportExcelFileToDatabase()
{
return view()
}
Then, create a view for ImportExcelFileToDatabase. return view()
}
@using (Html.BeginForm("ImportExcelFileToDatabase", "ControllerName", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div>
<input type="file" id="FileUpload"
name="FileUpload" required data-val="true" data-val-required="please select a file"/>
<input type="submit" id="Submit" class="submit" value="Upload" title="Click to upload file"/>
</div>
}
Then, create a Player-info model
public class Player-infoModel
{
public long ID { get; set; }
public string PlayerName { get; set; }
public string Country{ get; set; }
public int Matches{ get; set; }
public int Runs{ get; set; }
public string HS{ get; set; }
public int Hundreds{ get; set; }
public int Fifties{ get; set; }
}
Our requirement is to import data into SQL database from excel sheet using Asp.Net MVC. For this, we use OLEDB connection to read data from excel sheet in this post. If we have excel in .xls format then we use
Microsoft.Jet.OLEDB.4.0 and for .xlsx format then use Microsoft.ACE.OLEDB.12.0.
Then, Create a POST Action method to save the data into database. Follow the below code.
[HttpPost]
public ActionResult ImportExcelFileToDatabase(HttpPostedFileBase file)
{
if (Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);
System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
// Create a folder in App_Data named ExcelFiles because you need to save the file temporarily location and getting data from there.
string fileLocation =string.Format({0}/{1}",Server.MapPath("~/App_Data/ExcelFiles"),
Request.Files["FileUpload"].FileName);
Request.Files["FileUpload"].FileName);
if (System.IO.File.Exists(fileLocation))
System.IO.File.Delete(fileLocation);
Request.Files["FileUpload"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
DataSet ds = new DataSet();
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Player-infoModel model = new Player-infoModel ();
model.ID = ds.Tables[0].Rows[i]["ID"].ToString();
model.PlayerName = ds.Tables[0].Rows[i]["PlayerName "].ToString();
model.Country= ds.Tables[0].Rows[i]["Country"].ToString();
model.Matches= ds.Tables[0].Rows[i]["Matches"].ToString();
model.Runs= ds.Tables[0].Rows[i]["Runs"].ToString();
model.HS= ds.Tables[0].Rows[i]["HS"].ToString();
model.Hundreds= ds.Tables[0].Rows[i]["100"].ToString();
model.Fifties= ds.Tables[0].Rows[i]["50"].ToString();
model.HS= ds.Tables[0].Rows[i]["HS"].ToString();
model.Hundreds= ds.Tables[0].Rows[i]["100"].ToString();
model.Fifties= ds.Tables[0].Rows[i]["50"].ToString();
// SAVE THE DATA INTO DATABASE HERE. I HAVE USED WEB API HERE TO SAVE THE DATA.
// YOU CAN USE YOUR OWN PROCESS TO SAVE.
MemoryStream streampost = new MemoryStream();
WebClient clientpost = new WebClient();
clientpost.Headers["Content-type"] = "application/json";
DataContractJsonSerializer serializerpost = new
DataContractJsonSerializer(typeof(Player-infoModel ));
DataContractJsonSerializer(typeof(Player-infoModel ));
serializerpost.WriteObject(streampost, model);
//Posting data to DB.
string url1 = string.Format("{0}SavePlayerDetailsAPI", serviceUrl);
byte[] datapost = clientpost.UploadData(url1, "Post", streampost.ToArray());
streampost = new MemoryStream(datapost);
serializerpost = new DataContractJsonSerializer(typeof(string));
string result = (string)serializerpost.ReadObject(streampost);
}
ViewBag.message = "Information saved successfully.";
}
else
{
ModelState.AddModelError("", "Plese select Excel File.");
}
}
return View();
}
EXPORT:
Exporting to excel can be easily achieved using CLOSEDXML tool or you can directly copy paste from database to excel sheet. It is suitable for less data and for rare usage. But it will not easy to copy thousand's of records every time.
Create a button to export the file. Follow the VIEW code.
@using (Html.BeginForm("ExportExcelFromDatabase", "ControllerName", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div>
<input type="submit" value="PlayersList - Export to Excel" title="PlayersList - Export to Excel"/>
<br />
</div>
}
[HttpPost]
public void ExportExcelFromDatabase()
{
// I HAVE USED WEB API HERE TO GET THE DATA FROM DB.
// YOU CAN USE YOUR OWN PROCESS TO RETRIVE.
string webServiceUrl = String.Format("{0}APIForPlayerList", serviceUrl);
string formattedUrl = webServiceUrl + "?typeForFeed=Cricket";
HttpWebRequest webRequest = (HttpWebRequest)WebRequest.Create(formattedUrl);
HttpWebResponse webResponce = (HttpWebResponse)webRequest.GetResponse();
string Responce = string.Empty;
using (StreamReader sr = new StreamReader(webResponce.GetResponseStream()))
{
Responce = sr.ReadToEnd();
}
//SAVE THE DATA IN LIST
JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Player-infoModel > PlayerList = serializer.Deserialize<List<Player-infoModel>> (Responce);
// install closedXML(third-party) tool to your project and add namespace " using ClosedXML.Excel;".
XLWorkbook wb = new XLWorkbook();
string sheetName = "Players"; //Give name for export file.
var ws = wb.Worksheets.Add(sheetName);
ws.Cell(2, 1).InsertTable(PlayerList ); // assign list here.
HttpContext.Response.Clear();
HttpContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", sheetName.Replace(" ", "_")));
using (MemoryStream memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
memoryStream.WriteTo(HttpContext.Response.OutputStream);
memoryStream.Close();
}
HttpContext.Response.End();;
}
//Finally, Excel file in your downloads.
EXPORT:
Exporting to excel can be easily achieved using CLOSEDXML tool or you can directly copy paste from database to excel sheet. It is suitable for less data and for rare usage. But it will not easy to copy thousand's of records every time.
Create a button to export the file. Follow the VIEW code.
@using (Html.BeginForm("ExportExcelFromDatabase", "ControllerName", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div>
<input type="submit" value="PlayersList - Export to Excel" title="PlayersList - Export to Excel"/>
<br />
</div>
}
[HttpPost]
public void ExportExcelFromDatabase()
{
// I HAVE USED WEB API HERE TO GET THE DATA FROM DB.
// YOU CAN USE YOUR OWN PROCESS TO RETRIVE.
string webServiceUrl = String.Format("{0}APIForPlayerList", serviceUrl);
string formattedUrl = webServiceUrl + "?typeForFeed=Cricket";
HttpWebRequest webRequest = (HttpWebRequest)WebRequest.Create(formattedUrl);
HttpWebResponse webResponce = (HttpWebResponse)webRequest.GetResponse();
string Responce = string.Empty;
using (StreamReader sr = new StreamReader(webResponce.GetResponseStream()))
{
Responce = sr.ReadToEnd();
}
//SAVE THE DATA IN LIST
JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Player-infoModel > PlayerList = serializer.Deserialize<List<Player-infoModel>> (Responce);
// install closedXML(third-party) tool to your project and add namespace " using ClosedXML.Excel;".
XLWorkbook wb = new XLWorkbook();
string sheetName = "Players"; //Give name for export file.
var ws = wb.Worksheets.Add(sheetName);
ws.Cell(2, 1).InsertTable(PlayerList ); // assign list here.
HttpContext.Response.Clear();
HttpContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", sheetName.Replace(" ", "_")));
using (MemoryStream memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
memoryStream.WriteTo(HttpContext.Response.OutputStream);
memoryStream.Close();
}
HttpContext.Response.End();;
}
//Finally, Excel file in your downloads.
