0

I am working with C# Desktop Application. I am trying to delete header rows that appears multiple times in the excel sheet. The method which i have found i working and removing the rows from the sheet but they are taking too much time. the records in sheets are around 6k to 8k each. Below are the functions:

public void DeleteRow_GameDetailSheet(Excel.Worksheet clsWorksheet, string rowStartingWithString)
    {
        int rowIndex = 5;
        Excel.Range range = clsWorksheet.UsedRange;
        if (rowStartingWithString.Length > 0)
        {
            while (rowIndex >= 5)                                     
            {
                if (rowIndex >= 5)
                {
                    if(rowIndex==5)
                    {
                        ((Excel.Range)clsWorksheet.Rows[4, Type.Missing]).Delete();
                        ((Excel.Range)clsWorksheet.Rows[3, Type.Missing]).Delete();
                        ((Excel.Range)clsWorksheet.Rows[2, Type.Missing]).Delete();
                        ((Excel.Range)clsWorksheet.Rows[1, Type.Missing]).Delete();
                    }
                    else
                    {
                        ((Excel.Range)clsWorksheet.Rows[rowIndex, Type.Missing]).Delete();
                    }
                    if (rowIndex>5 && rowStartingWithString == "Club ID")
                    {
                        for (int i = 0; i < 8; i++)
                        {
                            rowIndex = rowIndex - 1;
                            ((Excel.Range)clsWorksheet.Rows[rowIndex, Type.Missing]).Delete();
                        }
                    }

                };
                rowIndex = FindRow(range, rowStartingWithString);
            }
        }
    }

  public int FindRow(Excel.Range range, String marker)
    {
        int row = -1;
        try
        {
           // row = range.Find(marker).Row;
            row = (int)(range.Find(marker)?.Row);
            return row;
        }
        catch (Exception ex)
        {
            row = -1;
            return row;
        }
    }

Kindly someone suggest me what am doing wrong that it's taking too much time. it normally took around 15 to 25 minute to complete the process. Any help would be highly appreciated. Thanks

Naina Blue
  • 11
  • 2
  • Maybe create the `Range` with exactly the rows you want, then delete the range – Charlieface Jan 07 '21 at 13:36
  • @Charlieface can you please guide me how i can set the range to only first column of the sheet. as i can get my required rows by searching only the first column of every row.. – Naina Blue Jan 07 '21 at 13:46
  • 1
    Instead of this whole `for (int i = 0; i < 8; i++)` loop, save the earlier `Range` object then `Range.MoveEnd(8)` or whatever, then `Range.Delete` – Charlieface Jan 07 '21 at 13:52

1 Answers1

0

I don't see anything wrong by looking at it. BUT just know that Office interop is extremely slow. don't use it for processing large data. don't use it at all

Use ClosedXML for this. see asnswer

How to use Closed XML site

string fileName = "C:\\Folder1\\Prev.xlsx";
 var workbook = new XLWorkbook(fileName);
 var ws1 = workbook.Worksheet(1);

i used Interop to process a file with 18klines and 150 columns. it took nearly 4 hours on my laptop. but with ClosedXML it took less than 5 minutes. so it technically should speed up your method.

janithcooray
  • 188
  • 2
  • 15