python替代excel_使用Python查找并替换Excel（.xlsx）中的字符串

最新推荐文章于 2023-02-08 17:08:35 发布

weixin_39830387

最新推荐文章于 2023-02-08 17:08:35 发布

阅读量397

点赞数

文章标签： python替代excel

这篇博客探讨了如何高效地在大型Excel文件中批量替换特定字符串。作者尝试使用Python和VBA两种方法，最终选择了将数据转换为CSV格式以简化操作。提供的Python代码示例读取CSV文件，使用字典进行查找和替换，然后将结果写回新的CSV文件。此外，还提供了一个VBA宏作为替代方案，该宏直接在Excel工作簿中进行替换操作。

摘要由CSDN通过智能技术生成

I am trying to replace a bunch of strings in an .xlsx sheet (~70k rows, 38 columns). I have a list of the strings to be searched and replaced in a file, formatted as below:-

bird produk - bird product

pig - pork

ayam - chicken

...

kuda - horse

The word to be searched is on the left, and the replacement is on the right (find 'bird produk', replace with 'bird product'. My .xlsx sheet looks something like this:-

name type of animal ID

ali pig 3483

abu kuda 3940

ahmad bird produk 0399

...

ahchong pig 2311

I am looking for the fastest solution for this, since I have around 200 words in the list to be searched, and the .xlsx file is quite large. I need to use Python for this, but I am open to any other faster solutions.

Edit:- added sheet example

Edit2:- tried some python codes to read the cells, took quite a long time to read. Any pointers?

from xlrd import open_workbook

wb = open_workbook('test.xlsx')

for s in wb.sheets():

print ('Sheet:',s.name)

for row in range(s.nrows):

values = []

for col in range(s.ncols):

print(s.cell(row,col).value)

Thank you!

Edit3:- I finally figured it out. Both VBA module and Python codes work. I resorted to .csv instead to make things easier. Thank you! Here is my version of the Python code:-

import csv

###### our dictionary with our key:values. ######

reps = {

'JUALAN (PRODUK SHJ)' : 'SALE( PRODUCT)',

'PAMERAN' : 'EXHIBITION',

'PEMBIAKAN' : 'BREEDING',

'UNGGAS' : 'POULTRY'}

def replace_all(text, dic):

for i, j in reps.items():

text = text.replace(i, j)

return text

with open('test.csv','r') as f:

text=f.read()

text=replace_all(text,reps)

with open('file2.csv','w') as w:

w.write(text)

解决方案

I would copy the contents of your text file into a new worksheet in the excel file and name that sheet "Lookup." Then use text to columns to get the data in the first two columns of this new sheet starting in the first row.

Paste the following code into a module in Excel and run it:

Sub Replacer()

Dim w1 As Worksheet

Dim w2 As Worksheet

'The sheet with the words from the text file:

Set w1 = ThisWorkbook.Sheets("Lookup")

'The sheet with all of the data:

Set w2 = ThisWorkbook.Sheets("Data")

For i = 1 To w1.Range("A1").CurrentRegion.Rows.Count

w2.Cells.Replace What:=w1.Cells(i, 1), Replacement:=w1.Cells(i, 2), LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False