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

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

Next i

End Sub