Ankush's Blog

where Technology wins

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();
        }
    }
}

About these ads

42 Responses to “Importing Charts from Spreadsheets to PowerPoint Presentation using Open XML SDK 2.0”

  1. Kit said

    thanks for the article. I have a question. Will the chart in the pptx be an image or a real chart? In the sense, Will I be able to edit the chart data by double clicking it?

    • Nixie said

      You will not. Lines like following ones should be added after ‘ChartPart importedChartPart = sld.AddPart(chartPart);’:

      EmbeddedPackagePart embPackage =
      importedChartPart.AddNewPart(
      “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”, “rId1″);
      embPackage.FeedData(new FileStream(“Microsoft_Office_Excel_Worksheet1.xlsx”, FileMode.Open, FileAccess.Read));

      Drawing.Charts.ExternalData ed = new DocumentFormat.OpenXml.Drawing.Charts.ExternalData();
      ed.Id = “rId1″;
      importedChartPart.ChartSpace.AppendChild(ed);

      These embed external worksheet (you may replace it with your own stream) and external data reference to it.

      • Kit said

        Yahoooooooooooooooooooooo! Thanks Nixie, It worked like a charm!

      • Kit said

        Hey Nixie, could you give an idea how to make this work if my spreadsheet has multiple worksheets and each of which has a chart on its own? How to specify the externalid? In some rare cases I might also have multiple charts on same sheet. How is it possible to relate chart to chart? thanks.

      • Manoj said

        Thanks Nixie for this helpful post

      • Thanks for this amazing article,
        the importedChartPart.ChartSpace.AppendChild(ed); add a binary file named package.bin
        when i right click on the chart and select edit data i get a readonly excel file.
        i can edit data in the table and see the edits on the chart but i can’t save the new data, how can i do this please ?

  2. Kit said

    I tried this code and it didnt work for me. the template slide is ‘damaged’ and powerpoint inserts a new slide :( I guess the problem is may be with my template file. could you explain what should the template.pptx have? I dont understand what is a share tree/shape tree??

  3. Sergey said

    Thanks for your article.
    I have a problem when I try to open pptx-file created using your example in PowerPoint 2003 with compatibility pack.
    Error says ‘There was an error accessing presentation.pptx’.
    Could you answer if you know how to fix this problem.
    Thanks.

  4. ImtiazQamar said

    Scenario:
    This works fine if the chart to be imported to powerpoint is creadted manually in the excel sheet. But, it doesn’t work if the chart is created dynamically.

    Problem:
    By following BrianJones blog, “Document Assembly Solution for SpreadsheetML”, a template excel file is used to generate a chart. When tried to import such dynamically created chart to Power point file using above code, only the chart which was initially present in the template file gets imported while missing the data values which were populated by coding.

    Code to generate the chart in excel file is mentioned below, any help shall be highly appreciated.

    void GenerateWorkbookFromDB()
    {
    //Make a copy of the template file
    File.Copy(“TopLineShipReport.xlsx”, “TopLineGenerated.xlsx”, true);

    //Open up the copied template workbook
    using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(“TopLineGenerated.xlsx”, true))
    {
    //Access the main Workbook part, which contains all references
    WorkbookPart workbookPart = myWorkbook.WorkbookPart;
    //Grab the first worksheet

    //WorksheetPart worksheetPart = GetWorksheetPartByName(myWorkbook, “TopLine”);
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
    //SheetData will contain all the data
    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();

    // Create a style sheet
    Stylesheet styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;

    //Connect to database
    TopLineShipReportDataContext db = new TopLineShipReportDataContext();

    //My data starts at row 8
    int index = 8;

    // getting values out from the table
    var territoryQuery =
    from t in db.TopLineShipDatas
    select t;

    //For each row in my database add a row to my spreadsheeet
    foreach (var item in territoryQuery)
    {

    string territoryName = item.Region;
    Int32 salesThisYear = Convert.ToInt32(item.DollarsTY);
    dollarsTYTotal += salesThisYear;

    Int32 salesLastYear = Convert.ToInt32(item.DollarsLY);
    dollarsLYTotal += salesLastYear;

    string salesDollarChg = item.DollarChng != null ? item.DollarChng : ” “;
    string salesDollarChgPercent = item.DollarChngPercnt != null ? item.DollarChngPercnt : ” “;

    // Add style for negative values

    //_negativeNumber = createCellFormat(styleSheet, null, 3, null);

    //Add a new row
    Row contentRow = CreateContentRow(index, territoryName, salesThisYear, salesLastYear, salesDollarChg, salesDollarChgPercent);
    index++;

    //Append new row to sheet data
    sheetData.AppendChild(contentRow);

    }

    worksheetPart.Worksheet.Save();
    }

    Cell CreateTextCell(string header, string text, int index)
    {
    //Create new inline string cell
    Cell c = new Cell();
    c.DataType = CellValues.InlineString;
    c.CellReference = header + index;

    //Add text to text cell
    InlineString inlineString = new InlineString();
    Text t = new Text();
    t.Text = text;

    inlineString.AppendChild(t);
    c.AppendChild(inlineString);

    return c;
    }

    Row CreateContentRow(int index, string territory, int salesThisYear, int salesLastYear, string salesDollarChg, string salesDollarChgPercent)
    {
    //Create new row
    Row r = new Row();
    r.RowIndex = (UInt32)index;

    //First cell is a text cell, so create it and append it
    Cell firstCell = CreateTextCell(headerColumns[0], territory, index);
    r.AppendChild(firstCell);

    //Create cells that contain data
    for (int i = 1; i < 5; i++)
    {
    Cell c = new Cell();
    c.CellReference = headerColumns[i] + index;

    CellValue v = new CellValue();

    if (i == 1)
    v.Text = salesThisYear.ToString();
    else if (i == 2)
    v.Text = salesLastYear.ToString() ;
    else if (i == 3)
    v.Text = salesDollarChg.ToString() == null ? " " : salesDollarChg.ToString();
    else
    v.Text = salesDollarChgPercent.ToString() == null ? " " : salesDollarChgPercent.ToString();

    c.AppendChild(v);
    r.AppendChild(c);
    }
    return r;
    }

  5. ImtiazQamar said

    Solution having related code files along with other artifacts involved have been e-mailed to your e-mail address. Your help shall be highly appreciated.

  6. ImtiazQamar said

    There are two steps involved:

    Step1 ) a template excel file is used to generate another excel file having a chart (code used from Brian Jones blog)
    Step2 ) generated excel file having chart in the above step is being imported to Powerpoint by using Ankush’s code.

    Interesting finding is that chart generated through code in first step doesn’t seem to get saved as part of the Excel file because when the same excel file (having chart to be imported) is manually saved (Ctrl+S) before executing step 2 (Ankush’s code), it gets imported to powerpoint successfully.

    Could you please help, how to overcome this situation. Thanks, in advance.

  7. Website said

    Website…

    Importing Charts from Spreadsheets to PowerPoint Presentation using Open XML SDK 2.0 « Ankush's Blog…

  8. shak said

    Thanks for the artical. It was really helpful..

  9. Matt Balraj said

    Dear Ankush

    I know this is a very old thread. I have a problem and would appreciate your help

    I have a Excel file that contains a Chart (Bar diagram). I also have a powerpoint presentation that contains two slides. Each slide already contains a chart. There is no title for these charts.

    I would like to update the Chart in Slide #2 with the chart in my Excel file.

    Your help will be greatly appreciated

  10. pallavi said

    thx ankush ….
    my excel sheet show updated data but not ppt …..
    if i save manually the excel sheet then my ppt show the correct data….plz tel me wht shld i do so tht my excel sheet get save atomatically

    • Ankush said

      for that, you need to look into chartdata.xml file..That is the file you need to update. I did this earlier for one of my customer..I can get you that code but not today. I will send you the code tomorrow.

      Thanks
      Ankush

  11. pallavi said

    thx a lot ankush..

    • Ankush said

      Hi Pallavi,

      As I mentioned to you earlier, you need to work with chart1.xml file. Basically when you use open xml sdk to copy the chart, it doesnt update display in ppt. But when you double click it, it works. I wrote this code for one of my users:

      Imports DocumentFormat.OpenXml.Packaging
      Imports DocumentFormat.OpenXml.Packaging.SpreadsheetDocument
      Imports DocumentFormat.OpenXml.Presentation
      Imports DocumentFormat.OpenXml.Spreadsheet
      Imports System.IO
      Imports DocumentFormat.OpenXml
      Imports C = DocumentFormat.OpenXml.Drawing.Charts
      Imports System.Text.RegularExpressions

      Public Class Form1

      ‘ Input Parameters. These varibales tells in whcih row and what data to insert
      Dim intRow As Integer = 14
      Dim strAdd As String = “Test3″
      Dim strVal As Double = “12.23″

      ‘ Variable to hold Columns data
      Dim ColumnA As New List(Of String)
      Dim ColumnB As New List(Of String)

      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

      Dim presentationDocument As PresentationDocument = presentationDocument.Open(“C:\Users\abhatia\Desktop\2.pptx”, True)

      Using (presentationDocument)

      If (presentationDocument Is Nothing) Then
      Throw New ArgumentNullException(“presentationDocument”)
      End If

      Dim presentationPart As PresentationPart = presentationDocument.PresentationPart

      ‘ Verify that the presentation is not empty.
      If (presentationPart Is Nothing) Then
      Throw New InvalidOperationException(“The presentation document is empty.”)
      End If

      ‘ Get the first slide in the target presentation.
      Dim slide As SlidePart = GetFirstSlide(presentationDocument)
      Dim strem As Stream = slide.ChartParts.First().EmbeddedPackagePart().GetStream()
      ModifyExcel(strem, strAdd, strVal)
      ‘ ModifyExcel(strem, 12)

      Dim chartPart As ChartPart = slide.ChartParts.First()
      ModifyChart(chartPart, strAdd, strVal)
      presentationPart.Presentation.Save()
      End Using

      End Sub
      ‘ This function modifies Chart1.XML
      Public Sub ModifyChart(ByRef chartpart As ChartPart, ByVal Text As String, ByVal Value As Double)
      Dim chartSpace As C.ChartSpace = chartpart.ChartSpace
      Dim barChart As C.BarChart = chartSpace.Descendants(Of C.BarChart).FirstOrDefault
      Dim barChartSeries As C.BarChartSeries = barChart.Descendants(Of C.BarChartSeries).FirstOrDefault()
      Dim categoryAxisData As C.CategoryAxisData = barChartSeries.Descendants(Of C.CategoryAxisData).FirstOrDefault()
      Dim values As C.Values = barChartSeries.Descendants(Of C.Values).FirstOrDefault()
      Dim errorBars As C.ErrorBars = barChartSeries.Descendants(Of C.ErrorBars).FirstOrDefault()
      Dim stringReference As C.StringReference = categoryAxisData.Descendants(Of C.StringReference).FirstOrDefault()
      Dim strFormula1, strFormula2 As String

      strFormula1 = “Sheet1!$A$2:$A$”
      strFormula2 = “Sheet1!$B$2:$B$”

      ‘ Step 1 Modify the formula so that it accomndate new Values we have inserted
      Dim stringCache As C.StringCache = stringReference.StringCache

      Dim formula As C.Formula = New C.Formula()
      formula.Text = strFormula1 + (ColumnA.Count + 1).ToString()
      stringReference.Formula = formula

      ‘ Step 2 Delete the Older String cache Values
      For i As Integer = stringCache.ChildElements.Count – 1 To 0 Step -1
      stringCache.ChildElements(i).Remove()
      Next

      ‘ Step 3 Create the String Ref object and append them into Chart1.XML
      For i = 0 To ColumnA.Count – 1
      Dim stringPoint As C.StringPoint = New C.StringPoint
      Dim uintVal As UInt32Value = New UInt32Value
      uintVal.Value = i
      stringPoint.Index = uintVal
      Dim numericValue As C.NumericValue = New C.NumericValue()
      numericValue.Text = ColumnA(i)
      stringPoint.Append(numericValue)
      stringCache.Append(stringPoint)
      Next

      ‘ Step 4 Modify the number Reference
      Dim numberReference As C.NumberReference = values.Descendants(Of C.NumberReference).FirstOrDefault()

      Dim formula1 As C.Formula = New C.Formula()
      formula1.Text = strFormula2 + (ColumnA.Count + 1).ToString()
      numberReference.Formula = formula1

      Dim numberingCache As C.NumberingCache = numberReference.NumberingCache

      For i = numberingCache.ChildElements.Count – 1 To 0 Step -1
      numberingCache.ChildElements(i).Remove()
      Next

      For i = 0 To ColumnB.Count – 1
      Dim stringPoint1 As C.StringPoint = New C.StringPoint
      Dim uintVal1 As UInt32Value = New UInt32Value
      uintVal1.Value = i
      stringPoint1.Index = uintVal1
      Dim numericValue1 As C.NumericValue = New C.NumericValue()
      numericValue1.Text = ColumnB(i)
      stringPoint1.Append(numericValue1)
      numberingCache.Append(stringPoint1)
      Next
      chartpart.ChartSpace.Save()

      End Sub

      Public Function GetColumnName(ByVal cellName As String) As String
      Dim regex As Regex = New Regex(“[A-Za-z]+”)
      Dim match As Match = regex.Match(cellName)
      Return match.Value
      End Function

      Public Sub IterateRowsAndCells(ByVal doc As SpreadsheetDocument, ByVal sheetName As String, ByVal columnName As String)

      Dim sheets As IEnumerable(Of Sheet) = doc.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)().Where(Function(s) s.Name = sheetName.ToString())
      If (sheets.Count = 0) Then
      ‘ The specified worksheet does not exist.
      Return
      End If

      Dim relationshipId As String = sheets.First.Id.Value
      Dim requiredSheetPart As WorksheetPart = CType(doc.WorkbookPart.GetPartById(relationshipId), WorksheetPart)

      Dim shareStringPart As SharedStringTablePart = doc.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
      Dim SharedStringItem() As SharedStringItem = shareStringPart.SharedStringTable.Elements(Of SharedStringItem)().ToArray()

      Dim headerColumnName As String = GetColumnName(columnName)
      Dim cells As IEnumerable(Of Cell) = requiredSheetPart.Worksheet.Descendants(Of Cell)().Where(Function(c) String.Compare(GetColumnName(c.CellReference.Value), headerColumnName, True) = 0)

      Dim headCell As Cell = cells.First()

      For Each c1 In cells
      If c1.Equals(headCell) Then

      Else
      If ((Not (c1.DataType) Is Nothing) AndAlso (c1.DataType.Value = CellValues.SharedString)) Then
      ColumnA.Add(SharedStringItem(Integer.Parse(c1.CellValue.Text)).InnerText)
      Debug.WriteLine(SharedStringItem(Integer.Parse(c1.CellValue.Text)).InnerText)

      ElseIf (c1.CellValue Is Nothing) Then
      If headerColumnName = “A” Then
      ColumnA.Add(“”)
      Else
      ColumnB.Add(“”)
      End If

      Debug.WriteLine(“”)
      Else
      ColumnB.Add(c1.CellValue.Text)
      Debug.WriteLine(c1.CellValue.Text)
      End If
      End If
      Next
      End Sub

      ‘ This function takes the Excel stream and modify it
      Public Sub ModifyExcel(ByRef stream As Stream, ByVal Text As String, ByVal Value As Double)

      Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(stream, True)

      Using (spreadSheet)
      ‘ Get the SharedStringTablePart. If it does not exist, create a new one.
      Dim shareStringPart As SharedStringTablePart

      If (spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).Count() > 0) Then
      shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First()
      Else
      shareStringPart = spreadSheet.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
      End If

      ‘ Insert the text into the SharedStringTablePart.
      Dim index As Integer = InsertSharedStringItem(Text, shareStringPart)

      ‘ Insert a new worksheet.
      ‘Dim worksheetPart As WorksheetPart = InsertWorksheet(spreadSheet.WorkbookPart)
      Dim sheets As IEnumerable(Of Sheet) = spreadSheet.WorkbookPart.Workbook.Descendants(Of Sheet)()
      If (sheets.Count() = 0) Then
      ‘ The specified worksheet does not exist.
      Return
      End If

      Dim worksheetPart As WorksheetPart = CType(spreadSheet.WorkbookPart.GetPartById(sheets.First().Id), WorksheetPart)
      Dim worksheet As Worksheet = worksheetPart.Worksheet

      ‘ WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);

      ‘ Insert cell A1 into the new worksheet.
      Dim cell As Cell = InsertCellInWorksheet(“A”, intRow, worksheetPart)

      ‘ Set the value of cell A1.
      cell.CellValue = New CellValue(index.ToString)
      cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)

      Dim cell1 As Cell = InsertCellInWorksheet(“B”, intRow, worksheetPart)

      ‘ Set the value of cell A1.
      cell1.CellValue = New CellValue(Value.ToString)
      cell1.DataType = New EnumValue(Of CellValues)(CellValues.Number)

      ‘Ankush Table Test
      Dim tables As TableDefinitionPart = worksheetPart.TableDefinitionParts.FirstOrDefault()

      Dim t As Table = tables.Table

      Dim strTemp As String = “A1:B”

      ‘ Save the new worksheet.
      worksheetPart.Worksheet.Save()
      IterateRowsAndCells(spreadSheet, “Sheet1″, “A1″)
      IterateRowsAndCells(spreadSheet, “Sheet1″, “B1″)
      t.Reference.Value = strTemp + (ColumnA.Count + 1).ToString()
      worksheetPart.Worksheet.Save()
      End Using

      End Sub

      ‘ Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text
      ‘ and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
      Private Function InsertSharedStringItem(ByVal text As String, ByVal shareStringPart As SharedStringTablePart) As Integer
      ‘ If the part does not contain a SharedStringTable, create one.
      If (shareStringPart.SharedStringTable Is Nothing) Then
      shareStringPart.SharedStringTable = New SharedStringTable
      End If

      Dim i As Integer = 0

      ‘ Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
      For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
      If (item.InnerText = text) Then
      Return i
      End If
      i = (i + 1)
      Next

      ‘ The text does not exist in the part. Create the SharedStringItem and return its index.
      shareStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text)))
      shareStringPart.SharedStringTable.Save()

      Return i
      End Function

      ‘ Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet.
      ‘ If the cell already exists, return it.
      Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart) As Cell
      Dim worksheet As Worksheet = worksheetPart.Worksheet
      Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
      Dim cellReference As String = (columnName + rowIndex.ToString())

      ‘ If the worksheet does not contain a row with the specified row index, insert one.
      Dim row As Row
      If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() 0) Then
      row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
      Else
      row = New Row()
      row.RowIndex = rowIndex
      sheetData.Append(row)
      End If

      ‘ If there is not a cell with the specified column name, insert one.
      If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
      Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
      Else
      ‘ Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
      Dim refCell As Cell = Nothing
      For Each cell As Cell In row.Elements(Of Cell)()
      If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
      refCell = cell
      Exit For
      End If
      Next

      Dim newCell As Cell = New Cell
      newCell.CellReference = cellReference

      row.InsertBefore(newCell, refCell)
      worksheet.Save()

      Return newCell
      End If
      End Function


      ‘ Get the slide part of the first slide in the presentation document.

      ‘ The presentation document.
      ‘ The SlidePart object of the first slide.
      Public Function GetFirstSlide(ByVal presentationDoc As PresentationDocument) As SlidePart

      ‘ Get relationship ID of the first slide.
      Dim part As PresentationPart = presentationDoc.PresentationPart
      Dim slideId As SlideId = part.Presentation.SlideIdList.GetFirstChild(Of SlideId)()
      Dim relId As String = slideId.RelationshipId

      ‘ Get the slide part by the relationship ID.
      Dim slidePart As SlidePart = CType(part.GetPartById(relId), SlidePart)

      Return slidePart

      End Function

      End Class

  12. rahul said

    sir i am also facing the same problem and i tried your code but it dont not work for me
    i already send u the code plz tel exactly where i am doing wrong.

  13. pallavi said

    hi Ankush ,
    even i send you my code .
    if you have time plz go through it and tell me where is the bug.

    Thanks

    • Ankush said

      I will setup your code and will let you know.

      Thanks
      Ankush

      • Ankush said

        Hi Pallavi,

        Please let me know what doesn’t work in the code?

        What happens when you run the code? Is the Chart1.xml file modified with the new data?

        Please send me the complete application and the Excel file and PPT file with which you are running the code.

        Thanks
        Ankush

  14. pallavi said

    hi ankush ,
    nope chart1.xml not going to modify by new data
    n infact i am even not getting how to give strref and numref as i have wrote different fun for modify excel

    Thanks

    • Ankush said

      Hi,

      I requested you to send me the complete code with the Excel and PPT file so that I can setup them and have a look.

      Ankush

  15. pallavi said

    i had sent you the complete code plz chk it.

    Thanks
    pallavi

  16. Ankit said

    Hi,

    Thanks so much and this works like a charm. I am trying to the same thing with excel books with about 1000 large charts in it across a large number of sheets.
    Can you please tell me how to loop through all the charts in all the sheets and put those in the powerpoint(lets say 4 charts on 1 slide). And code to create new slides as required.

    Thanks
    Ankit

  17. rahul said

    is there is any other way 2 set cell color directly than write createStyleSheet method
    plz reply asap

  18. rahul said

    sir plz check my code….n do reply

  19. rahima said

    how to update formula field value in open xml sdk 2.0

  20. Priya said

    Hi Ankush I have the same req for powerpoint. I need to generate the openxml format for the datatable
    Ex: in






    {5C22544A-7EE6-4342-B048-85BDC9FD1C3A}









    kind of the above format

  21. Priya said






    {5C22544A-7EE6-4342-B048-85BDC9FD1C3A}









  22. Priya said

    <p:cNvPr id=4;name="XYZ"

  23. om said

    hello ankush,
    i have currently my project working fine but when it comes to large excel file some performance issue get raises for d solution i follow dis link http://blogs.msdn.com/b/brian_jones/archive/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk.aspx but i cant understand wht shld i do if my every cel data is different

  24. Priya said

    HI Ankush,

    In our requirement we are generating the presentation only based on the xml format generated. It worked for tables but the problem is with charts. From some of the searches i got the chart cache data structure and have written the code to generate it,but the chart is not generated. the format is as below

    <!––>



    when i execute my code i get the xml similar to this including data but the chart is not generated.
    Please tell me where i am going wrong.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: