Checking if specific columns data exists in a different workbook c# ..

Tamal Banerjee

I've got a couple of different workbooks 1) newData.xlsx & 2) DATABASE.xlsx (image and file attached below)

I'm trying to check whether the Bill No. & Bill Date of the first row (i.e. row 6, as the relevant data starts from row 6) & last rows of the newData.xlsx file already exist in the DATABASE.xlsx file. The column numbers of both the target cells are different in both files.

How can I do this in the most efficient way possible using spire.xls & c#?

I've tried the below

string queryFrom=@"D:\sample\newData.xlsx";
string queryIn=@"D:\sample\Testing\DATABASE.xlsx";
Workbook workbook = new Workbook();
workbook.LoadFromFile(queryFrom);
Worksheet sheet = workbook.Worksheets[0];
var firstRowCellBillNo=sheet.Range["M6"].Value2.ToString().Trim();
var firstRowCellBillDate=sheet.Range["N6"].Value2.ToString().Trim();
var lastRowCellBillNo=sheet.Range["M"+(sheet.LastRow - 1)].Value2.ToString().Trim();
var lastRowCellBillDate=sheet.Range["N"+(sheet.LastRow - 1)].Value2.ToString().Trim();
Dictionary<string, string> dictionary = new Dictionary<string, string>();
Dictionary<string, string> dictionary2 = new Dictionary<string, string>();
if (!dictionary.ContainsKey(firstRowCellBillNo) && !dictionary.ContainsKey(lastRowCellBillNo))
{
dictionary.Add(firstRowCellBillNo.ToString(), firstRowCellBillDate.ToString());
dictionary.Add(lastRowCellBillNo.ToString(), lastRowCellBillDate.ToString());
}
workbook.Dispose();
Workbook wbk=new Workbook();
wbk.LoadFromFile(queryIn);
Worksheet sh=wbk.Worksheets[0];
CellRange xlRange=sh.Range[1,1,sh.LastRow,sh.LastColumn];
int rowCount = xlRange.RowCount;
for (int i = 1; i < rowCount; i++)
{
var col1 = xlRange.Columns[4].CellList[i].Value2.ToString();
var col2 = xlRange.Columns[5].CellList[i].Value2.ToString();
if (!dictionary2.ContainsKey(col1))
{
dictionary2.Add(col1.ToString(), col2.ToString());
}
}
//If ( the key value pair's of dictionary exist in dictionary2 )
{
Console.WriteLine("Data already exists in database!!");
}
//Else
{ Do some stuff }
wbk.Dispose();

I think my above code approach is complicating things and not to mention unfinished.

Can anyone help me on this?

1st image newData.xlsx and 2nd image DATABASE.xlsx

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tamal Banerjee5y ago
@Leon D Thanks for your reply. Can LINQ be used somehow to shorten / make the code efficient and fast? Just asking...
0
Leon D5y ago
Hi,

Try the following approach which might be faster than yours.
//Create two datatables for comparison
DataColumn dc1 = new DataColumn("NO", Type.GetType("System.String"));
DataColumn dc2 = new DataColumn("DATE", Type.GetType("System.String"));
DataColumn dc3 = new DataColumn("NO", Type.GetType("System.String"));
DataColumn dc4 = new DataColumn("DATE", Type.GetType("System.String"));

DataTable table1 = new DataTable();
table1.Columns.Add(dc1);
table1.Columns.Add(dc2);

DataTable table2 = new DataTable();
table2.Columns.Add(dc3);
table2.Columns.Add(dc4);

//Load the Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"E:\testdoc\findSame.xlsx");
//Get the first worksheet
Worksheet sheet1 = workbook.Worksheets[0];
//Get the second worksheet
Worksheet sheet2 = workbook.Worksheets[1];

//create two lists to save data
List<string[]> sheet1_data = new List<string[]>();
List<string[]> sheet2_data = new List<string[]>();

//Put the specified data in sheet1 to table1
sheet1_data.Add(new string[] { sheet1.Range["C4"].Value, sheet1.Range["E4"].Value } );
sheet1_data.Add(new string[] { sheet1.Range["C15"].Value, sheet1.Range["E15"].Value });
sheet1_data.Add(new string[] { sheet1.Range["C7"].Value, sheet1.Range["E7"].Value });
for (int i = 0; i < sheet1_data.Count; i++)
{
    DataRow dr = table1.NewRow();
    dr["NO"] = sheet1_data[i].GetValue(0);
    dr["DATE"] = sheet1_data[i].GetValue(1);
    table1.Rows.Add(dr);
}

//Put all data in sheet2 to table2
foreach (CellRange row in sheet2.Rows)
{
    sheet2_data.Add(new string[] { row.CellList[0].Value, row.CellList[1].Value });
}
for (int i = 0; i < sheet2_data.Count; i++)
{
    DataRow dr = table2.NewRow();
    dr["NO"] = sheet2_data[i].GetValue(0);
    dr["DATE"] = sheet2_data[i].GetValue(1);
    table2.Rows.Add(dr);
}

//Find the intersection (duplicate content) of table1 and table2
var data2 = table1.AsEnumerable().Intersect(table2.AsEnumerable(), DataRowComparer.Default);

if (data2.Count().Equals(0))
{
    Console.WriteLine("NO Duplicate Content \n");
}
else
{
    Console.WriteLine("The Duplicate Content \n");
    foreach (var data in data2)
    {
        Console.WriteLine("NO:" + data.ItemArray[0] + "   DATE:" + data.ItemArray[1] + "   Index in table1:" + table1.Rows.IndexOf(data));
    }
}
Console.ReadKey();
+1