Rajendra Tripathy

Rajendra Tripathy

  • NA
  • 823
  • 456k

I have to compere 1 Excel sheet (Column name- Phone ) with Notepad (Contain Phone numbers) through C#

Sep 19 2009 1:52 AM
I have to compere 1 Excel sheet (Column name- Phone ) with Notepad (Contain Phone numbers) through C# Programming. And after comparing the values (In excel there may be 100 phone nos, but in Notepad may be 10000 Phone nos) I need to Bold those Excel cells (Phone Nos) which is not present in Notepad(Phone Nos).  Below is the example You can see that in excel (Column Phone) 676868 & 878766 is not present in Notepad. So after comparing I have to get those two values , & Make it those cell Bold (in Excel- because i have get result in excel)

Excel                                                                                                                                NotePad

Name        Address      Phone                                                                                          123456
                                                                                                                                        3235235
x               ddddd         123456                                                                                        56778
y               mmmm        676868                                                                                        892849
z               jshcjh          565778                                                                                        565778
n               hghhh         878766




Please help me soon .....
Regards Raj.



This is my Code What I have Written ----


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel;
using System.Data.OleDb;
using System.IO;
using System.Collections;

namespace TestExcelBy_DB
{
    public partial class Form1 : Form
    {     
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            #region Read Notepad - DNC List
            string txtPath = @"D:\Raj Projects\List.txt";
            StreamReader objReader = new StreamReader(txtPath);
            string sLine = "";
            ArrayList arrText = new ArrayList();
            while (sLine != null)
            {
                sLine = objReader.ReadLine();
                if (sLine != null)
                    arrText.Add(sLine);
            }
            objReader.Close();
            #endregion

            #region Read Excel - Uncheck List

            string filePath = @"D:\Raj Projects\Uncheck.xlsx";
            string strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";           
            System.Data.OleDb.OleDbConnection objOleDbConnection = new System.Data.OleDb.OleDbConnection(strConnectionString);                          
            string strExcelQuery = (@"Select Phone from [Sheet1$] ");   //Select columnName Phone from Sheet1- Working Properly.
            //create the OleDb Command
            System.Data.OleDb.OleDbCommand objOleDbCommand = new System.Data.OleDb.OleDbCommand(strExcelQuery, objOleDbConnection);
            objOleDbConnection.Open();
            //Create OleDb DataReader
            System.Data.OleDb.OleDbDataReader objOleDbDataReader = objOleDbCommand.ExecuteReader();
            while (objOleDbDataReader.Read())
            {           
                string strValue = "";
                for (int i = 0; i < objOleDbDataReader.FieldCount; i++)
                {            
                    strValue = (objOleDbDataReader.GetValue(i)).ToString();     
                                
            #endregion

            #region Comapre Notepad(DNC List) with - Excel(Uncheck List)
                    if (!(arrText.Contains(strValue)))
                   
                    {
                    // Here I need to Bold those Excel cells (Phone Nos) which is not present in Notepad(Phone Nos).
                    }
            #endregion         
                  
                }              
            }

            objOleDbConnection.Close();      

        }
    }
}


Answers (8)