Automate Excel Pivot Table With Python
Excel being the most popular spreadsheet application + Python being one of the most popular programming language, by integrating these two applications, we can build some really useful and sophisticate applications.
In this tutorial, we will learn how to automate Excel pivot table with Python using pywin32 (win32com) library.
📑 Source Code: learndataanalysis.org/automat...
▶ References
🔖 Dataset Used: data.sfgov.org/Economy-and-Co...
📑 Excel VBA Pivot Table Reference: docs.microsoft.com/en-us/offi...
📑 Excel VBA Pivot Field Reference: docs.microsoft.com/en-us/offi...
► Buy Me a Coffee? Your support is much appreciated!
-------------------------------------------------------------------------------------------
☕ Paypal: www.paypal.me/jiejenn/5
☕ Venmo: @Jie-Jenn
💸 Join Robinhood with my link and we'll both get a free stock: bit.ly/3iWr7LC
► Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
🌳 Becoming a Patreon supporter: / jiejenn
🛒 By shopping on Amazon → amzn.to/2JkGeMD
📘 Facebook Page → / madeinpython
📘 More tutorial videos on my website → LearnDataAnalysis.org
👩💻 Follow me on Linked: / jiejenn
✉️ Business Inquiring: KZread@LearnDataAnalysis.org
#excel #python #ExcelAutomation
Пікірлер: 54
This video deserves a lot more views than this and I am sure it will. Very detailed, and simple to understand. Thank you for such a great video.
@intoperspective2023
2 жыл бұрын
+ 1 new subscriber
Great video!! Thanks for the help!
Wow, thank you!!
Спасибо за видео. Долго искал , как создать сводную. именно таким образом 😊👍👍👍👍👍👍👍👍👍
Hi Jie! Thanks for this video! It helped me a lot. Regards from Colombia.
@jiejenn
2 жыл бұрын
Glad the video helped.
2 жыл бұрын
@@jiejenn Hi. I'm making an automation for Excel which is generated from SAP. This will help me a lot. Thanks again.
Simply Amazing
@jiejenn
Жыл бұрын
👍
Really impressed, im looking how to create pivot table by using python, instead of pandas, Because when we use pandas we cant use pivot table functionality. By using win32 we can achieve this.
great video, thanks for the effort! Can you tell, or show, how would you filter out some value in 'Agency name' filter? I've been trying with CurrentPage and Add2, but I get the error every time.
Many thanks. First of all, I really appreciate your video. Secondly, may I ask how we can add all fields_rows in one column ? Because with Excel Pivot table, when you put all data in rows, it will automatically put them in one column, one under another.
That is really helpful! Thanks! May I also ask if it is possible to add an calculated field into the pivot table, supposedly "PivotTable.CalculatedFields().Add()"?
great sir thank you ❤❤❤❤❤❤❤💋💋💋💋💋💋
@jiejenn
2 жыл бұрын
Glad the video helped.
Amazing tutorial, i used this on one of my project. @Jie Jenn is it possible to add a calculated field here? I find it hard to mimic the manual calculated field creation.
Can we able to add the field settings to this table ? like layout & print options ?
Hi Jie, if i want to not show subtotals and repeat all labels, how shud i write? trying out these for repeating all labels: pt.RowAxisLayout(win32.constants.xlRepeatLabels) pt.ColumnAxisLayout(win32.constants.xlRepeatLabels), still getting error, do you have any ways to not show subtotals and repeating all labels ?
How can I get first row number after applying filter in Excel sheet using xlwings?
Hi Thank you for your videos. Could you also share how is your vscode is configured for python dev.
@jiejenn
2 жыл бұрын
My Python setup is a little bit messy since there are many moving pieces, but I will probably find sometime one of these days share my Python setup + the add-ons I used.
Thank you sir
Thank you so much for publishing this video. Need your help to hide the subtotals, I used Worksheets("Sheet1").Activate.ActiveCell.PivotField.Subtotals(1) = False but its not working. Please provide the code if you have already.
how can i find distinct count ?
Hi, if i don't want subtotal then what to do?
Hey hi , I cant see any pivot table generated . I tried the exact same code
Great!!! I have a question...How to add filter in pivote table with py?
@amiteshtripathi469
2 жыл бұрын
how can i find distinct count ?
How did you get autocompletion for win32 attributes? For example, when you Type, xlApp.Visible, it should suggest .Visible but mine is not doing that. Please help..
@jiejenn
2 жыл бұрын
Hey Anh, in VS Code, I am using TabNine and IntelliCode extensions to auto populate members of the pywin32 package.
How to use "Show values as (example: % of Row Total)", like Value Field Settings when right clicking on field?
@jiejenn
Жыл бұрын
Will probably have to look into this.
Can you tell how to add Filters just like columns and row and values in pt?
@laconispe06
Жыл бұрын
Filters must have their Orientation = 3. Then, if you want to filter based on the 'program_area': field_filters = {} field_filter[''program_area''] = pt.PivotFields("Program Area") field_filters['program_area'].Orientation = 3
@ABC9923XYZ
Жыл бұрын
@@laconispe06 Thanks man
@ashleycarter9726
4 ай бұрын
@jiejenn @laconispe06 thanks, this helped. How do you then add a specific filter? Say i wanted to filter on a specific word ?
Can you please tell that how we can create pivot with win32 in python when data set is larger and not fit in excel (more then 1 million records) Bty this is the great video and very help full
@jiejenn
11 ай бұрын
Use pandas.
@prashantdonga2575
10 ай бұрын
So win32 is not going to work for this??
While saving excel through win32, how to avoid getting the save as dialog box and directly save it
@jiejenn
Жыл бұрын
There is a method called "application.DisplayAlerts", just set that to False will turn off notification. Makes sure you set that back on.
Hi Jie, do you have any ideas on how I would be able to group Pivot Table Fields of Months and Years? I've been trying: Test = wsPivotTable.Range("B2") Test.Group(Start=True, End=True, Periods=list([False, False, False, False, True, False, True])) and it would group the Months only, but not the Years, even though I have its corresponding value to True....
@jiejenn
2 жыл бұрын
I am not in my office today, but I will take a look when I get a chance.
@jiejenn
2 жыл бұрын
Change your syntax to this Test.DataRange.Cells(1).Group((False, False, False, False, True, False, False))
@intoperspective2023
2 жыл бұрын
@@jiejenn Thanks for getting back to me. I tried that and I'm getting this: Traceback (most recent call last): File "c:\Python\PivotTable.py", line 64, in Test.DataRange.Cells(1).Group((False, False, False, False, True, False, False)) File "C:\Users\LaMisa\AppData\Local\Programs\Python\Python310\lib\site-packages\win32com\client\dynamic.py", line 639, in __getattr__ raise AttributeError("%s.%s" % (self._username_, attr)) AttributeError: Range.DataRange
@amiteshtripathi469
2 жыл бұрын
how can i find distinct count ?
Any way to add a slicer for the pivot tables? Any help is appreciated. Thank you.
@jiejenn
Жыл бұрын
You can look into the slicer object. More information here: learn.microsoft.com/en-us/office/vba/api/excel.slicer
@seekerj1643
Жыл бұрын
@@jiejenn ok thank you. I’ll have to figure out how to run this in the Python format but no worries.
Is there a way to do this with a io.BytesIO file?
@jiejenn
Жыл бұрын
You will need to be specific.
Can someone do this in an Linux/Ubuntu environment
@jiejenn
Ай бұрын
For Linux, you can use xlsxwriter if Excel is supported.