Issue with removing charts from the spreadsheet using Open XML SDK v2

Question

Hi,

I’m facing problem while removing the charts from the spreadsheet using OPN XML SDK v2.

Actual problem is not while saving the file after removing the chart part. But when I open the saved excel file it is telling me the file is not valid and is asking for recovering the file.

The problem seems to be with the drawing and the legacy drawing elements in the file. When I looked into the difference between the saved file and the file which was corrected by excel, the elements(Drawing, Legacy Drawing) have been removed.

I’m using below code for removing the charts from spreadhseet:

 using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
{
            WorkbookPart wbPart = document.WorkbookPart;
            var sheets = wbPart.GetPartsOfType<WorksheetPart>();

            foreach (var sheet in sheets)
            {
                sheet.DeletePart(sheet.DrawingsPart);
                sheet.DeleteParts<VmlDrawingPart>(sheet.VmlDrawingParts);

                //var drawingsParts = sheet.GetPartsOfType<DrawingsPart>();
                //if (drawingsParts != null)
                //{
                //    foreach (var dwgPart in drawingsParts)
                //    {
                //        dwgPart.DeleteParts<ChartPart>(dwgPart.ChartParts);

                //        //foreach (var chtPart in dwgPart.ChartParts)
                //        //{
                //        //    if (chtPart.ChartSpace != null)
                //        //    {
                //        //        chtPart.ChartSpace.RemoveAllChildren<Chart>();
                //        //        //Chart cht = chtPart.ChartSpace.GetFirstChild<Chart>();
                //        //        //cht.Remove();
                //        //    }
                //        //}
                //    }

                //    sheet.DeleteParts<DrawingsPart>(drawingsParts);
                //}


                //var vmlDrawingsParts = sheet.GetPartsOfType<VmlDrawingPart>();
                //if (vmlDrawingsParts != null)
                //{
                //    sheet.DeleteParts<VmlDrawingPart>(vmlDrawingsParts);
                //}

            }
}

Any help in this regard is highly appreciated.

Thanks,
Vinay TC

Answer 1

Hi Vinay,

Thanks for posting in the MSDN Forum.

To see whether http://blogs.msdn.com/b/brian_jones/archive/2009/08/03/the-open-xml-sdk-and-fluent-ui-extensibility.aspx  can help you.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Vinay,

Any Update?

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 3

Hi Vinay,

This code will work:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SpreadsheetDocument doc = SpreadsheetDocument.Open(@"****\Book1.xlsx", true))
            {
                try
                {
                    WorkbookPart wp = doc.WorkbookPart;
                    Sheet sheet = wp.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();
                    WorksheetPart wsp = wp.GetPartById(sheet.Id) as WorksheetPart;
                    IEnumerable<ChartPart> cp = wsp.DrawingsPart.ChartParts;
                    wsp.DeletePart(wsp.DrawingsPart);
                    wsp.Worksheet.Descendants<Drawing>().FirstOrDefault().Remove();
                    wsp.Worksheet.Save();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message+"\n"+ex.StackTrace);
                }
            }
            Console.WriteLine("Done");
            Console.ReadKey();
        }
        
    }
}

I hope it can help you.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Thanks Tom. it working perfectly fine now. :)

Sorry for late reply. :)

---

Vinay TC