How to read and write excel tables in python

(*-*)浩
Release: 2019-07-09 10:27:41
Original
6715 people have browsed it

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.

How to read and write excel tables in python

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()
Copy after login

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)
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!