In the past, in many financial, marketing, and administrative jobs, interviewers would ask, “Do you know anything about EXCEL?”

But today, they might be more likely to ask, “Do you know Python?”

More and more enterprises are using Python to process data, especially in the fields of finance, securities, commerce, and the Internet. Python has become standard for top jobs at top companies:

What is it about Python that makes it so popular?

Here’s an example:

In the past, if the boss wanted to get nearly 2 years’ worth of data from all the stocks in A-shares, you might have to query-download-record it into an Excel loop hundreds of times, even if you were an emotionless copy robot, which would take A day or two.

But if you’ve mastered Python, all you need to do is write a script and have your data downloaded over a cup of coffee. Combined with Python’s powerful graphing capabilities, you can collect, collate, analyze, and plot data all at once, presenting the results directly on a graph.

In this article, we’ve summarized third-party libraries and methods for manipulating Excel files using Python.

First, let’s learn how to create and save Excel documents in Python.

For people who work with data a lot, Excel is the go-to tool; The OpenPyXL library is a great tool for programmers who work with data. XLSX, XLSM, XLTX, XLTM, XLSX, XLTM, XLSX, XLTM

knowledge

  • Create/open a workbook
  • Access worksheet elements and their values
  • Save worksheet

Let’s start by learning about simple create and save functions.

Create/open a workbook

First, download the Shiyanlou. XLSX sample file required for the experiment, and install the specified version of the OpenPyXL library.

! wget -nc "https://labfile.oss.aliyuncs.com/courses/1585/shiyanlou.xlsx" ! PIP install openpyxl = = 3.0.3Copy the code

To use OpenPyXL, you don’t need to create a file on the file system, just import the Workbook class and get to work:

Teaching Code:

From OpenPyxl import Workbook wb = Workbook() print(wb)Copy the code

You can also open the existing local workbook for experimental operations:

from openpyxl import load_workbook
​
wb = load_workbook(filename='shiyanlou.xlsx')
​
print(wb)Copy the code

The following parameters can be used in load_workbook:

  • Data_only: Whether the cell with a formula has a formula (the default does) or the value that was stored the last time Excel read the worksheet.

  • Keep_vba: Sets whether to keep any Visual Basic elements (by default). Keep_vba is optional but does not support editing.

Workbook creation always creates a worksheet named Sheet by default, which can be obtained by using the workbook. active property:

Ws = wb.active print(ws)Copy the code

You can also use workbook.create_sheet () to create and name a sheet. If you do not set the name parameter, the default name is sheet, sheet1, sheet2… The created worksheet location is always inserted to the end by default:

Ws = wb.create_sheet() # sheet ws1 = wb.create_sheet("Mysheet") # name Mysheet ws2 = wb.create_sheet("Mysheet1") Ws3 = wb.create_sheet("Mysheet2", -1) # create Mysheet2 worksheet and insert it into the second to last position ws. Title = "shiyanlou" # rename the ws worksheet to shiyanlou print("Success")Copy the code

By default, the label background color of the worksheet is white. We can change the color with the worksheet.sheet_properties. tabColor property:

Ws.sheet_properties. TabColor = "1072BA" # RGB Format print(ws.sheet_properties. TabColor)Copy the code

After we name the worksheet, we can use it as a key for the workbook to simplify subsequent code, such as:

ws = wb["shiyanlou"]
print(ws)Copy the code

To view all worksheets in this Workbook, use the workbook.sheetName function:

print(wb.sheetnames)Copy the code

It is also possible to print all worksheets iteratively

for sheet in wb:
    print(sheet.title)Copy the code

We can create a copy of a worksheet in a single Workbook using the workbook.copy_worksheet () method:

Target = wb.copy_worksheet(source) print(target)Copy the code

Access worksheet elements and their values

Now that we’ve seen how to create a fetch workbook and worksheet, we’ll look at modifying the contents of a cell. The following operations are based on the Shiyanlou worksheet in the Shiyanlou. XLSX workbook.

Cells can be accessed directly as worksheet keys, using the value attribute for value access:

C = ws['A4'] # ws['A4'] # ws['A4'] #Copy the code

Note: When a worksheet is created in memory, it does not contain any cells, which are automatically created on first access.

The above code can also be implemented with simpler code:

Cell (row=4, column=2, value=10) # B4 ws['B4']Copy the code

Access multiple cells

You can use slices to access all cells ranging from A1 to C2:

cell_range = ws['A1':'C2']
​
cell_rangeCopy the code

The range of rows or columns can be similarly obtained:

Print (col_range, row_range) print(col_range, row_range)Copy the code

You can also use worksheet.iter_rows () to return rows:

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell_row in row:
        print(cell_row)Copy the code

Return columns using worksheet.iter_cols () :

for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell_col in col:
        print(cell_col)Copy the code

If you need to iterate over all rows or columns of a Worksheet, you can use the worksheet.rows attribute. For performance reasons, this property is not available in read-only mode:

ws['C9'] = 'hello world'
tuple(ws.rows)Copy the code

Or use the worksheet.columns attribute. For performance reasons, this property is not available in read-only mode:

tuple(ws.columns)Copy the code

Access to the value

If you only need the values from the Worksheet, you can use the worksheet. values attribute.

Iterate over all rows in the worksheet, but only return cell values:

for row in ws.values:
    for value in row:
        print(value)Copy the code

Worksheet.iter_rows() and worksheet.iter_cols () can use values_only to return only the value of the cell:

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
    print(row)Copy the code

Save worksheet

Save the workbook we created earlier as an XLSX file or overwrite it if it already exists:

wb.save("shiyanlou.xlsx")Copy the code

Wenyuan network, only for the use of learning, such as infringement, contact deletion.

I have compiled good technical articles and lessons learned on my public account, Python Circle.

You will definitely encounter difficulties in learning Python. Don’t panic, I have a set of learning materials, including 40+ e-books, 600+ teaching videos, covering Python basics, crawlers, frameworks, data analysis, machine learning, etc.