Friday, 28 December 2012

get excel sheet data


     private void btnCompare_Click(object sender, EventArgs e)
        {
            string filename1 = FileTxt1.Text;
            string filename2 = FileTxt2.Text;

            string file1_sheet = GetExcelSheets(filename1);
            string file2_sheet = GetExcelSheets(filename2);

            //String sConnectionString1 = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            //"Data Source=" + filename1 + ";" +
            //"Extended Properties=Excel 8.0;";

            //String sConnectionString2 = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            //"Data Source=" + filename2 + ";" +
            //"Extended Properties=Excel 8.0;";
            String sConnectionString1 = "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + filename1 + ";" +
            "Extended Properties=Excel 12.0;";

            String sConnectionString2 = "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + filename2 + ";" +
            "Extended Properties=Excel 12.0;";
            OleDbConnection objConn = new OleDbConnection(sConnectionString1);

            objConn.Open();
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + file1_sheet + "$]", objConn);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;
            DataSet objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");
            DataTable dt1 = objDataset1.Tables[0];
            objConn.Close();


            objConn = new OleDbConnection(sConnectionString2);
            objConn.Open();
            objCmdSelect = new OleDbCommand("SELECT * FROM [" + file2_sheet + "$]", objConn);
            objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;
            objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");
            DataTable dt2 = objDataset1.Tables[0];
            objConn.Close();
            compareDataTables(dt1, dt2);

        }



   public string GetExcelSheets(string excelFileName)
        {
            Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
            workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
            "",
            true,
            false,
            0,
            true,
            false,
            false);
            Excel.Sheets sheets = workBookObject.Worksheets;

            // get the first and only worksheet from the collection of worksheets
            Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
            //MessageBox.Show(worksheet.Name);
            return worksheet.Name;
        }

2 comments: