[Go] Python action on Excel

"Turn" http://www.cnblogs.com/belid/archive/2013/05/02/3054119.html

A pair of Excel read operations

Install XLRD module, go to official website http://pypi.python.org/pypi/xlrd download, Pyhon setup.py build, python setup.py install. The following demo is used in detail.

One row column row    Two column    row three columns two rows one column    two rows two columns    two rows three columns 4    5    8    9
Contents of the EXECL (test.xlsx) file

 1 #coding: UTF8 2 import xlrd 3 4 execl_name=r "F:\python_source\test.xlsx" 5 print "open Excel file Read data". Decode (' UTF8 '). Encode (' GBK ') #注意编码转换 6 Data=xlrd.open_workbook (execl_name) 7 8 table=data.sheets () [0] #通过索引顺序获取 9 #table =DATA.S Heet_by_index (0) #通过索引顺序获取10 #table =data.sheet_by_name (' Sheet1 ') #通过名称获取11 print "-------------------------------- ---"Print table.row_values (2) #获取第三行整行的值 (array) print table.col_values (2) #获取第三列整列的值 (array) print"-----                           ------------------------------"Print" prints all the values of the first line: ". Decode (' UTF8 ') for I in Table.row_values (0): Print I, #打印第一行所有的值19 print print '-----------------------------------' print ' line number '. Decode (' UTF8 ') + ":" , table.nrows #行数23 print "Number of columns". Decode (' UTF8 ') + ":", table.ncols #列数24 print---------------- -------------------all values for "execl print" output: ". Decode (' UTF8 ') for x in range (table.nrows): $ for I in Table.row_value S (x): Print I,#print table.row_values (x) print32 print '-----------------------------------' print ' cell: '. Decode (' UTF8 ') 35 Print Table.cell (0,0). Value #单元格, one row of print Table.cell (3,2). Value #四行三列37 print '------------------- ----------------' #cell_A1 =table.row (0) [0].value-print "cell_a1%s"% table.row (0) [0].value #一行一列41 Print] Cell_b     1%s "% table.row (0) [1].value #一行二列42 print '----------------------------------' for I in Range (Table.ncols): 45 Print Table.row (1) [I].value #输出所有第二行的值46 print '----------------------------------' for I in range (table. nrows): Table.col print (1) [I].value #输出所有第二列的值

Output Result:

Open Excel file read Data-----------------------------------[4.0, 5.0, 6.0][u ' \u4e00\u884c\u4e09\u5217 ', U ' \u4e8c\u884c\u4e09 \u5217 ', 6.0, 9.0]-----------------------------------print all the values of the first row: one row, row by column, two columns, three columns-----------------------------------rows : 4 columns: 3-----------------------------------output EXECL All values: Row by column row Two column row three column two row one column two row two column two row three column 4.0 5.0 6.0 7.0 8.0 9.0--------------- --------------------cell: Row by column 9.0-----------------------------------cell_a1 row by column Cell_b1 Row two column----------------------------------two row one column two row two column two row three column----------------------------------row two column two row two column 5.08.0

Tips: Due to the name of the script file named xlrd.py, and then debugging the time has not been successful, is the module import problem, file name and module name is duplicated.

Ii. Write operations to Excel

Install pip install XLWT

#coding: Utf-8import xlwtimport re WBK=XLWT. Workbook (encoding= ' cp936 ')  //Modify Code Sheet=wbk.add_sheet (' Sheet 1 ') ' Sheet.write (0,1, "test")   // Write Sheet.write (0,2, "Hello") wbk.save (' Test.xls ')    //Save ' Sheet.write (0,0, "account name". Decode (' UTF8 ')) sheet.write (0,1, "Account ID". Decode (' UTF8 ')) sheet.write (0,2, "role name". Decode (' UTF8 ')) sheet.write (0,3, "rank". Decode (' UTF8 ')) sheet.write (0,4 , "recharge Amount". Decode (' UTF8 ')) sheet.write (0,5, "registration Time". Decode (' UTF8 ')) sheet.write (0,6, "Last offline Time". Decode (' UTF8 ')) Sheet.write (0,7, "Registered IP". Decode (' UTF8 ')) A=[]l1 = 1fopen=open (r "E:\test\4.txt", ' R ') for line in fopen:    A=re.split (r ' \s ', line)    #a =line.split (')    sheet.write (l1,0,a[0])    sheet.write (l1,1,a[1])    Sheet.write (l1,2,a[ 2])    Sheet.write (l1,3,a[3])    sheet.write (l1,4,a[4])    sheet.write (l1,5,a[5]+ "" +a[6])    sheet.write (L1,6,a[7])    Sheet.write (l1,7,a[9])    L1 + = 1fopen.close () wbk.save (' 6_7.xls ')

[Go] Python action on Excel