Let's run Excel with Python

Introduction

SLP Advent Calendar December 17, 2020 article You can see other people's articles here https://adventar.org/calendars/5402 When I was looking for what to make, I decided to find an article about automating Excel with python.

Preparation (installation)

First download pyrhon There is a site or youtube video that is better explained than I explain so please refer to that (This time, the editor uses VS Code (Visual studio code)) ・ [Python Super Introductory Course] 03. Environment Construction for Windows  https://youtu.be/Cs7friXdqdM · Set up a development environment for Python beginners using Visual Studio Code  <https:https://docs.microsoft.com/ja-jp/learn/modules/python-install-vscode/

After setting the environment of python, next install the additional library to be used this time The additional libraries to be installed this time are as follows ・ Pandas ・ Openpyxl At the command prompt, type:

pip install pandas

pip install openpyxl

Now you are ready

Write in an excel file using pyrhon

Create an Excel file using python and write it in a cell to make a sample

excel sample.py

import openpyxl as px
import random

#A1 in excel sheet,B1,C1,Day to D1,Product,Place,Write as Fee
wb = px.Workbook()
ws = wb.active
sheet = wb.worksheets[0]
ws.title = 'sheet_1'
ws['A1'] = 'time'
ws['B1'] = 'Product'
ws['C1'] = 'Branch'
ws['D1'] = 'Profit'

#Randomly generate either product 1 or product 2 in column B
for k in range(2, 501):
    a = ('Product 1', 'Product 2')
    ws['B'+ str(k)] = random.choice(a)

#Randomly generate either company 1 or company 2 in column C
for m in range(2, 501):
    b = ('Branch 1', 'Branch 2')
    ws['C'+ str(m)] = random.choice(b)

#Randomly generate numbers in column D from values ​​in the range 80-155
for n in range(2, 501):
    ws['D'+ str(n)] = random.randint(10000, 100000)

#Randomly generated month and day range 2020-1-1～2020―12-Decide 17
from datetime import timedelta 
from datetime import date
from random import randrange
import random
start_date = date(2020, 1, 1)
end_date = date(2020, 12, 17)

time_between_dates = end_date - start_date
days_between_dates = time_between_dates.days
random_number_of_days = random.randrange(days_between_dates)
random_date = start_date + timedelta(days=random_number_of_days)

 #2020-1 in row A-Randomly generated in the range of 1 to 2020-12-17
for p in range(2, 500):
    start_date = date(2020, 1, 1)
    end_date = date(2020, 12, 17)

    time_between_dates = end_date - start_date
    days_between_dates = time_between_dates.days
    random_number_of_days = random.randrange(days_between_dates)
    random_date = start_date + timedelta(days=random_number_of_days)

    ws['A'+ str(p)] = (random_date)
    #Cell size adjustment
    sheet.column_dimensions['A'].width = 13
    
#Save your changes
wb.save('sample1.xlsx')

Doing this will generate an excel file like this (Since the sample file created this time generates values ​​randomly, the values ​​will change.) Next, we will operate using this sample file

Divide Excel file by type, generate graph

Divide the sample file created in the previous item for each product and generate a line graph.

excel test.py

import pandas as pd
import openpyxl as px
from datetime import datetime
import os
 #Extract Excel file
Filepath = os.path.abspath('sample1.xlsx') 
 #Read an excel file with pandas
df = pd.read_excel(Filepath)  
 #Year-Moon-日からYear-Moonに表示変更
df['time'] = pd.to_datetime(df['time']).dt.strftime("%Y-%m")
#Create a duplicated list in each column
D = list(df['time'].unique()) 
P = list(df['Product'].unique())
F = list(df['Profit'].unique())
Pl = list(df['Branch'].unique())
 #Get the current date
now = datetime.now()
hiduke = now.strftime('%Y-%m-%d')
 #Generate a data sheet for each product
for products in P: 
    filtered = df[df['Product'] == f'{products}']
    sales = pd.pivot_table(df, index=filtered['time'], columns='Branch', values='Profit', aggfunc='sum', fill_value=0)
    #file name({hiduke}To the current date{products}Product name)
    filepath = f'{hiduke}_{products}.xlsx' 
    sales.to_excel(filepath, sheet_name='Monthly total profit', startrow=3)
    wb = px.load_workbook(filepath)
    #Get sheet name
    ws = wb['Monthly total profit'] 
    
    #In cell A1, "{products}_Enter "Sales"
    ws.cell(row=1, column=1).value = f'{products}_Earnings' 
    #Font settings
    ws.cell(row=1, column=1).font = px.styles.Font(size=12, bold=True) 
    #Enter "Monthly sales" in cell A2
    ws.cell(row=2, column=1).value = 'Monthly sales' 
    #Font settings
    ws.cell(row=2, column=1).font = px.styles.Font(size=12, bold=True)

    #Line graph generation
    chart = px.chart.LineChart() 
    data = px.chart.Reference(ws, min_col=2, max_col=len(Pl)+1, min_row=4, max_row=len(D)+4)
    categories = px.chart.Reference(ws, min_col=1, max_col=1, min_row=5, max_row=len(D)+4)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(categories)
     #Enter the size of the graph display, the title of the graph, and the unit of the graph.
    chart.style = 14 
    chart.title = 'Product sales'
    chart.y_axis.title = 'Circle'
    chart.x_axis.title = 'Year month'
    chart.height = 9
    chart.width = 16
    #Graph generation
    ws.add_chart(chart, "G2")
     #Save each file
    wb.save(filepath)

When executed, an Excel file named 2020-12-17_Product 1 and an Excel file named 2020-12-17_Product 2 will be generated. (The date of the file name is the date of generation, so it depends on the date of generation) The contents of the two files are as follows

Also

chart = px.chart.LineChart()

 Part of

#### **` chart = px.chart.BarChart()`**

If you change to, a bar graph will be generated.

chart = px.chart.PieChart()

 If you change it to, you can generate various graphs just by changing one line, as if a pie chart [^ *] is generated.
 [^ *]: In the case of a pie chart, there is no concept of x-axis or y-axis, so except for chart.y_axis.title which means y-axis unit display and chart.y_axis.title which means x-axis unit display. Must have
## Summary
 How was that?
 I think that using python allows you to operate faster and more accurately than actually operating in Excel, which is very convenient for people who use Excel on a daily basis.
### Finally
 I had a hard time using python for the first time (sweat)
 I think I spent most of my time especially on understanding the random generation of dates and the data acquisition part of graph generation.
 This time I could only divide the data and generate the graph, so I will try to make it more efficient.
#### References
 Creating an Excel graph using OpenPyXL with Python ~ How to make a graph ~
https://tonari-it.com/python-openpyxl-graph-2/
 Automate graphing by reading Excel with Python and analyzing with Pandas
https://fastclassinfo.com/entry/python_excel_charts/
 datetime --- Basic date and time types
https://docs.python.org/ja/3/library/datetime.html#aware-and-naive-objects
 Such