Friday 14 September 2012

Export data to excel from c#

Export data to excel from c# windows application

  string   string1=comboBoxFromMarch.Text;
            string 
  string2=comboBoxToApril.Text;
            string 
  string3 = comboBox1.Text;

            Excel.Application myExcelApp;
            Excel.Workbooks myExcelWorkbooks;
            Excel.Workbook myExcelWorkbook;


            object misValue = System.Reflection.Missing.Value;

            myExcelApp = new Excel.ApplicationClass();
            myExcelApp.Visible = true;
            myExcelWorkbooks = myExcelApp.Workbooks;
            String fileName = @"E:\Documents and Settings\My Documents\Visual Studio 2008\\files\\3A.xls";
            // set this to your file you want
            myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);

            Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;

            //String cellFormulaAsString = myExcelWorksheet.get_Range("A10", misValue).Formula.ToString();
            // this puts the formula in Cell A2 or text depending whats in it in the string.
          
            myExcelWorksheet.get_Range("G7", misValue).Formula = 
  string1;
            myExcelWorksheet.get_Range("I7", misValue).Formula = 
  string2;
            myExcelWorksheet.get_Range("D10", misValue).Formula = 
  string3;

 Import Excel sheet data to c# windows application

 OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\filename.xls;Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'");
            OleDbCommand cmd;
            OleDbDataReader dr;
            con.Open();
            cmd = new OleDbCommand("Select * from [Sheet1$]WHERE fullname='" + comboBox1.Text + "'", con);
            dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                textBoxFatherHusbandName.Text = dr[""].ToString();
                textBoxPFActNo.Text = dr[""].ToString();
            }

            else
            {
                lblInfo.Visible = true;
                lblInfo.Text = "Person Does Not Exist";
            }
            con.Close();