“Offer comes, ask friends to take it! I am participating in the 2022 Spring Recruit Punch card campaign. Click here for more details.”

In this blog, we will learn the module pyWin32. This module mainly encapsulates the Win32 API of Windows system. You can use it to operate COM objects and graphical window interface. It is important to note that this module cannot be applied to systems outside Windows.

Module installation is very simple, use the following command.

pip install pywin32
Copy the code

Operating Excel files

Next, directly enter the Excel operation link, using the following code

import win32com.client
import os

#Gets the current working directory
base_dir = os.path.dirname(os.path.abspath(__file__))
# print(base_dir)
xlsx_app = win32com.client.Dispatch('Excel.Application')
#If the Excel file is visible, you can test True or False
xlsx_app.Visible = True
#The file nameFilename = "test.xlsx"#Get the complete file path, note that the absolute path must be used, otherwise the file does not exist
path = os.path.join(base_dir, filename)
#Open the Excel file with the above name
xlsx_book = xlsx_app.Workbooks.Open(path, ReadOnly=False)
#Open the sheet with the corresponding name and make sure the sheet exists in ExcelSHT = xlsx_book.worksheets (' Test Sheet')#Clear contents of the current Sheet
sht.UsedRange.ClearContents()

#Gets the number of rows in the sheet workspace
nrows = sht.UsedRange.Rows.Count

print(nrows)
Copy the code

The code above contains a lot of information that you can learn along with the comments.

For example, Open a read-only Excel file with xlsx_app.workbooks.open () and call the Worksheets() method that returns the object to retrieve the worksheet. Then you can manipulate the worksheet using the familiar workflow.

Save the file as

Use xlsx_book.saveas (newfilename) to save Excel files.

The file created by the code above is not in the directory in which the py file resides

Note the path problem again
newpath = os.path.join(base_dir, 'test 123. XLSX')

xlsx_book.SaveAs(newpath)
Copy the code

Here is another method to Close the file, as shown below, where the important method is Close() and the SaveChanges parameter indicates whether the data needs to be saved. 0 does not save, 1 saves.

sht.Cells(1.1).Value = "Nice"
xlsx_book.Close(SaveChanges=1)
Copy the code

Read cell data


import win32com.client
import os

Get the current working directory
base_dir = os.path.dirname(os.path.abspath(__file__))
# print(base_dir)
xlsx_app = win32com.client.Dispatch('Excel.Application')
Test True or False to see if the Excel file is visible
xlsx_app.Visible = False

# filename
filename = "Test. XLSX"
[root@localhost] [root@localhost] [root@localhost] [root@localhost
path = os.path.join(base_dir, filename)
Open the Excel file with the above name
xlsx_book = xlsx_app.Workbooks.Open(path)

Open the name of the sheet and ensure that the sheet exists in Excel
sht = xlsx_book.Worksheets('the test Sheet)

Get cell data
data_a1 = sht.Cells(1.1).Value

print(data_a1)
Copy the code

The parameters in the Cells(row, column) method correspond to the row and column numbers, and the subscripts start at 1

Set cell data

Read the cell data above, reassign it is also relatively simple, execute the following code

sht.Cells(1.1).Value = "Nice"
xlsx_book.Close(SaveChanges=1)
Copy the code

Select area data

And the method for selecting a region is Range()

data = sht.Range(sht.Cells(1.1), sht.Cells(3.3)).Value
print(data)
Copy the code

In addition, you can also refer to the following code

sht.Range("A1").Value = "Test A1"
sht.Range("A2:B2").Value = "A2:B2"
sht.Range("A3:B5,A4:B7").Value = "A3:B5,A4:B7"
Copy the code

Other operations, such as deleting data, setting the background color

Row data can be deleted using entirerow.delete () and column data can be deleted using Entirecolumn.delete ().

sht.Rows(2).EntireRow.Delete() Delete line 2
sht.Columns(2).EntireColumn.Delete() Drop column 2
Copy the code

Set the background color

sht.Cells(1.1).Interior.ColorIndex = 1
Copy the code

The background color here is achieved by numeric index, there are 56 colors, where the special 0 is colorless, 1 is black, and 2 is white.

If you want to set the column width, refer to the code below

Set the column width
sht.Columns(1).ColumnWidth = 60
Copy the code

Set the font

# set font size
sht.Cells.Font.Name = Microsoft Yahei
Copy the code

More information can be found at timgolden.me.uk/pywin32-doc…

Recording time

In 2022, 581/1024 articles were written by Flag. You can follow me, like me, comment on me, favorites me.