Loading

读取excel文件模块（xlwt模块、xlrd模块、xlwings模块）

目录

xlwt模块

  • 简单使用xlwt

    import xlwt #导入模块
workbook = xlwt.Workbook(encoding='utf-8') #创建workbook 对象
worksheet = workbook.add_sheet('sheet1') #创建工作表sheet
worksheet.write(0, 0, 'hello') #往表中写内容,第一各参数 行,第二个参数列,第三个参数内容
workbook.save('students.xls') #保存表为students.xls

  • 为内容设置style

    workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
#设置字体样式
font = xlwt.Font()
#字体
font.name = 'Time New Roman'
#加粗
font.bold = True
#下划线
font.underline = True
#斜体
font.italic = True

#创建style
style = xlwt.XFStyle()
style.font = font
#根据样式创建workbook
worksheet.write(0, 1, 'world', style)
workbook.save('students.xls')

  • 合并单元格

    workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
#通过worksheet调用merge()创建合并单元格
#第一个和第二个参数单表行合并,第三个和第四个参数列合并,

#合并第0列到第2列的单元格
worksheet.write_merge(0, 0, 0, 2, 'first merge')

#合并第1行第2行第一列的单元格
worksheet.write_merge(0, 1, 0, 0, 'first merge')

workbook.save('students.xls')

  • 设置单元格的对齐方式

    workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
alignment = xlwt.Alignment()
# 水平居中
alignment.horz = xlwt.Alignment.HORZ_CENTER
# 垂直居中
alignment.vert = xlwt.Alignment.VERT_CENTER
style = xlwt.XFStyle()
style.alignment = alignment
#设置单元格宽度
worksheet.col(0).width = 6666

#设置单元格的高度
worksheet.row(0).height_mismatch = True
worksheet.row(0).height = 1000

worksheet.write(0, 0, 'hello world', style)
workbook.save('center.xls')

  • 设置单元格调边框

    workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
border = xlwt.Borders()
# DASHED虚线
# NO_LINE没有
# THIN实线
border.left = xlwt.Borders.THIN
# 设置颜色
border.left_coloure = 0x40b
border.right = xlwt.Borders.THIN

border.right_colour = 0x40b
border.top = xlwt.Borders.THIN

border.top_colour = 0x40b
border.bottom = xlwt.Borders.THIN

border.bottom_colour = 0x40b
style = xlwt.XFStyle()

style.borders = border
worksheet.write(0, 0, 'love', style)
workbook.save('dashed.xls')

  • 设置单元格边框

    workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')

border = xlwt.Borders()
# DASHED虚线
# NO_LINE没有
# THIN实线
border.left = xlwt.Borders.THIN
border.right = xlwt.Borders.THIN
border.top = xlwt.Borders.THIN
border.bottom = xlwt.Borders.THIN

style = xlwt.XFStyle()
style.borders = border
worksheet.write(1, 1, 'love', style)

workbook.save('dashed.xls'

  • 设置单元格背景色

    workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN

# 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 = 3
style = xlwt.XFStyle()
style.pattern = pattern
worksheet.write(1, 1, 'shit', style)
workbook.save('shit.xls')

  • 设置字体颜色

    workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')

font = xlwt.Font()
# 设置字体为红色
font.colour_index=xlwt.Style.colour_map['red']

style = xlwt.XFStyle()

style.font = font

worksheet.write(0, 1, 'world', style)
workbook.save('students.xls')

xlrd模块

  • xlrd模块介绍

    • xlrd是读取excel表格数据；
    • 支持 xlsx和xls 格式的excel表格；
    • 三方模块安装方式：pip3 install xlrd；
    • 模块导入方式： import xlrd

  • xlrd模块基本操作

    • 打开workbook获取Book对象
      • xlrd.open_workbook(filename[, logfile, file_contents, ...])：打开excel文件
      • filename：需操作的文件名（包括文件路径和文件名称）；
        • 若filename不存在，则报错FileNotFoundError；
        • 若filename存在，则返回值为xlrd.book.Book对象
    • 获取Book对象中所有sheet名称
      • BookObject.sheet_names()：获取所有sheet的名称，以列表方式显示
    • 获取Book对象中所有Sheet对象
      • BookObject.sheets()：获取所有sheet的对象，以列表形式显示
      • BookObject.sheet_by_index(sheetx)：通过sheet索引获取所需sheet对象
        • sheetx为索引值，索引从0开始计算；
        • 若sheetx超出索引范围，则报错IndexError；
        • 若sheetx在索引范围内，则返回值为xlrd.sheet.Sheet对象
      • BookObject.sheet_by_name(sheet_name)：通过sheet名称获取所需sheet对象
        • sheet_name为sheet名称；
        • 若sheet_name不存在，则报错xlrd.biffh.XLRDError；
        • 若sheet_name存在，则返回值为xlrd.sheet.Sheet对象
    • 判断Book对象中某个sheet是否导入
      • BookObject.sheet_loaded(sheet_name_or_index)：通过sheet名称或索引判断该sheet是否导入成功
      • 返回值为bool类型，若返回值为True表示已导入；若返回值为False表示未导入
    • 对Sheet对象中的行操作
      • SheetObject.nrows：获取某sheet中的有效行数
      • SheetObject.row_values(rowx[, start_colx=0, end_colx=None])：获取sheet中第rowx+1行从start_colx列到end_colx列的数据，返回值为列表。
        • 若rowx在索引范围内，以列表形式返回数据；
        • 若rowx不在索引范围内，则报错IndexError
      • SheetObject.row(rowx)：获取sheet中第rowx+1行单元，返回值为列表；
        • 列表每个值内容为： 单元类型:单元数据
      • SheetObject.row_slice(rowx[, start_colx=0, end_colx=None])：以切片方式获取sheet中第rowx+1行从start_colx列到end_colx列的单元，返回值为列表；
        • 列表每个值内容为： 单元类型:单元数据
      • SheetObject.row_types(rowx[, start_colx=0, end_colx=None])：获取sheet中第rowx+1行从start_colx列到end_colx列的单元类型，返回值为array.array类型。
        • 单元类型ctype：empty为0，string为1，number为2，date为3，boolean为4， error为5（左边为类型，右边为类型对应的值）；
      • SheetObject.row_len(rowx)：获取sheet中第rowx+1行的长度

        rowx：行标，行数从0开始计算（0表示第一行）, 必填参数；
        start_colx：起始列，表示从start_colx列开始取值，包括第start_colx的值；
        end_colx：结束列，表示到end_colx列结束取值，不包括第end_colx的值；
        start_colx默认为0，end_colx默认为None：表示取整行相关数据；

      • SheetObject.get_rows()：获取某一sheet所有行的生成器
    • 对Sheet对象中的列操作
      • SheetObject.ncols：获取某sheet中的有效列数
      • SheetObject.col_values(self, colx[, start_rowx=0, end_rowx=None])：获取sheet中第colx+1列从start_rowx行到end_rowx行的数据，返回值为列表。
      • SheetObject.col_slice(colx[, start_rowx=0, end_rowx=None])：以切片方式获取sheet中第colx+1列从start_rowx行到end_rowx行的数据，返回值为列表。
        • 列表每个值内容为： 单元类型:单元数据
      • SheetObject.col_types(colx[, start_rowx=0, end_rowx=None])：获取sheet中第colx+1列从start_rowx行到end_rowx行的单元类型，返回值为列表；
    • 对Sheet对象的单元格执行操作

      • ShellObeject.cell(rowx, colx)：获取sheet对象中第rowx+1行，第colx+1列的单元对象，返回值为'xlrd.sheet.Cell'类型，返回值的格式为“单元类型:单元值”。

      • ShellObject.cell_value(rowx, colx)：获取sheet对象中第rowx+1行，第colx+1列的单元数据，返回值为当前值的类型（如float、int、string...）；

      • ShellObject.cell_type(rowx, colx)：获取sheet对象中第rowx+1行，第colx+1列的单元数据类型值；

        • 单元类型ctype：empty为0，string为1，number为2，date为3，boolean为4， error为5；
      1.1. 打开workbook获取Book对象
xlrd.open_workbook(filename[, logfile, file_contents, ...])：打开excel文件
filename：需操作的文件名（包括文件路径和文件名称）；
若filename不存在，则报错FileNotFoundError；
若filename存在，则返回值为xlrd.book.Book对象

1.2. 获取Book对象中所有sheet名称
BookObject.sheet_names()：获取所有sheet的名称，以列表方式显示
1.3. 获取Book对象中所有Sheet对象
BookObject.sheets()：获取所有sheet的对象，以列表形式显示

BookObject.sheet_by_index(sheetx)：通过sheet索引获取所需sheet对象
sheetx为索引值，索引从0开始计算；
若sheetx超出索引范围，则报错IndexError；
若sheetx在索引范围内，则返回值为xlrd.sheet.Sheet对象

BookObject.sheet_by_name(sheet_name)：通过sheet名称获取所需sheet对象
sheet_name为sheet名称；
若sheet_name不存在，则报错xlrd.biffh.XLRDError；
若sheet_name存在，则返回值为xlrd.sheet.Sheet对象

1.4. 判断Book对象中某个sheet是否导入
BookObject.sheet_loaded(sheet_name_or_index)：通过sheet名称或索引判断该sheet是否导入成功
返回值为bool类型，若返回值为True表示已导入；若返回值为False表示未导入
1.5. 对Sheet对象中的行操作
SheetObject.nrows：获取某sheet中的有效行数

SheetObject.row_values(rowx[, start_colx=0, end_colx=None])：获取sheet中第rowx+1行从start_colx列到end_colx列的数据，返回值为列表。
若rowx在索引范围内，以列表形式返回数据；
若rowx不在索引范围内，则报错IndexError

SheetObject.row(rowx)：获取sheet中第rowx+1行单元，返回值为列表；
列表每个值内容为： 单元类型:单元数据

SheetObject.row_slice(rowx[, start_colx=0, end_colx=None])：以切片方式获取sheet中第rowx+1行从start_colx列到end_colx列的单元，返回值为列表；
列表每个值内容为： 单元类型:单元数据

SheetObject.row_types(rowx[, start_colx=0, end_colx=None])：获取sheet中第rowx+1行从start_colx列到end_colx列的单元类型，返回值为array.array类型。
单元类型ctype：empty为0，string为1，number为2，date为3，boolean为4， error为5（左边为类型，右边为类型对应的值）；

SheetObject.row_len(rowx)：获取sheet中第rowx+1行的长度

rowx：行标，行数从0开始计算（0表示第一行）, 必填参数；
start_colx：起始列，表示从start_colx列开始取值，包括第start_colx的值；
end_colx：结束列，表示到end_colx列结束取值，不包括第end_colx的值；

start_colx默认为0，end_colx默认为None：表示取整行相关数据；

SheetObject.get_rows()：获取某一sheet所有行的生成器
1.6. 对Sheet对象中的列操作
SheetObject.ncols：获取某sheet中的有效列数

SheetObject.col_values(self, colx[, start_rowx=0, end_rowx=None])：获取sheet中第colx+1列从start_rowx行到end_rowx行的数据，返回值为列表。

SheetObject.col_slice(colx[, start_rowx=0, end_rowx=None])：以切片方式获取sheet中第colx+1列从start_rowx行到end_rowx行的数据，返回值为列表。
列表每个值内容为： 单元类型:单元数据

SheetObject.col_types(colx[, start_rowx=0, end_rowx=None])：获取sheet中第colx+1列从start_rowx行到end_rowx行的单元类型，返回值为列表；

1.7. 对Sheet对象的单元格执行操作
ShellObeject.cell(rowx, colx)：获取sheet对象中第rowx+1行，第colx+1列的单元对象，返回值为'xlrd.sheet.Cell'类型，返回值的格式为“单元类型:单元值”。

ShellObject.cell_value(rowx, colx)：获取sheet对象中第rowx+1行，第colx+1列的单元数据，返回值为当前值的类型（如float、int、string...）；

ShellObject.cell_type(rowx, colx)：获取sheet对象中第rowx+1行，第colx+1列的单元数据类型值；
单元类型ctype：empty为0，string为1，number为2，date为3，boolean为4， error为5；

  • xlrd模块读取单元格内容为日期/时间

    若单元格内容的类型为date，即ctype值为3时，则代表此单元格的数据为日期

    • xlrd.xldate_as_tuple(xldate, datemode)：若xldate数据为日期/时间，则将转化为适用于datetime的元组；
      • 返回值为元组，格式为：(year, month, day, hour, minute, nearest_second)
      • xldate：sheet对象中单元格的数据
      • datemode：日期模式
      """ 读取sheet对象中的日期 """
import datetime
workbook = xlrd.open_workbook("测试.xlsx")
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)  # 结果：3
if value_type == 3:
    print("单元格数据为日期")
    cell_value = sheet2_object.cell_value(0, 1)
    print(cell_value)  # 结果：43506.0
    date_tuple = xlrd.xldate_as_tuple(cell_value, workbook.datemode)
    print(date_tuple)  # 结果：(2019, 2, 10, 0, 0, 0)
    date_value = datetime.date(*date_tuple[:3])
    print(date_value)  # 结果：2019-02-10
    date_format = date_value.strftime('%Y/%m/%d')
    print(date_format)  # 结果：2019/02/10

  • xlrd模块对合并单元格执行操作

    • 获取合并的单元格

      • 若表格为xls格式的，打开workbook时需将formatting_info设置为True，然后再获取sheet中的合并单元格；
      • 若表格有xlsx格式的，打开workbook时保持formatting_info为默认值False，然后再获取sheet中的合并单元格；
      • SheetObject.merged_cells：获取sheet中合并单元格的信息，返回值为列表；
        • 若sheet对象中无合并单元格，则返回值为空列表；
        • 列表中每个单元格信息的格式为：(row_start, row_end, col_start, col_end)
        • row_start表示合并单元格的起始行；
        • row_end表示合并单元格的结束行；
        • col_start表示合并单元格的起始列；
        • col_end表示合并单元格的结束列；
        • 合并单元格的行取值范围为[row_start, row_end]，包括row_start，不包括row_end；
        • 合并单元格的列取值范围为[col_start, col_end]，包括col_start，不包括col_end；
        • 如：(1, 3, 4, 6)：表示从第1到2行合并，从第4到第5列合并；

    • 读取合并单元格的数据

      • 读取合并单元格数据仅需merged_cells数据中的row_start和col_start这两个索引即可
      • SheetObject.cell_value(rowx=row_start, colx=col_start)：获取合并单元格的数据
      """ 获取合并的单元格并读取单元格数据 """
# 获取xlsx格式的excel文件中的合并单元格
workbook = xlrd.open_workbook("测试.xlsx")
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("测试.xls", 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))   # 结果：合并
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))

xlwings模块

  • xlwings实际操作举例

    import xlwings as xw

wb=xw.Book("D:\桌面\新建 XLSX 工作表.xlsx")    #建立excel表连接

sht=wb.sheets["sheet1"]  #实例化工作表对象

wb.fullname #返回工作表绝对路径
print(wb.fullname)

sht.name    #返回工作蒲名字
print(sht.name)

sht.range('A1').value="123"     #在单元格A1中写入数据123

sht.range('A1').value       #读取单元格A1的内容
print(sht.range('A1').value)

sht.range('A1').clear() #清除单元表内容和格式

sht.range('A1').column   #获取单元格列标
sht.range('A1').row     #获取行标
sht.range('A1').column_width    #获取列宽
sht.range('A1').row_height      #获取行高
print(sht.range('A1').column ,sht.range('A1').row ,sht.range('A1').column_width ,sht.range('A1').row_height )

sht.range('A1').rows.autofit()  #行高自适应
sht.range('A1').columns.autofit()#列宽自适应

sht.range('A1').color=(34,156,65)   #给单元格A1上背景色

sht.range('A1').color   #返回单元格颜色的RGB值
print(sht.range('A1').color)

sht.range('A1').color = None  #清楚单元格颜色
print(sht.range('A1').color)

sht.range('A1').formula='=SUM(B6:B7)'   #输入公式，相应单元格执行结果

sht.range('A1').formula_array   #获取单元格公式

sht.range('A1').value=[['a1','a2','a3'],[1,2,3]]  #向指定单元格位置写入批量信息

sht.range('A1').expand().value  #使用expand()方法读取表中批量数据
print(sht.range('A1').expand().value)
posted @ 2023-01-31 21:46  爱learn  阅读(236)  评论(0)  编辑  收藏  举报