paste rows if doesn't already exist in excel sheet using c#

Question

I've 2 excel sheets(Sheet1 & Sheet2) in my Excel workbook. I want to copy row data from Sheet2 to Sheet1.

Condition is:

if Sheet2 copied row doesn't exist in Sheet1 then paste it otherwise don't paste the row.

Copied Rows except 1st row in Sheet2:

Range dataWithoutFirstRow = xlAccrualSheet.Range[xlAccrualSheet.UsedRange.Cells[2, 1],
                            xlAccrualSheet.UsedRange.SpecialCells(XlCellType.xlCellTypeLastCell)];
dataWithoutFirstRow.Copy();

Paste in below used range in Sheet1:

Range DataRange = xlAccrualWorkSheet.Cells[emptycell, 1];
DataRange.PasteSpecial(XlPasteType.xlPasteAllUsingSourceTheme);

Please Tell me How to check already exist rows in Sheet1.

Awaiting for Your Response

quetzalcoatl quetzalcoatl · Accepted Answer · 2016-12-27T10:38:06

Please Tell me How to check already exist rows in Sheet1.

Use Range to read out all data from the workSheet. You are already doing a similar thing accrualSheet. Then, you can use range.Cells(i,j) or range.Rows(r).Value or even range.Rows(r).Cells(i,j) to get the data inside each specific row.

When pasting occurs, loop over all pasted rows, and for each pasted row compare it with rows from workSheet. You may do it directly by reading that on the fly (as mentioned above), or you may read all rows from workSheet and store them in a List and compare incoming rows against that list - it will work much faster that way.

Now, one of the most interesting things is probably what does it mean to "compare rows". Either you will need to compare all cells within a row with another one, or you will need to compare just a specific set of "columns" like "date, time, cause, origin, caseId" etc. But that.. noone knows, you said nothing about that. If there's no info on that, then you probably should compare whole rows and assume that any difference in any cell means that the rows are different.

paste rows if doesn't already exist in excel sheet using c#

1 Answers