问题描述
类似下面的excel表格,第一列为关键字,第二列为关键字出现的次数。统计每个关键字的总数目。
譬如ab 是12+1, cd是13+2, ef是0。
XlsxWriter的安装
pip install XlsxWriter之后,python仍然可能提示找不到XlsxWriter。这个时候可以尝试下面的方法安装:
$ git clone https://github.com/jmcnamara/XlsxWriter.git
$ cd XlsxWriter
$ python setup.py install
Dict的操作
首次往Dict里面某个关键字写入数据的时候,要用setdefault。后面更新内容就可以summaryDict[v.Value][0]了。
源代码
import win32com.client
import json
import xlsxwriter
app = win32com.client.Dispatch("Excel.Application")
s = app.ActiveWorkbook.Sheets(1)
def saveToTxte(dict):
with open("file.txt", "w") as file:
file.write(json.dumps(dict))
#https://stackoverflow.com/questions/23113231/write-dictionary-values-in-an-excel-file
def saveToExcel(dict):
workbook = xlsxwriter.Workbook("data.xlsx")
worksheet = workbook.add_worksheet()
row = 0
col = 0
for key in dict.keys():
row += 1
col = 0
worksheet.write(row, col, key)
for item in dict[key]:
worksheet.write(row, col + 1, item)
col += 1
workbook.close()
def readExcel():
i = 0
summaryDict = {}
for v in s.Range("B1:B20"):
i = i+1
if v.Value in summaryDict:
summaryDict[v.Value][0] = str(int(summaryDict[v.Value][0])+1)
summaryDict[v.Value][1] = str(int(summaryDict[v.Value][1])+int(s.Range("C"+str(i)).Value))
else:
print v.Value
summaryDict.setdefault(v.Value,[]).append(str(1))
summaryDict.setdefault(v.Value,[]).append(str(int(s.Range("C"+str(i)).Value)))
print(summaryDict)
saveToExcel(summaryDict)
def main():
readExcel()
main()