Read excel with openpyxl

There is a module called openpyxl.

It allows you to read and write Excel in Python. For the time being, I used this module to read an existing Excel file.

In addition, it seems that some people are writing articles on Qiita about how to create an Excel file using openpyxl. If you are interested, please go there.

First of all, basic (installation, import, file reading)

Installation

  > easy_install openpyxl

import

  import openpyxl

Read file

  book = openpyxl.load_workbook(filename)
  sheet = book.active #Get the active sheet object

Access cell, turn with for

Access to the cell

  print(sheet.cell("A1").value)

Turn with for

  for row in sheet.rows:
    for cell in row:
      print(cell.value, end=" ")
    print("")

Precautions for use

Can I use the table written in the Excel worksheet?

It can not be used.

When I read the document, it seems that I can create a table, but I can only operate the table created there.

If you can't use the table, where is the end when you turn it in a for loop?

** It seems to be the bottom right cell where the value is written **.

How to read rows and columns merged cells?

It is not possible to know from openpyxl whether a cell is row / column merged **.

However, when row / column merge is performed, all values of the merged cells other than the upper left cell will be None. So, isn't it possible to guess the value set in the cell based on that?

For example

For example, if you have the following column ... image.png

  for row in sheet.rows:
    for cell in row:
      print(cell.value, end=" ")
    print("")

When you run, the output looks like this:

A 1 2
B 3 None
None 5 6
C 7 8
None 9 0

It will be a form of guessing what was written in the merged cell from this point.

Recommended Posts

Read excel with openpyxl
Operate Excel with Python openpyxl
Excel with Python
[Easy Python] Reading Excel files with openpyxl
[Python] How to read excel file with pandas
Handle Excel with python
Read Excel name / cell range with Python VBA
Operate Excel with Python (1)
Operate Excel with Python (2)
Read csv with python pandas
Graph Excel data with matplotlib (1)
Read image coordinates with Python-matplotlib
Let's run Excel with Python
Read json data with python
How to read an Excel file (.xlsx) with Pandas [Python]
solver> Link> Solve Excel Solver with python
Create an Excel file with Python3
Let's play with Excel with Python [Beginner]
[python] Read information with Redmine API
Handle Excel CSV files with Python
[For beginners] Read Excel / CSV files into DataFrame with Google Colaboratory
Excel, csv import, export with Django
Read fbx from python with cinema4d
Read system environment variables with python-Part 1
Read system environment variables with python-Part 2
Excel aggregation with Python pandas Part 1
Read CSV file with python (Download & parse CSV file)
Draw Nozomi Sasaki in Excel with python
Quickly create an excel file with Python #python
Convert Excel data to JSON with python
[C] [python] Read with AquesTalk on Linux
Let's read the RINEX file with Python ①
Read and write csv files with numpy
Excel aggregation with Python pandas Part 2 Variadic
Create Excel file with Python + similarity matrix
[Python] Read images with OpenCV (for beginners)
Create an add-in-enabled Excel instance with xlwings
[Automation] Read a Word document with Python
[Easy Python] Reading Excel files with pandas
Excel table creation with Python [Progress management table]
Read a character data file with numpy
How to read problem data with paiza
By linking Maya with Excel or spreadsheets ~
Read text in images with python OCR
Read CSV and analyze with Pandas and Seaborn
[Automation] Read mail (msg file) with Python