Introduction

Microsoft Excel provides a feature to add some properties to the Excel files. These document properties allow some useful information to be stored along with the documents (Excel files). There are two kinds of document properties as follows:

• System Defined (Built-in) Properties
• User Defined (Custom) Properties

Built-in properties contain general information about the document like document title, author's name, document statistics and so on. Custom properties are those ones, which are defined by the users as Name/Value pairs, where both name and value are defined by the user. The most important point to know about the built-in and custom properties is that built-in properties can be accessed and modified only but not created or removed because these properties are system defined. However, custom properties can be created and managed freely by the developers because these properties are defined by the users.

Managing Document Properties Using MS Excel

Microsoft Excel allows to manage the document properties of the Excel files in a WYSIWYG manner. All you have to do, is to click the File | Properties menu item of the Microsoft Excel as shown below:

 

Figure: Selecting Properties menu item

After you select Properties menu item, a dialog would appear allowing you to manage the document properties of the Excel file as shown below in the figure:

 

Figure: Properties Dialog

In the above Properties Dialog, you can see that there are many tab pages like General, Summary, Statistics, Contents and Custom. All these tab pages allow to configure different kinds of information related to the Excel files. Custom tab is used to manage custom properties of the Excel files.

Working with Document Properties Using Aspose.Cells

Developers can also manage the document properties of the Excel files at runtime using Aspose.Cells. This feature helps developers to store some useful information along with the Excel files such as when the file was received, processed, time stamped and so on.

Accessing Document Properties

As we have described earlier that Aspose.Cells supports two kinds of document properties, which are Built-in and Custom properties. So, developers can access both kinds of properties with the use of Aspose.Cells API. Aspose.Cells provides a class, Workbook. Workbook class represents an Excel file and like an Excel file, Workbook class can also contain multiple worksheets in itself that are represented by the Worksheets collection in the Workbook class.

Developers can use Worksheets collection of the Workbook class to access the document properties of the Excel files described below:

• To access Built-in document properties, use Worksheets.BuiltInDocumentProperties
• To access Custom document properties, use Worksheets.CustomDocumentProperties

Both, Worksheets.BuiltInDocumentProperties and Worksheets.CustomDocumentProperties return a DocumentProperties collection. This collection contains DocumentProperty objects, each of which represents a single built-in or custom document property.

Developers can make use of the Index or Name of the property to get a specific property from a DocumentProperties collection as demonstrated below in the example.

Example:

[C#]

 

 

//Instantiate a Workbook object by calling its empty constructor

Workbook workbook = new Workbook();

 

 

//Open and Excel file by calling the Open method of the Workbook object

workbook.Open("C:\\book1.xls");

 

 

//Retrieve a list of all custom document properties of the Excel file

DocumentProperties customProperties = workbook.Worksheets.CustomDocumentProperties;

 

 

//Accessng a custom document property by using the property index

DocumentProperty customProperty1 = customProperties[3];

 

 

//Accessng a custom document property by using the property name

DocumentProperty customProperty2 = customProperties["Owner"];

 

 

[VB.NET]

 

 

'Instantiate a Workbook object by calling its empty constructor

Dim workbook As Workbook = New Workbook()

 

 

'Open and Excel file by calling the Open method of the Workbook object

workbook.Open("C:\\book1.xls")

 

 

'Retrieve a list of all custom document properties of the Excel file

Dim customProperties As DocumentProperties = workbook.Worksheets.CustomDocumentProperties

 

 

'Accessng a custom document property by using the property index

Dim customProperty1 As DocumentProperty =  customProperties(3)

 

 

'Accessng a custom document property by using the property name

Dim customProperty2 As DocumentProperty =  customProperties("Owner")

 

 

[JAVA]

 

 

Instantiate a Workbook object by calling its empty constructor

Workbook workbook = new Workbook();

 

 

//Open and Excel file by calling the Open method of the Excel object

workbook.open("C:\\book1.xls");

 

 

//Retrieve a list of all custom document properties of the Excel file

CustomDocumentProperties customProperties = workbook.getWorksheets().getCustomDocumentProperties();

 

 

//Accessng a custom document property by using the property index

DocumentProperty customProperty1 = customProperties.get(3);

 

 

//Accessng a custom document property by using the property name

DocumentProperty customProperty2 = customProperties.get("Owner");

The DocumentProperty class allows to retrieve the Name, Value, and Type of the document property:

• To get the Name of a property, use DocumentProperty.Name
• To get the Value of a property, use DocumentProperty.Value. DocumentProperty.Value returns the value as an Object but DocumentProperty class also provides a set of methods that return the values of other data types (see below)
• To get the Type of a property, use DocumentProperty.Type. This returns one of the PropertyType enumeration values. After you get to know about the type of the property, you can use one of the DocumentProperty.ToXXX methods to obtain the value of the appropriate type instead of using DocumentProperty.Value. These DocumentProperty.ToXXX methods are described in the following table:

Member Name	Description	ToXXX Method
Boolean	The property data type is Boolean.	ToBool
Date	The property data type is DateTime. Note that MS Excel stores only date portion, no time can be stored in a custom property of this type.	ToDateTime
Float	The property data type is Double.	ToDouble
Number	The property data type is Int32.	ToInt
String	The property data type is String.	ToString

Example:

[C#]

 

 

//Instantiate a Workbook object by calling its empty constructor

Workbook workbook = new Workbook();

 

 

//Open an Excel file by calling the Open method of the Workbook object

workbook.Open("C:\\book1.xls");

 

 

//Retrieve a list of all custom document properties of the Excel file

DocumentProperties customProperties = workbook.Worksheets.CustomDocumentProperties;

 

 

//Accessing a custom document property

DocumentProperty customProperty1 = customProperties[0];

 

 

//Storing the value of the document property as an object

object objectValue = customProperty1.Value;

 

 

//Accessing a custom document property

DocumentProperty customProperty2 = customProperties[1];

 

 

//Checking the type of the document property and then storing the value of the

//document property according to that type

if (customProperty2.Type == PropertyType.Number)

int intValue = customProperty2.ToInt();

 

 

[VB.NET]

 

 

'Instantiate a Workbook object by calling its empty constructor

Dim workbook As Workbook = New Workbook()

 

 

'Open an Excel file by calling the Open method of the Workbook object

workbook.Open("C:\\book1.xls")

 

 

'Retrieve a list of all cutom document properties of the Excel file

Dim customProperties As DocumentProperties =  workbook.Worksheets.CustomDocumentProperties

 

 

'Accessing a custom document property

Dim customProperty1 As DocumentProperty =  customProperties(0)

 

 

'Storing the value of the document property as an object

Dim objectValue As Object =  customProperty1.Value

 

 

'Accessing a custom document property

Dim customProperty2 As DocumentProperty =  customProperties(1)

 

 

'Checking the type of the document property and then storing the value of the

'document property according to that type

If customProperty2.Type = PropertyType.Number Then

Dim intValue As Integer =  customProperty2.ToInt()

End If

 

 

[JAVA]

 

 

//Instantiate a Workbook object by calling its empty constructor

Workbook workbook = new Workbook();

 

 

//Open and Excel file by calling the Open method of the Workbook object

workbook.open("C:\\book1.xls");

 

 

//Retrieve a list of all custom document properties of the Excel file

CustomDocumentProperties customProperties = workbook.getWorksheets().getCustomDocumentProperties();

 

 

//Accessing a custom document property

DocumentProperty customProperty1 = customProperties.get(0);

 

 

//Storing the value of the document property as an object

Object objectValue = customProperty1.getValue();

 

 

//Accessing a custom document property

DocumentProperty customProperty2 = customProperties.get(1);

 

 

//Checking the type of the document property and then storing the value of the

//document property according to that type

if (customProperty2.getType() == PropertyType.NUMBER){

int intValue = customProperty2.toInt();

}

 

Adding or Removing Custom Document Properties

As we have described earlier in the beginning of this topic, developers can't add or remove built-in properties because these properties are system defined but it's possible to add or remove custom properties because these are user defined.

Adding Custom Properties

To add custom document properties using Aspose.Cells, call Add method of the CustomDocumentProperties class and pass the name and value of the new property to Add method. Add method adds that property to the Excel file and returns the reference of that newly created document property as a DocumentProperty object.

Example:

[C#]

 

 

//Instantiate a Workbook object by calling its empty constructor

Workbook workbook = new Workbook();

 

 

//Open an Excel file by calling the Open method of the Workbook object

workbook.Open("C:\\book1.xls");

 

 

//Retrieve a list of all custom document properties of the Excel file

CustomDocumentProperties customProperties = workbook.Worksheets.CustomDocumentProperties;

 

 

//Adding a custom document property to the Excel file

DocumentProperty publisher = customProperties.Add("Publisher", "Aspose");