Using Python Libraries xlwt, xlrd, and openpyxl to Read and Write Excel Files

Data processing is a major application scenario for Python, and Excel is the most popular tool for handling tabular data, so interacting with Excel files using Python is essential.

The commonly used Python libraries for Excel are xlrd (read .xls and .xlsx), xlwt (write .xls), xlutils (utility functions), and openpyxl (read/write/modify .xlsx, does not support .xls).

Install the libraries via the command line:

<code>pip install xlwt</code><code>pip install xlrd</code><code>pip install openpyxl</code>

Using xlwt to write data

Steps: import the library, create a workbook, add a sheet, write cells, and save the file.

<code># coding = utf-8</code><code>import xlwt</code><code>book = xlwt.Workbook()</code><code>sh1 = book.add_sheet('登录数据')</code><code>sh1.write(0,0,'用户名')</code><code>sh1.write(0,1,'密码')</code><code>row1 = ['test','test123']</code><code>for i in range(len(row1)):</code><code>    sh1.write(1,i,row1[i])</code><code>book.save('login.xls')</code>

Using xlrd to read data

Steps: import the library, open the workbook, select a sheet, and read rows, columns, or individual cells.

<code># coding = utf-8</code><code>import xlrd</code><code>book = xlrd.open_workbook('login.xls')</code><code>sh1 = book.sheet_by_name('登录数据')</code><code>row1 = sh1.row_values(0)</code><code>print('第一行数据：', row1)</code><code>col1 = sh1.col_values(0)</code><code>print('第一列数据：', col1)</code><code>cell = sh1.cell(1,1).value</code><code>print('A2单元格的值：', cell)</code>

Using openpyxl to write data

Steps: import the library, load the workbook, select a sheet, write values by cell name or by row/column indices, and save.

<code># coding = utf-8</code><code>import openpyxl</code><code>book = openpyxl.load_workbook('test_api.xlsx')</code><code>sh1 = book['register']</code><code>sh1['I2'] = '不通过'</code><code>sh1.cell(3,9).value = '通过'</code><code>book.save('test_api.xlsx')</code>

Using openpyxl to read data

Steps: import the library, load the workbook, select a sheet, and read cells, rows, or the entire sheet.

<code># coding = utf-8</code><code>import openpyxl</code><code>book = openpyxl.load_workbook('test_api.xlsx')</code><code>sh1 = book['login']</code><code>cell1 = sh1['A1'].value</code><code>print('A1单元格的值为：', cell1)</code><code>cell2 = sh1.cell(1,2).value</code><code>print('B1单元格的值为：', cell2)</code><code>for row in sh1.iter_rows(max_row=2):</code><code>    for cell in row:</code><code>        print(cell.value, end='\t|\t')</code><code>    print()</code>