none
Get Internal Metadata for Excel File RRS feed

  • Question

  • Hello,

    i wanted to fetch below metadata for excel file without opening excel office Instance.(reason : opening office instance gives popup notification for opening some of files and it reduce in speed also, we wanted to do this dynamic i mean no one will be there to click button for popup windows)

    hasHeader
    hasFooter
    hasProtectedSheet
    protectedSheetNames
    hiddenSheetNames
    hasHiddenRows

    Is there any way we can achieve the same?

    Thanks


    • Edited by Viral84 Tuesday, September 6, 2016 7:16 AM Addition
    Tuesday, September 6, 2016 7:14 AM

Answers

  • Hi,

    >>opening office instance gives popup notification for opening some of files

    What is the detail information of the popup notification? In my opinion, if you just check metadata of your workbook, it shouldn’t show any popup notifications.

     

    I think you could use Open XML Library to get its internal metadata. Using SpreadsheetML, we could avoid opening Office instance.

    For more information about SpreadsheetML, please visit Spreadsheets (Open XML SDK)

    To check its XML node you could use Open XML SDK Tool.

    Click here to download Open XML SDK Tool.

    Besides, you could directly generate the code by Open XML SDK Tool.

    1. Create two documents with one is original file and one is the target file;

    2. Use Open XML SDK 2.5 productivity Tool for Microsoft Office

    3. Click the Compare Files, generate the code, and then you could get the code what you want

     

    E.g. Check if has header or footer

        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart wbPart = doc.WorkbookPart;
                    foreach (WorksheetPart wsPart in wbPart.WorksheetParts )
                    {
                        HeaderFooter headerFooter = wsPart.Worksheet.Descendants<HeaderFooter>().FirstOrDefault();               
                        if (headerFooter == null)
                        {
                            Console.WriteLine("There is no headerfooter");
                        }
                        else
                        {
                            //..
                        }
                        OddHeader header = wsPart.Worksheet.Descendants<OddHeader>().FirstOrDefault();
                        if (header == null)
                        {
                            Console.WriteLine("There is no header");
                        }
                        else
                        {
                            //..
                        }
                        OddFooter  footer = wsPart.Worksheet.Descendants<OddFooter>().FirstOrDefault();
                        if (header == null)
                        {
                            Console.WriteLine("There is no footer");
                        }
                        else
                        {
                            //..
                        }
                    }         
                }
    

    Wednesday, September 7, 2016 11:17 AM
    Moderator
  • If you want to get the sheet name, I suggest you retrieve wsPart by the relationship id as shown below.

        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart wbPart = doc.WorkbookPart;
                    Sheets sheets = wbPart.Workbook.Sheets;
                    foreach (Sheet sheet in sheets)
                    {
                        var rId = sheet.Id;
                        WorksheetPart wsPart = (WorksheetPart)doc.WorkbookPart.GetPartById(rId);
                        SheetProtection ProtectedWorkbook = wsPart.Worksheet.Descendants<SheetProtection>().FirstOrDefault();
                        if (ProtectedWorkbook != null)
                        {
                            Console.WriteLine("{0} is Protected Sheet", sheet.Name);            
                        }
                    }
                }
    

    • Marked as answer by Viral84 Tuesday, September 20, 2016 5:57 AM
    Monday, September 12, 2016 11:58 PM
    Moderator

All replies

  • Hi,

    >>opening office instance gives popup notification for opening some of files

    What is the detail information of the popup notification? In my opinion, if you just check metadata of your workbook, it shouldn’t show any popup notifications.

     

    I think you could use Open XML Library to get its internal metadata. Using SpreadsheetML, we could avoid opening Office instance.

    For more information about SpreadsheetML, please visit Spreadsheets (Open XML SDK)

    To check its XML node you could use Open XML SDK Tool.

    Click here to download Open XML SDK Tool.

    Besides, you could directly generate the code by Open XML SDK Tool.

    1. Create two documents with one is original file and one is the target file;

    2. Use Open XML SDK 2.5 productivity Tool for Microsoft Office

    3. Click the Compare Files, generate the code, and then you could get the code what you want

     

    E.g. Check if has header or footer

        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart wbPart = doc.WorkbookPart;
                    foreach (WorksheetPart wsPart in wbPart.WorksheetParts )
                    {
                        HeaderFooter headerFooter = wsPart.Worksheet.Descendants<HeaderFooter>().FirstOrDefault();               
                        if (headerFooter == null)
                        {
                            Console.WriteLine("There is no headerfooter");
                        }
                        else
                        {
                            //..
                        }
                        OddHeader header = wsPart.Worksheet.Descendants<OddHeader>().FirstOrDefault();
                        if (header == null)
                        {
                            Console.WriteLine("There is no header");
                        }
                        else
                        {
                            //..
                        }
                        OddFooter  footer = wsPart.Worksheet.Descendants<OddFooter>().FirstOrDefault();
                        if (header == null)
                        {
                            Console.WriteLine("There is no footer");
                        }
                        else
                        {
                            //..
                        }
                    }         
                }
    

    Wednesday, September 7, 2016 11:17 AM
    Moderator
  • Hello,

    Thanks for the information provided, i able to find most of properties using open xml DSK.

    If you can help in below point to get Protected Sheet Names,

    I have writen below code to identify if xls has Protected Sheet, now how do i get Protected sheet name ?

    SheetProtection ProtectedWorkbook = wsPart.Worksheet.Descendants<SheetProtection>().FirstOrDefault();
    
                        if(ProtectedWorkbook != null)
                        {                        
                            Console.WriteLine("There is  Protected Sheet");                        
                        }

    Thanks

    Monday, September 12, 2016 7:43 AM
  • If you want to get the sheet name, I suggest you retrieve wsPart by the relationship id as shown below.

        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart wbPart = doc.WorkbookPart;
                    Sheets sheets = wbPart.Workbook.Sheets;
                    foreach (Sheet sheet in sheets)
                    {
                        var rId = sheet.Id;
                        WorksheetPart wsPart = (WorksheetPart)doc.WorkbookPart.GetPartById(rId);
                        SheetProtection ProtectedWorkbook = wsPart.Worksheet.Descendants<SheetProtection>().FirstOrDefault();
                        if (ProtectedWorkbook != null)
                        {
                            Console.WriteLine("{0} is Protected Sheet", sheet.Name);            
                        }
                    }
                }
    

    • Marked as answer by Viral84 Tuesday, September 20, 2016 5:57 AM
    Monday, September 12, 2016 11:58 PM
    Moderator