Click here to Skip to main content
15,790,052 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I have written the below code to delete a row from an excel spreadsheet using OOXML . I can able to remove the row data , however the row still existing as a blank row. Please help me to completely remove the row from the excel file.
public static void DeleteRowFromExcel(string docName, string sheetName, uint rowIndex)
        // Open the document for editing.
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
            IEnumerable<sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<sheets>().Elements<sheet>().Where(s => s.Name == sheetName);
            if (sheets.Count() == 0)
                // The specified worksheet does not exist.
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
            Row row=GetRow(worksheetPart,rowIndex);

Updated 13-Sep-19 23:38pm
Michael_Davies 10-Sep-15 11:11am    
Just a guess; try row.Remove(); without the AllChildren part.
Maciej Los 10-Sep-15 15:30pm    
My virtual 5!
Post it as an asnwer and i'll promise to upvote it!
Anoob Aliyar 11-Sep-15 1:53am    
Thanks Michael for your reply. I have tried row.Remove() , however the row still existing as a blank row.
Michael_Davies 11-Sep-15 2:36am    
Looking around there does not seemto be any definitive answer to this question;

The above does not help as it does not give and example just states the problem involved in removing a row.

The only other code example I can find uses row.Remove(), give it a whirl.
kingsa 7-Apr-16 5:30am    
Did you got solution for this ?? bcoz i am also struck in same area !!!

Sorry for my English, but I get result for this problem as next:
1) When I delete one row by row.Remove(), then for all the following rows I change RowIndex property to RowIndex.Value -= 1;
2) For all cells of all following rows I also change CellReference property by decrease it on -1. CellReference property stored as like Excel cell address.

For example, if I need delete fourth row in excel sheet, so for row.RowIndex.Value = 4 I call row.Remove(). For next row with row.RowIndex.Value = 5, I set RowIndex.Value to 4 and for all it cells change CellReference.Value like 'A5' -> 'A4', 'B5' -> 'B4' and so on.
Share this answer
This logic worked for me

<pre lang="c#">

for (uint j = i; j < rowsCount-1; j++)
rows.ToArray()[j].RowIndex.Value = rows.ToArray()[j].RowIndex.Value - 1;

foreach (Cell c in rows.ToArray()[j])
c.CellReference.Value = c.CellReference.Value.Substring(0, 1) +
(uint.Parse(c.CellReference.Value.Substring(1, c.CellReference.Value.Length - 1))-1);

Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900