Python operations Excel

First, python operations Excel , Python operations Excel uses XLRD, XLWT, and xlutils modules, XLRD modules are read in Excel, XLWT modules are written in Excel, and xlutils is used to modify Excel. These modules are installed using PIP, the following are the use of these modules.

Second, xlrd Module , the XLRD module is used to read Excel, using the following details :

1234567891011121314151617

import xlrd #打开excel WB=xlrd. Open_workbook(' abc.xlsx ')#打开的这个excel必须存在, or will error #获取所有sheet页的名字Print(wb. Sheet_names()) #按名字查找第二张表单# sheet=wb.sheet_by_name (' ABC2 ') #根据sheet页的名字获取sheet页sheet = wb. Sheet_by_index(0)#根据sheet页的索引获取sheet页 #获取sheet页的行数和列数 Print(sheet. nrows) Print(sheet. Ncols) #打印每行信息 for rownum in range(sheet. Nrows): #循环取每行的数据 Print(sheet. Row_values(rownum))#取每行的数据 #按照索引打印对应单元格内容cell_a2=sheet. Cell(0,1). Value#获取指定单元格的值, the first value is a column, the second value is a row Print(cell_a2)

three, XLWT module , the XLWT module is used to write Excel, write a new Excel

1234567891011121314151617

import XLWT title = [ ' name ',' age ',' Gender ',' score ' ]Stus=[[' Mary ',20,Female,89.9],[ ' Mary ' ,20, ' female ' ,89.9],[ Mary" ,20, ' female ' ,89.9] [ ' Mary ' ,20, ' female ' ,89.9]" #新建一个excel对象wbk = xlwt. Workbook() sheet page for #添加一个名为 curriculumsheet = wbk. Add_sheet(' stu ') for i in range(lentitle):#写入表头 sheet. Write(0,i,title[i])#写入每行, first value is row, second value is column, third is write value for i in range(len(stus)): if i! =0:#如果不是表头的话 for J in range(4): sheet. Write(i,J,stus[i][J])#循环写入每行数据 #保存数据到 ' test.xls ' filewbk. Save(' Szz.xls ')#保存excel必须使用后缀名是. xls, not can be. xlsx

Four, xlutils module ,xlutils module to modify the contents of Excel, can not directly modify the original Excel content, you must first copy a new Excel, and then the new Excel to modify the following usage:

1234567891011

From xlrd import open_workbook#导入xlrd模块中打开excel模块 From xlutils. Copy import copy#导入xlutils模块的复制excel模块 rb = open_workbook(' Szz.xls ') Sheet obtained by #通过sheet_by_index () rs = rb. Sheet_by_index(0) #复制一个excel wb = copy(RB) #通过获取到新的excel里面的sheet页 ws = wb. Get_sheet(0) ws. Write(1, 0, ' Lily ')#写入excel, the first value is a row, the second value is a column WB. Save (' Szz_new.xls ')#保存新的excel, saving Excel must use the suffix name of. xls, not can be. xlsx

Python operations Excel