Share via

Excel Online does not show "+" / "-" signs for grouped rows in generated files

Anonymous
2019-11-29T11:52:01+00:00

Good morning,

I am currently using the python module "openpyxl" to generate some Excel files that must be shared in a synchronized folder on sharepoint and then visualized by Excel Online.

I am having some troubles with the Excel grouping rows feature, since when files are visualized on Encel online the plus button of grouped rows is absent and it's not possible to show grouped rows.

Opening the same file with standard local Excel does not present any issue.

I would have expected that if code generated file works for standard local Excel, and grouping feature is available also in Excle Online, than code generated file should work also in Excel Online.

The issue is independent from the used browser.

The following python code reproduces the issue. input_file.xlsx is a completly new and empty Excel file except for the fact that has 2 (identically empty) sheets renamed 'Sheet A' and 'Sheet B'. Data is a sharepoint syncronized folder.

from openpyxl import load_workbook

import os

if __name__ == '__main__':

# Path to a file synchronized in a sharepoint folder (Data)

workbook_path = os.path.join('Data', 'input_file.xlsx')

output_path = os.path.join('Data', 'output_file.xlsx')

# load input workbook

workbook = load_workbook(workbook_path)

worksheet_a = workbook['Sheet A']

# Apply grouping to first sheet

worksheet_a.row_dimensions.group(7, 38, hidden=True)

worksheet_b = workbook['Sheet B']

# Apply grouping to second sheet

worksheet_b.row_dimensions.group(7, 38, hidden=True)

# save output workbook in the synchronized sharepoint folder

workbook.save(output_path)

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-12-02T16:09:14+00:00

    Hi Paolo,

    Thank you for your help.

    I already noticed the fact mentioned in your first suggestion so I resolved my problem as follows:

    1. Generate all reports with groups not hidden.
    2. Run a VBS script that file by file closes all groups.

    It looks like vbs function does not invalidate the buttons visibility and the result is the one I expected from the beginning. I know, it's not clean, but I had no better ideas.

    Hope in future this issue will be resolved in order to avoid this overhead.

    If of any help I add the vbs script here:

    Dim ObjExcel, ObjWB, Subfolder, objFile

    Set ObjExcel = CreateObject("excel.application")

    Set FSO = CreateObject("Scripting.FileSystemObject")

    ' Open folder containing files

    Set mainFolder = FSO.GetFolder("main\folder\path")

    ' Get list of files

    Set colFiles = mainFolder.Files

    Wscript.Echo "Elaborating file: "

    For Each objFile in colFiles

        ' keep only xlsx files

        If LCase(FSO.GetExtensionName(objFile.Name)) = "xlsx" Then

            Wscript.Echo objFile.Name

            Set ObjWB = ObjExcel.Workbooks.Open(objFile.Path)

            ' select worksheet

            Set objWorksheet = ObjWB.Worksheets(1)

            ' collapse all grouped rows       

            objWorksheet.Outline.ShowLevels(1)

            ObjWB.Save

            ObjWB.Close True

        End If

    Next

    ObjExcel.Quit

    Wscript.Echo ""

    Wscript.Echo "File update completed"

    Set objWorksheet = Nothing

    Set mainFolder = nothing

    Set FSO = Nothing

    Set ObjExcel = Nothing

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2019-11-29T13:09:55+00:00

    Hi Jegan,

    Thank you for your assistance.

    Unfortunately  this does not solve my problem. I need the +/- buttons to be displayed in the moment the workbook is opened. I must generate a lot of this reports and I can't ask all the users to rely on a shortcut otherwise the report won't work.

    There is plenty of workaround to display buttons on the single file, e.g. clicking on the group number 1, 2 symbols at the top of the sheet, but I need a more stable and scalable solution.

    Hope you can help me to find it.

    Davide.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-11-29T13:06:47+00:00

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-11-29T13:06:21+00:00

    The difference in viewing Excel desktop and excel Web for same grouping feature:

    Was this answer helpful?

    0 comments
  5. Anonymous
    2019-11-29T12:32:19+00:00

    Hi Salvaggio

    You try this keyboard shortcuts for Excel Online?

    To display the detail data within a group, click the plus for the group, or press ALT+SHIFT+=.

    To hide the detail data for a group, click the minus for the group, or press ALT+SHIFT+-.

    Was this answer helpful?

    0 comments