How can I delete a row from an excel file using Open Office XML SDK

Question

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.

C#

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.
                return;
            }
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
            Row row=GetRow(worksheetPart,rowIndex);
            row.RemoveAllChildren();
            worksheetPart.Worksheet.Save();                
        }

    }</sheet></sheets></sheet>

Posted 10-Sep-15 0:18am

Anoob Aliyar

Updated 13-Sep-19 22:38pm

Michael_Davies

v2

Add a Solution

Comments

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;

http://codekea.com/53jeBMZL3gND/how-to-delete-a-row-in-excel-file-with-open-xml-sdk.html

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 !!!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

M.Schener · Answer 1 · 2016-09-25T21:32:00

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.

mhassanist · Answer 2 · 2019-09-13T22:39:00

This logic worked for me

<pre lang="c#">

row.Remove();

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);

}
}
rowsCount--;
worksheetPart.Worksheet.Save();

How can I delete a row from an excel file using Open Office XML SDK

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0