CODEWITHSUNDEEP
c#excelopenxmlepplus
Cannon
Cannon

Reputation: 2783

Worksheet.CustomProperites OpenXML

I need to read Worksheet.CustomProperies. Is there any way to read this properties?

I have also tried getting XmlDocument of a workbook and worksheet using

 XmlDocument xlDoc =  ws.WorksheetXml;

Gives me:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<dimension ref="A3:K24" />
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0"><selection activeCell="H14" sqref="H14" /></sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="15" />
<cols></cols><sheetData />
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" />
<pageSetup orientation="portrait" horizontalDpi="4294967293" verticalDpi="4294967293" r:id="rId2" />
**<customProperties><customPr name="My_CustomProperty" r:id="rId3" /></customProperties>**
</worksheet>

I can see a CustomProperty there but not able to see the CustomProperty value. When I go to CustomProperty bin file(Zip the xlsx and extract contents), value is there.

I have uploaded the document here

Upvotes: 2

Views: 2166

Answers (1)

Gloopy
Gloopy

Reputation: 37785

I'm not familiar with these custom properties but here is one way to extract the contents of the customProperty1.bin file from your sample document using the latest version of EPPlus:

using (ExcelPackage p = new ExcelPackage(new FileInfo(@"C:\Users_Template_12_22_Template.xlsx")))
{
    var parts = p.Package.GetParts();
    foreach (var part in parts)
    {
        if (part.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.customProperty")
        {
            using (var stream = part.GetStream())
            {
                byte[] data = new byte[stream.Length];
                stream.Read(data, 0, (int)stream.Length);
                stream.Close();

                string customPropertyInfo = System.Text.Encoding.Unicode.GetString(data);
            }
        }
    }
}

If you know the name/location of the customProperty1.bin file you can access it using GetPart() instead of GetParts():

var u = new Uri("/xl/customProperty1.bin", UriKind.Relative);
var part = p.Package.GetPart(u);

Note you'll need to add a reference to WindowsBase.dll (under the .NET tab in Add Reference) to use Packaging related methods.

Upvotes: 2

Related Questions