This is the 8th day of my participation in the August More Text Challenge
Python output data is often more intuitive to view in Excel files, and Excel data often needs to be read into Python programs. Python’s XLRD and XLWT modules were created for this purpose. Text records how these two modules are used.
The test case
Use read_test.xlsx as the test file for reading Excel. The two worksheets are:
Module is installed
Easy to install with PIP
pip install xlrd
pip install xlwt
Copy the codexlrd
Read Excel files (XLS, XLSX)
import xlrd
## Read the Excel file
try:
data = xlrd.open_workbook('read_test.xlsx')
except Exception as err:
print(err)
Copy the codeGet worksheet information
Get worksheet information
print(len(data.sheets()))
sheet_names = data.sheet_names()
print(sheet_names)
>>>2
['Information Table 2'.'Information Table 1']
Copy the codeGet the worksheet contents
Through the index
Get worksheet contents by index
info_sheet_1 = data.sheet_by_index(0)
info_sheet_2 = data.sheet_by_index(1)
print(f'sheet :{info_sheet_1.name} row_num: {info_sheet_1.nrows} column_num: {info_sheet_1.ncols}')
print(f'sheet :{info_sheet_2.name} row_num: {info_sheet_2.nrows} column_num: {info_sheet_2.ncols}')
>>> Sheet: Indicates an information table1 row_num: 5 column_num: 7Sheet: Indicates an information table2 row_num: 5 column_num: 7
Copy the codeBy name
Get the worksheet content by name
sheet_names = data.sheet_names()
info_sheet_1 = data.sheet_by_name(sheet_names[0])
info_sheet_2 = data.sheet_by_name(sheet_names[1])
print(f'sheet :{info_sheet_1.name} row_num: {info_sheet_1.nrows} column_num: {info_sheet_1.ncols}')
print(f'sheet :{info_sheet_2.name} row_num: {info_sheet_2.nrows} column_num: {info_sheet_2.ncols}')
>>> Sheet: Indicates an information table1 row_num: 5 column_num: 7Sheet: Indicates an information table2 row_num: 5 column_num: 7
Copy the codeGets the data content in the worksheet
Gets worksheet row information
Get worksheet row information
for i in range(info_sheet_1.nrows):
print(info_sheet_1.row_values(i))
for i in range(info_sheet_2.nrows):
print(info_sheet_2.row_values(i))
>>> ['Information Table 1'.' '.' '.' '.' '.' '.' ']
['name'.'gender'.'age'.'native'.'mobile phone'.'email'.'note']
['she Finn'.'woman'.32.0.'shandong'.12345678900.0.'[email protected]'.'Love reading']
['the founding of the'.'male'.43.0.'Beijing'.13265498700.0.'[email protected]'.'Both children']
['fat'.'male'.18.0.'gansu'.12435698700.0.'[email protected]'.' ']
['Information Table 2'.' '.' '.' '.' '.' '.' ']
['name'.'gender'.'age'.'native'.'mobile phone'.'email'.'note']
['three chicken'.'woman'.28.0.'henan'.12388678900.0.'[email protected]'.' ']
['wang cong'.'male'.31.0.'zhejiang'.13266498700.0.'[email protected]'.' ']
['Lilei'.'male'.22.0.'Australia'.12499998700.0.'[email protected]'.'Fluent In English']
Copy the codeGet worksheet column information
Get the worksheet column information
for i in range(info_sheet_1.ncols):
print(info_sheet_1.col_values(i))
for i in range(info_sheet_2.ncols):
print(info_sheet_2.col_values(i))
>>>['Information Table 1'.'name'.'she Finn'.'the founding of the'.'fat']
[' '.'gender'.'woman'.'male'.'male']
[' '.'age'.32.0.43.0.18.0]
[' '.'native'.'shandong'.'Beijing'.'gansu']
[' '.'mobile phone'.12345678900.0.13265498700.0.12435698700.0]
[' '.'email'.'[email protected]'.'[email protected]'.'[email protected]']
[' '.'note'.'Love reading'.'Both children'.' ']
['Information Table 2'.'name'.'three chicken'.'wang cong'.'Lilei']
[' '.'gender'.'woman'.'male'.'male']
[' '.'age'.28.0.31.0.22.0]
[' '.'native'.'henan'.'zhejiang'.'Australia']
[' '.'mobile phone'.12388678900.0.13266498700.0.12499998700.0]
[' '.'email'.'[email protected]'.'[email protected]'.'[email protected]']
[' '.'note'.' '.' '.'Fluent In English']
Copy the codeGets worksheet location information
Get worksheet location information
print(info_sheet_1.cell(2.1).value)
print(info_sheet_1.cell_value(2.1))
print(info_sheet_1.row(2) [1].value)
print(info_sheet_1.row_values(2) [1])
print(info_sheet_1.col(1) [2].value)
print(info_sheet_1.col_values(1) [2[) >>> woman woman woman woman womanCopy the codeGets the type of data in the worksheet
Get the type of data in the worksheet
for i in range(info_sheet_1.ncols):
print(info_sheet_1.cell(2,i).ctype)
>>> 1
1
2
1
2
1
1
Copy the codeCtype: 0 empty,1 string, 2 number, 3 date, 4 Boolean, 5 error
Gets merged cell information
Get merged cell information
print(info_sheet_1.merged_cells)
>>> [(0.1.0.7)]
Copy the codeThe returned four numbers locate A combined cell operation. If the four numbers are represented as (A,B,C,D), it can be understood as follows:
- The initial (upper-left) coordinate of the merged cell (within the merged region) is [A,C].
- The end of the merged cell (bottom right corner) the cell (outside the merged area) coordinates [B,D]
xlwt
Create XLWT objects
import xlwt
Create XLWT objects
wb = xlwt.Workbook()
Copy the codeSet up a worksheet
## Create a worksheet
test_sheet_1 = xlsx_writer.add_sheet('Test Sheet 1')
test_sheet_2 = xlsx_writer.add_sheet('Test Sheet 2')
try:
xlsx_writer.save('write_test.xlsx') # save XLSX
xlsx_writer.save('write_test.xls') # save XLS
except Exception as err:
print(err)
Copy the code
Writes to a data table
Write data to table
test_sheet_1.write(1.1.123)
test_sheet_1.write(1.2.110)
test_sheet_1.write(1.3,xlwt.Formula("B2+C2"))
test_sheet_1.write(1.4.'test')
test_sheet_1.write(1.5.True)
test_sheet_2.write(3.3,xlwt.Formula("' {} '! $B$2+'{}'! $D$2".format(test_sheet_1.name,test_sheet_1.name)))
try:
xlsx_writer.save('write_test.xlsx') # save XLSX
xlsx_writer.save('write_test.xls') # save XLS
except Exception as err:
print(err)
Copy the code
Set the column width and row height
Set column width and row height
test_sheet_1.col(0).width = 400*30
test_sheet_1.row(0).height = 1000
try:
xlsx_writer.save('write_test.xlsx') # save XLSX
xlsx_writer.save('write_test.xls') # save XLS
except Exception as err:
print(err)
Copy the code
Setting content Styles
## Initialize the style
style = xlwt.XFStyle() # Style class instance
## Create font
font = xlwt.Font() Example of the # font class
font.name = 'Times New Roman' # font name
font.bold = True # bold
font.italic =True # tilt
font.height = 300 # 200 = 10 points
font.colour_index=3 # color coding
## Create a border
borders= xlwt.Borders() # border class instance
borders.left= 6
borders.right= 6
borders.top= 6
borders.bottom= 6
## Create alignment
alignment = xlwt.Alignment() # align class instances
Horz = xlwt.horz_left
Horz = xlwt.horz_right
alignment.horz = xlwt.Alignment.HORZ_CENTER # Horizontal center
Vert = xlwt.vert_top
VERT_BOTTOM = xlwt.vert_bottom
alignment.vert = xlwt.Alignment.VERT_CENTER # Center vertically
alignment.wrap = 1 # wrap
## Create mode
pattern = xlwt.Pattern() # pattern class instance
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # Fixed style
pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow'] # Background color
## Apply styles
style.font = font
style.borders = borders
style.num_format_str = '#, # # 0.0000' # Content format
style.alignment = alignment
style.pattern=pattern
## Merge cell (A,B,C,D) ## merge cell (B,C,D)
test_sheet_1.write_merge(3.5.3.5.' Merge ',style) # 'Merge' to write content, apply the style style
test_sheet_1.write(0.0.1234567.890123,style) Write data to the [0,0] coordinate cell, apply style style
style.num_format_str = '#, # # 0.000%' # Content format
test_sheet_1.write(6.0.67.8123456,style) The integer part is separated by commas, and the decimal part is reserved for 3 decimal places and expressed as a percentage
style.num_format_str = '# # # %' # Content format
test_sheet_1.write(6.5.0.128,style)
style.num_format_str = '# # #. # # %' # Content format
test_sheet_1.write(6.4.0.128,style)
style.num_format_str = '000.00%' # Content format
test_sheet_1.write(6.3.0.128,style)
try:
xlsx_writer.save('write_test.xlsx') # save XLSX
xlsx_writer.save('write_test.xls') # save XLS
except Exception as err:
print(err)
Copy the code
Access to the source code
The test environment and all the source code can be downloaded on Github.