Python 3 openpyxl документация

Содержание

Simple usage¶
Example: Creating a simple spreadsheet and bar chart¶
openpyxl — A Python library to read/write Excel 2010 xlsx/xlsm files¶
Security¶
Mailing List¶
Documentation¶
Support¶
How to Contribute¶
Other ways to help¶
openpyxl — A Python library to read/write Excel 2010 xlsx/xlsm files¶
Security¶
Mailing List¶
Documentation¶
Support¶
How to Contribute¶
Other ways to help¶

Simple usage¶

Example: Creating a simple spreadsheet and bar chart¶

In this example we’re going to create a sheet from scratch and add some data and then plot it. We’ll also explore some limited cell style and formatting.

The data we’ll be entering on the sheet is below:

Species	Leaf Color	Height (cm)
Maple	Red	549
Oak	Green	783
Pine	Green	1204

To start, let’s load in openpyxl and create a new workbook. and get the active sheet. We’ll also enter our tree data.

>>> from openpyxl import Workbook

>>> wb = Workbook() >>> ws = wb.active >>> treeData = [["Type", "Leaf Color", "Height"], ["Maple", "Red", 549], ["Oak", "Green", 783], ["Pine", "Green", 1204]]

Next we’ll enter this data onto the worksheet. As this is a list of lists, we can simply use the Worksheet.append() function.

>>> for row in treeData: . ws.append(row)

Now we should make our heading Bold to make it stand out a bit more, to do that we’ll need to create a styles.Font and apply it to all the cells in our header row.

>>> from openpyxl.styles import Font

>>> ft = Font(bold=True) >>> for row in ws["A1:C1"]: . for cell in row: . cell.font = ft

It’s time to make some charts. First, we’ll start by importing the appropriate packages from openpyxl.chart then define some basic attributes

>>> from openpyxl.chart import BarChart, Series, Reference

>>> chart = BarChart() >>> chart.type = "col" >>> chart.title = "Tree Height" >>> chart.y_axis.title = 'Height (cm)' >>> chart.x_axis.title = 'Tree Type' >>> chart.legend = None

That’s created the skeleton of what will be our bar chart. Now we need to add references to where the data is and pass that to the chart object

>>> data = Reference(ws, min_col=3, min_row=2, max_row=4, max_col=3) >>> categories = Reference(ws, min_col=1, min_row=2, max_row=4, max_col=1)

>>> chart.add_data(data) >>> chart.set_categories(categories)

Finally we can add it to the sheet.

>>> ws.add_chart(chart, "E1") >>> wb.save("TreeData.xlsx")

And there you have it. If you open that doc now it should look something like this

Versions latest stable 3.1.2 3.1.1 3.1.0 3.1 3.0 2.6 2.5.14 2.5 2.4 Downloads html On Read the Docs Project Home Builds Free document hosting provided by Read the Docs.

Источник

openpyxl — A Python library to read/write Excel 2010 xlsx/xlsm files¶

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

It was born from lack of existing library to read/write natively from Python the Office Open XML format.

All kudos to the PHPExcel team as openpyxl was initially based on PHPExcel.

Security¶

By default openpyxl does not guard against quadratic blowup or billion laughs xml attacks. To guard against these attacks install defusedxml.

Mailing List¶

from openpyxl import Workbook wb = Workbook() # grab the active worksheet ws = wb.active # Data can be assigned directly to cells ws['A1'] = 42 # Rows can also be appended ws.append([1, 2, 3]) # Python types will automatically be converted import datetime ws['A2'] = datetime.datetime.now() # Save the file wb.save("sample.xlsx")

Documentation¶

Support¶

This is an open source project, maintained by volunteers in their spare time. This may well mean that particular features or functions that you would like are missing. But things don’t have to stay that way. You can contribute the project Development yourself or contract a developer for particular features.

Professional support for openpyxl is available from Clark Consulting & Research and Adimian. Donations to the project to support further development and maintenance are welcome.

Bug reports and feature requests should be submitted using the issue tracker. Please provide a full traceback of any error you see and if possible a sample file. If for reasons of confidentiality you are unable to make a file publicly available then contact of one the developers.

The repository is being provided by Octobus and Clever Cloud.

How to Contribute¶

Any help will be greatly appreciated, just follow those steps:

1. Please join the group and create a branch (https://foss.heptapod.net/openpyxl/openpyxl/) and follow the Merge Request Start Guide. for each independent feature, don’t try to fix all problems at the same time, it’s easier for those who will review and merge your changes 😉

2. Hack hack hack

3. Don’t forget to add unit tests for your changes! (YES, even if it’s a one-liner, changes without tests will not be accepted.) There are plenty of examples in the source if you lack know-how or inspiration.

4. If you added a whole new feature, or just improved something, you can be proud of it, so add yourself to the AUTHORS file 🙂

5. Let people know about the shiny thing you just implemented, update the docs!

6. When it’s done, just issue a pull request (click on the large “pull request” button on your repository) and wait for your code to be reviewed, and, if you followed all theses steps, merged into the main repository.

For further information see Development

Other ways to help¶

There are several ways to contribute, even if you can’t code (or can’t code well):

triaging bugs on the bug tracker: closing bugs that have already been closed, are not relevant, cannot be reproduced, …
updating documentation in virtually every area: many large features have been added (mainly about charts and images at the moment) but without any documentation, it’s pretty hard to do anything with it
proposing compatibility fixes for different versions of Python: we support 3.6, 3.7, 3.8 and 3.9.

Источник

openpyxl — A Python library to read/write Excel 2010 xlsx/xlsm files¶

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

It was born from lack of existing library to read/write natively from Python the Office Open XML format.

All kudos to the PHPExcel team as openpyxl was initially based on PHPExcel.

Security¶

By default openpyxl does not guard against quadratic blowup or billion laughs xml attacks. To guard against these attacks install defusedxml.

Mailing List¶

from openpyxl import Workbook wb = Workbook() # grab the active worksheet ws = wb.active # Data can be assigned directly to cells ws['A1'] = 42 # Rows can also be appended ws.append([1, 2, 3]) # Python types will automatically be converted import datetime ws['A2'] = datetime.datetime.now() # Save the file wb.save("sample.xlsx")

Documentation¶

Support¶

Professional support for openpyxl is available from Clark Consulting & Research and Adimian. Donations to the project to support further development and maintenance are welcome.

The repository is being provided by Octobus and Clever Cloud.

How to Contribute¶

Any help will be greatly appreciated, just follow those steps:

2. Hack hack hack

4. If you added a whole new feature, or just improved something, you can be proud of it, so add yourself to the AUTHORS file 🙂

5. Let people know about the shiny thing you just implemented, update the docs!

For further information see Development

Other ways to help¶

There are several ways to contribute, even if you can’t code (or can’t code well):

triaging bugs on the bug tracker: closing bugs that have already been closed, are not relevant, cannot be reproduced, …
updating documentation in virtually every area: many large features have been added (mainly about charts and images at the moment) but without any documentation, it’s pretty hard to do anything with it
proposing compatibility fixes for different versions of Python: we support 3.6, 3.7, 3.8 and 3.9.

Источник