python operation Excel (xlrd and xlwt)
# Version: Python 3.10.1
# Function: Python operates excel (mainly use the two libraries of xlrd and xlwt, that is, xlrd is the library for reading excel, and xlwt is the library for writing excel.)
# Note: 1.Python2 and Python3 have different file path formats; 2.WPS and Office operations are different; rows and columns are counted from "0"
import xlrd
import xlwt
# absolute path
Path_RD = "E:\Python_Projects\2-PythonExcel\Read Excel file.xls"
# relative path
Path_WT = "2-PythonExcel\write Excel file.xls"
# 1 Use python xlrd to read Excel files (support formats: .xls .xlsx)
# 1.1. Open Excel file
Data_RD = xlrd.open_workbook(Path_RD)
# 1.2 Table operation
# 1.2.1 Get a worksheet in the book (excel file)
# 1.2.2 The following three functions will return the name of a sheet.Sheet() object worksheet
Table1_RD = Data_RD.sheets()[0] # Get by index order
Table2_RD = Data_RD.sheet_by_index(1) # Get by index order
Table3_RD = Data_RD.sheet_by_name("Sheet3") # get by name
# 1.2.3 Get the name of the specified worksheet through the sheet.Sheet() object
Table_Name = Data_RD.sheet_names()[0] # Get by index order
print("Table_Name = ", Table_Name)
# 1.2.4 Through the sheet.Sheet() object, get
Sheets_Names_List = Data_RD.sheet_names() # Return the names of all worksheets in the book
print("Sheets_Names_List = ", Sheets_Names_List)
# 1.2.5 Check whether a sheet has been imported (True, False)
Result1 = Data_RD.sheet_loaded(1)
Result2 = Data_RD.sheet_loaded("Sheet2")
print("Result1 = ", Result1, "Result2 = ", Result2)
# 1.3 row operations row
# 1.3.1 Get the number of rows in the Sheet. Note: There is no () behind table.nrows here.
nrows = Table1_RD.nrows
print("nrows = ", nrows)
# 1.3.2 Get a list of all cells in the "row" object
Rows_List1 = Table1_RD.row(0)
print("Rows_List1 = ", Rows_List1)
print("Rows_List1[0].value", Rows_List1[0].value)
for list_Text in Rows_List1:
print("list_Text = ", list_Text)
Rows_List2 = Table1_RD.row_slice(1)
print("Rows_List2 = ", Rows_List2)
# 1.3.3 Get a list of data in all cells" in the " row? ?
Rows_List3 = Table1_RD.row_types(3, start_colx=0, end_colx=None)
print("Rows_List3 = ", Rows_List3)
# 1.3.4 Get the effective cell length of the row, that is, how much data is in this row
Rows_Len1 = Table1_RD.row_len(1)
print("Rows_Len4 = ", Rows_Len1)
# 1.4 Column operation col
# 1.4.1 Get the number of valid columns in the list
Table1_ncols = Table1_RD.ncols
print("Table1_ncols = ", Table1_ncols)
# 1.4.2 Get all objects in the column
# 1.4.2.1 Get a list of all cell objects in the column
Col_List1 = Table1_RD.col(0, start_rowx=0, end_rowx=None)
print("Col_List1 = ", Col_List1)
# 1.4.2.2 Get a list of all cell objects in the column
Col_List2 = Table1_RD.col_slice(1, start_rowx=0, end_rowx=None)
print("Col_List2 = ", Col_List2)
# 1.4.2.4 Get a list of the data types of all cells in this column? ?
Col_List3 = Table1_RD.col_types(2, start_rowx=0, end_rowx=None)
print("Col_List3 =", Col_List3)
# 1.4.2.5 Get a list of data from all cells in the column
Col_List4 = Table1_RD.col_values(3, start_rowx=0, end_rowx=None)
print("Col_List4 = ", Col_List4)
# 1.5 Cell operations
# 1.5.1 Get the corresponding location cell object
Cell1 = Table1_RD. cell(2, 2)
print("Cell1 = ", Cell1)
# 1.5.2 Get the data type of the corresponding cell
Cell1_Type = Table1_RD. cell_type(2, 2)
print("Cell_Type = ", Cell1_Type)
# 1.5.3 Get the data of the corresponding cell
Cell1_Value = Table1_RD. cell_value(2, 2)
print("Cell_Value = ", Cell1_Value)
# 1.6 Examples
# 1.6.1 Open Excel file
xlsx1 = xlrd.open_workbook("2-PythonExcel\read Excel file.xls")
# 1.6.2 Get worksheet sheet
# Find by sheet name: xlsx.sheet_by_name("sheet1")
# Find by index: xlsx.sheet_by_index(3)
table1 = xlsx1. sheet_by_index(0)
# 1.6.3 Get the value of the cell cell
# Get a single table value (2,1) means to get the value of the cell in row 3, column 2
value1 = table1. cell_value(2, 1)
print("The value of line 3 and column 2 is", value1)
# 1.6.4 Get the total number of rows in the table
nrows1 = table1.nrows
print("The table has a total of", nrows1, "rows")
# 1.6.5 Get column 3, all values starting from row 1 (list)
#Note: The index value of the row and column starts from 0, that is, the coordinate of the first cell in the table is (0, 0)
# name_list1 = [str(table1. cell_value(i, 2)) for i in range(1, nrows)]
name_list1 = table1.col_values(2, start_rowx=1, end_rowx=None)
print("All values in column 4:", name_list1)
# 2 Use python xlwt to write Excel files (support format: .xls)
# 2.1 Create a new workbook (that is, a new Excel)
new_workbook = xlwt. Workbook(encoding="ascii")
# 2.2 Create a new sheet
new_sheet = new_workbook.add_sheet("new_sheet")
# 2.3 Setting styles
# 2.3.1 Initialize style
style1 = xlwt.XFStyle()
# 2.3.2 Create font formats for styles
font = xlwt. Font()
font.name = "New Roman" # font
font.bold = True # bold
font.underline = True # underline
font.italic = True # italic
# 2.3.3 Setting styles
style1.font = font
# 2.3.4 Set the column width (256 is the unit of measure, 20 means 20 characters wide)
new_sheet.col(0).width = 256 * 20
# 2.3.5 Set line height
style2 = xlwt.easyxf("font:height 360;") # 18pt, type small font size
row = new_sheet.row(0)
row. set_style(style2)
# 2.3.6 Merge rows and columns (merge rows 2-3, columns 0-3)
new_sheet.write_merge(2, 3, 0, 3, "Remark: Python operation Excel")
# 2.3.7 Set border style
borders = xlwt.Borders() # Create borders (border style)
# May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR,
# MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED,
# MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
# DASHED dashed line
# NO_LINE no
# THIN solid line
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style3 = xlwt.XFStyle()
style3. borders = borders
# 2.3.8 Set cell background color
pattern = xlwt. Pattern()
# May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow,
# 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow,
# almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on... ?
pattern.pattern_fore_colour = 2
style4 = xlwt.XFStyle()
style4.pattern = pattern
# 2.3.9 Set cell alignment
style5 = xlwt.XFStyle()
alignment = xlwt. Alignment()
# VERT_TOP = 0x00 Top alignment
# VERT_CENTER = 0x01 center alignment (vertically)
# VERT_BOTTOM = 0x02 low end alignment
# HORZ_LEFT = 0x01 left-aligned
# HORZ_CENTER = 0x02 center alignment (horizontally)
# HORZ_RIGHT = 0x03 right-aligned
alignment.horz = 0x02 # set horizontal center
alignment.vert = 0x01 # set vertical center
style5. alignment = alignment
# 2.4 Write table content
new_sheet.write(0, 0, "Number", style1)
new_sheet.write(1, 0, "001", style2)
new_sheet.write(4, 0, "Test1", style3)
new_sheet.write(4, 1, "Test2", style4)
new_sheet.write(4, 2, "Test3", style5)
# 2.5 Save the file
new_workbook.save("new_workbook_1.xls")