CODEWITHSUNDEEP
c#excel
er ser
er ser

Reputation: 11

delete from multiple worksheet in Excel

I like to delete rows from multiple worksheet in Excel. Currently my code only delete rows from active worksheet. I am looking for a specific value on the cell then when I find this value then I am deleting all the rows up to that value that stored on that row.

Code

private void button2_Click(object sender, EventArgs e)
    {

        Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
        Workbook workBook = Excel.Workbooks.Open(FilePath);
        Worksheet ws = (Worksheet)Excel.ActiveSheet;


         for (int j = 1; j <= 10; j++)
        {                
            for (int i = 1; i <= 20; i++)
            {                        
                if (Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, i]).Value2) == "Matter")                   
                {
                    for (int r = 1; r <= j; r++)
                    {
                        ((Range)ws.Rows[r]).Delete(XlDeleteShiftDirection.xlShiftUp);
                        MessageBox.Show(Convert.ToString(r));
                        workBook.SaveAs(@"C:\Users\Separate\New.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                                        false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    }
                }

            }
        }             
         workBook.Close(Type.Missing, Type.Missing, Type.Missing);
         Excel.Quit();
         System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Excel);
         Excel = null;

    }

Upvotes: 1

Views: 300

Answers (1)

Gordon Allocman
Gordon Allocman

Reputation: 778

I would highly suggest trying to use ClosedXML as it makes working with Excel so much easier. Specifically with ClosedXML you can have a line like this:

ws.Row(5).Delete();

where ws is your initialized worksheet, and it handles the heavy lifting for deleting that row. Getting or setting cell data is just as simple:

 ws.Cell(2, 2).Value = "Initial Value";

or alternatively

ws.Cell("B2").Value = "Value;

They also have type specific value assignment like this:

ws.Cell("B2").SetValue<T>(object);

The documentation is very thorough, and you can get the package through nuget (note it requires the DocumentFormat.OpenXML package installed as well)

EDIT: I missed the part about multiple worksheets so here it is. The XLWorkbook type has a property Worksheets, which is an enumerable of all the worksheets in the workbook. Use this to get the worksheets you want to delete rows on

Upvotes: 1

Related Questions