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

    Pergunta

  • 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

    sexta-feira, 9 de março de 2012 12:10

Respostas

  • 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

    quinta-feira, 15 de março de 2012 06:34

Todas as Respostas

  • 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

    segunda-feira, 12 de março de 2012 07:33
  • Hi Vinay,

    Any Update?

    Have a good day,

    Tom


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

    quarta-feira, 14 de março de 2012 07:17
  • 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

    quinta-feira, 15 de março de 2012 06:34
  • Thanks Tom. it working perfectly fine now. :)

    Sorry for late reply. :)


    Vinay TC

    segunda-feira, 6 de agosto de 2012 04:41
  • 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

    quinta-feira, 28 de novembro de 2013 13:21