C# EPPlus delete row from excel sheet

Question

I'm currently working with an Excel file that has leading rows that have information I don't need. These extra rows also mess with importing that data in the header row below. So I'm trying to remove them to work with the data.

using (var pack = new ExcelPackage(myFileInfo))
{
    // Should return the sheet name
    var ws = pack.Workbook.Worksheets.FirstOrDefault();
    // Should Delete rows 1-5 and shift up the rows after deletion
    ws.DeleteRow(1,5,true);
}

I was thinking something like the above would work, but I've not had much success with it.

The goal would be to delete rows 1-5, shift up the rest of the data (maybe a merge would work?) then convert it into a datatable.

Anyone have tips tips or resources on removing rows from my excel sheet (prior to moving it into a datatable since that is where the issue occurs)

Answer 1

The code as you have it will remove the first 5 rows but you also need to do something with the amended file. You could save it in place with:

pack.Save();

or save to a new location with:

pack.SaveAs(new FileInfo(outputFilePath));

I have uploaded a complete example here:

static void Main(string[] args)
{
    var myFileInfo = new FileInfo("Demo.xlsx");
    using (var pack = new ExcelPackage(myFileInfo))
    {
        var ws = pack.Workbook.Worksheets.FirstOrDefault();
        ws.DeleteRow(1, 5, true);
        pack.SaveAs(new FileInfo("output.xlsx"));
    }
}

If you build and run the solution you can see that it transforms the demo file from this in the input file (Demo.xlsx):

to this in the output file:

with the first 5 rows removed and everything shifted up.