add macro openpyxl

• Stack Overflow Public questions & answers
• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Talent Build your employer brand
• Advertising Reach developers & technologists worldwide
• Labs The future of collective knowledge sharing
• About the company

New search experience powered by AI

Stack Overflow is leveraging AI to summarize the most relevant questions and answers from the community, with the option to ask follow-up questions in a conversational format.

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

How to save XLSM file with Macro, using openpyxl

I have .xlsm file with a Macro function. I'm loading it using openpyxl and write some data to the file and finally want to save as a different .xlsm file.

To save the file as XLSM file I have used below code in my Python script.

But I cannot open that file if I saved as above. But if I saved it as .xlsx then I can open the file without the Macro function that original file had.

I want to open a Excel sheet that has Macro function, edit the file and save it as new .xlsm file using openpyxl . How can I do that?

6 Answers 6

For me this worked, together with version openpyxl==2.3.0-b2

It is also mentioend in the documentation here: http://openpyxl.readthedocs.org/en/latest/usage.html?highlight=keep_vba#write-a-workbook-from-xltm-as-xlsm

• Is it possible to call and run the macro from python thereafter? –  ctrl-alt-delete May 25, 2016 at 8:08
• If you have MS Office installed and running Windows then you can run it with the OLE api . Otherwise you have to re-engineer the macro code with openpyxl in Python. –  eule May 26, 2016 at 19:15
• This link does not go to anything related to xlsm ! In fact, if you go to openpyxl.readthedocs.io/en/latest/usage.html?highlight=xlsm You'll see, nothing is higlighted, since there is no anything about xlsm. –  Srđan Popić Feb 20, 2018 at 8:24
• I think they removed that section about xlsm now, but still mention that the file type is supported. Important seems to set the keep_vba flag explicitly as it defaults to false. openpyxl.readthedocs.io/en/latest/usage.html?highlight=keep_vba –  eule Feb 21, 2018 at 10:22
• 1 This method does not always keep the ActiveX components like the buttons connected to the code. Might be the same as the openpyxl bug of buttons being converted to images. –  Prof Mar 28, 2019 at 1:19

I don't know if this is still relevant for the person that asked the question, but I am dealing with the same problem and found a possible solution.

• open the original file (say: 1.xlsm ) and do magic with openpyxl;
• save as 2.xlsx ;
• both files are actually zipped files: extract them to a temporary directory;
• copy files from the directory of the original file to the directory of the xlsx files: one of the files is the macro ( vbaProject.bin ) and 2 of the files are necessary because they describe the type of the file among other things;
• put all of the files that belong to the xlsx directory back into a zip file and rename this from zip to xlsm . This file contains the original macro and has been edited with openpyxl ;
• ( Optional ) delete the two temporary directories and the 2.xlsx file.

Example code:

• 1 This solution is closest that I found, but still, I am getting some errors on file, that excel tries to recover. During this recovery, it (sometimes, not always) mess up the macros! –  Srđan Popić Feb 20, 2018 at 8:26

I had the same issue when editing xlsm files using openpyxl. I tried most of the solutions/workarounds available in stackoverflow and in other forums. But none of them worked. Then I found xlwings , this python library handles xlsm document, it preserve all the macros.

• Error : TypeError: 'Book' object is not subscriptable –  Vineesh TP Sep 3, 2019 at 11:02
• it worked for me. try this if approach above was of no success –  im_infamous Nov 20, 2020 at 16:45
• Of note, potentially a good solution but doesn't work on Linux as XLWings doesn't work there. –  T. Shaffner Mar 12, 2021 at 5:07
• @T.Shaffner yes... it needs an installation of Excel (and there is none on Linux) –  Jean-Francois T. May 20, 2022 at 2:48
• Thank you, thank you, thank you! Super useful for programs that do a lot of work in Excel. –  king_anton Jan 13 at 18:02

That's right, openpyxl cannot read and write VBA code.

According to this thread :

I think you should not give the xlsM extension, because the file will contain no VBA code. openpyxl is used to build xlsX files only.

Give a try to this fork instead: if you pass keep_vba=True parameter to load_workbook it should do the job.

Hope that helps.

• 2 It didn't work for me. Here is my code for that wb = load_workbook('template.xlsm', keep_vba = True); –  AnujAroshA Jul 16, 2013 at 12:35
• 1 If this fork doesn't work - then, there is no way to make it work using openpyxl . –  alecxe Jul 16, 2013 at 12:39
• Cant find that fork but there is bitbucket.org/amorris/editpyxl/src/master –  rleir Jan 14, 2021 at 20:23

if your initial excel was created with python you might need to add the workbook.xml as well and parse the sheet xlms:

it's better to use xlwings to work with xlsm documents. I have tested it.

import xlwings as xw

wb = xw.Book(DATA.xlsm)

Your Answer

Sign up or log in, post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct .

Not the answer you're looking for? Browse other questions tagged python openpyxl xlsm or ask your own question .

• The Overflow Blog
• Behind the scenes with OverflowAI Search
• Featured on Meta
• If more users could vote, would they engage more? Testing 1 reputation voting...
• Alpha test for short survey in banner ad slots starting on week of September...
• Temporary policy: Generative AI (e.g., ChatGPT) is banned
• Expanding Discussions: Let's talk about flag reasons
• Collectives Updates to the Community Bulletin in the Right Sidebar
• OverflowAI Search is now available for alpha testing (September 13, 2023)

Hot Network Questions

• Is it possible to redefine the limit operator to print Lim, not lim?
• Packing Osiris Rex capsule after landing
• A Trivial Pursuit #11 (Science and Nature 2/4): The Search
• Delete list elements above a certain threshold
• When was the end of the floppy-only IBM PC clone?
• What's the most recent version of AmigaOS?
• How to set up a virtual payment card on a phone that a child can use abroad (Poland) to withdraw cash and pay in shops?
• What attracted Abimelech to an old woman in her 80s or 90s?
• How to get tofu to absorb flavour?
• Aligment of the hightlight in the TikZ figure
• Is it illegal to have a product delivered to a different ZIP code to pay less sales tax?
• Space-ships and stations...made from cast-iron?
• Is it possible to work on your personal idea as PhD thesis?
• Very old telefilm about an invisible monster stalking a horror movie set
• why use template in year_month class?
• Resolve a "the eggs take longer than the pizza" issue?
• Lawn: Newly sown turf grows at least twice as fast as the "old" turf
• What is the force of gesehen haben muss : You [one has] gotta see, You ought to have seen, or you must already have seen?
• Flaps Cessna 172 breaker tripping
• How can I properly translate possessive form of nouns?
• Alien invasion novel, teens fight against blobs that enter people heads
• Word Implying Lack of Caring Because You're Already (Wrong/Losing/a Failure/Behind/Insignificant)
• Can the collapse of the wave function be modelled as a quantum system on its own?
• How to identify constraints that make problem not solvable in polynomial time?

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy .

Python – How to save XLSM file with Macro, using openpyxl

openpyxl python xlsm

I have .xlsm file with a Macro function. I'm loading it using openpyxl and write some data to the file and finally want to save as a different .xlsm file.

To save the file as XLSM file I have used below code in my Python script.

But I cannot open that file if I saved as above. But if I saved it as .xlsx then I can open the file without the Macro function that original file had.

I want to open a Excel sheet that has Macro function, edit the file and save it as new .xlsm file using openpyxl . How can I do that?

Best Solution

For me this worked, together with version openpyxl==2.3.0-b2

It is also mentioend in the documentation here: http://openpyxl.readthedocs.org/en/latest/usage.html?highlight=keep_vba#write-a-workbook-from-xltm-as-xlsm

Related Solutions

Python – how to check whether a file exists without exceptions.

If the reason you're checking is so you can do something like if file_exists: open_it() , it's safer to use a try around the attempt to open it. Checking and then opening risks the file being deleted or moved or something between when you check and when you try to open it.

If you're not planning to open the file immediately, you can use os.path.isfile

Return True if path is an existing regular file. This follows symbolic links, so both islink() and isfile() can be true for the same path.

if you need to be sure it's a file.

Starting with Python 3.4, the pathlib module offers an object-oriented approach (backported to pathlib2 in Python 2.7):

To check a directory, do:

To check whether a Path object exists independently of whether is it a file or directory, use exists() :

You can also use resolve(strict=True) in a try block:

Python – How to copy a file in Python

shutil has many methods you can use. One of which is:

• Copy the contents of the file named src to a file named dst . Both src and dst need to be the entire filename of the files, including path.
• The destination location must be writable; otherwise, an IOError exception will be raised.
• If dst already exists, it will be replaced.
• Special files such as character or block devices and pipes cannot be copied with this function.
• With copy , src and dst are path names given as str s.

Another shutil method to look at is shutil.copy2() . It's similar but preserves more metadata (e.g. time stamps).

If you use os.path operations, use copy rather than copyfile . copyfile will only accept strings.

Related Question

• Python – How to safely create a nested directory in Python
• Python – How to change the size of figures drawn with Matplotlib
• Python – How to parse XML and count instances of a particular node attribute
• Python – How to upgrade all Python packages with pip
• Python – How to delete a file or folder in Python
• Python – Save plot to image file instead of displaying it using Matplotlib

Feature to add VBA functions to a workbook

David Michael Pennington

Hi David, I guess you have good reasons not to make the calculation in python and just write the result in another cell :)

To be honest,I never tried to put vba in an openpyxl workbook,and I can't estimate the cost of adding such a feature for now,but I assume the effort would be quite big :/

If you need a quick solution for your problem,you'd better go with the insane Excel formula as answered on SU :) if you have more time in your hands,then you can file a ticket on the bugtracker and I can make some research on the topic,but I have many critical stuff to fix before :(

Cheers, Eric

On Dec 14, 1:54 am, Eric < [email protected] > wrote: > Hi David, > I guess you have good reasons not to make the calculation in python and > just write the result in another cell :) >

> To be honest,I never tried to put vba in an openpyxl workbook,and I can't > estimate the cost of adding such a feature for now,but I assume the effort > would be quite big :/ > > If you need a quick solution for your problem,you'd better go with the > insane Excel formula as answered on SU :) if you have more time in your > hands,then you can file a ticket on the bugtracker and I can make some > research on the topic,but I have many critical stuff to fix before :( >

Well I was hoping to contribute some code, even if in a private branch assuming someone understood the scope of the problem. I could use the formulas, but that solution is only marginally acceptable in the situation that someone adds another value in the comma-separated list.

• start with an empty workbook with the VBA function already in
• write your workbook with formulas in openpyxl, callling the VBA function (which is not yet in there)
• still while in Python, take the vbaProject.bin file from your empty workbook, and copy it into your new workbook in the same location
• maybe fiddle a bit with the .ref files to add relations to your vbaProject.bin file

Apparently, VBA is compiled in Excel in a proprietary binary format, and is not part of the OOXML specification (assumptions based on http://techrights.org/2007/01/11/proprietary-open-xml-extensions-already/ , have not checked the specs to confirm, but this seems highly probable).

Hope it will get you started, let me know if you need more help ;-)

[email protected]

Charlie Clark

Am .08.2015, 22:25 Uhr, schrieb John Bovey < [email protected] >: Possibly the problem is in the workbook.xml.rels file. In Filipe's version of openpyxl the type for target vbaProject is: http://schemas.openxmlformats.org/officeDocument/2006/relationships/vbaProject In the current branch 2.3 it is http://schemas.microsoft.com/office/2006/relationships/vbaProject

Am .08.2015, 13:59 Uhr, schrieb John Bovey < [email protected] >: Filipe's code is ok, as is the latest version of the openpyxl code. He just needs to get the latest version. I just was not sure of the best way for him to do that.

[Example code]-openpyxl and excel macros

Related posts.

• Creating an output json file from list of string objects
• Commands don't work in vscode powershell terminal
• How to add target to Default context in ZAPapi using python?
• How to disable contextMenu from QWebEngineView?
• Given bytes that map to a C struct, how to access the bytes via the C struct's variable names
• In Python how do you convert strings in a list of list to other types, such as ints, floats, booleans, etc?
• Refresh Main Window When Secondary Window Is Closed PyQt5
• How to find if two Numpy arrays have same items?
• Multiple MongoDB database triggers in same Python module
• How to send and receive some data via a GSM modem during an active call (Python and AT Command)
• Overwrite create method of res.users to add users upon creation to a group
• Is it possible to effectively initialize bytearray with non-zero value?
• Is 'encoding is an invalid keyword' error inevitable in python 2.x?
• python Dictionary for printing inventory if value is less than input
• How to move the entire window to a place on the screen (Tkinter, Python3)

Other Popular Tags

• Calculating Big O Notation with Recursion
• How to run a method before/after all class function calls with arguments passed?
• Python: Importing subprocess modules from v3.3 to v2.7.4
• Docker container always shows ssl connection error
• Python async co-routine execution time
• Missing test coverage pytest python class
• Type hinting Callable with no parameters
• How Can I connect to Sqlite3 database over SSH using Python?
• Problems with pip und Command line
• cx_Oracle: Error 933. ORA-00933: “SQL command not properly ended”: SQL command error?

machine_learning

• Weka NaiveBayes Classifier giving different (wrong?) means/standard-deviation on numerical values
• Text selection / personalisation for different users
• What's the difference between local and global basis functions in a regression?
• Mallet Trained Model Load
• Is it possible to write a Java client to access Tensorflow Server?
• Can I use reinforcement learning in tensorflowjs?
• parameter passing in python
• Python RandomForest sk-learn: stuck for a few hours, what is going on?
• Unsupervised Learning in R? Classify Matrices - what is the right package?
• how does sklearn's Adaboost predict_proba works internally?

I am on winodws 10, Python 3.7. When i work with openpyxl and .xlsm files, once my xlsm is edited/saved through openpyxl, all buttons assigned to macros disappear (the macros are still there). Here is my test code: import openpyxl from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook(filename='my_file.xlsm', keep_vba=True) ws = wb["sheet1"] c = ws.cell(row = 1, column = 1) c.value = "test" wb.save('my_file.xlsm') Any opinions on why the buttons disappear? Thanks.

When you save the file. It basically saves as a new file instead of replacing the old one. Try saving with a different name and then you can see the difference.

Related Query

• openpyxl and excel macros
• find images and delete images in excel using openpyxl
• Write to an existing excel file using Openpyxl starting in existing sheet starting at a specific column and row
• how to append data using openpyxl python to excel file from a specified row?
• Python: How to read and load an excel file from AWS S3?
• openpyxl - adding new rows in excel file with merged cell existing
• Insert an image into Excel with openpyxl
• Reading colours of cell in an excel sheet using python: openpyxl or any other library
• openpyxl causing loss of existing DataValidation in excel
• Conditional parsing and output of xlsx files with Openpyxl
• which is a good library to read and write MS excel files with Python 3
• Openpyxl created excel file with table causes file that requires recovery error
• openpyxl - overwrite datasheet and preserve pivot table
• Losing images after loading and saving a workbook in openpyxl
• Getting Excel cell background themed color as hex with openpyxl
• How to copy csv contents and paste to Excel sheet?
• Place value into Excel cell through xlwings based on row number and column number
• I have imported an excel file in python and I am trying to make the 7th ROW the starting Columns. I need to hide the first 6 rowsHow can i do that?
• how to delete columns by column name from an excel using python openpyxl
• How to search a set of input strings in an excel file and return all the matched rows using Python?
• How to access an Excel file on Onedrive with Openpyxl
• reading and writing excel files from s3 using boto3 in lambda
• OPENPYXL For loop that goes through each cell in a specified column and replaces its value with an uppercase one
• I want to draw diagonal lines to Excel cells with openpyxl module in python
• using openpyxl Tokenizer to parse Excel IF statements
• Python program to read Excel files and copy their sqls into new text file
• reading 300k cells in excel using read_only in openpyxl not enough
• OpenPyXL - How to delete rows from an Excel file based on some condition?
• Read Excel Cells and Copy content to txt file
• Getting value from Excel cell with formula via openpyxl
• How to read from excel and write in json file using python?
• Increase the speed of an excel file operations (using openpyxl): check value and delete rows operations if condition
• Open excel file in Tkinter and plot graphs
• Reading an excel spreadsheet and formatting the text from the cells
• Python and openpyxl - how can I copy the style when I "append" a row in a new file?
• How to return results of web scraping in loop and save to Excel file?
• How to replace string in Excel and check whole cell string?
• How to delete first row in excel with openpyxl
• Issues with reading from excel CSV and writing to another
• Python to get no. of characters of files ending with .docx from a directory and divide each file's characters by 65 and save them to an Excel File
• How to print a random element from a list on to excel file rows on openpyxl
• Python Openpyxl Apply Filter and delete all visible rows
• Python excel drop down filter not working with openpyxl nor with xlsxwriter
• How do i get a table and put it in excel without long code
• How do I write to an existing excel file using xlwt and keep the formatting?
• How to detect if excel has used any filter in openpyxl
• I’m trying to generate a standalone exe file from multiple python files and additional files (image, excel spreadsheet)
• Python Writing Rows and Cols to Excel
• How to compare two excel data and write into new excel file
• python3 and pywin32 closing excel

More Query from same tag

• What could be the reason for fatal python error:initfsencoding:unable to load the file system codec?
• Set colors according to values in a ColumnDataSource in Bokeh's scatter plot
• How does this "for loop" index through the different lines of the object
• Prevent print() statement from overwriting text from input() statement
• `pip list` listing packages which are actually not installed
• Global variable not changing between files in python
• Python: SyntaxError 'return' outside function error
• Mitmproxy how to launch from script, and save dumps to file
• Finding all numbers that evenly divide a number
• Ordering by frequency rather than alphabetically in a set (in Python)
• TypeError in Python 3.x
• How to distinguish np.ndarray or None?
• name 'guild' is not defined
• Ignore an error generated by a bot in python3 with discord.py
• What does "**" mean in python?
• What does -h in lambda (h, t): (-h, t) do?
• PyQt5 opening second window from main
• Python(3.6)-How can I create a demo program that will create an object from the VotingMachine class I have created?
• How To Adding Timestamp when Webstream Video Raspberry-Pi Python?
• Slicing where stop is larger than the string length
• Python 3 arcgis.gis.user for lastLogin accessing ArcGIS Portal
• Why when I do 4 clusters clustering with K-means, I have only one intertia and not 4?
• Removing an item from dictionary in Py3 during iteration
• Writing ByteIO as audio python for use with Bluemix
• How to extract digits separated by a dot followed by a specific set of words regex python?
• Getting errors while trying to update column in DynamoDb using python client
• How would I Recursively get unique items out of nested lists?
• Can I create an object if I don't have all instance variables values available at __init__ time?
• install django widget twraks
• Why the python program is not creating any pixel?
• Rewriting some functions for xlsxwriter box borders from Python 2 to Python 3
• Making classes within classes communicate
• Joining elements in list of Strings in loop with a condition
• How do I initialize and fill in a list of lists of different lengths?
• AttributeError: 'ElementTree' object has no attribute 'tag' in Python

scikit_learn

Beautifulsoup.

Copyright 2023 www.appsloveworld.com . All rights reserved.

• PyQt5 ebook
• Tkinter ebook
• SQLite Python
• wxPython ebook
• Windows API ebook
• Java Swing ebook
• Java games ebook
• MySQL Java ebook

Python openpyxl

last modified July 8, 2023

In this article, we show how to work with Excel files in Python using openpyxl library.

The openpyxl is a Python library to read and write Excel 2010 xlsx/xlsm/xltx/xltm files.

In this tutorial we work with xlsx files. The xlsx is a file extension for an open XML spreadsheet file format used by Microsoft Excel. The xlsm files support macros. The xls format is a proprietary binary format while xlsx is based on Office Open XML format.

We install openpyxl with the pip tool.

Openpyxl create new file

In the first example, we create a new xlsx file with openpyxl .

In the example, we create a new xlsx file. We write data into three cells.

We import the Workbook class from the openpyxl module. A workbook is the container for all other parts of the document.

A new workbook is created. A workbook is always created with at least one worksheet.

We get the reference to the active sheet with active property.

We write numerical data to cells A1 and A2.

We write current date to the cell A3.

We write the contents to the sample.xlsx file with the save method.

Openpyxl write to a cell

There are two basic ways to write to a cell: using a key of a worksheet such as A1 or D3, or using a row and column notation with the cell method.

In the example, we write two values to two cells.

Here, we assing a numerical value to the A1 cell.

In this line, we write to cell B2 with the row and column notation.

Openpyxl append values

With the append method, we can append a group of values at the bottom of the current sheet.

In the example, we append three columns of data into the current sheet.

The data is stored in a tuple of tuples.

We go through the container row by row and insert the data row with the append method.

Openpyxl read cell

In the following example, we read the previously written data from the sample.xlsx file.

The example loads an existing xlsx file and reads three cells.

The file is opened with the load_workbook method.

We read the contents of the A1, A2, and A3 cells. In the third line, we use the cell method to get the value of A3 cell.

Openpyxl read multiple cells

We have the following data sheet:

We read the data using a range operator.

In the example, we read data from two columns using a range operation.

In this line, we read data from cells A1 - B6.

The format function is used for neat output of data on the console.

Openpyxl iterate by rows

The iter_rows function return cells from the worksheet as rows.

The example iterates over data row by row.

We provide the boundaries for the iteration.

Openpyxl iterate by columns

The iter_cols function returns cells from the worksheet as columns.

The example iterates over data column by column.

For the next example, we need to create a xlsx file containing numbers. For instance, we have created 25 rows of numbers in 10 columns with the RANDBETWEEN function.

In the example, we read all values from the sheet and compute some basic statistics.

The statistics module is imported to provide some statistical functions, such as median and variance.

Using the data_only option, we get the values from the cells, not the formula.

We get all the rows of cells that are not empty.

In two for loops, we form a list of integer values from the cells.

We compute and print mathematical statistics about the values. Some of the functions are built-in, others are imported with the statistics module.

Openpyxl filter & sort data

A sheet has an auto_filter attribute, which allows to set filtering and sorting conditions.

Note that Openpyxl sets the conditions but we must apply them inside the Spreadsheet application.

In the example, we create a sheet with items and their colours. We set a filter and a sort condition.

Openpyxl dimensions

To get those cells that actually contain data, we can use dimensions.

The example calculates the dimensions of two columns of data.

We add data to the worksheet. Note that we start adding from the third row.

The dimensions property returns the top-left and bottom-right cell of the area of non-empty cells.

Witht the min_row and max_row properties, we get the minimum and maximum row containing data.

With the min_column and max_column properties, we get the minimum and maximum column containing data.

We iterate through the data and print it to the console.

Each workbook can have multiple sheets.

Let's have a workbook with these three sheets.

The program works with Excel sheets.

The get_sheet_names method returns the names of available sheets in a workbook.

We get the active sheet and print its type to the terminal.

We get a reference to a sheet with the get_sheet_by_name method.

The title of the retrieved sheet is printed to the terminal.

In this example, we create a new sheet.

A new sheet is created with the create_sheet method.

The sheet names can be shown with the sheetnames attribute as well.

A sheet can be removed with the remove_sheet method.

A new sheet can be created at the specified position; in our case, we create a new sheet at position with index 0.

It is possible to change the background colour of a worksheet.

The example modifies the background colour of the sheet titled "March".

We change the tabColor property to a new colour.

The background colour of the third worksheet has been changed to some blue colour.

Merging cells

Cells can be merged with the merge_cells method and unmerged with the unmerge_cells method. When we merge cells, all cells but the top-left one are removed from the worksheet.

In the example, we merge four cells: A1, B1, A2, and B2. The text in the final cell is centered.

In order to center a text in the final cell, we use the Alignment class from the openpyxl.styles module.

We merge four cells with the merge_cells method.

We get the final cell.

We set text to the merged cell and update its alignment.

Openpyxl freeze panes

When we freeze panes, we keep an area of a worksheet visible while scrolling to another area of the worksheet.

The example freezes panes by the cell B2.

To freeze panes, we use the freeze_panes property.

Openpyxl formulas

The next example shows how to use formulas. The openpyxl does not do calculations; it writes formulas into cells.

In the example, we calculate the sum of all values with the SUM function and style the output in bold font.

We create two columns of data.

We get the cell where we show the result of the calculation.

We write a formula into the cell.

We change the font style.

Openpyxl images

In the following example, we show how to insert an image into a sheet.

In the example, we write an image into a sheet.

We work with the Image class from the openpyxl.drawing.image module.

A new Image class is created. The icesid.png image is located in the current working directory.

We add a new image with the add_image method.

Openpyxl Charts

The openpyxl library supports creation of various charts, including bar charts, line charts, area charts, bubble charts, scatter charts, and pie charts.

According to the documentation, openpyxl supports chart creation within a worksheet only. Charts in existing workbooks will be lost.

In the example, we create a bar chart to show the number of Olympic gold medals per country in London 2012.

The openpyxl.chart module has tools to work with charts.

A new workbook is created.

We create some data and add it to the cells of the active sheet.

With the Reference class, we refer to the rows in the sheet that represent data. In our case, these are the numbers of olympic gold medals.

We create a category axis. A category axis is an axis with the data treated as a sequence of non-numerical text labels. In our case, we have text labels representing names of countries.

We create a bar chart and set it data and categories.

Using legend and majorGridlines attributes, we turn off the legends and major grid lines.

Setting varyColors to True , each bar has a different colour.

A title is set for the chart.

The created chart is added to the sheet with the add_chart method.

In this tutorial we have worked with the openpyxl library. We have read data from an Excel file, written data to an Excel file.

My name is Jan Bodnar and I am a passionate programmer with many years of programming experience. I have been writing programming articles since 2007. So far, I have written over 1400 articles and 8 e-books. I have over eight years of experience in teaching programming.

List all Python tutorials .

Something went wrong. Wait a moment and try again.