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();
}
}
}
Loading

Rajendra TripathyPosted Jun 6, 2010, 5:53 AM
I have to Sort an Excel sheet (Sort On Font Color). Lets say there is Some rows in an excel sheet, Some of them are in blue and some rows are in Green. So I need Green rows to be in top and blue rows in button. For that I think I have to Sort that Excel sheet (Sort On Font Color). Hope you can understand. So please let me know how to do in C# Code.
Regards
Raj.
Rajendra TripathyPosted Sep 30, 2009, 12:15 PM
Thanks for all your Support and help. I solved this issue.. So no need to post any Comments.
Regards.
Rajendra TripathyPosted Sep 26, 2009, 11:13 AM
Excel.Workbook workBook = app.Workbooks.Open(filePath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
// Search Phone Index (Row Index & Column Index)
string myColName = "Phone";
int rowIndex = 0;
int phoneIndex = 0;
object[,] values = workSheet.UsedRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault) as object[,];
int colCount = workSheet.UsedRange.Columns.Count;
int rowCount = workSheet.UsedRange.Rows.Count;
for (int i = 1; i <= rowCount; i++)
{
for
(int j = 1; j <= colCount; j++)
{
if (values[i, j] != null)
{
if (myColName == values[i, j].ToString())
{
rowIndex = i;
phoneIndex = j;
while (((Excel.Range)workSheet.Cells[rowIndex, phoneIndex]).Value2 != null)
{
string strValue = ((Excel.Range)workSheet.Cells[rowIndex, phoneIndex]).Value2.ToString();
if ((arrText.Contains(strValue)))
{
//Excel.Range range = workSheet.get_Range(rowIndex, phoneIndex);
//range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
//// bold number
// ((Excel.Range)workSheet.Cells[rowIndex, phoneIndex]).Font.Bold = true;
}
//increase rowIndex
rowIndex = rowIndex + 1;
}
workBook.Save();
app.Quit();
Plz give your Input.
Regards
Serban CosminPosted Sep 25, 2009, 3:07 PM
Rajendra TripathyPosted Sep 24, 2009, 11:32 PM
So plz late me know how can I get the column index (Column Name="Phone").
Thanx in advance.
Waiting 4 ur Reply......
Serban CosminPosted Sep 21, 2009, 2:58 PM
Rajendra TripathyPosted Sep 21, 2009, 8:37 AM
as per my requirement I am getting the value of phone say (String PhoneNo- 565778 ). Now I need to open the excel sheet & through that string "PhoneNo- 565778" I have to serch the ROW which contain that value"565778". After that I need to color that Row Yellow, Or I can Bold that Row also.
Excel (Sheet1)
Name Address Phone
x ddddd 123456
y mmmm 676868
z jshcjh 565778
n hghhh 878766
Please let me know the Code in C#.
Regards
RAJ.
Serban CosminPosted Sep 19, 2009, 8:31 AM