How to read and write excel tables in python

I have been doing some data processing and calculation work recently. Because the data is saved in .CSV format, I started to use Excel to process it directly. But while doing it, I found that the repetitive work actually didn't make much sense, so I thought about writing a small tool to help deal with it. I happened to read in a book before about using Python to process Excel tables, but unfortunately I didn't read it carefully.

Python mainly uses the two libraries xlrd and xlwt to operate Excel, that is, xlrd is a library for reading Excel, and xlwt is a library for writing Excel.

Writing Excel in Python——xlwt(Recommended learning: Python video tutorial)

The difficulty of writing Excel in Python is not to construct a The Workbook itself, but the populated data, is outside the scope.

In the operation of writing Excel, there are also thorny problems. For example, writing merged cells is more troublesome. In addition, there are different writing styles.

The detailed code is as follows:

import xlwt

#设置表格样式
def set_style(name,height,bold=False):
    style = xlwt.XFStyle()
    font = xlwt.Font()
    font.name = name
    font.bold = bold
    font.color_index = 4
    font.height = height
    style.font = font
    return style

#写Excel
def write_excel():
    f = xlwt.Workbook()
    sheet1 = f.add_sheet('学生',cell_overwrite_ok=True)
    row0 = ["姓名","年龄","出生日期","爱好"]
    colum0 = ["张三","李四","恋习Python","小明","小红","无名"]
    #写第一行
    for i in range(0,len(row0)):
        sheet1.write(0,i,row0[i],set_style('Times New Roman',220,True))
    #写第一列
    for i in range(0,len(colum0)):
        sheet1.write(i+1,0,colum0[i],set_style('Times New Roman',220,True))

    sheet1.write(1,3,'2006/12/12')
    sheet1.write_merge(6,6,1,3,'未知')#合并行单元格
    sheet1.write_merge(1,2,3,3,'打游戏')#合并列单元格
    sheet1.write_merge(4,5,3,3,'打篮球')

    f.save('test.xls')

if __name__ == '__main__':
    write_excel()

Python reads Excel - xlrd

Python reads Excel tables. Compared with xlwt, xlrd provides There are many interfaces, but there are also several troublesome problems in the process, such as reading dates and reading merged cell contents.

The overall idea is to open the file, select the table, read the row and column content, and read the data in the table

The detailed code is as follows:

import xlrd
from datetime import date,datetime

file = 'test3.xlsx'

def read_excel():

    wb = xlrd.open_workbook(filename=file)#打开文件
    print(wb.sheet_names())#获取所有表格名字

    sheet1 = wb.sheet_by_index(0)#通过索引获取表格
    sheet2 = wb.sheet_by_name('年级')#通过名字获取表格
    print(sheet1,sheet2)
    print(sheet1.name,sheet1.nrows,sheet1.ncols)

    rows = sheet1.row_values(2)#获取行内容
    cols = sheet1.col_values(3)#获取列内容
    print(rows)
    print(cols)

    print(sheet1.cell(1,0).value)#获取表格里的内容，三种方式
    print(sheet1.cell_value(1,0))
    print(sheet1.row(1)[0].value)

More Python related technical articles , please visit the Python Tutorial column to learn!

The above is the detailed content of How to read and write excel tables in python. For more information, please follow other related articles on the PHP Chinese website!