[X]

asp.net SQL server - import export excel worksheet using ado.net

Import/Export E worksheet using ADO.Net

Excel is very known format for storing small amount of data. So you may want your data residing in ms sql or oracle or any other data source do be exported because of any of the following reason:

  • Running calculation to derive the wanted information
  • Doing some statistical computation like MEAN, MEDIAN, MODE
  • Filtering/Sorting
  • Generating Graphical report
  • Making use of PivotTable and PivotChart

Apart from above listed one there exists also lots of other reasons to export data in excel format. Similarily we can use excel to do bulk import of data and put it into sql server database. It is very easy and faster to do data entry in excel sheet and then import those data to rdbms system.

Export to Excel from SQL Server:

There are may ways we can read data from sql server and export to excel sheet like using third party controls, binding data to some data controls and then exporting using "GridView.RenderControl()" method.

But here I am going to show how to export from sql server to ms excel using ado.net.

1. Create an asp.net website project named "ExcelExportImport" and 2 folders to it "DownloadFiles" and "UploadFiles".

2. Download AdventureWorksLT2008 database and attach ot your sql server database.

3. Add a connection string to your web.config file

 

<connectionStrings>
		<add name="AWConnectionString" connectionString="Data
Source=localhost\SQL2008; Initial Catalog=AdventureWorksLT2008; Integrated Security=True;"/>
	</connectionStrings>

 

 

 

4. Add an asp.net page and name it to "ExcelExport.aspx"

 

<%@  Page Language="C#" AutoEventWireup="true" CodeBehind="ExcelExport.aspx.cs" Inherits="ExcelExportImport.ExcelExport" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Please choose the Excel version:<br />
        <asp:RadioButtonList ID="rblExtension" runat="server">
            <asp:ListItem Selected="True" Value="2003">Excel 97-2003</asp:ListItem>
            <asp:ListItem Value="2007">Excel 2007</asp:ListItem>
        </asp:RadioButtonList>
        Generate the download link when finished:
        <asp:RadioButtonList ID="rblDownload" runat="server">
            <asp:ListItem Selected="True" Value="Yes">Yes</asp:ListItem>
            <asp:ListItem Value="No">No</asp:ListItem>
        </asp:RadioButtonList>
        <br />
        <asp:Button ID="btnExport" runat="server" Text="Export" OnClick="btnExport_Click" />
        <br />
        <asp:HyperLink ID="hlDownload" runat="server"></asp:HyperLink>
    </div>
    </form>
</body>
</html>

 

5. Open the ExcelExport.aspx.cs file and write below code:

 

#region Using directives 
using System; 
using System.Collections.Generic; 
using System.Linq;
using System.Web; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Data;
using System.Data.OleDb; 
using System.Data.SqlClient; 
using System.Configuration;
#endregion Using directives 
namespace ExcelExportImport
{
    public partial class ExcelExport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e) { }
        protected void btnExport_Click(object sender, EventArgs e)
        {
            string strDownloadFileName
                = ""; string strExcelConn = ""; if (rblExtension.SelectedValue == "2003")
            { // Excel97-2003 
                strDownloadFileName = "~/DownloadFiles/SalesLT.Customers_" + DateTime.Now.ToString("yyyyMMddHHmmss")
            + ".xls"; strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strDownloadFileName)
            + ";Extended Properties='Excel 8.0;HDR=Yes'";
            }
            else
            { // Excel 2007 
                strDownloadFileName
            = "~/DownloadFiles/SalesLT.Customers_" + DateTime.Now.ToString("yyyyMMddHHmmss")
            + ".xlsx"; strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(strDownloadFileName)
            + ";Extended Properties='Excel 12.0 Xml;HDR=Yes'";
            } // Retrieve data from SQL Serve table. 
            DataTable dtSQL = GetCustomerData(); // Export data to an Excel spreadsheet.
            ExportData2Excel(strExcelConn, dtSQL); if (
                rblDownload.SelectedValue == "Yes")
            {
                hlDownload.Visible = true; // Display the download link. 
                hlDownload.Text = "Click here to download file."; hlDownload.NavigateUrl = strDownloadFileName;
            }
            else
            {
                // Hide the download link. 
                hlDownload.Visible = false;
            }
        } //get data from sql server table 
        protected DataTable GetCustomerData()
        {
            DataTable table = new DataTable();
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AWConnectionString"].ConnectionString))
            {
                SqlDataAdapter da = new SqlDataAdapter(@"SELECT TOP 1000 [CustomerID] ,cast([NameStyle]
as varchar(1)) as NameStyle ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix]
,[CompanyName] ,[SalesPerson] ,[EmailAddress] ,[Phone] ,[PasswordHash] ,[PasswordSalt]
,cast([rowguid] as varchar(100)) as rowguid ,[ModifiedDate] FROM [SalesLT].[Customer]",
                    con); da.Fill(table);
            } return table;
        } //export data to an excel sheet via ado.net
        protected void ExportData2Excel(string strCon, DataTable tableSQL)
        {
            using (OleDbConnection
                oleCon = new OleDbConnection(strCon))
            {
                OleDbCommand oleCmd = new OleDbCommand();
                oleCmd.Connection = oleCon; oleCmd.CommandText = @"CREATE TABLE Customer( CustomerID
integer, NameStyle varchar (1), Title varchar(8) , FirstName varchar (50) , MiddleName
varchar (50) , LastName varchar (50) , Suffix varchar(10) , CompanyName varchar(128)
, SalesPerson varchar(250) , EmailAddress varchar(50) , Phone varchar(20) , PasswordHash
varchar(128) , PasswordSalt varchar(10) , rowguid varchar (100) , ModifiedDate date
)"; oleCmd.CommandType = CommandType.Text; oleCon.Open(); oleCmd.ExecuteNonQuery();
                oleCmd.CommandText = @"insert into Customer( CustomerID, NameStyle , Title , FirstName
, MiddleName , LastName , Suffix , CompanyName , SalesPerson, EmailAddress, Phone
, PasswordHash , PasswordSalt, rowguid , ModifiedDate) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                oleCmd.Parameters.Add("CustomerID", OleDbType.Integer, 5, "CustomerID"); oleCmd.Parameters.Add("NameStyle",
                OleDbType.VarChar, 1, "NameStyle"); oleCmd.Parameters.Add("Title", OleDbType.VarChar,
                8, "Title"); oleCmd.Parameters.Add("FirstName", OleDbType.VarChar, 50, "FirstName");
                oleCmd.Parameters.Add("MiddleName", OleDbType.VarChar, 50, "MiddleName"); oleCmd.Parameters.Add("LastName",
                OleDbType.VarChar, 50, "LastName"); oleCmd.Parameters.Add("Suffix", OleDbType.VarChar,
                10, "Suffix"); oleCmd.Parameters.Add("CompanyName", OleDbType.VarChar, 128, "CompanyName");
                oleCmd.Parameters.Add("SalesPerson", OleDbType.VarChar, 250, "SalesPerson"); oleCmd.Parameters.Add("EmailAddress",
                OleDbType.VarChar, 50, "EmailAddress"); oleCmd.Parameters.Add("Phone", OleDbType.VarChar,
                20, "Phone"); oleCmd.Parameters.Add("PasswordHash", OleDbType.VarChar, 128, "PasswordHash");
                oleCmd.Parameters.Add("PasswordSalt", OleDbType.VarChar, 10, "PasswordSalt"); oleCmd.Parameters.Add("rowguid",
                OleDbType.VarChar, 100, "rowguid"); oleCmd.Parameters.Add("ModifiedDate", OleDbType.Date,
                100, "ModifiedDate"); OleDbDataAdapter oleDa = new OleDbDataAdapter("select * from Customer", oleCon); oleDa.InsertCommand = oleCmd; foreach (DataRow sqlRow in tableSQL.Rows)
                { sqlRow.SetAdded(); } oleDa.Update(tableSQL);
            }
        }
    }
}

 

 

6. Run the page and test the functionality.

Excel to SQL Server Import:

1. Add an asp.net page and name it to "ExcelImport.aspx" to the same project.

 

<%@  Page Language="C#" AutoEventWireup="true" CodeBehind="ExcelImport.aspx.cs" Inherits="ExcelExportImport.ExcelImport" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"



"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Please select a Excel spreadsheet to import:<br />
        <asp:FileUpload ID="fupExcel" runat="server" />
        <br />
        <br />
        Archive the Excel file on server after importing:
        <asp:RadioButtonList ID="rblArchive" runat="server">
            <asp:ListItem Value="Yes">Yes</asp:ListItem>
            <asp:ListItem Selected="True" Value="No">No</asp:ListItem>
        </asp:RadioButtonList>
        <br />
        <asp:Button ID="btnImport" runat="server" Text="Import" OnClick="btnImport_Click" />
        <br />
        <br />
        <asp:Label ID="lblMessages" runat="server" Text=""></asp:Label>
    </div>
    </form>
</body>
</html>

 

 

 

2. Open the ExcelImport.aspx.cs file and write below code:

 

#region Using directives 
using System; using System.Collections.Generic; using System.Linq;
using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data;
using System.Data.OleDb; using System.Data.SqlClient; using System.IO; using System.Configuration;
#endregion Using directives 
namespace ExcelExportImport
{
    public partial class ExcelImport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e) { }
        protected void btnImport_Click(object sender, EventArgs e)
        {
            bool fileUpload = false;
            string strExtension = string.Empty; string strFileName = string.Empty; string strUploadFileName
            = string.Empty; // Before attempting to import the file, verify // that the FileUpload control contains a file. 
            if (fupExcel.HasFile)
            { // Get the name of the Excel spreadsheet to upload. 
                strFileName = Server.HtmlEncode(fupExcel.FileName); // Get the extension of the Excel spreadsheet. 
                strExtension = Path.GetExtension(strFileName); // Validate the file extension. 
                if (strExtension != ".xls" && strExtension != ".xlsx")
                {
                    Response.Write("<script>                                                                                                 alert('Please select a Excel spreadsheet to import!');</script>");
                    return;
                } // Generate the file name to save. 
                strUploadFileName = "~/UploadFiles/"
        + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension; // Save the Excel spreadsheet on server. 
                fupExcel.SaveAs(Server.MapPath(strUploadFileName)); fileUpload = true;
            } if (fileUpload)
            { // Generate the connection string for Excel file. 
                string strExcelConn = ""; // There is no column name In a Excel spreadsheet. // So we specify "HDR=YES" in the connection string to use 
                // the values in the first row as column names.
                if (strExtension == ".xls")
                { // Excel 97-2003
                    strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties='Excel 8.0;HDR=YES;'";
                }
                else
                { // Excel 2007 
                    strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                + Server.MapPath(strUploadFileName) + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                } DataTable dtExcel = GetDataFromExcel(strExcelConn); // Get the row counts before importing. 
                int iStartCount = GetSQLRowCounts(); // Import the data. 
                SQLBulkCopyImport(dtExcel);
                // Get the row counts after importing. 
                int iEndCount = GetSQLRowCounts(); // Display the number of imported rows. 
                lblMessages.Text = Convert.ToString(iEndCount - iStartCount)
            + " rows were imported into Customer table"; if (rblArchive.SelectedValue == "No")
                { // Remove the uploaded Excel spreadsheet from server. File.Delete(Server.MapPath(strUploadFileName));
                }
            }
        }
        protected int GetSQLRowCounts()
        {
            int rowCount = 0; using (SqlConnection con
= new SqlConnection(ConfigurationManager.ConnectionStrings["AWConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("select count(CustomerID) from [dbo].[Customer]",
                    con); con.Open(); rowCount = (int)cmd.ExecuteScalar();
            } return rowCount;
        }
        protected
            DataTable GetDataFromExcel(string strCon)
        {
            DataTable tblExcel = new DataTable();
            using (OleDbConnection oleCon = new OleDbConnection(strCon))
            {
                OleDbDataAdapter
                    oleDa = new OleDbDataAdapter("select * from [Customer]", oleCon); oleDa.Fill(tblExcel);
            } return tblExcel;
        }
        protected void SQLBulkCopyImport(DataTable tblExcel)
        {
            using
                (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AWConnectionString"].ConnectionString))
            {
                con.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
                {
                    bulkCopy.DestinationTableName
                        = "[dbo].[Customer]"; foreach (DataColumn dc in tblExcel.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(dc.ColumnName,
                            dc.ColumnName);
                    } bulkCopy.WriteToServer(tblExcel);
                }
            }
        }
    }
}

 

 

 

3. Create a sample excel file like or download it from ""

CustomerID NameStyle Title FirstName MiddleName LastName Suffix CompanyName SalesPerson EmailAddress Phone PasswordHash PasswordSalt rowguid ModifiedDate
1 0 Mr. Orlando N. Gee
A Bike Store adventure-works\pamela0 orlando0@adventure-works.com 245-555-0173 L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7w= 1KjXYs4= 3F5AE95E-B87D-4AED-95B4-C3797AFCB74F 8/1/2001
2 0 Mr. Keith
Harris
Progressive Sports adventure-works\david8 keith0@adventure-works.com 170-555-0127 YPdtRdvqeAhj6wyxEsFdshBDNXxkCXn+CRgbvJItknw= fs1ZGhY= E552F657-A9AF-4A7D-A645-C429D6E02491 8/1/2002
3 0 Ms. Donna F. Carreras
Advanced Bike Components adventure-works\jillian0 donna0@adventure-works.com 279-555-0130 LNoK27abGQo48gGue3EBV/UrlYSToV0/s87dCRV7uJk= YTNH5Rw= 130774B1-DB21-4EF3-98C8-C104BCD6ED6D 9/1/2001
4 0 Ms. Janet M. Gates
Modular Cycle Systems adventure-works\jillian0 janet1@adventure-works.com 710-555-0173 ElzTpSNbUW1Ut+L5cWlfR7MF6nBZia8WpmGaQPjLOJA= nm7D5e4= FF862851-1DAA-4044-BE7C-3E85583C054D 7/1/2002
5 0 Mr. Lucy
Harrington Metropolitan Sports Supply adventure-works\shu0 lucy0@adventure-works.com 828-555-0186 KJqV15wsX3PG8TS5GSddp6LFFVdd3CoRftZM/tP0+R4= cNFKU4w= 83905BDC-6F5E-4F71-B162-C98DA069F38A 9/1/2002
6 0 Ms. Rosmarie J. Carroll
Aerobic Exercise Company adventure-works\linda3 rosmarie0@adventure-works.com 244-555-0112 OKT0scizCdIzymHHOtyJKQiC/fCILSooSZ8dQ2Y34VM= ihWf50M= 1A92DF88-BFA2-467D-BD54-FCB9E647FDD7 9/1/2003
7 0 Mr. Dominic P. Gash
Associated Bikes adventure-works\shu0 dominic0@adventure-works.com 192-555-0173 ZccoP/jZGQm+Xpzc7RKwDhS11YFNybwcPVRYTSNcnSg= sPoUBSQ= 03E9273E-B193-448E-9823-FE0C44AEED78 7/1/2002
10 0 Ms. Kathleen M. Garza
Rural Cycle Emporium adventure-works\josé1 kathleen0@adventure-works.com 150-555-0127 Qa3aMCxNbVLGrc0b99KsbQqiVgwYDfHcsK9GZSUxcTM= Ls05W3g= CDB6698D-2FF1-4FBA-8F22-60AD1D11DABD 9/1/2002
11 0 Ms. Katherine
Harding
Sharp Bikes adventure-works\josé1 katherine0@adventure-works.com 926-555-0159 uRlorVzDGNJIX9I+ehTlRK+liT4UKRgWhApJgUMC2d4= jpHKbqE= 750F3495-59C4-48A0-80E1-E37EC60E77D9 8/1/2001
12 0 Mr. Johnny A. Caprio Jr. Bikes and Motorbikes adventure-works\garrett1 johnny0@adventure-works.com 112-555-0191 jtF9jBoFYeJTaET7x+eJDkd7BzMz15Wo9odbGPBaIak= wVLnvHo= 947BCAF1-1F32-44F3-B9C3-0011F95FBE54 8/1/2002
16 0 Mr. Christopher R. Beck Jr. Bulk Discount Store adventure-works\jae0 christopher1@adventure-works.com 1 (11) 500 555-0132 sKt9daCzEEKWAzivEGPOp8tmaM1R3I+aJfcBjzJRFLo= 8KfYx/4= C9381589-D31C-4EFE-8978-8D3449EB1F0F 9/1/2002
18 0 Mr. David J. Liu
Catalog Store adventure-works\michael9 david20@adventure-works.com 440-555-0132 61zeTkO+eI5g8GG0swny8Wp/6GzZMFnT71fnW4lTHNY= c7Ttvv0= C04D6B4D-94C6-4C5C-A44C-B449C0AC1B45 8/1/2001
19 0 Mr. John A. Beaver
Center Cycle Shop adventure-works\pamela0 john8@adventure-works.com 521-555-0195 DzbqWX7B3EK5Dub92CKHYSUGKGbZCbrcVDpVe/xyBeI= zXNgrJw= 69AE5D43-31BE-4B76-BFBB-5A23C4788BBC 4/1/2003
20 0 Ms. Jean P. Handley
Central Discount Store adventure-works\david8 jean1@adventure-works.com 582-555-0113 o1GVo3vExeNzo0/ctdRGf2eDK3uzTlcUbr18tN+Slf8= uMsvfdo= E010C10A-F1C3-4BBA-81CA-A7E083350400 9/1/2001
21 0
Jinghao
Liu
Chic Department Stores adventure-works\jillian0 jinghao1@adventure-works.com 928-555-0116 IaD5AeqK9mRiIrJi/etZGVO6EiybLf/oksA2CqrpoJ0= p6pOqKc= 564E0B42-4609-43DE-9881-914DA433D82D 9/1/2002
22 0 Ms. Linda E. Burnett
Travel Systems adventure-works\jillian0 linda4@adventure-works.com 121-555-0121 23AwhujCoXYSPiN/B+G8Z9rk36xl35EbdLT7akTMTqU= SmyIPjE= 9774AED6-D673-412D-B481-2573E470B478 8/1/2001
23 0 Mr. Kerim
Hanif
Bike World adventure-works\shu0 kerim0@adventure-works.com 216-555-0122 d0WSjosAd7Y3XOWjNAkoTClCb50vwPuAawOSI1iosgs= 33g5co8= 733F8250-3251-4C2A-8C85-C285BB76B7B4 9/1/2002
24 0 Mr. Kevin
Liu
Eastside Department Store adventure-works\linda3 kevin5@adventure-works.com 926-555-0164 ylTpkIOHKLcjihNjS0j/k10eOHOsWQMNhlbuOCp+UTY= TgZnUOg= C111E51D-178D-4DBD-A6FF-F1CCBF4B1AAD 9/1/2002
25 0 Mr. Donald L. Blanton
Coalition Bike Company adventure-works\shu0 donald0@adventure-works.com 357-555-0161 pKYDelLBOZMO98GBzhMxBSzzE0gUYKx9dXzYTYNuBgw= jKtOaOw= 31D03546-FB2A-4488-890E-4709A659E3F0 9/1/2002
28 0 Ms. Jackie E. Blackwell
Commuter Bicycle Store adventure-works\josé1 jackie0@adventure-works.com 972-555-0163 wqhgKfOTfef4Zo3cb6FwsFzvG/yCVstuYh3AuwjRszQ= SZ+r60o= 9B8A04A4-D909-4F49-B1C5-3DE317D69B2A 8/1/2003
29 0 Mr. Bryan
Hamilton
Cross-Country Riding Supplies adventure-works\josé1 bryan2@adventure-works.com 344-555-0144 ftRWIIT4oF+if+ddn1ROYXHw6PAooKFX3uALZ0uEU98= IRNPDIw= ECA7C8FA-490B-4B97-9C32-043C90F789E8 8/1/2001
30 0 Mr. Todd R. Logan
Cycle Merchants adventure-works\garrett1 todd0@adventure-works.com 783-555-0110 FV6z03ywMJOumcU+TEoL/Z/s4YP2fe8B3MJUUTA0CHU= mFRhaEg= 2B15DFB1-831E-49E7-B337-395798B129E7 9/1/2002
34 0 Ms. Barbara J. German
Cycles Wholesaler & Mfg. adventure-works\jae0 barbara4@adventure-works.com 1 (11) 500 555-0181 2Pyd3S3Os61yt+lfSjMgSDNwT1LL4Qs51m1ob42We40= Jvsxxrg= 9EDDA794-7862-4FF4-B8E2-528847CAB2BD 7/1/2003
37 0 Mr. Jim
Geist
Two Bike Shops adventure-works\pamela0 jim1@adventure-works.com 724-555-0161 cvqeC4fJcKwJ9jlluiWvK5/MyuSi8neLnjFDGdvzJy4= ot8WcXk= C6EBB29A-CC67-459C-90E3-339E0F912906 9/1/2002
38 0 Ms. Betty M. Haines
Finer Mart adventure-works\david8 betty0@adventure-works.com 867-555-0114 Q/nGAVzOO1ZT/4+BQ1kwltqQyzycLA2GGmDmBBxLMnM= 6IvcbVg= E5EDA3F3-4EF1-4806-BD36-E61F3CB2E044 9/1/2003
39 0 Ms. Sharon J. Looney
Fitness Hotel adventure-works\jillian0 sharon2@adventure-works.com 377-555-0132 Uo3kAuNh936QfPTIfPt6I6Z3+olLMRu5IC5awuzDaG8= uHgb0IU= 6808B1ED-3C9A-401D-B284-9AC31166D9EB 9/1/2002
40 0 Mr. Darren
Gehring
Journey Sporting Goods adventure-works\jillian0 darren0@adventure-works.com 417-555-0182 kqptixZ7LqTuOKcc7ylpabWrgWr5BDXa2fpkDxEwaCY= Xe7grug= AA5E28F6-16C8-4D80-894E-17531F2CF972 8/1/2001

4. Run the page and test the functionality.

Download Sample Code: ExcelExportImport.rar (5.47 kb)

blog comments powered by Disqus

Posts By Month