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

  • 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

    Friday, March 9, 2012 12:10 PM

Answers

  • 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

    Thursday, March 15, 2012 6:34 AM
    Moderator

All replies

  • 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

    Monday, March 12, 2012 7:33 AM
    Moderator
  • Hi Vinay,

    Any Update?

    Have a good day,

    Tom


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

    Wednesday, March 14, 2012 7:17 AM
    Moderator
  • 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

    Thursday, March 15, 2012 6:34 AM
    Moderator
  • Thanks Tom. it working perfectly fine now. :)

    Sorry for late reply. :)


    Vinay TC

    Monday, August 6, 2012 4:41 AM
  • This code deletes the images from the sheet too. :( , and I would like to remove only the charts from the sheet.

    Any help in this is highly appreciated.

    Thanks, 

    Toni

    Thursday, November 28, 2013 1:21 PM