Python3 read and write excel table data sample code

import xlrd
 
# Open an excel spreadsheet
data_excel=xlrd.open_workbook('data/')
 
# Get all sheet names
names=data_excel.sheet_names()
 
# Get the book in the sheet worksheet of the three methods, return a () object
table=data_excel.sheets()[0] # Get sheets by index order
table=data_excel.sheet_by_index(sheetx=0) # Get sheets by index order
table=data_excel.sheet_by_name(sheet_name='Sheet1') # By name
 
# excel worksheet rows and columns manipulation
n_rows= # Get the number of valid rows in the sheet
n_cols= # Get the number of valid columns in the sheet
row_list=(rowx=0) # Returns a list of all cell objects in a row
cols_list=(colx=0) # Returns a list of all the cell objects in a column
# Returns a list of all the cells in a row.
row_data=table.row_values(0,start_colx=0,end_colx=None)
# Returns a list of all the cells in a given column.
cols_data=table.col_values(0,start_rowx=0,end_rowx=None)
row_lenth=table.row_len(0) # Returns the effective cell length of a row
 
# excel worksheet cell manipulation
row_col=(rowx=0,colx=0) # Return the cell object
row_col_data=table.cell_value(rowx=0,colx=0) # Returns the data in the cell

import xlrd
 
""" Open an excel spreadsheet """
workbook = xlrd.open_workbook("")
print(workbook)       # Result: < object at 0x000000000291B128>
 
""" Get all sheet names """
sheet_names = workbook.sheet_names()
print(sheet_names)     # Results: ['Sheet1', 'Sheet2']
 
""" Get all or a sheet object """
# Get all sheet objects
sheets_object = ()
print(sheets_object)    # Result: [< object at 0x0000000002956710>, < object at 0x0000000002956AC8>]
# Get the first sheet object by index
sheet1_object = workbook.sheet_by_index(0)
print(sheet1_object)    # Result: < object at 0x0000000002956710>
# Get the first sheet object by name
sheet1_object = workbook.sheet_by_name(sheet_name="Table 1")
print(sheet1_object)    # Result: < object at 0x0000000002956710>
 
""" Determine if a sheet has been imported """
# Determine if sheet1 is imported by index
sheet1_is_load = workbook.sheet_loaded(sheet_name_or_index=0)
print(sheet1_is_load)    # Result: True
# Determine if sheet1 is imported by sheet name
sheet1_is_load = workbook.sheet_loaded(sheet_name_or_index="Table 1")
print(sheet1_is_load)    # Result: True
 
""" Perform operations on rows in the sheet object """
# Get the number of valid rows in sheet1
nrows = sheet1_object.nrows
print(nrows)        # Outcome: 5
# Get the data in row 3 of sheet1
all_row_values = sheet1_object.row_values(rowx=2)
print(all_row_values)      # Result: [3.0, 'b', 1, '']
row_values = sheet1_object.row_values(rowx=2, start_colx=1, end_colx=3)
print(row_values)        # Result: ['b', 1]
# Get the cell object in row 3 of sheet1
row_object = sheet1_object.row(rowx=2)
print(row_object)        # Result: [number:3.0, text:'b', bool:1, empty:'']
# Get the cell in row 3 of sheet1
row_slice = sheet1_object.row_slice(rowx=2)
print(row_slice)        # Result: [number:3.0, text:'b', bool:1, empty:'']
# Get the cell type of row 3 in sheet1
row_types = sheet1_object.row_types(rowx=2)
print(row_types)        # Result: array('B', [2, 1, 4, 0])
# Get the length of row 3 in sheet1
row_len = sheet1_object.row_len(rowx=2)
print(row_len)         # Outcome: 4
# Get generator for all rows of sheet1
rows_generator = sheet1_object.get_rows()
print(rows_generator)      # in the end：<generator object Sheet.get_rows.<locals>.<genexpr> at 0x00000000028D8BA0>
 
""" Perform operations on columns in the sheet object """
# Get the number of valid columns in sheet1
ncols = sheet1_object.ncols
print(ncols)        # Outcome: 4
# Get the data in column colx=1 of sheet1.
col_values = sheet1_object.col_values(colx=1)
print(col_values)      # Results: ['test', 'a', 'b', 'c', 'd']
col_values1 = sheet1_object.col_values(1, 1, 3)
print(col_values1)     # Result: ['a', 'b']
# Get the cell in column 2 of sheet1
col_slice = sheet1_object.col_slice(colx=1)
print(col_slice)      # Result: [text:'test', text:'a', text:'b', text:'c', text:'d']
# Get the cell type of column 2 in sheet1
col_types = sheet1_object.col_types(colx=1)
print(col_types)      # Results: [1, 1, 1, 1, 1]
 
"""Performs an operation on a cell in a sheet object."""
# Get the rowx=1 row and colx=2 column of sheet1.
cell_info = sheet1_object.cell(rowx=1, colx=2)
print(cell_info)      # result: text:'m'
print(type(cell_info))   # Results: <class ''>
# Get the cell value of rowx=1 and colx=2 in sheet1.
cell_value = sheet1_object.cell_value(rowx=1, colx=2)
print(cell_value)      # Results: m
# Get the cell type value of rowx=1 and colx=2 in sheet1.
cell_type = sheet1_object.cell_type(rowx=1, colx=2)
print(cell_type)      # Outcome: 1
 
#Unit typectype：emptybecause of0，stringbecause of1，numberbecause of2，datebecause of3，booleanbecause of4，errorbecause of5；

import xlrd
import datetime
 
""" Read the date in the sheet object """
workbook = xlrd.open_workbook("")
sheet2_object = workbook.sheet_by_name("Sheet2")
# value_type = sheet2_object.cell(0, 1).ctype
value_type = sheet2_object.cell_type(0, 1)
print(value_type) # Result: 3, indicating that the value is date
if value_type == 3:
  print("Cell data as date")
  cell_value = sheet2_object.cell_value(1, 0)
  print(cell_value) # Result: 43567.0
  date_tuple = xlrd.xldate_as_tuple(cell_value, )
  print(date_tuple) # Result: (2020, 4, 12, 0, 0, 0)
  date_value = (*date_tuple[:3])
  print(date_value) # Results: 2020-04-12
  date_format = date_value.strftime('%Y/%m/%d')
  print(date_format) # in the end：2020/04/12

import xlrd
 
""" 获取合并的单元格并读取单元格数据 """
# Get merged cells in xlsx format excel file
workbook = xlrd.open_workbook("")
sheet2_object = workbook.sheet_by_name("Sheet2")
print(sheet2_object.merged_cells)  # 结果: [(1, 2, 0, 2), (3, 6, 0, 2)]
# 获取xls格式的excel文件中的合并单元格
workbook1 = xlrd.open_workbook("", formatting_info=True)
sheet2_object1 = workbook1.sheet_by_name("Sheet2")
print(sheet2_object1.merged_cells)  # 结果: [(1, 2, 0, 2), (3, 6, 0, 2)]
 
# 读取合并单元格数据（仅需“起始行起始列”即可获取数据）
print(sheet2_object.cell_value(1, 0))  # 结果：总结1
print(sheet2_object.cell_value(3, 0))  # 结果：总结2
# 或使用for循环获取所有的合并单元格数据
for (row_start, row_end, col_start, col_end) in sheet2_object.merged_cells:
  print(sheet2_object.cell_value(rowx=row_start, colx=col_start))

import xlwt
  import datetime
  # Create a workbook Set the code
  workbook = (encoding='utf-8')
  # Create a worksheet
  worksheet = workbook.add_sheet('Sheet1')
 
  #Font style settings
  style = () # Initialize styles
  font = () # Create fonts for styles
   = 'Times New Roman'
   = 20 * 11 # Font size, 11 is the font size, 20 is the unit of measurement
   = True # Bold
   = True # Underline
   = True # Italics
   = font # Setting styles
  # Write data to excel, parameters correspond to rows, columns, values
  (0, 0, 'test_data') # Write without style
  (1, 0, 'test_data', style) # Write with font style
 
  # Set cell width
  (0).width = 3333
 
  # Set cell background color
  pattern = ()
   = .SOLID_PATTERN
  pattern.pattern_fore_colour = 13
  style = () # Create the Pattern
   = pattern # Add Pattern to Style
  (2, 0, 'colour', style)
 
  # Add a border to the cell method one
  borders = () # Create Borders
   =   #DASHED dashed line, NO_LINE none, THIN solid line
   =  #=1 indicates a solid line
   = 
   = 
  borders.left_colour=0x40
  borders.right_colour = 0x40
  borders.top_colour = 0x40
  borders.bottom_colour = 0x40
  style = () # Create Style
   = borders # Add Borders to Style
  (3,0 , 'border1', style)
 
  # Add a border to the cell method two
  # Fine solid line: 1, small thick solid line: 2, fine dotted line: 3, medium thin dotted line: 4, large thick solid line: 5, double line: 6, fine dotted dotted line: 7, large thick dotted line: 8, fine dotted line: 9, thick dotted line: 10, fine double dotted line: 11, thick double dotted line: 12, diagonal dotted line: 13
  borders = ()
   = 1  # Set to a thin solid line
   = 1
   = 1
   = 1
  borders.left_colour = 2 #Color set to red
  borders.right_colour = 2
  borders.top_colour = 2
  borders.bottom_colour = 2
  style = () # Create Style
   = borders # Add Borders to Style
  (4, 0, 'border2', style)
 
  # Enter a date into a cell
  style = ()
  style.num_format_str = 'M/D/YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
  (5, 0, (), style)
 
  # Cells to add formulas
  (0, 1, 2) # Outputs 2
  (0, 2, 3) # Outputs 3
  (1, 1, ('B1*C1')) # Should output "6" (B1[2] * B2[6])
  (1, 2, ('SUM(B1,C1)')) # Should output "5" (B1[2] + C1[3])
 
  # Add a hyperlink to a cell
  (0, 3, ('HYPERLINK("";"baidu")')) # Outputs the text "baidu" linking to 
 
  # Cell Merge
  worksheet.write_merge(0, 0, 4, 5, 'First Merge')  # Merge 4 to 5 columns in row 0
  worksheet.write_merge(1, 2, 4, 5, 'Second Merge') # Merge 4 to 5 columns in rows 1 and 2
 
  # Set the cell content to its way
  alignment=() ## Create Alignment
  =.HORZ_CENTER
  =.VERT_CENTER
  style=()
  =alignment  # Add Alignment to Style
  (0, 6, 'alignment', style)
 
  # Save the document
  ('data_test.xls')