workbook.custom_document_properties allows me to add new properties, but does not find the existing properties in the Excel file
doc_properties = workbook.custom_document_properties
doc_properties.add(‘TEST’ , ‘TEST’) # OK
i = doc_properties.index_of(‘TEST’)
doc_properties[i].value = ‘after you alphonse’ #OK
but it doesn’t see all the properties that are already in the file.
Please use built_in_document_properties instead of custom_document_properties if you need to retrieve existing (built-in) document properties.
e.g.,
Sample code:
# Load the workbook
workbook = Workbook("e:\\test2\\Bk_properties1.xlsx")
# Get the built-in document properties
document_properties = workbook.worksheets.built_in_document_properties
# Get first document property with its value
print(document_properties[0].name)
print(document_properties[0].value)
Hope, this helps a bit.
Thanks for the suggestion.
But I mean custom_document_properties that have been manually added to the file, not the built in properties like Title & Subject.
I am trying to update a copy of template that has been created manually. The DocProperties that are already in the file don’t show up in workbook.custom_document_properties.
I see that the issue is specific to this attached Excel file. When I created a test from scratch the doc_properties were read as expected. But I need to use this exact Excel file, because it comes from a legacy system that I cannot change.
Thanks for the template Excel file.
Which version of Aspose.Cells for Python via .NET you are using? Please try latest version.
I checked your template Excel file and found there is “TEST” custom property in the workbook. I used the following sample code and it works fine and as expected.
workbook = Workbook("e:\\test2\\CR-MS-GEN=NEL6-GEN-PMA-ASN-0003-_-_.xlsx")
# Get the custom document properties
document_properties = workbook.worksheets.custom_document_properties
print(document_properties[0].name + " : " + document_properties[0].value)
i = document_properties.index_of('TEST')
print(document_properties[i].value)
output:
TEST : after you alphonse
after you alphonse
Let us know with details if there is other issue and we are not aware of it?
The custom_document_property TEST is created with Aspose - but if you look at the list in Excel, there are a whole lot more defined - but these do not show up. See the attached screenshot.
DocProperties.png (9.8 KB)
My Excel is Microsoft® Excel® for Microsoft 365 MSO (Version 2411 Build 16.0.18227.20082) 64-bit
@rlloyd
Please change extension “.xlsx” of your template “CR-MS-GEN=NEL6-GEN-PMA-ASN-0003--.xlsx” file as “.zip”, then unzip the file. All Document Properties are stored in the files under directory “docProps”. We can not find your mentioned properties.
Sorry, I can see that I have sent you the wrong version of the file,
Here is the original template.
Excel Normal.zip (42.1 KB)
custom.xml contains many DocProperties.
@rlloyd
We can print all custom document properties with the following codes:
Workbook workbook = new Workbook(dir + "Excel Normal.xlsx");
workbook.CustomDocumentProperties.Add("TEST", "TEST");
workbook.Save(dir + "dest.xlsx");
workbook = new Workbook(dir + "dest.xlsx");
for (int i = 0; i < workbook.CustomDocumentProperties.Count; i++)
{
Console.WriteLine(workbook.CustomDocumentProperties[i].Name);
}
Attached Excel view and out text of console:
Downloads.zip (70.1 KB)
This seems to be a confusion on my part. When I separated the Aspose part from the rest of the web page the problem disappeared, so it is not a Aspose issue. Thanks for your patience.
Yes, there seems no issue on Aspose.Cells for Python part. I just changed the extension of your “Excel Normal.zip” to “Excel Normal.xlsx” and used the following sample code using Aspose.Cells for Python via .NET and it works fine:
workbook = Workbook("e:\\test2\\customdocprop\\new1\\Excel Normal.xlsx")
document_properties = workbook.worksheets.custom_document_properties
for document_Property in document_properties:
print(document_Property.name + ":" + document_Property.value)
output:
MS_DocID:Metadata:MS_DocID
MS_ShortTitle:Metadata:MS_ShortTitle
MS_SerialNo:
MS_SerialNo_NoZeroes:
MS_DateOfIssue:2018-12-06
MS_AuthorInitials:Metadata:AuthorInitials
MS_AuthorName:Metadata:AuthorName
MS_IssuersDocID:Metadata:IssuersDocID
MS_LanguageCode:DK
MS_AssociationType:_
MS_AssociationSubType:_
PW_UpdateDate:2018-12-06
PW_Version:10.0
MP_ProjectDiscipline:
MP_ProjectName:
MC_CaseID:
MC_IssuersDateOfIssue:
MC_RecipientAddress:
MC_RecipientOrganisationName:
MS_ProjectFullName:CITYRINGEN
MS_DocumentSubtype:
CR_CheckerInitials:Metadata:CheckerInitials
CR_CheckerName:Metadata:CheckerName
CR_ApproverInitials:Metadata:ApproverInitials
CR_ApproverName:Metadata:ApproverName
MS_IssuerID:MS
MS_IssuerOrganisationName:Metroselskabet I/S
Folder_Number:
Folder_Code:
Folder_Name:
Folder_Description:
/Folder_Name/:
/Folder_Description/:
Folder_Version:
Folder_VersionSeq:
Folder_Manager:
Folder_ManagerDesc:
Folder_Storage:
Folder_StorageDesc:
Folder_Creator:
Folder_CreatorDesc:
Folder_CreateDate:
Folder_Updater:
Folder_UpdaterDesc:
Folder_UpdateDate:
Document_Number:
Document_Name:
Document_FileName:
Document_Version:
Document_VersionSeq:
Document_Creator:
Document_CreatorDesc:
Document_CreateDate:
Document_Updater:
Document_UpdaterDesc:
Document_UpdateDate:
Document_Size:
Document_Storage:
Document_StorageDesc:
Document_Department:
Document_DepartmentDesc:
The problem was with the Excel cache - Excel is reading the cached doc properties as per this post. I have tried to disable caching with ‘Delete files from the office document cache when they are closed’, but this still does not help.
So we found that for some reason if you edit the properties/metadata then open the file, it seems to open the version which is sitting in your cache which is not necessarily the current version and therefore when you update things on it and save, it overrides the metadata to whatever is in the version you opened. With the Microsoft tech, we checked and could not find a reason why if you open a file directly to the app, it doesn’t use the latest version of the file (although the content within it are up to date). This may be due to the change Microsoft is currently doing with removing/retiring Upload Centre but we aren’t sure.
Thank you for your feedback and the information provided. It seems that the issue may be linked to the Excel cache on your end. Did you sort it out or the workaround figure out your issue? If there’s anything we can look into or assess on the Aspose.Cells part, please let us know, and we would be glad to assist.
I can see the changes in the custom.xml file but these do not appear inside Excel, which keeps reverting to the original values, but only for the original docProperties. The new docProperties adding in Aspose do not revert ( presumably because they are not in the cache ).
But I can’t see what is overriding the values in the custom.xml file.
We will evaluate it in details and get back to you. By the way, do you see this issue with all Excel files (having some custom document properties) or with specific file (you already shared a sample file) only?
You can also try the following methods: you can set Excel to “Disable AutoSave (File → Options → Save → AutoSave…)” or “Use the Office File Protection feature (File → Info → Protect Workbook)” to prevent Excel from performing certain auto-save actions.