Get/Set custom Sharepoint properties values

Topics: Developer Forum, Project Management Forum, User Forum
Jun 11, 2012 at 1:17 PM

I'm trying to get/set the values for the custom Sjarepoint server properties values from an excel file but I cannot find a way to do it. I tried the getCustomProperties() function but the properties do not appear there.

In Excel the properties do not appear under the custom properties tab in the advanced properties window. They only appear if you click the "Show more properties link" in the properties section under the file menu. Most of them cannot be edited and say that they should be edited on the server, but some of them can be edited in Excel, either from the properties window in File menu or in a separate properties panel

I have also uploaded a sample file for this issue.

Jun 13, 2012 at 9:01 PM
Edited Jun 13, 2012 at 9:14 PM

The script I've used to test this is:

$inputFileType = 'Excel2007';
$inputFileName = 'SharepointProperties.xlsx';

echo "Load Properties Test from $inputFileType file<br />\n";
$objPHPExcelReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objPHPExcelReader->load($inputFileName);
echo '<h3>Core Properties</h3>';
echo '<b>Created by </b>',$objPHPExcel->getProperties()->getCreator(),'<br />';
echo '<b>Created on </b>',date('d-M-Y',$objPHPExcel->getProperties()->getCreated()),' at ',date('H:i:s',$objPHPExcel->getProperties()->getCreated()),'<br />';
echo '<b>Last Modified by </b>',$objPHPExcel->getProperties()->getLastModifiedBy(),'<br />';
echo '<b>Last Modified on </b>',date('d-M-Y',$objPHPExcel->getProperties()->getModified()),' at ',date('H:i:s',$objPHPExcel->getProperties()->getModified()),'<br />';
echo '<b>Title: </b>',$objPHPExcel->getProperties()->getTitle(),'<br />';
echo '<b>Subject: </b>',$objPHPExcel->getProperties()->getSubject(),'<br />';
echo '<b>Description: </b>',$objPHPExcel->getProperties()->getDescription(),'<br />';
echo '<b>Keywords: </b>',$objPHPExcel->getProperties()->getKeywords(),'<br />';

echo '<hr />';

echo '<h3>Extended (Application) Properties</h3>';
echo '<b>Category: </b>',$objPHPExcel->getProperties()->getCategory(),'<br />';
echo '<b>Company: </b>',$objPHPExcel->getProperties()->getCompany(),'<br />';
echo '<b>Manager: </b>',$objPHPExcel->getProperties()->getManager(),'<br />';

echo '<hr />';

echo '<h3>Custom Properties</h3>';
$customProperties = $objPHPExcel->getProperties()->getCustomProperties();
$customPropertyCount = count($customProperties);
echo '<p>There ',(($customPropertyCount !== 1) ? 'are' : 'is'),' ',$customPropertyCount,' custom propert',(($customPropertyCount !== 1) ? 'ies' : 'y'),'</p>';
foreach($customProperties as $customProperty) {
    $propertyValue = $objPHPExcel->getProperties()->getCustomPropertyValue($customProperty);
    $propertyType = $objPHPExcel->getProperties()->getCustomPropertyType($customProperty);
    if ($propertyType == PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE) {
        echo '<b>'.$customProperty.' (',$propertyType,') : </b>',date('d-M-Y H:i:s',$propertyValue),'<br />';
    } elseif ($propertyType == PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN) {
        echo '<b>'.$customProperty.' (',$propertyType,') : </b>',(($propertyValue) ? 'TRUE' : 'FALSE'),'<br />';
    } else {
        echo '<b>'.$customProperty.' (',$propertyType,') : </b>',$propertyValue,'<br />';

The result I'm getting is:

Load Properties Test from Excel2007 file

Core Properties

Created by
Created on 16-Sep-2006 at 01:00:00
Last Modified by
Last Modified on 11-Jun-2012 at 14:26:45
Title: Report_2012_WK21

Extended (Application) Properties

Company: Microsoft Corporation

Custom Properties

There is 1 custom property

ContentTypeId (s) : 0x0101002604D0191AC41D45BCA83F3A621C78F7003FCAA407CBF69B45A08153D003D3CC20


And this isn't crashing or causing any problems with script execution.

Still investigating why all the properties aren't being read, but it looks as though there's a CustomXML section of the document that's being used for these additional property definitions and values

Jun 14, 2012 at 9:43 AM

Thanks for all this work on your side. I think you are right about the custom XML with the rest of the properties. 
I managed to work around the update of this custom XML properties for now, by doing the following:

1) I unzip the xlsx file

2) I edit the xml values in the file "customXml/item1.xml"

3) I save the file

4) I repack everything into an excel file

5) The custom excel properties are updated


I'm guessing that the problem is that phpExcel is not searching for customXML property files. I'm not sure about this, it's just a guess...

Jun 14, 2012 at 10:34 AM

I don't know if this helps but I can edit the customXML properties using the following vba code withing the Excel file.

    Dim ExcelDoc As Excel.Workbook 
    Dim Prop
    For Each Prop In ExcelDoc.ContentTypeProperties
        If Prop.Name = "col_Year" Then
            Prop.Value = intYear 'Integer value
        End If
    Next Prop