Converting Excel Sheet to database sheet and converting Database sheet to Excel to excel sheet

Question

User1527485495 posted

Greetings all, i'm design an HR application for an organization and part of there requirement is for me provide a link/button  that will convert excel sheet into database  sheet and upload to the database from front end (Index in mvc) or convert from database  (index view) to excel sheet... PLEASE NOTE : they told me they don't  want the normal import and export option from database....

 Please i want the house to enlighten me more about this ..... Thanks Dreal

Answer 1

User475983607 posted

Greetings all, i'm design an HR application for an organization and part of there requirement is for me provide a link/button  that will convert excel sheet into database  sheet and upload to the database from front end (Index in mvc) or convert from database  (index view) to excel sheet... PLEASE NOTE : they told me they don't  want the normal import and export option from database....

 Please i want the house to enlighten me more about this ..... Thanks Dreal

The question is not clear.  What is "normal" import and export?  What database are you using?

Answer 2

User1527485495 posted

MICROSOFT SQL , IMPORT AND EXPORTING OF DATA FROM DATABASE 

Answer 3

User-1508012637 posted

Hi Dreal1,

i'm design an HR application for an organization and part of there requirement is for me provide a link/button  that will convert excel sheet into database  sheet and upload to the database from front end (Index in mvc) or convert from database  (index view) to excel sheet... PLEASE NOTE : they told me they don't  want the normal import and export option from database....

According to your description, I've created a demo which has the function of:

1. The user downloads the excel file from server and upload it after modified. 

2. The server reads the uploaded file and save the new records to database.

3. The server updates the excel file for users could download and modify next time.

The screenshot as below:

Please refer to the following sample code:

Model

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string ContactNo { get; set; }
}

Controller

public class UserController : Controller
{
    private UserContext db = new UserContext();

    // GET: User
    public ActionResult Index()
    {
        return View();
    }

    public FileResult DownloadExcel()
    {
        string path = "/Files/Users.xlsx";
        return File(path, "application/vnd.ms-excel", "Users.xlsx");
    }

    [HttpPost]
    public JsonResult UploadExcel(User users, HttpPostedFileBase FileUpload)
    {
        List<string> data = new List<string>();
        if (FileUpload != null)
        {
            // tdata.ExecuteCommand("truncate table OtherCompanyAssets");  
            if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                string filename = FileUpload.FileName;
                string targetpath = Server.MapPath("~/Files/");
                FileUpload.SaveAs(targetpath + filename);
                string pathToExcelFile = targetpath + filename;
                var connectionString = "";
                if (filename.EndsWith(".xls"))
                {
                    connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
                }
                else if (filename.EndsWith(".xlsx"))
                {
                    connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
                }

                var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
                var ds = new DataSet();

                adapter.Fill(ds, "ExcelTable");

                DataTable dtable = ds.Tables["ExcelTable"];

                string sheetName = "Sheet1";

                var excelFile = new ExcelQueryFactory(pathToExcelFile);
                var artistAlbums = from a in excelFile.Worksheet<User>(sheetName) select a;

                foreach (var a in artistAlbums)
                {
                    try
                    {
                        if (a.Name != "" && a.Address != "" && a.ContactNo != "")
                        {
                            User TU = new User();
                            TU.Name = a.Name;
                            TU.Address = a.Address;
                            TU.ContactNo = a.ContactNo;
                            db.Users.AddOrUpdate(u => u.Name, TU);

                            db.SaveChanges();
                        }
                        else
                        {
                            data.Add("<ul>");
                            if (a.Name == "" || a.Name == null) data.Add("<li> name is required</li>");
                            if (a.Address == "" || a.Address == null) data.Add("<li> Address is required</li>");
                            if (a.ContactNo == "" || a.ContactNo == null) data.Add("<li>ContactNo is required</li>");

                            data.Add("</ul>");
                            data.ToArray();
                            return Json(data, JsonRequestBehavior.AllowGet);
                        }
                    }

                    catch (DbEntityValidationException ex)
                    {
                        foreach (var entityValidationErrors in ex.EntityValidationErrors)
                        {
                            foreach (var validationError in entityValidationErrors.ValidationErrors)
                            {
                                Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
                            }
                        }
                    }
                }

                return Json("success", JsonRequestBehavior.AllowGet);
            }
            else
            {
                //alert message for invalid file format  
                data.Add("<ul>");
                data.Add("<li>Only Excel file format is allowed</li>");
                data.Add("</ul>");
                data.ToArray();
                return Json(data, JsonRequestBehavior.AllowGet);
            }
        }
        else
        {
            data.Add("<ul>");
            if (FileUpload == null) data.Add("<li>Please choose Excel file</li>");
            data.Add("</ul>");
            data.ToArray();
            return Json(data, JsonRequestBehavior.AllowGet);
        }
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            db.Dispose();
        }
        base.Dispose(disposing);
    }
}

Index View

@{
    ViewBag.Title = "Index";
}

<h4>Add Users via Excel</h4>
<hr />

@using (Html.BeginForm("UploadExcel", "User", FormMethod.Post, new { enctype = "multipart/form-data", onsubmit = "return myFunction()" }))
{
    <div class="form-horizontal">
        <div class="form-group">
            <div class="control-label col-md-2">Download Format:</div>
            <div class="col-md-10">
                <a href="/User/DownloadExcel/">
                    <img src="../../Images/excel.ico" width="25" height="25" title="Download Excel format" alt="User.xlsx" />
                </a>
            </div>
        </div>

        <div class="form-group">
            <div class="control-label col-md-2">Excel:</div>
            <div class="col-md-10">
                <input type="file" id="FileUpload" name="FileUpload" class="" />
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Upload" id="btnUpload" class="btn btn-primary" />
            </div>
        </div>
    </div>
}  

If you have any other questions, please feel free to contact me any time.

Best Regards
Velen

Answer 4

User1527485495 posted

Thanks Valen,  Please do i have to create a new Model  and controller ? if yes bro, what step do i have to take . thanks in anticipation bro.

Answer 5

User-1508012637 posted

Hi Dreal1,

Please do i have to create a new Model  and controller ?

It depends on you. The model User I posted above is just for reference and you could change it to the Employee, Staff, Product or any other models.

Best Regards
Velen