Friday, 29 June 2012

Working with Excel Sheet - Import/Export Data

Handling Database Part :


First of all create a table (say BankDetails) in Sql Server
then  create a type as Taqle >>

CREATE TYPE [dbo].[BankDetailsTableType] AS TABLE(
    [AccNo] [varchar](50) NULL,
    [BankName] [varchar](50) NULL,
    [Branch] [varchar](50) NULL,
    [StartDate] [datetime] NULL,
    [TotalAmount] [float] NULL,
    [CurrentRate] [float] NULL
)

and then create a Stored procedure that will use this Table type for getting and inserting the imported data from the  Excel sheet.

CREATE PROCEDURE usp_InsertBankDetails
 (
       @TableVariable BankDetailsTableType READONLY        
 )
 AS
 BEGIN
       INSERT INTO BankDetails
       (
         AccNo,BankName,Branch,StartDate,TotalAmount,CurrentRate   
       )
       SELECT
             AccNo,BankName,Branch,StartDate,TotalAmount,CurrentRate
       FROM
             @TableVariable
        

 END

Creating Excel Sheet: 

Now create an excel sheet similar to our database table 'BankDetails' containing some data.
We will use this sheet to import data and insert into our database table.


Lets do Some Code Now: 

Here we will use a file upload control to upload the Excel sheet that we have just created.

Method to read uploaded excel sheet >>

string path = FileUpload1.PostedFile.FileName; 
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"  path + "';Extended Properties=Excel 8.0");

            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", oconn);
            oconn.Open();
            DataSet ds = new DataSet();
            da.Fill(ds);   // will use this dataset as the TableType parameter





Method to Insert uploaded excel sheet data  >>

SqlConnection con = new SqlConnection(CONNECTIONSTRING);
con.Open();
            SqlCommand com = new SqlCommand("usp_InsertBankDetails", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add(new SqlParameter("@TableVariable", ds.Tables[0]));           
            com.ExecuteNonQuery();
            con.Close();

Exporting Data to Excel Sheet: 


Method to Export saved data to Excel sheet >>

        string str = "select * from BankDetails";     
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(str,con);
        da.Fill(ds);
        DataGrid dg = new DataGrid();   //Temp Datagrid
        dg.DataSource = ds.Tables[0];
        dg.DataBind();
        ExportToExcel("BudgeReport.xls", dg);



    private void ExportToExcel(string strFileName, DataGrid dg)
    {
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
        Response.ContentType = "application/excel";
        System.IO.StringWriter sw = new System.IO.StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        dg.RenderControl(htw);         
        Response.Write(sw.ToString());
        Response.End();
     }













No comments: