How to automate excel with python: first install the python toolkit; then read the excel table from the specified file path and perform certain operations; then save it to another excel file; and finally call the [excel_update] method.
Related free learning recommendations: python video tutorial
How to automate excel with python:
1. Toolkit
1. xlrd: from Excel spreadsheet Extract data doc address: https://xlrd.readthedocs.io/en/latest/
2. xlwt: Write data into Excel spreadsheet doc address: https://xlwt.readthedocs.org/en /latest/
3. xlutils: Provides a set of utilities for processing Excel files. Doc address: https://xlutils.readthedocs.io/en/latest/
2. Installation
python -m pip install xlrd xlwt xlutils
3. Basic usage
The related toolkit for python operating excel can be detailed in Manipulate the fill style, value type, value size, etc. of the specified cell. However, Python requires certain pandas data processing skills to operate excel. A chapter will be added later: pandas data processing skills
1. Read the excel table 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('./') # 从指定文件路径读取excel表格 df = pd.read_excel('D:/mypaper/data/data.xlsx') # 查看df内容 # 根据age算出出生年份,增加一列 import datetime import os year = datetime.datetime.now().year#获取当前系统时间对应的年份 df['birth'] = year-df['age'] df.to_excel('result.xlsx')#保存到当前工作目录,可以用os.getcwd()查看 #查看下此时df的内容,可以看到已经生成了birth这一列
At first glance, it seems that only pandas is used, and the three toolkits introduced above have not been used. Here is an introduction to using python to operate the underlying layer of excel
2. Cell operation
# 定义方法:读取指定目录下Excel文件某个sheet单元格的值 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 # 定义方法:单元格值及样式 write_obj_list = [] def concat_obj(cols,rows,value): write_obj_list.append({'cols':cols,'rows':rows,'value':value,\ 'style':xlwt.easyxf('font: name 宋体,height 280;alignment: horiz centre')}) # 定义方法:合并单元格 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 宋体,height 280;alignment: horiz centre')}) # 定义方法:更新excel excel_update(file_path,write_obj_list,new_path): old_excel = xlrd.open_workbook(file_path, formatting_info=True) #管道作用 new_excel = copy(old_excel) ''' 通过get_sheet()获取的sheet有write()方法 ''' sheet1 = new_excel.get_sheet(0) ''' 1代表是修改第几个工作表里,从0开始算是第一个。此处修改第一个工作表 ''' 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']) ''' 如果报错 dict_items has no attributes sort 把syle源码中--alist.sort() 修改为----> sorted(alist) 一共修改2次 ''' new_excel.save(file_path) #参数详解 # srows:合并的起始行数 # erows:合并的结束行数 # scols:合并的起始列数 # ecols:合并的结束列数 # value:合并单元格后的填充值 # style:合并后填充风格: # font: name 宋体 # height 280; # alignment: horiz centre # ... 与excel操作基本保持一致
Note: This method only saves the actions that need to be performed directly into a list. The real action has not yet been executed. The execution action occurs in the excel_update method
Finally call the excel_update method and pass in the write_obj_list of the operations and fill values required for each cell and the file saving path file_path
The desired Excel result file can be generated in the current working directory .
Note:
1.write_obj_list supports user customization
2.write_obj_list can also read existing excel files to be modified according to the excel_read method (can be maintained (original table format) and generate
There are many other basic methods for Python to operate excel. Due to space limitations, they will not be stated and demonstrated. Those who want to study in depth can click on the doc address above.
Learn python to operate excel cells and master the above methods, you can basically realize automated report operations in excel.
The above is the detailed content of How to automate excel in python. For more information, please follow other related articles on the PHP Chinese website!

Python is easier to learn and use, while C is more powerful but complex. 1. Python syntax is concise and suitable for beginners. Dynamic typing and automatic memory management make it easy to use, but may cause runtime errors. 2.C provides low-level control and advanced features, suitable for high-performance applications, but has a high learning threshold and requires manual memory and type safety management.

Python and C have significant differences in memory management and control. 1. Python uses automatic memory management, based on reference counting and garbage collection, simplifying the work of programmers. 2.C requires manual management of memory, providing more control but increasing complexity and error risk. Which language to choose should be based on project requirements and team technology stack.

Python's applications in scientific computing include data analysis, machine learning, numerical simulation and visualization. 1.Numpy provides efficient multi-dimensional arrays and mathematical functions. 2. SciPy extends Numpy functionality and provides optimization and linear algebra tools. 3. Pandas is used for data processing and analysis. 4.Matplotlib is used to generate various graphs and visual results.

Whether to choose Python or C depends on project requirements: 1) Python is suitable for rapid development, data science, and scripting because of its concise syntax and rich libraries; 2) C is suitable for scenarios that require high performance and underlying control, such as system programming and game development, because of its compilation and manual memory management.

Python is widely used in data science and machine learning, mainly relying on its simplicity and a powerful library ecosystem. 1) Pandas is used for data processing and analysis, 2) Numpy provides efficient numerical calculations, and 3) Scikit-learn is used for machine learning model construction and optimization, these libraries make Python an ideal tool for data science and machine learning.

Is it enough to learn Python for two hours a day? It depends on your goals and learning methods. 1) Develop a clear learning plan, 2) Select appropriate learning resources and methods, 3) Practice and review and consolidate hands-on practice and review and consolidate, and you can gradually master the basic knowledge and advanced functions of Python during this period.

Key applications of Python in web development include the use of Django and Flask frameworks, API development, data analysis and visualization, machine learning and AI, and performance optimization. 1. Django and Flask framework: Django is suitable for rapid development of complex applications, and Flask is suitable for small or highly customized projects. 2. API development: Use Flask or DjangoRESTFramework to build RESTfulAPI. 3. Data analysis and visualization: Use Python to process data and display it through the web interface. 4. Machine Learning and AI: Python is used to build intelligent web applications. 5. Performance optimization: optimized through asynchronous programming, caching and code

Python is better than C in development efficiency, but C is higher in execution performance. 1. Python's concise syntax and rich libraries improve development efficiency. 2.C's compilation-type characteristics and hardware control improve execution performance. When making a choice, you need to weigh the development speed and execution efficiency based on project needs.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

Zend Studio 13.0.1
Powerful PHP integrated development environment

SublimeText3 English version
Recommended: Win version, supports code prompts!

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool