Day53--python working with Excel data

(under Windows Operations) first install some modules that work with Excel:

1 pip install xlrd        #  for reading Excel data 2 pip install XLWT        #  Used to write Excel data 3 pip install xlutils     #  for modifying Excel data

Python reads Excel data:

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 4 Importxlrd5 6 defreadexcel ():7data = Xlrd.open_workbook ('1.xlsx')#open an Excel table8Table = data.sheets () [0]#Open the first table of an Excel table9nrows = Table.nrows#get the number of rows per tableTen      forLineinchRange (nrows):#traverse each row One         Print(Table.row_values (line))#get the value of each row A  - if __name__=="__main__": -Readexcel ()

['name','Sex','Age']['Xiao Ming','male', 17.0]['Little Red','female', 18.0]['Xiao Li','male', 19.0]['Xiao Zhang','male', 20.0]

Python reads a column of data:

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 4 Importxlrd5 6 defreadexcel ():7data = Xlrd.open_workbook ('1.xlsx')#open an Excel table8Table = Data.sheets () [1]#Open the first table of an Excel table9Ncols = Table.ncols#get the number of columns per tableTen      forColinchRange (Ncols):#iterate through each column One         Print(Table.col_values (COL) [0])#get the value of the first column A  - if __name__=="__main__": -Readexcel ()

Python to create an Excel table:

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 4 ImportXLWT5 6Excel = XLWT. Workbook ()#Create an Excel file7Sheet1 = Excel.add_sheet ("Sheet1")#add a table with the table named "Sheet1"8Sheet1.write (0, 0,"Name")#indicates that the first column of the first row writes the content "Name"9Sheet1.write (1, 0,"John")#indicates that the first column of the second row writes the content "John"TenSheet1.write (2, 0,"Jeny")#indicates that the first column of the third row writes the content "Jeny" OneExcel.save ("1.xls")#Save the Excel file and name it "1.xls"

Python reads a column of data:

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 4 Importxlrd5 6 defreadexcel ():7data = Xlrd.open_workbook ('1.xlsx')#open an Excel table8Table = Data.sheets () [1]#Open the first table of an Excel table9Ncols = Table.ncols#get the number of columns per tableTen      forColinchRange (Ncols):#iterate through each column One         Print(Table.col_values (COL) [0])#get the value of the first column A  - if __name__=="__main__": -Readexcel ()

Python to create an Excel table:

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 4 ImportXLWT5 6Excel = XLWT. Workbook ()#Create an Excel file7Sheet1 = Excel.add_sheet ("Sheet1")#add a table with the table named "Sheet1"8Sheet1.write (0, 0,"Name")#indicates that the first column of the first row writes the content "Name"9Sheet1.write (1, 0,"John")#indicates that the first column of the second row writes the content "John"TenSheet1.write (2, 0,"Jeny")#indicates that the first column of the third row writes the content "Jeny" OneExcel.save ("1.xls")#Save the Excel file and name it "1.xls"

Python modifies Excel data:

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 4 Importxlrd5 Importxlutils.copy6 7data = Xlrd.open_workbook ('1.xls')#Open an Excel file8Excel = Xlutils.copy.copy (data)#equivalent to copying an Excel file and then manipulating it on the copied file9Sheet1 = Excel.get_sheet (0)#get the table to be modified (here I modify the first table of the Excel file)TenSheet1.write (2, 0,'Tom')#change the data in the first column of the third row to ' Tom ' OneExcel.save ('1.xls')#Save Excel File

Day53--python working with Excel data