python读写excel

读写excel xlrd xlutils

目的，去除指定行列的￥符号。

from xlutils.copy import copy
import xlrd,xlwt

#xlutils:修改excel

def read(filename):
    book1 = xlrd.open_workbook(filename)
    sheet = book1.sheet_by_index(0)#根据顺序获取sheet
    row,col = sheet.nrows,sheet.ncols
    print(row, col)
    book2 = copy(book1)
    sheetwrite = book2.get_sheet(0)
    # 循环行2-row
    for i in range(2,row):
        # 循环4，5, 6列
        for j in range(4,7):
            money = sheet.cell(i,j).value
            if type(money)==str and len(money) >1 and money[0] == '￥':
                # print('写入覆盖处理',money[1:])
                sheetwrite.write(i, j, money[1:])
        # print(i)
    book2.save(filename)
    return True

read('test.xls')

pandas读写excel

import pandas as pd

data = pd.read_excel("liteng-20221107（76户2月）.xls")
# print(len(data['Unnamed: 4']))
for index in range(len(data['Unnamed: 4'])):
    money = data['Unnamed: 4'][index]
    if type(money)==str and len(money) >1 and money[0] == '￥':
        data['Unnamed: 4'][index] = money[1:]

for index in range(len(data['Unnamed: 5'])):
    money = data['Unnamed: 5'][index]
    if type(money)==str and len(money) >1 and money[0] == '￥':
        data['Unnamed: 5'][index] = money[1:]

for index in range(len(data['Unnamed: 6'])):
    money = data['Unnamed: 6'][index]
    if type(money)==str and len(money) >1 and money[0] == '￥':
        data['Unnamed: 6'][index] = money[1:]

data.to_excel("result.xls",index=False,sheet_name="Sheet0")