Sunday, July 14, 2013

Import/Export MS-EXCEL file data to database in ASP.NET MVC using C#.

      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()
        {
         return view()
        }
Then, create a view for  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.

        [HttpPost]
        public ActionResult ImportExcelFileToDatabase(HttpPostedFileBase file)
        {

            if (Request.Files["FileUpload"].ContentLength > 0)
            {
                string fileExtension =          
                                     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);

                    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.
                    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();
                        
// 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 ));
                        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.





2 comments:

  1. I think it's better to use MimeMultipartContent than HttpPostedFile because you can consume web service from other platform such as Android or Ios. I found this article described pretty detailed how it works http://hintdesk.com/android-upload-files-to-asp-net-web-api-service/ . Just an example.

    ReplyDelete
  2. i get some users appId or not get some users AppId, give me solution of all users appId how to get.... plz
    thank you in advance...

    ReplyDelete