Wednesday, June 19, 2013

Import data from excel c# code

Here i share how to import data from excel to sql server using c#.

Follow steps to import data from excel using c# :

1.    Import Namespace :

using System.Reflection;
using Microsoft.Office.Interop.Excel
 
2. Add common function for generate dataset from excel data.
  private DataSet generateData()

    {

        DataSet ds = new DataSet();

        string savepath;

        string fileName = Server.MapPath("..//TempFiles//") + FileUploadEx.PostedFile.FileName;

        FileUploadEx.SaveAs(Server.MapPath("..//TempFiles//") + FileUploadEx.FileName);

        savepath = Server.MapPath("..//TempFiles//") + FileUploadEx.PostedFile.FileName;

        Application oXL = new ApplicationClass();

        Workbook oWB;

        Worksheet oSheet;

        Range oRng;

        Range oRng1;

        try

        {

            oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value,

            Missing.Value, Missing.Value, Missing.Value,

            Missing.Value, Missing.Value, Missing.Value,

            Missing.Value, Missing.Value, Missing.Value, Missing.Value,

            Missing.Value, Missing.Value);

            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];

            System.Data.DataTable dt = new System.Data.DataTable("dtExcel");

         

            ds.Tables.Add(dt);

            DataRow dr;

            System.Data.DataTable dtValidData = new System.Data.DataTable("dtValid");

            System.Data.DataTable dtInvalidData = new System.Data.DataTable("dtInvalid");

            DataSet dsSummary = new DataSet();

            dsSummary.Tables.Add(dtValidData);

            dsSummary.Tables.Add(dtInvalidData);

            DataRow drValid;

            DataRow drInValid;

            StringBuilder sb = new StringBuilder();

            int jValue = oSheet.UsedRange.Cells.Columns.Count;

            int iValue = oSheet.UsedRange.Cells.Rows.Count;

            //  get data columns

            for (int j = 1; j <= jValue; j++)

            {

                oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];

                string strValue = oRng.Text.ToString().Replace(" ", "").Replace("/", "");

                //if (strValue != "Mobile" && j == 1)

                //{

                //    RegisterStartupScript("key", "<script>alert('File is not in proper Format..Column Name is mismatch');</script>");

                //    //return;

                //}

                dt.Columns.Add(strValue, System.Type.GetType("System.String"));

                dtValidData.Columns.Add(strValue, System.Type.GetType("System.String"));

                dtInvalidData.Columns.Add(strValue, System.Type.GetType("System.String"));

            }

            string stlmntdate = DateTime.Now.ToShortDateString();

            for (int i = 2; i <= iValue; i++)

            {

                dr = ds.Tables["dtExcel"].NewRow();

                for (int j = 1; j <= jValue; j++)

                {

                    oRng1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];

                    string ColValue = oRng1.Text.ToString().Replace(" ", "").Replace("/", "");

                    oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];

                    string strValue = oRng.Text.ToString();

                    dr[ColValue] = strValue;

                }

                ds.Tables["dtExcel"].Rows.Add(dr);

            }

            oWB.Close(Missing.Value, fileName, Missing.Value);

            return ds;

           

    }

    catch(Exception ex)

    {

        Common.InsertErorLog(Common.GetCurrentPageName(), ex.ToString());

        RegisterStartupScript("key", "<script>alert('" + ex.Message.Replace("'", "") + "');</script>");

   

    }

        return ds;

 

    }

3. Now Call this function where you want:
DataSet ds = generateData();

4. Now Do operation "DataSet ds" as you want


If you are searching life partner. your searching end with kpmarriage.com. now kpmarriage.com offer free matrimonial website which offer free message, free chat, free view contact information. so register here : kpmarriage.com- Free matrimonial website