I'm trying to remove a row in Excel with C#, but it doesn't work-CodePudding

I'm working on an project, where you can add contacts to an Excel file. Now I'm trying to remove the contacts from the Excel file. To remove the contact I need to delete the row where the data of the contact is, but the code I've tried doesn't delete only the specific row, it clears every row.

public static void deleterow(string path, int sheetnumber, int row)
{
    _Application excel = new Application();

    Workbook wb = excel.Workbooks.Open(path);

    Worksheet ws = wb.Worksheets[sheetnumber];

    ws.Rows.EntireRow.Delete(row);
            
    wb.Save();
    wb.Close();
}

CodePudding user response：

Try this one

_Application excel = new Application();
Workbook wb = excel.Workbooks.Open(path);

Worksheet ws = (Worksheet) wb.Worksheets[sheetnumber];

Range rg = (Range) ws.Rows[row];
rg.EntireRow.Delete();

wb.Save();
wb.Close();

CodePudding user response：

You can use Excel.Ranges and find needed one (or more) by provided value:

Excel.Application excel = new Excel.Application();
excel.Visible = true;
Excel.Workbook workbook = excel.Workbooks.Open(excelFile);
Excel.Worksheet worksheet = workbook.ActiveSheet;

List<Excel.Range> rangesToDelete = new List<Excel.Range>();

foreach (Excel.Range range in worksheet.UsedRange)
    if (range.Value2 is string value && value == "SomeContact") // <-- Your contact name or other identifier
        rangesToDelete.Add(range);

foreach (Excel.Range rangeToDelete in rangesToDelete)
    rangeToDelete.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);

For example, if I specify "Person2", I'll get this result (before deleting/after deleting):