Python——读写Excel文件

需求：

　　读取原始Exdel数据，将其中的某一列数据进行增删改操作，然后将改后的数据写入到Exdel文件中

 

import xlrd
import xlwt
import re

# 读取excel文件的数据
def read_xlsx():
    workbook = xlrd.open_workbook('11.xlsx')
    booksheet = workbook.sheet_by_name('Sheet1')
    p = list()
    for row in range(booksheet.nrows):
        row_data = []
        for col in range(booksheet.ncols):
            cel = booksheet.cell(row, col)
            val = cel.value
            try:
                val = cel.value
                val = re.sub(r'\s+', '', val)
            except:
                pass

            if type(val) == float:
                val = int(val)
            else:
                val = str(val)
            row_data.append(val)
        p.append(row_data)

    return p

# 将数据写入到exdel文件中，并保存
def write_xlsx(data):
    book = xlwt.Workbook(encoding='utf-8', style_compression=0)
    sheet = book.add_sheet('test', cell_overwrite_ok=True)
    # 其中的test是这张表的名字,cell_overwrite_ok，表示是否可以覆盖单元格，其实是Worksheet实例化的一个参数，默认值是False
    # 向表test中添加数据
    p = 0
    for ii in data_list:
        p += 1
        print(ii)
        for k in range(len(ii)):
            sheet.write(p-1, k, ii[k])
            print(ii[k])
    # 最后，将以上操作保存到指定的Excel文件中
    book.save(r'test.xls')  # 在字符串前加r，声明为raw字符串，这样就不会处理其中的转义了。否则，可能会报错


if __name__ == '__main__':
   data_list =  list()
   data_list = read_xlsx()
   k = 0
   for i in data_list:
       k += 1
       change = i[2]
       change1 = i[2][:7]
       change2 = i[2][7:8] + "层"
       change3 = i[2][7:]+"室"
       change = change1+change2+change3
       data_list[k-1][2] = change
   write_xlsx(data_list)