none
OpenXML Version 1 - Excel - How To Get Custom Document Property RRS feed

  • Question

  • Hi everyone, does anyone know how to get a custom document property from an Excel Workbook using OpenXML Version 1?

    I am almost able to do it with OpenXML Version 2 using the following code. The properties I need are contained in the CustomFilePropertiesPart.Properties but I am not sure exactly how to extract them using the property name.

    I have also found out that I am required to use OpenXML Version 1 which makes it more difficult.

    Imports System.IO
    Imports System.Linq
    Imports DocumentFormat.OpenXml.Packaging
    
    Private Const XMLNS_CP As String = "http://schemas.openxmlformats.org/officeDocument /2006/custom-properties"
    
    Public Shared Function GetCDP(ByVal workbookFileName As String, ByVal customDocumentPropertyName As String) As Object
            Try
                Using excelDocument As SpreadsheetDocument = SpreadsheetDocument.Open(workbookFileName, False)
                    Dim customFilePropertiesPart As CustomFilePropertiesPart = excelDocument.CustomFilePropertiesPart
                    Dim e As DocumentFormat.OpenXml.OpenXmlElement = customFilePropertiesPart.Properties.Descendants().FirstOrDefault(Function(xe) xe.GetAttribute("name", XMLNS_CP.NamespaceName).Value = customDocumentPropertyName)
                End Using
                Return Nothing
            Catch ex As Exception
                
                Return Nothing
            End Try
        End Function
    
    

    Thanks in advance for your help.
     

     

    Friday, October 7, 2011 8:08 AM

Answers

  • Hi ernie99,

     

    I can’t get the OpenXml SDK 1.0, I approach your goal via OpenXml SDK 2.0. In order to keep compatibility between these versions, I omitted all specific object which only SDK 2.0 has. I hope it can help you.

     

    Imports DocumentFormat.OpenXml.Packaging
    Imports DocumentFormat.OpenXml.CustomProperties
    
    Module Module1
    
        Sub Main()
            Dim xSpreadsheetDocument As SpreadsheetDocument
            Dim xCustomFilePropertiesPart As CustomFilePropertiesPart
            xSpreadsheetDocument = SpreadsheetDocument.Open("C:\*******\Book1.xlsx", True)
            xCustomFilePropertiesPart = xSpreadsheetDocument.CustomFilePropertiesPart
            For Each e As DocumentFormat.OpenXml.OpenXmlElement In xCustomFilePropertiesPart.Properties.Descendants(Of DocumentFormat.OpenXml.OpenXmlElement)().ToList()
                For Each ea As DocumentFormat.OpenXml.OpenXmlAttribute In e.GetAttributes()
                    If ea.LocalName.Equals("name") Then
                        Console.Write(ea.Value)
                    End If
                Next
                For Each ex As DocumentFormat.OpenXml.OpenXmlElement In e.Descendants(Of DocumentFormat.OpenXml.OpenXmlElement)().ToList()
                    If ex.XmlQualifiedName.Name.Equals("lpwstr") Then
                        Console.WriteLine(" is a String Property, its value is " & ex.InnerText)
                    ElseIf ex.XmlQualifiedName.Name.Equals("i4") Then
                        Console.WriteLine(" is a Number Property, its value is " & ex.InnerText)
                    ElseIf ex.XmlQualifiedName.Name.Equals("filetime") Then
                        Console.WriteLine(" is a Date Property, its value is " & ex.InnerText)
                    ElseIf ex.XmlQualifiedName.Name.Equals("bool") Then
                        Console.WriteLine(" is a Yes/No Property, its value is " & ex.InnerText)
                    End If
                Next
            Next
            Console.ReadKey()
        End Sub
    
    End Module
    

    Have a good day,

     

    Tom

     

     

     


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 12, 2011 8:14 AM
    Moderator

All replies

  • Hi ernie99,

     

    Thanks for posting in the MSDN Forum.

     

    I’m confused with what you want to access, there has at lowest two nodes below the <op:Properties> node. I can’t find out your goal clearly via your snippet. Would you tell me you want to retrieve the value of property or other things?

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 10, 2011 10:40 AM
    Moderator
  • Hi Tom, thank you for your reply.

    I want to get the value of the property. What I'm trying to achieve is to create a reusable function that allows me to get the value of a Custom Document Property by passing the property name and workbook file path. 

    In the Excel Workbook the property is located in Document Properties > Advanced Properties > Custom (see image). I'm not that familiar with the XML structure of Office Documents but I believe the property I am trying to access is found in customFilePropertiesPart.Properties. I just need to figure out the code to extract property values from this node.

    Thanks for your help.

    Monday, October 10, 2011 1:00 PM
  • Hi ernie99,

     

    I can’t get the OpenXml SDK 1.0, I approach your goal via OpenXml SDK 2.0. In order to keep compatibility between these versions, I omitted all specific object which only SDK 2.0 has. I hope it can help you.

     

    Imports DocumentFormat.OpenXml.Packaging
    Imports DocumentFormat.OpenXml.CustomProperties
    
    Module Module1
    
        Sub Main()
            Dim xSpreadsheetDocument As SpreadsheetDocument
            Dim xCustomFilePropertiesPart As CustomFilePropertiesPart
            xSpreadsheetDocument = SpreadsheetDocument.Open("C:\*******\Book1.xlsx", True)
            xCustomFilePropertiesPart = xSpreadsheetDocument.CustomFilePropertiesPart
            For Each e As DocumentFormat.OpenXml.OpenXmlElement In xCustomFilePropertiesPart.Properties.Descendants(Of DocumentFormat.OpenXml.OpenXmlElement)().ToList()
                For Each ea As DocumentFormat.OpenXml.OpenXmlAttribute In e.GetAttributes()
                    If ea.LocalName.Equals("name") Then
                        Console.Write(ea.Value)
                    End If
                Next
                For Each ex As DocumentFormat.OpenXml.OpenXmlElement In e.Descendants(Of DocumentFormat.OpenXml.OpenXmlElement)().ToList()
                    If ex.XmlQualifiedName.Name.Equals("lpwstr") Then
                        Console.WriteLine(" is a String Property, its value is " & ex.InnerText)
                    ElseIf ex.XmlQualifiedName.Name.Equals("i4") Then
                        Console.WriteLine(" is a Number Property, its value is " & ex.InnerText)
                    ElseIf ex.XmlQualifiedName.Name.Equals("filetime") Then
                        Console.WriteLine(" is a Date Property, its value is " & ex.InnerText)
                    ElseIf ex.XmlQualifiedName.Name.Equals("bool") Then
                        Console.WriteLine(" is a Yes/No Property, its value is " & ex.InnerText)
                    End If
                Next
            Next
            Console.ReadKey()
        End Sub
    
    End Module
    

    Have a good day,

     

    Tom

     

     

     


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 12, 2011 8:14 AM
    Moderator