none
Way to loop through Worksheet and copy cells' properties RRS feed

  • Question

  • Hi,

    Further to this thread

    how about that I want to loop through all worksheets of the workbook and copy all other sheets into this "overall" sheet?

    BTW, to the current codes in that, how can I also copy the properties, like width, color, bold/italic, font, of each cell, within the Worksheets?

    Many Thanks & Best Regards, Hua Min

    Thursday, August 4, 2016 9:29 AM

Answers

All replies

  • Any help?

    Many Thanks & Best Regards, Hua Min

    Friday, August 5, 2016 4:25 AM
  • >>loop through all worksheets of the workbook

     

    To loop through all worksheets of a workbook, you could use

                        WorkbookPart workbookPart = doc.WorkbookPart;

                        foreach (WorksheetPart wsPart in workbookPart.WorksheetParts)

                        { }

     

    >>copy all other sheets into this "overall" sheet?

     

    Are the target sheet(overall sheet) and other source sheets in the one workbook? Or they are in two worksheet separately?

    Anyway, you can get all the source data then insert them into the "overall" sheet.

    To get all cells' value, see How to: Retrieve the values of cells in a spreadsheet document (Open XML SDK)

    To insert cells into overall sheet, see How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    • Marked as answer by Jackson_1990 Friday, August 5, 2016 9:34 AM
    Friday, August 5, 2016 5:59 AM
    Moderator
  • To write to the overall sheet, can I each time copy one row from other sheets, and paste the row into 

    A1
    A2
    ...

    of the overall sheet, one row after another? Any example to this?

    Many Thanks & Best Regards, Hua Min

    Friday, August 5, 2016 7:49 AM
  • Hi,

    You can refer to the following similar thread to copy the rows. 

    https://social.msdn.microsoft.com/Forums/office/en-US/65c9ca1c-25d4-482d-8eb3-91a3512bb0ac/copy-and-insert-row?forum=oxmlsdk

    If you have any issue with copying and inserting, i suggest you post a new thread for a new issue

    Friday, August 5, 2016 8:42 AM
    Moderator
  • Hi,

    You can refer to the following similar thread to copy the rows. 

    https://social.msdn.microsoft.com/Forums/office/en-US/65c9ca1c-25d4-482d-8eb3-91a3512bb0ac/copy-and-insert-row?forum=oxmlsdk

    If you have any issue with copying and inserting, i suggest you post a new thread for a new issue

    Hi,

    Is there one better example to copy the row, other than the current one in above?


    Many Thanks & Best Regards, Hua Min



    Friday, August 5, 2016 10:02 AM
  • Celeste covered the way to get the data. To get the formatting, you need to go two levels lower from the WorksheetPart. Once you have the proper sheet, use Worksheet properties to recover what you need. These are the things available in the Worksheet class:

    [ChildElementInfoAttribute(typeof(WorksheetExtensionList))] [ChildElementInfoAttribute(typeof(Hyperlinks))] [ChildElementInfoAttribute(typeof(TableParts))] [ChildElementInfoAttribute(typeof(SheetDimension))] [ChildElementInfoAttribute(typeof(SheetViews))] [ChildElementInfoAttribute(typeof(SheetFormatProperties))] [ChildElementInfoAttribute(typeof(Columns))] [ChildElementInfoAttribute(typeof(SheetData))] [ChildElementInfoAttribute(typeof(SheetCalculationProperties))] [ChildElementInfoAttribute(typeof(SheetProtection))] [ChildElementInfoAttribute(typeof(ProtectedRanges))] [ChildElementInfoAttribute(typeof(Scenarios))] [ChildElementInfoAttribute(typeof(AutoFilter))] [ChildElementInfoAttribute(typeof(SortState))] [ChildElementInfoAttribute(typeof(DataConsolidate))] [ChildElementInfoAttribute(typeof(CustomSheetViews))] [ChildElementInfoAttribute(typeof(MergeCells))] [ChildElementInfoAttribute(typeof(PhoneticProperties))] [ChildElementInfoAttribute(typeof(ConditionalFormatting))] [ChildElementInfoAttribute(typeof(DataValidations))] [ChildElementInfoAttribute(typeof(SheetProperties))] [ChildElementInfoAttribute(typeof(PrintOptions))] [ChildElementInfoAttribute(typeof(PageMargins))] [ChildElementInfoAttribute(typeof(PageSetup))] [ChildElementInfoAttribute(typeof(HeaderFooter))] [ChildElementInfoAttribute(typeof(RowBreaks))] [ChildElementInfoAttribute(typeof(ColumnBreaks))] [ChildElementInfoAttribute(typeof(CustomProperties))] [ChildElementInfoAttribute(typeof(CellWatches))] [ChildElementInfoAttribute(typeof(IgnoredErrors))] [ChildElementInfoAttribute(typeof(SmartTags))] [ChildElementInfoAttribute(typeof(Drawing))] [ChildElementInfoAttribute(typeof(LegacyDrawing))] [ChildElementInfoAttribute(typeof(LegacyDrawingHeaderFooter))] [ChildElementInfoAttribute(typeof(DrawingHeaderFooter), FileFormatVersions.Office2010)] [ChildElementInfoAttribute(typeof(Picture))] [ChildElementInfoAttribute(typeof(OleObjects))] [ChildElementInfoAttribute(typeof(Controls))] [ChildElementInfoAttribute(typeof(WebPublishItems))]

    As an example, Columns contains the Width property and SheetProperties contains the TabColor.

        EDIT: I forgot to add that you are using the links Celeste added and the Ancestors() Method to enumerate each property you need to copy.

    • Edited by JimSnyder Tuesday, August 9, 2016 2:25 PM Additional information
    Tuesday, August 9, 2016 2:16 PM