In python, the following three modules are used to read, write and append data to excel tables:
1. wlrd reading data in excel
2. xlwt creates a new excel file, and then writes and saves the contents of this file.
3. xlutils reads an excel file, and then modifies or appends it. xlsx cannot be operated, only xls can be operated.
1, Reading excel
xlrd module is needed to read excel
1. Import module
2. Open excel file
table = data.sheets() #Get by index order table = data.sheet_by_index(0) #Get by index order table = data.sheet_by_name(u'Sheet1')#Get by name
The code is as follows:
import xlrd data = xlrd.open_workbook(r"C:\Users\907968\Desktop\test.xlsx") table1 = data.sheets() table2 = data.sheet_by_index(0) table3=data.sheet_by_name(u'Sheet1') print(table1) print(table2) print(table3)
<xlrd.sheet.Sheet object at 0x0000000002F7F208> <xlrd.sheet.Sheet object at 0x0000000002F7F208> <xlrd.sheet.Sheet object at 0x0000000002F7F208>
3. Get the number of rows and columns
import xlrd data = xlrd.open_workbook(r"C:\Users\907968\Desktop\test.xlsx") table = data.sheets() nrows = table.nrows ncols = table.ncols print("Number of rows:%d\n Number of columns:%d"%(nrows,ncols))
Number of rows: 13
Number of columns: 3
4. Get the values of the whole row and column and return them in list form
rows = table.row_values(0) cols = table.col_values(0) print("rows:%s\ncols:%s"%(rows,cols))
rows:['A1', 'B1', 'C1'] cols:['A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10', 'A11', 'A12', 'A13']
5. Get cell data
cell_A1 = table.cell_value(0,0) cell_C4 = table.cell_value(3,2) print("A1:%s\nC4:%s"%(cell_A1,cell_C4))
You can also use row and column indexes to get cell data
cell_A1 = table.row(0).value cell_C4 = table.col(2).value print("A1:%s\nC4:%s"%(cell_A1,cell_C4))
3, Write excel operation
2. Create workbook
workbook = xlwt.Workbook(encoding='utf-8', style_compression=0)
Encoding: set the character code, which is generally set as follows: w = Workbook(encoding = 'utf-8'), then you can output Chinese in excel. The default is ascii
style_compression: indicates whether to compress. It is not commonly used.
3. Create a sheet object that corresponds to a table in an Excel file
sheet = workbook.add_sheet('test', cell_overwrite_ok=True)
test is the name of the table, cell_overwrite_ok indicates whether cells can be overwritten. In fact, it is a parameter instantiated by Worksheet. The default value is False
4. Adding data to a table
sheet.write(0, 0, 'EnglishName') # where '0-row, 0-column' specifies the cell in the table, and 'EnglishName' is the content written to the cell
sheet.write(1, 0, 'Marcovaldo') txt1 = 'Chinese name' sheet.write(0, 1, txt1) txt2 = 'Marcovado' sheet.write(1, 1, txt2)
4, Additional data
import xlrd import xlutils.copy data = xlrd.open_workbook(r'C:\Users\907968\Desktop\test222.xls') ws = xlutils.copy.copy(data) table=ws.get_sheet(0) table.write(0,3,'D1') ws.save(r'C:\Users\907968\Desktop\test222.xls')
A1 B1 C1
A1 B1 C1 D1
It's good to learn Python well, whether in employment or sideline, but to learn python, you still need to have a learning plan. Finally, let's share a full set of Python learning materials to help those who want to learn Python!
All directions of Python is to sort out the commonly used technical points of Python and form a summary of knowledge points in various fields. Its purpose is that you can find corresponding learning resources according to the above knowledge points to ensure that you learn more comprehensively.
If a worker wants to do well, he must sharpen his tools first. The commonly used development software for learning Python is here, which saves you a lot of time.
When we watch videos, we can't just move our eyes and brain without hands. The more scientific learning method is to use them after understanding. At this time, the hand training project is very suitable.
Optical theory is useless. We should learn to knock together and practice, so as to apply what we have learned to practice. At this time, we can make some practical cases to learn.
We must learn Python in order to find a high paying job. The following interview questions are the latest interview materials from front-line Internet manufacturers such as Alibaba, Tencent and byte, and Alibaba boss has given authoritative answers. After brushing this set of interview materials, I believe everyone can find a satisfactory job.
This complete set of Python learning materials has been uploaded to CSDN. Friends can scan the official authentication QR code of CSDN below on wechat and get it for free [guaranteed to be 100% free]