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 the PIP list to check whether the configuration was successful:

XLRD Operation #

The following are common syntax operations:

Excel_data = xlrd.open_workbook (file path ') #得到对应的工作表

Sheet = excel_data.sheets () [Sheet number] #得到对应工作表中的某一个表格

Sheet.row_values (2) #对应的某一行的数据

Sheet.cell (6,1). value# corresponds to a column of data

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

Specify the corresponding table and one of the tables first, or you will be prompted to fail the compilation. Run the above code to get:

The source code is as follows:

Import xlrd

Import XLWT

Excel_data = xlrd.open_workbook (file path ')

Excel_data = Xlrd.open_workbook (R ' C:\Users\hys mac\desktop\ practical Excel form template. xls ')

Sheet = excel_data.sheets () [Sheet number]

Sheet = excel_data.sheets () [0]

Print (Sheet.row_values (2)) #打印某一行数据

Print (Sheet.col_values (1)) #打印某一列

Print ()

Print (Sheet.cell (6,1). Value) #打印某个具体值

The results of the operation are as follows:

Compare the table to get some notable places, the number of rows specified is starting from 0, when you open the specified table, you should pay attention to the absolute path with the filename and file suffix to be taken together to successfully read the data.

The next experiment reads the data for a specified cell:

Sheet.cell (row, column). Value

Run to get:

Compare tables to get data:

is one of the (7,b), and the zero-based count mentioned earlier is consistent.

In addition to some of the above-listed operations, there are other common statements can be viewed in the relevant documents and official website, at the end of the relevant links, and then experiment with another XLWT library to achieve the Excel table write operations.

XLWT Operation #

The general concerns about Excel tables are the following:

Create workbooks and tables

Write cells

Common formatting (currency text, etc.)

Create a formula

Save

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

The workbook and table correspond to the following objects: Workbook,sheet

2. Assign values to cells:

Here's a try. Using negative numbers

Compile no problem leave it behind and check the results correctly after saving.

3. Format settings:

It is visible that the corresponding incoming parameter includes the default omitted parameter in addition to the row and column values. The format of the experiment settings here is bold. Other formats can be referenced to find end-of-document implementations. Here are some other formats that are common:

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 a formula:

Use XLWT. Formula can implement Excel formulas.

Sheet.write (0, 0, XLWT. Formula (' HYPERLINK ' ("http://yujitomita.com"; "Click Me"))

5. Save the Operation:

Workbook.save ("Python.xls")

Wbk.save (' path name ')

It is worth reminding that the name of the worksheet is less than 31 characters and cannot include special characters (:. , etc.).

Operation Result:

Follow the previous pit ( -1,0) here to test the result:

# Encoding:utf-8
Import xlrd
Import XLWT

Workbook = xlwt. Workbook ()
Sheet = workbook.add_sheet ("Sheet 1")

#创建工作簿 (Workbook) and Worksheets (sheet)

Sheet.write ( -1, 0, ' Python ') #行列对应

style = XLWT.EASYXF (' Font:bold 1 ')
Sheet.write (1, 1, ' style ', style)

Workbook.save (' C:\Users\hys mac\desktop\mr.c\python.xls ')

The error results are as follows:

SyntaxError: (Unicode error) ' Unicodeescape ' codec can ' t decode bytes in position 2-3: truncated \uxxxxxxxx escape

Encountered the problem of file encoding, modify the following sentence:

Workbook.save (R ' C:\Users\hys Mac\desktop\mr.c\python.xls ')

Back to the beginning of the pit, cell assignment rules should be consistent with usage habits cannot be negative. The last modification gets:

Import xlrd
Import XLWT

Workbook = xlwt. Workbook ()
Sheet = workbook.add_sheet ("Sheet 1")

#创建工作簿 (Workbook) and Worksheets (sheet)

Sheet.write (1, 0, ' Python ') #行列对应

style = XLWT.EASYXF (' Font:bold 1 ')
Sheet.write (1, 1, ' style ', style)

Workbook.save (R ' C:\Users\hys Mac\desktop\mr.c\python1.xls ') #文件路径要存在且不能同名

