Let's play with Excel with Python [Beginner]

Introduction

"Try to count the number of customers in the city, ward, and county from the address list in Python" https://qiita.com/kkhouse/items/d8ff2c67cef686a508f8

It is a continuation of.

Last time, I wrote a code that calculates the number of customers in any district using the member's address list and makes it a list.

Since I learned a lot, I will post the code explanation of OpenPyXl used.

Commentary

Completion code

As I mentioned in the previous article, here is the completed code. The conditional branch is long ~

main.py

import csv
import sys
import os
import openpyxl as px

#csv → xlsx conversion
wb = openpyxl.Workbook()
ws = wb.active
query_word = sys.argv[1] 

f = open("./data_csv/{}.csv".format(query_word))
reader = csv.reader(f, delimiter=":")
for row in reader:
  ws.append(row)
f.close()

wb.save("./data_xlsx/{0}{1}.xlsx".format(query_word,"_Aggregate"))


#Write to xlsx in Python



folder = "data_xlsx"

filePath = []
for root,dirs,files in os.walk(folder):
  for fname in files:
    fnamePath = os.path.join(root,fname)
    if fnamePath.find(".xlsx")!= -1:
      filePath.append(fnamePath)
    if filePath ==[]:
      print("there is no data!")
xlsx_files_list = [s for s in filePath if query_word in s]



#Get the created xlsx
wb = px.load_workbook(xlsx_files_list[0])
ws = wb[wb.sheetnames[0]]

#List all addresses in xlsx


Miyagi_col =["Ishinomaki City","Miyagino Ward","Wakabayashi Ward","Aoba Ward","Izumi Ward","Taichi Ward","Tagajo","Other"]


adress_list = []

#Empty list of each city
isinomaki =[]
miyagino =[]
wakabayasi =[]
aoba =[]
izumi =[]
taihaku =[]
tagazyo =[]
hoka =[]

#List of each municipality
for i in range(ws.max_row):
  adress_list.append(ws.cell(row=i+1, column=1).value)

  if "Ishinomaki City" in ws.cell(row=i+1,column=1).value:
    isinomaki.append(ws.cell(row=i+1,column=1).value)

  elif "Miyagino Ward" in ws.cell(row=i+1,column=1).value:
    miyagino.append(ws.cell(row=i+1,column=1).value)

  elif "Wakabayashi Ward" in ws.cell(row=i+1,column=1).value:
    wakabayasi.append(ws.cell(row=i+1,column=1).value)

  elif "Aoba Ward" in ws.cell(row=i+1,column=1).value:
    aoba.append(ws.cell(row=i+1,column=1).value)

  elif "Izumi Ward" in ws.cell(row=i+1,column=1).value:
    izumi.append(ws.cell(row=i+1,column=1).value)

  elif "Taichi Ward" in ws.cell(row=i+1,column=1).value:
    taihaku.append(ws.cell(row=i+1,column=1).value)

  elif "Tagajo" in ws.cell(row=i+1,column=1).value:
    tagazyo.append(ws.cell(row=i+1,column=1).value)

  else:
    hoka.append(ws.cell(row=i+1,column=1).value)

pre_list = [isinomaki,miyagino,wakabayasi,aoba,izumi,taihaku,tagazyo,hoka]

#Column of list to create
for i,j in zip(list(range(1,9)),Miyagi_col):
  ws.cell(row=i, column=7).value = j
#List data to create
for i,g in zip(list(range(1,9)),pre_list):
  ws.cell(row=i, column=8).value = len(g)

#Data storage
wb.save("./data_xlsx/{0}{1}.xlsx".format(query_word,"_Aggregate"))

Convert csv file to xlsx

#csv → xlsx conversion
wb = openpyxl.Workbook(
ws = wb.active
query_word = sys.argv[1] 

f = open("./data_csv/{}.csv".format(query_word))
reader = csv.reader(f, delimiter=":")
for row in reader:
  ws.append(row)
f.close()

wb.save("./data_xlsx/{0}{1}.xlsx".format(query_word,"_Aggregate"))

Convert CSV format to XLSX

wb = openpyxl.Workbook () #Create new ws = wb.active #active sheet

query_word = sys.argv[1] f = open("./data_csv/{}.csv".format(query_word)) Get the filename entered in the terminal with sys.argv and the format method (Python main.py ○○ (file name))

reader = csv.reader(f, delimiter=":") for row in reader: ws.append(row) f.close() load csv

wb.save ("./data_xlsx/{0}{1}.xlsx".format (query_word, "_aggregate")) Convert to xlsx file and save.

At the end

It's time so far! Next time from the code that looks for XLSX files in the directory