xlrd/xlwt读写excel文件

小灰灰 2022-11-15 12:23 225阅读 0赞

1.安装

> pip install xlrd  
> pip install xlwt

**2.xlrd读excel**

import xlrd
    path=""
    book=xlrd.open_workbook(path)
    sheet0=book.sheets()[0]#表格索引，从0开始
    print(sheet0)
    sheet1=book.sheets()[1]
    print(sheet1)
    rows=sheet1.nrows
    cols=sheet1.ncols
    print("表格总行数：",rows)
    print("表格总列数：",cols)
    print("第一行的值:",sheet1.row_values(0))
    print("第一列的值:",sheet1.col_values(0))
    print("第一行第一列的值为：",sheet1.cell(0,0).value)

excel表格内容为:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdWxhbmJh_size_16_color_FFFFFF_t_70]

运行结果为：

<xlrd.sheet.Sheet object at 0x0000023EEF7C29C8>
    <xlrd.sheet.Sheet object at 0x0000023EEF7C2188>
    表格总行数： 4
    表格总列数： 4
    第一行的值: ['0.0', '0.1', '0.2', 0.3]
    第一列的值: ['0.0', '1.0', '2.0', '3.0']
    第一行第一列的值为： 0.0

其他常见用法：

zz=book.sheets()#以列表形式返回所有的表
    sheet0=book.sheet_by_index(0)#根据索引获取列表
    sheet1=book.sheet_by_name('Sheet1') #根据表名获取
    def sheet_loaded(sheet_name_or_index):#加载表格，表格不存在则返回False
        # returns True if sheet is loaded, ``False`` otherwise.
        pass
    
    def unload_sheet(sheet_name_or_index):#删除表格
        #param sheet_name_or_index: Name or index of sheet to be unloaded.
        pass

**3.xlwt写excel**

import xlwt
    path2=""
    book=xlwt.Workbook()
    sheet0=book.add_sheet('testsheet0')
    sheet0.write(0,0,"name")
    sheet0.write(0,1,'sex')
    sheet0.write(0,2,'age')
    sheet0.write(1,0,'Tom')
    sheet0.write(1,1,'mail')
    sheet0.write(1,2,18)
    sheet0.write(2,0,'Jain')
    sheet0.write(2,1,'famail')
    sheet0.write(2,2,22)
    book.save(path2)

运行结果为：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdWxhbmJh_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdWxhbmJh_size_16_color_FFFFFF_t_70]: /images/20221022/36e634d1046a4364831637553a60b2b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdWxhbmJh_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210401131614650.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdWxhbmJh,size_16,color_FFFFFF,t_70