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

    질문

  • 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

    2012년 3월 9일 금요일 오후 12:10

답변

  • 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

    2012년 3월 15일 목요일 오전 6:34
    중재자

모든 응답

  • 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

    2012년 3월 12일 월요일 오전 7:33
    중재자
  • Hi Vinay,

    Any Update?

    Have a good day,

    Tom


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

    2012년 3월 14일 수요일 오전 7: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

    2012년 3월 15일 목요일 오전 6:34
    중재자
  • Thanks Tom. it working perfectly fine now. :)

    Sorry for late reply. :)


    Vinay TC

    2012년 8월 6일 월요일 오전 4: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

    2013년 11월 28일 목요일 오후 1:21