Excel is one of the most widely used tools in daily office. Learning it well can help us save time, improve work efficiency, and learn to use Excel flexibly and efficiently.

I think those who can use most excel built-in formulas and VBA are experts.

But the more advanced is to use the program to operate excel. For the similar or repetitive and tedious excel tasks, one program can achieve once and for all. Exaggeration: it can save your life.

The following is the introduction of python operation excel:

1, Kit

1.xlrd: extract data doc address from Excel spreadsheet: https://xlrd.readthedocs.io/en/latest/
2.xlwt: write data to Excel spreadsheet doc address: https://xlwt.readthedocs.org/en/latest/
3.xlutils: provides a set of doc addresses for Excel files: https://xlutils.readthedocs.io/en/latest/

2, Installation

python -m pip install xlrd xlwt xlutils

3, Basic usage

The related toolkit of Python operation Excel can be specific to the operation of specified cell filling style, value type, value size, etc. However, python needs some data processing skills of pandas to operate excel, and the following chapter will be added: Pandas data processing skills

1. Read the EXCEL form from the specified file path, perform certain operations, and then save it to another excel file: result.xlsx

import xlwt
import xlrd
from xlutils.copy import copy
import pandas as pd
from pandas import DataFrame,Series
import os
os.chdir('./')
# Reading excel form from specified file path
df = pd.read_excel('D:/mypaper/data/data.xlsx')
# View df content

# Calculate the year of birth according to age, add a column
import datetime
import os
year = datetime.datetime.now().year#Get the year corresponding to the current system time
df['birth'] = year-df['age']
df.to_excel('result.xlsx')#Save to the current working directory. You can use os.getcwd() view
#Check the content of df at this time, and you can see that the birth column has been generated

At first glance, it seems that only pandas has been used, and the three toolkits mentioned above have not been used. Next, we will introduce how to use python to operate excel bottom layer

2. Cell operation

# Definition method: read the value of a sheet cell in the Excel file in the specified directory
def excel_read(file_path,table,x,y):
     data = xlrd.open_workbook(file_path)
     table = data.sheet_by_name(table)
     return table.cell(y,x).value

# Definition method: cell value and style
write_obj_list = []
def concat_obj(cols,rows,value):
    write_obj_list.append({'cols':cols,'rows':rows,'value':value,\
'style':xlwt.easyxf('font: name Tahoma,height 280;alignment: horiz centre')})

# Definition method: merge cells
def merge_unit(srows,erows,scols,ecols,value):
    write_obj_list.append({'id':'merge','srows':srows,'erows':erows,'scols':scols,\
'ecols':ecols,'value':value,'style':xlwt.easyxf('font: name Tahoma,height 280;alignment: horiz centre')})

# Definition method: update excel
excel_update(file_path,write_obj_list,new_path):
    old_excel = xlrd.open_workbook(file_path, formatting_info=True)
    #Pipeline action
    new_excel = copy(old_excel)
    '''
    //Through get_sheet() gets sheet with write() method
    '''
    sheet1 = new_excel.get_sheet(0)
    '''
    1 Representative is the first one to modify the working table from 0. Modify first sheet here
    '''
    for item in write_obj_list:
        if 'id' not in item.keys():
            if 'style' in item.keys():
                sheet1.write(item['rows'], item['cols'], item['value'],item['style'])
            else:
                sheet1.write(item['rows'], item['cols'], item['value'])
        else:
            if 'style' in item.keys():
                sheet1.write_merge(item['srows'],item['erows'],item['scols'], item['ecols'], item['value'],item['style'])
            else:
                sheet1.write_merge(item['srows'],item['erows'],item['scols'], item['ecols'], item['value'])
    '''
    //If the error is reported, dict_items has no attributes sort
    //In the source code of syle-- alist.sort() is modified to ----- > sorted (alist) 
    //2 modifications in total
    '''
    new_excel.save(file_path)

#Parameter details
# srows: number of start rows to merge
# Rows: number of merged end rows
# Scopes: number of start columns to merge
# ecols: number of merged end columns 
# Value: fill value after merging cells
# Style: fill style after merging:
#     font: name in Tahoma
#     height 280;
#     alignment: horiz centre
#     ... basically consistent with excel operation

//Note: this method is only to save the actions that need to go straight to a list. The real actions have not been executed yet, and the execution actions take place in Excel_ In the update method

Finally call excel_update method, passing in the required operation of each cell and the write of filling value_ obj_ List and file saving path file_path

You can generate the desired Excel result file in the current working directory.

There are many other basic ways for python to operate excel. Due to the limitation of space, it is no longer stated and demonstrated. For further study, you can click the doc address above.

Learn how to operate excel cells by python and master the above methods, then you can basically realize the automatic report operation of Excel

Zero basic free study of python, plus my wechat 17706130227