Compare two excel files sheets for mathing values and put check mark on both files if they match

Question

Hello,

My project scenario is, I am receiving a pdf document from a client and I have an own excel spreadsheet to compare with that. I was able to find out the solution for pdf to excel conversion. so now, I would like to compare two excel files sheets (PDF to excel conversion one and my own excel ) for the matching dates and amounts. So looking for some solution, it will allow users to find out the same dates and amounts from both excel files sheets for the specific date range and then put check mark on the matching amount for both files and then convert to pdf client excel file again. what could be the solution programmatically?

Thanks in advanced!

Answer 1

Hi @Raki ,
First, you can use OleDb to convert Excel data into DataTable data.
Code:

public static System.Data.DataTable DBExcelToDataTable(string pathName, string sheetName = "")  
        {  
            System.Data.DataTable dt = new System.Data.DataTable();  
            string ConnectionString = string.Empty;  
            FileInfo file = new FileInfo(pathName);  
            if (!file.Exists) { throw new Exception("The file does not exist"); }  
            string extension = file.Extension;  
            switch (extension)                            
            {  
                case ".xls":  
                    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";  
                    break;  
                case ".xlsx":  
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=no;IMEX=1;'";  
                    break;  
                default:  
                    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";  
                    break;  
            }  
            System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(ConnectionString);  
            try  
            {  
                con.Open();  
                if (sheetName != "")                        
                {        
                    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + sheetName + "$] where F1 is not null ", con);  
                    System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);  
                    try  
                    {  
                        apt.Fill(dt);  
                    }  
                    catch (Exception ex) { throw new Exception("The specified worksheet name was not found in the Excel file," + ex.Message); }  
                    dt.TableName = sheetName;  
                }  
                else  
                {  
                    var tables = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { });  
                    if (tables.Rows.Count == 0)  
                    { throw new Exception("Excel must contain a table"); }  
                    foreach (System.Data.DataRow row in tables.Rows)  
                    {  
                        string strSheetTableName = row["TABLE_NAME"].ToString();     
                        if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))  
                        {  
                            System.Data.DataTable tableColumns = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, strSheetTableName, null });  
                            if (tableColumns.Rows.Count < 2)                      
                                continue;  
                            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + strSheetTableName + "] where F1 is not null", con);  
                            System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);  
                            apt.Fill(dt);  
                            dt.TableName = strSheetTableName.Replace("$", "").Replace("'", "");  
                            break;  
                        }  
                    }  
                }  
                if (dt.Rows.Count < 2)  
                    throw new Exception("The table must contain data");  
                System.Data.DataRow headRow = dt.Rows[0];  
                foreach (System.Data.DataColumn c in dt.Columns)  
                {  
                    string headValue = (headRow[c.ColumnName] == DBNull.Value || headRow[c.ColumnName] == null) ? "" : headRow[c.ColumnName].ToString().Trim();  
                    if (headValue.Length == 0)  
                    { throw new Exception("You must enter column headings"); }  
                    if (dt.Columns.Contains(headValue))  
                    { throw new Exception("You cannot use duplicate column headers：" + headValue); }  
                    c.ColumnName = headValue;  
                }  
                dt.Rows.RemoveAt(0);  
                return dt;  
            }  
            catch (Exception ee)  
            { throw ee; }  
            finally  
            { con.Close(); }  
        }  

Since the time date is fixed in the Excel standard column, you can filter the date by a fixed column in DataTable, and then get the match by iterating through the data within all these dates.
Finally use Conholdate.Total for . .NET completes the conversion of Excel to PDF.
Code:

using (Converter converter = new Converter("input.xlsx"))  
{  
    WatermarkOptions watermark = new WatermarkOptions  
    {  
        Text = "Sample watermark",  
        Color = Color.Red,  
        Width = 100,  
        Height = 100,  
        Background = true  
    };  
  
    PdfConvertOptions options = new PdfConvertOptions  
    {  
        Watermark = watermark  
    };  
  
    converter.Convert("output.pdf", options);  
}  

Best regards,
Qi You

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Raki ,
After my modifications, you can use the following code to convert Excel data into DataTable data.

 public class Program  
        {  
            public DataTable FileToDataTable(string fileName)  
            {  
                DataTable dt = new DataTable();  
                string extendName = Path.GetExtension(fileName);  
                switch (extendName.ToLower())  
                {  
                    case ".xls":  
                        dt = XlsToDataTable(fileName);  
                        break;  
                    case ".xlsx":  
                        dt = XlsxToDataTable(fileName);  
                        break;  
                    default:  
                        break;  
                }  
                return dt;  
            }  
            private DataTable XlsToDataTable(string fileName)  
            {  
                DataTable dataTable = new DataTable();  
                Stream stream = null;  
                try  
                {  
                    stream = File.OpenRead(fileName);  
                    HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);  
                    HSSFSheet hssfsheet = (HSSFSheet)hssfworkbook.GetSheetAt(hssfworkbook.ActiveSheetIndex);  
                    HSSFRow hssfrow = (HSSFRow)hssfsheet.GetRow(0);  
                    int lastCellNum = (int)hssfrow.LastCellNum;  
                    for (int i = (int)hssfrow.FirstCellNum; i < lastCellNum; i++)  
                    {  
                        DataColumn column = new DataColumn(hssfrow.GetCell(i).StringCellValue);  
                        dataTable.Columns.Add(column);  
                    }  
                    dataTable.TableName = hssfsheet.SheetName;  
                    int lastRowNum = hssfsheet.LastRowNum;  
                    for (int i = hssfsheet.FirstRowNum + 1; i < hssfsheet.LastRowNum; i++)//  
                    {  
                        HSSFRow hssfrow2 = (HSSFRow)hssfsheet.GetRow(i);  
                        DataRow dataRow = dataTable.NewRow();  
                        for (int j = (int)hssfrow2.FirstCellNum; j < lastCellNum; j++)//  
                        {  
                            dataRow[j] = hssfrow2.GetCell(j);//  
                        }  
                        dataTable.Rows.Add(dataRow);  
                    }  
                    stream.Close();  
                }  
                catch (Exception ex)  
                {  
                    throw new Exception("Xls to DataTable: \n" + ex.Message);  
                }  
                finally  
                {  
                    if (stream != null)  
                    {  
                        stream.Close();  
                    }  
                }  
                return dataTable;  
            }  
            public DataTable XlsxToDataTable(string vFilePath)  
            {  
                DataTable dataTable = new DataTable();  
                try  
                {  
                    SLDocument sldocument = new SLDocument(vFilePath);  
                    dataTable.TableName = sldocument.GetSheetNames()[0];  
                    SLWorksheetStatistics worksheetStatistics = sldocument.GetWorksheetStatistics();  
                    int startColumnIndex = worksheetStatistics.StartColumnIndex;  
                    int endColumnIndex = worksheetStatistics.EndColumnIndex;  
                    int startRowIndex = worksheetStatistics.StartRowIndex;  
                    int endRowIndex = worksheetStatistics.EndRowIndex;  
                    for (int i = startColumnIndex; i <= endColumnIndex; i++)  
                    {  
                        SLRstType cellValueAsRstType = sldocument.GetCellValueAsRstType(1, i);  
                        dataTable.Columns.Add(new DataColumn(cellValueAsRstType.GetText(), typeof(string)));  
                    }  
                    for (int j = startRowIndex + 1; j <= endRowIndex; j++)  
                    {  
                        DataRow dataRow = dataTable.NewRow();  
                        for (int i = startColumnIndex; i <= endColumnIndex; i++)  
                        {  
                            dataRow[i - 1] = sldocument.GetCellValueAsString(j, i);  
                        }  
                        dataTable.Rows.Add(dataRow);  
                    }  
                }  
                catch (Exception ex)  
                {  
                    throw new Exception("Xlsx to DataTable: \n" + ex.Message);  
                }  
                return dataTable;  
            }  
      
            public static void Main(string[] args)  
            {  
               Program program = new Program();  
               DataTable dt = program.FileToDataTable("C:\\Users\\Administrator\\Desktop\\ABCD.xlsx");  
                for (int i = 1; i < dt.Rows.Count; i++)  
                {  
                     
                        string str = dt.Rows[i][2].ToString();  
                        Console.WriteLine(str);  
                      
                }  
                Console.ReadLine();  
            }  
        }  

Finally, the ticking operation is completed through a judgment statement.

if (dt1[1][i] == dt1[1][j])&& (dt1[3][i] == dt2[3][j])  
                    dt2[4][j] = "singed";  

Next, since the data you are comparing is the same column in the DataTable, just compare whether they are similar, you can mark the REF column in the DataTable. Finally use my method above to convert Excel to PDF.

Best regards,
Qi You