读写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")