How to import /export database data from /to csv/xml/Excel using ASP.NET and C#.

Introduction

In this post, I explain how to import / export database data from / to Excel using ASP.NET and C#.
Import/export data from/to some common format is a very useful technique for transfer data between two other programs/ system on a different platform.
I have also post: How to export gridview to excel & Word file with formatting in asp.net

Part 1 : Steps for import / export database data from/to CSV file.
Part 2 : Steps for import / export database data from/to XML file.
Part 3: Steps for import / export database data from/to EXCEL file.

Steps :

Steps for import / export database data from/to Excel file.

Step - 1: Create New Project.

Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create table for import / export data.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used one table as below

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Add a Webpage and Design for import, show and export data.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code

<h3>Import / Export database data from/to Excel.</h3>
<div>
    <table>
        <tr>
            <td>Select File : </td>
            <td>
                <asp:FileUpload ID="FileUpload1" runat="server" />
                </td>
            <td>
                <asp:Button ID="btnImport" runat="server" Text="Import Data" OnClick="btnImport_Click" />
            </td>
        </tr>
    </table>
    <div>
        <br />
        <asp:Label ID="lblMessage" runat="server"  Font-Bold="true" />
        <br />
        <asp:GridView ID="gvData" runat="server" AutoGenerateColumns="false">
            <EmptyDataTemplate>
                <div style="padding:10px">
                    Data not found!
                </div>
            </EmptyDataTemplate>
            <Columns>
                <asp:BoundField HeaderText="Employee ID" DataField="EmployeeID" />
                <asp:BoundField HeaderText="Company Name" DataField="CompanyName" />
                <asp:BoundField HeaderText="Contact Name" DataField="ContactName" />
                <asp:BoundField HeaderText="Contact Title" DataField="ContactTitle" />
                <asp:BoundField HeaderText="Address" DataField="EmployeeAddress" />
                <asp:BoundField HeaderText="Postal Code" DataField="PostalCode" />
            </Columns>
        </asp:GridView>
        <br />
        <asp:Button ID="btnExport" runat="server" Text="Export Data" OnClick="btnExport_Click" />
    </div>
</div>

Step-6: Write code into page load event for show data.

Write below code into Page_Load event for show data from database.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        PopulateData();
        lblMessage.Text = "Current Database Data!";
    }
}

And here is the functioin populateDatabaseData

private void populateDatabaseData()
{
    using (MuDatabaseEntities dc = new MuDatabaseEntities())
    {
        gvData.DataSource = dc.EmployeeMasters.ToList();
        gvData.DataBind();
    }
}

Step-7: Write code for import Data from EXCEL to database

Write below code into button click event for import Data from EXCEL to the database.

protected void btnImport_Click(object sender, EventArgs e)
{
    if (FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel" ||
        FileUpload1.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    {
        try
        {
            string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload1.PostedFile.FileName));
            FileUpload1.PostedFile.SaveAs(fileName);
 
            string conString = "";
            string ext = Path.GetExtension(FileUpload1.PostedFile.FileName);
            if (ext.ToLower() == ".xls")
            {
                conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; ;
            }
            else if (ext.ToLower() == ".xlsx")
            {
                conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
 
            string query = "Select [Employee ID],[Company Name], [Contact Name],[Contact Title],[Employee Address],[Postal Code] from [EmployeeData$]";
            OleDbConnection con = new OleDbConnection(conString);
            if (con.State == System.Data.ConnectionState.Closed)
            {
                con.Open();
            }
            OleDbCommand cmd = new OleDbCommand(query, con);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
 
            DataSet ds = new DataSet();
            da.Fill(ds);
            da.Dispose();
            con.Close();
            con.Dispose();
 
            // Import to Database
            using (MuDatabaseEntities dc = new MuDatabaseEntities())
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    string empID = dr["Employee ID"].ToString();
                    var v = dc.EmployeeMasters.Where(a => a.EmployeeID.Equals(empID)).FirstOrDefault();
                    if (v != null)
                    {
                        // Update here
                        v.CompanyName = dr["Company Name"].ToString();
                        v.ContactName = dr["Contact Name"].ToString();
                        v.ContactTitle = dr["Contact Title"].ToString();
                        v.EmployeeAddress = dr["Employee Address"].ToString();
                        v.PostalCode = dr["Postal Code"].ToString();
                    }
                    else
                    {
                        // Insert
                        dc.EmployeeMasters.Add(new EmployeeMaster
                        {
                                EmployeeID = dr["Employee ID"].ToString(),
                                CompanyName = dr["Company Name"].ToString(),
                                ContactName = dr["Contact Name"].ToString(),
                                ContactTitle = dr["Contact Title"].ToString(),
                                    EmployeeAddress = dr["Employee Address"].ToString(),
                                    PostalCode = dr["Postal Code"].ToString()
                        });
                    }
                }
 
                dc.SaveChanges();
            }
 
            PopulateData();
            lblMessage.Text = "Successfully data import done!";
        }
        catch (Exception)
        {                    
            throw;
        }
    }
}

Step-8: Write code for export Data from a database to EXCEL.

Write below code into button click event for export Data from a database to EXCEL.

protected void btnExport_Click(object sender, EventArgs e)
{
    using (MuDatabaseEntities dc = new MuDatabaseEntities())
    {
        List<EmployeeMaster> emList = dc.EmployeeMasters.ToList();
        StringBuilder sb = new StringBuilder();
 
        if (emList.Count > 0)
        {
            string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx");
            string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0 Xml;HDR=Yes'";
            using (OleDbConnection con = new OleDbConnection(conString))
            {
                string strCreateTab = "Create table EmployeeData ("+
                    " [Employee ID] varchar(50), " +
                    " [Company Name] varchar(200), " +
                    " [Contact Name] varchar(200), " +
                    " [Contact Title] varchar(200), " +
                    " [Employee Address] varchar(200), " +
                    " [Postal Code] varchar(50))";
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
 
                OleDbCommand cmd = new OleDbCommand(strCreateTab, con);
                cmd.ExecuteNonQuery();
 
                string strInsert = "Insert into EmployeeData([Employee ID],[Company Name],"+
                    " [Contact Name], [Contact Title], [Employee Address], [Postal Code]" +
                    ") values(?,?,?,?,?,?)";
                OleDbCommand cmdIns = new OleDbCommand(strInsert, con);
                cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
                cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
 
                foreach (var i in emList)
                {
                    cmdIns.Parameters[0].Value = i.EmployeeID;
                    cmdIns.Parameters[1].Value = i.CompanyName;
                    cmdIns.Parameters[2].Value = i.ContactName;
                    cmdIns.Parameters[3].Value = i.ContactTitle;
                    cmdIns.Parameters[4].Value = i.EmployeeAddress;
                    cmdIns.Parameters[5].Value = i.PostalCode;
 
                    cmdIns.ExecuteNonQuery();
                }
            }
 
            // Create Downloadable file
            byte[] content = File.ReadAllBytes(fileName);
            HttpContext context = HttpContext.Current;
 
            context.Response.BinaryWrite(content);
            context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=EmployeeData.xlsx");
            Context.Response.End();
        }
    }
}