none
Problem to line RRS feed

  • Question

  • Hi,
    How to resolve
    Error	2	'DocumentFormat.OpenXml.OpenXmlElement' does not contain a definition for 'Id' and no extension method 'Id' accepting a first argument of type 'DocumentFormat.OpenXml.OpenXmlElement' could be found (are you missing a using directive or an assembly reference?)	C:\App0\App5\Program.cs	170	127	App5


    due to last line below?
                            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(@Path0, true))
                            {
                                ...
                                WorksheetPart worksheetPart2 = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets.First().Id);



    Many Thanks & Best Regards, Hua Min


    • Edited by Jackson_1990 Thursday, December 29, 2016 2:51 AM
    Thursday, December 29, 2016 2:50 AM

Answers

  • So do you want to ensure there is sheets("vSN") in an existing workbook, if not, create new?

    Please see How to: Insert a new worksheet into a spreadsheet document (Open XML SDK)

     
      public static void checkSheet(string filepath, string worksheetName)
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.
                   Open(filepath, true))
                {
                    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
                    // if the specific sheet doesnot exist, create new 
                    if (sheets.Count() == 0)
                    {
                        InsertWorksheetCus(document.WorkbookPart, worksheetName);
                    }
                    // get the worksheetPart
                    WorksheetPart wsPartSpe = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
    
                }
            }
    
            private static WorksheetPart InsertWorksheetCus(WorkbookPart workbookPart,string worksheetName)
            {
                // Add a new worksheet part to the workbook.
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
    
                // Get a unique ID for the new sheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
    
                // Append the new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = worksheetName };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();
    
                return newWorksheetPart;
            }
    
    


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jackson_1990 Friday, December 30, 2016 8:59 AM
    Friday, December 30, 2016 8:02 AM
    Moderator

All replies

  • Hi,

    What is your "sheets"?

    Please see the two method to get the worksheetPart:

    Method One:

       IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
    
                    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
    

    Method Two:

         Sheet theSheet0 = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Id == "rId15").FirstOrDefault();
                    Sheet theSheet1 = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.SheetId == "22").FirstOrDefault();
                    Sheet theSheet2 = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet11").FirstOrDefault();
    
                    WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(theSheet0.Id);
    
    

    Regards,

    Celeste




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 30, 2016 2:08 AM
    Moderator
  • sheets have been declared before the problematic line like
                                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
                                ...
                                WorksheetPart worksheetPart2 = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets.First().Id);
    Why does the error come out, due to my declaration of sheets in above?



    Many Thanks & Best Regards, Hua Min



    Friday, December 30, 2016 2:24 AM
  • Hi,

    Are you creating a new workbook?

    Please see How to: Create a spreadsheet document by providing a file name (Open XML SDK)

    A workbook part must have at least one worksheet. To add a worksheet, create a new Sheet. When you create a new Sheet, associate the Sheet with the Workbook by passing the Id, SheetId and Name parameters. Use the GetIdOfPart(OpenXmlPart) method to get the Id of the Sheet. Then add the new sheet to the Sheet collection by calling the Append([]) method of the Sheets class.

    If you are creating several sheets, you need to add several worksheetPart for each sheet.

    For example, the code below creates a new workbook with two sheets.

            public static void CreateSpreadsheetWorkbook(string filepath)
            {
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                    Create(filepath, SpreadsheetDocumentType.Workbook);
    
                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
    
                // Add Sheets to the Workbook.
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                    AppendChild<Sheets>(new Sheets());
    
    
                // Add WorksheetPart to the WorkbookPart for each sheet.
                WorksheetPart worksheetPart1 = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart1.Worksheet = new Worksheet(new SheetData());
    
                WorksheetPart worksheetPart2 = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart2.Worksheet = new Worksheet(new SheetData());
    
                Sheet sheet1 = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                    GetIdOfPart(worksheetPart1),
                    SheetId = 1,
                    Name = "mySheet1"
                };
    
                Sheet sheet2 = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                  GetIdOfPart(worksheetPart2),
                    SheetId = 2,
                    Name = "mySheet2"
                };
    
                sheets.Append(sheet1);
                sheets.Append(sheet2);
    
                workbookpart.Workbook.Save();
                spreadsheetDocument.Close();
            }

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 30, 2016 5:50 AM
    Moderator
  • No, I was to refer to the sheet after having appended new sheet below, and have got the problem to last line like

                                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
                                if (sheets.Count() == 0)
                                {
                                    // Add a WorkbookPart to the document.
                                    WorkbookPart workbookpart = spreadSheet.AddWorkbookPart();
                                    workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
    
                                    // Add a WorksheetPart to the WorkbookPart.
                                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                                    worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new SheetData());
    
                                    Sheet sheet = new Sheet()
                                    {
                                        Id = spreadSheet.WorkbookPart.
                                            GetIdOfPart(worksheetPart),
                                        SheetId = 1,
                                        Name = vSN
                                    };
                                    sheets.Append(sheet);
    
                                }
                                WorksheetPart worksheetPart2 = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets.First().Id);



    Many Thanks & Best Regards, Hua Min

    Friday, December 30, 2016 6:23 AM
  • Hi,

    You could use sheets.Elements<Sheet>() to return the sheets collection from sheets.

                WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheets.Elements<Sheet>().First().Id);

    In fact, there is no need to add if (sheets.Count() == 0), because it would always be 0 if you are create a new workbook. So then we could directly use worksheetPart to refer the part.

    If you are creating new sheets for an existing workbook, there is no need to append new Sheets to the workbook. It exists already.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 30, 2016 7:00 AM
    Moderator
  • Hi,
    >> If you are creating new sheets for an existing workbook, there is no need to append new Sheets to the workbook. It exists already.
    How about that I want to ensure that there is specific Worksheet with given name like this?

                                    Sheet sheet = new Sheet()
                                    {
                                        Id = spreadSheet.WorkbookPart.
                                            GetIdOfPart(worksheetPart),
                                        SheetId = 1,
                                        Name = vSN
                                    };



    Many Thanks & Best Regards, Hua Min

    Friday, December 30, 2016 7:25 AM
  • So do you want to ensure there is sheets("vSN") in an existing workbook, if not, create new?

    Please see How to: Insert a new worksheet into a spreadsheet document (Open XML SDK)

     
      public static void checkSheet(string filepath, string worksheetName)
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.
                   Open(filepath, true))
                {
                    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
                    // if the specific sheet doesnot exist, create new 
                    if (sheets.Count() == 0)
                    {
                        InsertWorksheetCus(document.WorkbookPart, worksheetName);
                    }
                    // get the worksheetPart
                    WorksheetPart wsPartSpe = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
    
                }
            }
    
            private static WorksheetPart InsertWorksheetCus(WorkbookPart workbookPart,string worksheetName)
            {
                // Add a new worksheet part to the workbook.
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
    
                // Get a unique ID for the new sheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
    
                // Append the new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = worksheetName };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();
    
                return newWorksheetPart;
            }
    
    


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jackson_1990 Friday, December 30, 2016 8:59 AM
    Friday, December 30, 2016 8:02 AM
    Moderator