When faced with hundreds of excel file , When repeated reading and writing is required , Will you have a big head ?

Instead of spending days doing these tedious and meaningless operations , It's better to learn python How to batch read / write excel file , For the following introduction xlrd and xlwt Library processing Excel data .

Let's build a new one excel form (table1.xlsx) For case explanation :

one , use xlrd Library read excel data

1,python read excel File specific data

 
import xlrd data =
xlrd.open_workbook(r'E:\Anaconda\data\hc200115\table1.xlsx') # open xlsx file ( Absolute path )
table = data.sheets()[0] # Open the first table nrows = table.nrows # Gets the number of rows in the table # Loop line by line output for i
in range(nrows): print(table.row_values(i)[:4]) # Get the first four columns of data ['Name', 'Age',
'Class', 'Score'] ['Tom', 19.0, 162061.0, 80.0] ['Jack', 20.0, 172051.0, 90.0]
['Alan', 18.0, 150461.0, 100.0] # Excel Rows of data , Number of columns nrows=table.nrows
ncols=table.ncols print(nrows) print(ncols) 4 4
( notes ) open xlsx The difference between relative path and absolute path :

data = xlrd.open_workbook(r'E:\Anaconda\data\hc200115\table1.xlsx')   # Absolute path
data = xlrd.open_workbook('table1.xlsx')                                     
            # Relative path ( Current working directory )

2,python Read read excel File all data
import xlrd # Open a xls file workbook = xlrd.open_workbook('table1.xlsx')
# Grab all sheet The name of the page worksheets = workbook.sheet_names() print('worksheets is %s'
%worksheets) worksheets is ['Sheet1'] # Locate to sheet1 worksheet1 =
workbook.sheet_by_name(u'Sheet1') # ergodic sheet1 All rows in row num_rows = worksheet1.nrows
for curr_row in range(num_rows): row = worksheet1.row_values(curr_row)
print('row%s is %s' %(curr_row,row)) # ergodic sheet1 All columns in col num_cols =
worksheet1.ncols for curr_col in range(num_cols): col =
worksheet1.col_values(curr_col) print('col%s is %s' %(curr_col,col)) row0 is
['Name', 'Age', 'Class', 'Score'] row1 is ['Tom', 19.0, 162061.0, 80.0] row2 is
['Jack', 20.0, 172051.0, 90.0] row3 is ['Alan', 18.0, 150461.0, 100.0] col0 is
['Name', 'Tom', 'Jack', 'Alan'] col1 is ['Age', 19.0, 20.0, 18.0] col2 is
['Class', 162061.0, 172051.0, 150461.0] col3 is ['Score', 80.0, 90.0, 100.0]
# ergodic sheet1 All cells in cell for rown in range(num_rows): for coln in range(num_cols):
cell = worksheet1.cell_value(rown,coln) print(cell) Name Age Class Score Tom
19.0 162061.0 80.0 Jack 20.0 172051.0 90.0 Alan 18.0 150461.0 100.0
two , use xlwt Library to write excel data
import xlwt # establish workbook and sheet object workbook = xlwt.Workbook() # be careful Workbook Beginning of W Capitalize
sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True) # towards sheet Write data to page
sheet1.write(0,0,'Name1') sheet1.write(0,1,'aaaa') sheet1.write(1,0,'Name2')
sheet1.write(1,1,'bbbb') # Save this excel file , Directly overwrite when there is a file with the same name workbook.save('test.xlsx')
print(' establish excel File complete !') establish excel File complete !
( Generate in working directory “test.xlsx” file )

 

 

Technology
©2019-2020 Toolsou All rights reserved,
Android Using wechat in H5 Payment result refresh during payment shock !!C++ Can make a sound ! Basic operation of single linked list C Language explanation Java Implement an epidemic number management system 2021 year 11 World programming language ranking linux upper mysql Invalid command _linux lower mysql The command is useless Java project : Campus dormitory management system (java+jsp+javaweb+mysql+ajax) Wechat applet development project directory linux ubuntu Which version ,Ubuntu Which version is the best ?python Code painting cherry blossoms - How to use Python Draw a beautiful cherry blossom