win32com The main modules are Python Provide calls windows The underlying component pair word 、Excel、PPT And so on ;
But it has its limitations , Only in Windows Environmental use , And you need to install office Relevant software (WPS It's OK );
win32com Modules can not only operate Excel, You can also operate other in windows Applications installed on , Here is only for operation Excel Make a brief introduction ;
Install and introduce
There are many ways to install , Recommended pip Installation mode , Open the command line window , Carry out orders
pip install pywin32
pip install pypiwin32
among python2 This module is pywin32 Inside ,python3 yes pypiwin32
introduce win32com client :
from win32com import client
Easy to get started :
1、 open Excel Program
app = client.Dispatch(‘Excel.Application’)
open word The program needs to pass in word.Application
2、 Set visible , Let the program display in the foreground
app.Visible = True
3、 Create a workbook using the following methods
wb = app.Workbooks.Add()
4、 Or open an existing file
wb = app.Workbooks.Open(‘openpyxl.xlsx’)
5、 obtain sheet Two ways of the table
By name sheet = wb.Worksheets(‘Sheet2’)
According to the logo sheet = wb.Worksheets[0]
6、 Add one sheet And name
wb.Worksheets.Add().Name = ‘New’
print(sheet.name)
7、 Assign a value to the specified cell :
If you give the 8 Xing di 1 Cell assignments for Columns , Here the cells start from 1 Start counting not from 0 Start counting
sheet.Cells(8,1).Value = ‘test’
print(sheet.Cells(8,1).Value)
8、 Get the maximum row and column respectively
print(len(sheet.UsedRange.Rows))
print(len(sheet.UsedRange.Columns))
9、 Read across lines
list0=sheet.Range(‘A2:A5’).Value
print(list0) What you read is a two-dimensional tuple
10、 Save file and exit
wb.SaveAs(‘H:/openpyxl.xlsx’)
app.Quit()
ws.Range("A1:B1").Font.Bold = True # In bold ws.Range("C1:D1").Merge() # merge cell , The first cell must be selected for both reading and writing after merging ws.Range("A1:T1").Columns.AutoFit() # Automatically adjust border width , Sometimes the adjustment is not accurate , Manual recalibration is required ws.Range("A1").ColumnWidth = 40 # Set the width manually