Implementing excel table reading and writing based on Python

First install the corresponding xlrd and xlwt

Open the cmd command window and enter pip install xlrd and pip install xlwt to install. Then enter pip list to check whether the configuration is successful:

xlrd operation#

The next step is the commonly used syntax operation:

excel_data = xlrd.open_workbook(file path')#Get the corresponding worksheet

sheet = excel_data.sheets()[worksheet serial number]#Get the data of a row corresponding to a certain table in the corresponding worksheet

sheet.row_values(2)#

sheet.cell(6,1).value#corresponding For data in a certain column

Take this table as an example and try the corresponding statement first:

First specify the corresponding table and one of the tables, otherwise an error will be prompted and the compilation will not pass. Run the above code:

Source code as follows:

import xlrd

import xlwt

excel_data = xlrd.open_WORKBOOK (file path ')

excel_data = xlrd.open_Workbook SKTOP practical excel table template.xls')

sheet = excel_data.sheets()[worksheet serial number]

sheet = excel_data.sheets()[0]

print(sheet.row_values(2)) #Print a certain row of data

print(sheet.col_values(1))#Print a certain column

print()

print (sheet.cell(6,1).value)#Print a specific value

The operation results are as follows:

Comparing the tables brings something worth noting. The specified number of rows and columns starts from 0. When opening the specified table, please note that the absolute path must be included along with the file name and file suffix to successfully read the data.

Next, the experiment reads the data of a specified cell:

sheet.cell(row, column).value

Run and get:

Compare the table and get Data:

is (7, B), which is consistent with the counting from zero mentioned before.

In addition to some of the operations listed above, there are other common statements that can be viewed in relevant documents and official websites. There are relevant links at the end of the article. Next, we will experiment with another xlwt library to implement writing operations to excel tables.

xlwt operation#

The general concerns about excel tables are the following aspects:

Creating workbooks and tables

Writing into cells

Common format settings (currency text, etc.)

Creating formulas

Save

Next, try each of the functions mentioned above in turn:

The corresponding objects of workbooks and tables are: workbook, sheet

2. Cell assignment:

Here I tried using negative numbers

There is no problem with compilation. Save it later to check whether the result is correct.

3. Format setting:

It can be seen that in addition to the row, column and value, the corresponding incoming parameters also include style, which is omitted by default. The format of the test settings here is bold. Other formats can be implemented by referring to the search document at the end of the article. Here are some other common formats:

style = easyxf(num_format_str='$#,##0.00')

# or set it directly on the style object

style = easyxf('font: bold 1')

style.num_format_str = '$#,##0.00'

sheet.write(0, 0, '100.00', style)

4. Create formula:

Excel formula can be implemented using xlwt.Formula. W Sheet.write (0, 0, xlwt.Formula ('Hyperlink ("http://yujitomita.com"; "click me"))

5. Save operation:

workbook.save ("pythonon (" pythonon ("pythonon (" pythonon ("pythonon ".

Running results:

According to the previous pit (-1,0), the trial running results are here:

# encoding: utf-8

import xlrd

import xlwt

workbook = xlwt.Workbook()

sheet = workbook. add_sheet("Sheet 1")

#Create a workbook and a worksheet

sheet.write(-1, 0, 'Python')#The rows and columns correspond

style = xlwt.easyxf('font : bold 1')

sheet.write(1, 1, 'style ', style)

workbook.save('C:Usershys macDesktopmr.cpython.xls')

The error result is as follows:

SyntaxError: (unicode error ) 'unicodeescape' codec can't decode bytes in position 2-3: truncated UXXXXXXXX escape

Encountered a file encoding problem, modified the next sentence:

workbook.save(r'C:Usershys macDesktopmr.cpython.xls')

It’s back to the pit left at the beginning. The cell assignment rules should be consistent with usage habits and cannot be negative numbers. The final modification is:

import xlrd

import xlwt

workbook = xlwt.Workbook()

sheet = workbook.add_sheet("Sheet 1")

#Create workbook (workbook) and worksheet (sheet)

sheet .write(1, 0, 'Python')#The rows and columns correspond to

style = xlwt.easyxf('font: bold 1')

sheet.write(1, 1, 'style ', style)

workbook.save( r'C:Usershys macDesktopmr.cpython1.xls') #The file path must exist and cannot have the same name