Lets Learn

Opinion Matters

Archive for February, 2010

Importing Charts from Spreadsheets to PowerPoint Presentation using Open XML SDK 2.0

Posted by Ankush on February 24, 2010

Recently I worked on a scenario where I had to import a chart from Excel to PowerPoint. Consider this, you have a worksheet which contains a chart. You modify the chart data and chart gets updated. Now you would like to import the updated chart into another documents.  

A very nice blog from Brian already explain how you can import chart from Excel to Word. This blog post is all about how you can import a chart from a spreadsheet to a PowerPoint presentation.  

Following are the actions required in order to accomplish the above scenario.  

  • Create a template in Excel that contains a sample chart.
  • Create an application which uses Open XML SDK and modify the Excel cell data. This is accomplished in this blog.
  • Create a PowerPoint file in which you would like to insert the chart.
  • Open up the PowerPoint file via the Open XML SDK and access its slides where we want to insert the chart.
  • Locate the ShapeTree.
  • Open up the spreadsheet via the Open XML SDK and access all the appropriate parts (main workbook part, worksheet part, drawing part, and chart part)
  • Clone the chart part and add it to the Slide.
  • Clone the chart graphic information (name of the chart and properties) from the spreadsheet and add it to the ShapeTree.
  • Give the added chart a unique name and id in the Word document.
  • Save changes made to the PowerPoint

For the sake of this post, let’s say I am starting with a spreadsheet which contains a chart.Also, let’s say I am starting with a blank PowerPoint document, which contains a ShareTree:   

The Code   

static void ImportChartFromSpreadsheetToPPT(string spreadsheetFileName, string PPTFileName)
{
    //Open PowerPoint Presentation
    using (PresentationDocument myPresDoc = PresentationDocument.Open(PPTFileName, true))
    {
        PresentationPart pptPart = myPresDoc.PresentationPart;
        var sld = pptPart.SlideParts.Last();

        // Get the p:cSld element.Shape tree of a slide (spTree) is a child element of cSld
        // because all slide types may contain a shape tree. Hence we get the CSld and get the ShapeTree.
        CommonSlideData comSlddata = sld.Slide.CommonSlideData;
        //p:spTree Element
        ShapeTree shapeTree = comSlddata.ShapeTree;
        //This element specifies the existence of a graphics frame.                
        // p:graphicFrame Element
        GraphicFrame graphicFrame = new GraphicFrame();

        //This element specifies all non-visual properties for a graphic frame
        //p:nvGraphicFramePr element
        NonVisualGraphicFrameProperties nonVisualGraphicFrameProperties = new NonVisualGraphicFrameProperties();

        //This element specifies non-visual canvas properties. Currently I have hardcoded the id and the name.
        // Feel free to choose a unqiue id and the name.
        // p:cNvPr Element
        NonVisualDrawingProperties nonVisualDrawingProperties = new NonVisualDrawingProperties() { Id = (UInt32Value)4U, Name = “Chart 3” };

        //This element specifies the non-visual drawing properties for a graphic frame. These non-visual properties are properties that the
        //generating application would utilize when rendering the slide surface.
        //p:cNvGraphicFramePr
        NonVisualGraphicFrameDrawingProperties nonVisualGraphicFrameDrawingProperties = new NonVisualGraphicFrameDrawingProperties();

        //p:nvPr Element
        ApplicationNonVisualDrawingProperties applicationnonVisualDrawingProperties = new ApplicationNonVisualDrawingProperties();

        nonVisualGraphicFrameProperties.Append(nonVisualDrawingProperties);
        nonVisualGraphicFrameProperties.Append(nonVisualGraphicFrameDrawingProperties);
        nonVisualGraphicFrameProperties.Append(applicationnonVisualDrawingProperties);

        //This element specifies the transform to be applied to the corresponding graphic frame. (2D Transform for Graphic Frame)
        //p:xfrm Element
        Transform transform = new Transform();

        //Notice that I have hardcoded the dimensions of the chart I will be importing.
        //Feel free to choose any dimension that works best for your document
        //a:off Element.Specifies the location of the bounding box of an object
        Drawing.Offset offset = new Drawing.Offset() { X = 2286000L, Y = 2057400L };
        //a:ext Element. Specifies the size of the bounding box enclosing the referenced object
        Drawing.Extents extents = new Drawing.Extents() { Cx = 4572000L, Cy = 2743200L };

        // Add position element to transform element.
        transform.Append(offset);
        transform.Append(extents);

        // Add Transform and the non-visual properties to GraphicFrame element.
        graphicFrame.Append(nonVisualGraphicFrameProperties);
        graphicFrame.Append(transform);

        // Open SpreadSheet
        using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(spreadsheetFileName, true))
        {
            //Get all the appropriate parts
            WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;
            WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(“rId1”);
            DrawingsPart drawingPart = worksheetPart.DrawingsPart;
            ChartPart chartPart = (ChartPart)drawingPart.GetPartById(“rId1”);

            //Add a Chart Part to the Slide and get the relationship
            ChartPart importedChartPart = sld.AddPart<ChartPart>(chartPart);
            string relId = sld.GetIdOfPart(importedChartPart);

            //This element describes a single graphical object frame for a spreadsheet which contains a graphical object.
            DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame frame = drawingPart.WorksheetDrawing.Descendants<DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame>().First();
            string chartName = frame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name;
           
            //Clone this node so we can add it to my slide
            Drawing.Graphic clonedGraphic = (Drawing.Graphic)frame.Graphic.CloneNode(true);
            Drawing.Charts.ChartReference c = clonedGraphic.GraphicData.GetFirstChild<Drawing.Charts.ChartReference>();
            c.Id = relId;

            //Add it
            graphicFrame.Append(clonedGraphic);
            shapeTree.Append(graphicFrame);
            myPresDoc.Close();
        }
    }
}

Advertisements

Posted in Open XML SDK | 43 Comments »

How to Delete a Worksheet from Excel using Open XML SDK 2.0

Posted by Ankush on February 11, 2010

Recently I worked on a scenario where a user wanted to delete a worksheet from a workbook using Open XML SDK 2.0. The worksheet may  contains some formulas, defined names, pivot tables etc…  Though MSDN provides a code snippet which explains how to delete a worksheet, it doesn’t cover the scenarios when we have formula, defined names, pivot tables etc. If you delete the worksheet following the MSDN article, Excel may not open the workbook and would throw an error (Error info goes here).

This blog post demonstrates how to delete a worksheet so that the Workbook opens without errors.

A worksheet in a workbook, apart from its part, also contains other dependent parts, entries inside the workbook. We need to delete the dependent/linked parts and the dependencies along with deleting the worksheet part to be able to completely/correctly delete the worksheet.

Here is the sample code which deletes the formula, defined names, pivot tables, CalculationChain associated with the worksheet being deleted. This probably is not covering all sorts of dependencies that a sheet can have, so you may still see errors even after using this code. In such a case, I encourage you to open the workbook in Visual Studio 2008 (using the cool Visual Studio 2008 power tools plugin) and look for any other traces of the worksheet that may be left over. If you do find something that is currently not covered by the code below, please leave me a comment on this post and I will try to incorporate that.

private void DeleteAWorkSheet(string fileName, string sheetToDelete)
{
     string Sheetid="";
    //Open the workbook
     using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
    {
        WorkbookPart wbPart = document.WorkbookPart;

         // Get the pivot Table Parts
         IEnumerable<PivotTableCacheDefinitionPart>  pvtTableCacheParts =wbPart.PivotTableCacheDefinitionParts;
         Dictionary<PivotTableCacheDefinitionPart,string > pvtTableCacheDefinationPart = new Dictionary<PivotTableCacheDefinitionPart,string>();
         foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
         {
             PivotCacheDefinition pvtCacheDef= Item.PivotCacheDefinition;
             //Check if this CacheSource is linked to SheetToDelete
             var pvtCahce=pvtCacheDef.Descendants<CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToDelete);
             if (pvtCahce.Count() > 0)
             {
                 pvtTableCacheDefinationPart.Add(Item, Item.ToString());
             }
         }
         foreach (var Item in pvtTableCacheDefinationPart)
         {
             wbPart.DeletePart(Item.Key);
         }
         //Get the SheetToDelete from workbook.xml
        Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetToDelete).FirstOrDefault();
        if (theSheet == null)
        {
          // The specified sheet doesn't exist.
        }
         //Store the SheetID for the reference
        Sheetid = theSheet.SheetId;
         
        // Remove the sheet reference from the workbook.
        WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
        theSheet.Remove();

        // Delete the worksheet part.
        wbPart.DeletePart(worksheetPart);

         //Get the DefinedNames
        var definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault();
        if (definedNames != null)
        {
            foreach (DefinedName Item in definedNames)
            {
                // This condition checks to delete only those names which are part of Sheet in question
                if (Item.Text.Contains(sheetToDelete + "!"))
                Item.Remove();
            }
        }
        // Get the CalculationChainPart 
        //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the 
         //workbook whose value is calculated from any formula

         CalculationChainPart calChainPart;
         calChainPart = wbPart.CalculationChainPart;
         if (calChainPart != null)
         {
             var calChainEntries = calChainPart.CalculationChain.Descendants<CalculationCell>().Where(c => c.SheetId == Sheetid);
             foreach (CalculationCell Item in calChainEntries)
             {
                 Item.Remove();
             }
             if (calChainPart.CalculationChain.Count() == 0)
             {
                 wbPart.DeletePart(calChainPart);
             }
         }
            
        // Save the workbook.
        wbPart.Workbook.Save();
    }
}

Posted in Open XML SDK | 3 Comments »

 
%d bloggers like this: