Python operations Excel

First, use XLRD to read Excel

1, the installation of XLRD:

Install xlrd==0.9. 4

2. Basic Operation Example:

#Coding:utf-8ImportXlrd#Import xlrd ModuleXlsfile=r"D:\workspace\host.xls"#get the Book object for ExcelBook = Xlrd.open_workbook (Filename=none, file_contents=Xlsfile.read ())#can also be directly written as follows:Book =Xlrd.open_workbook (xlsfile)#There are two ways to get the sheet object:Sheet_name = Book.sheet_names () [0]#gets the name of the sheet of the specified indexPrintSheet_namesheet1= Book.sheet_by_name (Sheet_name)#get Sheet object by sheet nameSheet0 = Book.sheet_by_index (0)#get sheet objects by sheet index#get the number of rows and columns:nrows= Sheet.nrows#total number of rowsNcols = Sheet.ncols#total number of columns#gets the value of the specified row, column, and return object as a list of values:Row_data= sheet.row_values (0)#get the data list for line 1thCol_data = sheet.col_values (0)#get the data list for column 1th#通过cell的位置坐标获取指定cell的值: Cell_value1= Sheet.cell_value (0,1)#get only the contents of the cell, such as: http://xx.xxx.xxPrintcell_value1cell_value2= Sheet.cell_value (0,1)#In addition to the contents of the cell, there are additional properties, such as: Text:u ' http://xx.xxx.xx 'PrintCell_value2

Ii. using the XLWT module to write Excel

1. Installation:

Install XLWT

2. Basic Operation:

#Coding:utf-8ImportXLWT#Create a Wbk object, use Utf-8 encoding, and set the compression ratioWBK = XLWT. Workbook (encoding='Utf-8', style_compression=0)#Add a Sheet objectSheet = Wbk.add_sheet ('Sheet 1', cell_overwrite_ok=true)#The second parameter is used to confirm whether the same cell cell can reset the valueSheet.write (0,0,'Sometext')#writes data to a specified cellSheet.write (0,0,'Overwrite')#overwrite write, requires cell_overwrite_ok=true#Set cell style, specify font format, etc.style =XLWT. Xfstyle () Font=XLWT. Font () Font.Name='Times New Roman'Font.Bold=Truestyle.font=fontsheet.write (0,1,'text', Style) Wbk.save ('D:\test.xls')#the file name must exist

Python operations Excel