Read generated excel sheet from pivot table show details option using python

user_10792413 0
Viewed 194

I've an excel workbook having a single sheet in it. That sheet contains only the pivot table.

In the pivot table there is one column having category details and one column with the counts. On the counts column on each cell if I double click or select "Show Details" option another sheet is generated automatically.

My requirement is, if a cell value matches a given count I need to read the generated sheet (manually done by double click on that cell to generate the sheet).

For example: Consider the below table as a pivot table. Suppose I'm given with the value 8 to match. So I've to double click on the cell having value 8 in count column which generates a sheet with the details(rows that contribute to get count value 8).

Is there any way I can generate and read the excel programmatically (may it be as pandas dataframe or worksheet in openpyxl or in any other form so that I can automate the process).

Category Count
ABC 5
OPQ 8
XYZ 10

I'm working with pivot tables for the 1st time, so forgive and correct me if my question is misunderstood.

pivot-table python-3.x excel automation
1 Answers

If you can use win32com, which will essentially lock you into Windows, you can try the following:

import win32com.client as win32

# Situation configuration
filename = r'C:\Users\You\Path\To\my_excel_sheet.xlsx'
pivot_table_sheet = 'Plan4'
count_column = 'B'
match_value = 8

excel = win32.gencache.EnsureDispatch('Excel.Application')

try:
    wb = excel.Workbooks.Open(filename)
    
    worksheet = wb.Sheets(pivot_table_sheet)
    row_count = worksheet.UsedRange.Rows.Count

    # count_range is a str on the format 'B1:B4'
    count_range = f'{count_column}1:{count_column}{row_count}'

    for cell in worksheet.Range(count_range):
        if cell.Value == float(match_value):
            print(f'Cell {cell.GetAddress()} at column "Count" matches the value!')

            # This is the attribute that performs the "Show Details" action
            worksheet.Range(cell.GetAddress()).ShowDetail = True

    wb.SaveCopyAs(f'{filename}_modified.xlsx')
    wb.Close(SaveChanges=0)

except Exception as e:
    print(str(e))

finally:
    excel.Quit()

This will save a copy of your original Workbook, with the proper worksheets created. See below for images detailing the tested scenario. Some changes may be needed depending on your real situation

Raw data set in Plan1 (first rows)

Raw data set

Pivot table in Plan4

Note that I've added a second Category that matches the value 8 in comparison with your original example

Pivot table

Output worksheet for Category OPQ (Plan2)

OPQ worksheet

Output worksheet for Category AGG (Plan3)

AGG worksheet

user_59107570
Related