Excel X Python The fastest way to work

The era of VBA seems to be over

Recently, a book called "Excel X Python Fastest Work Technique" was sold as of the end of December 2019, and has won the first place in Amazon's spreadsheet book ranking. It's amazing because it sells better than that Excel's strongest textbook! (The spreadsheet category is a fairly popular category) Amazon Link

The core of this book is that ** doing something like VBA from Python to Excel ** is what the fastest work technique is. This time, for my own learning, I will introduce how to operate Excel workbooks from Python learned from this book and do VBA-like things.

This book consistently uses a library called ** openpyxl ** to operate Excel.

Load the workbook. (New workbook)

import openpyxl
lwb = openpyxl.Workbook()
lsh = lwb.active #Load sheet for writing to cell

Load Excel that has already been created

import openpyxl
wb = openpyxl.load_workbook('file name.xlsx')

Save the document

import openpyxl
wb = openpyxl.load_workbook('file name.xlsx')
wb.save(filename = 'sample_book.xlsx')

Write to cell

import openpyxl
lwb = openpyxl.Workbook()
lsh = lwb.active #Load sheet for writing to cell
lsh.cell(Number of lines,Number of columns).value = "hogehoge"

Use charts (line charts)

I will put the code of the citation source Source link

from datetime import date

from openpyxl import Workbook
from openpyxl.chart import (
    LineChart,
    Reference,
)
from openpyxl.chart.axis import DateAxis

wb = Workbook()
ws = wb.active

rows = [
    ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
    [date(2015,9, 1), 40, 30, 25],
    [date(2015,9, 2), 40, 25, 30],
    [date(2015,9, 3), 50, 30, 45],
    [date(2015,9, 4), 30, 25, 40],
    [date(2015,9, 5), 25, 35, 30],
    [date(2015,9, 6), 20, 40, 35],
]

for row in rows:
    ws.append(row)

c1 = LineChart()
c1.title = "Line Chart"
c1.style = 13
c1.y_axis.title = 'Size'
c1.x_axis.title = 'Test Number'

data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
c1.add_data(data, titles_from_data=True)

# Style the lines
s1 = c1.series[0]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline

s1.graphicalProperties.line.noFill = True

s2 = c1.series[1]
s2.graphicalProperties.line.solidFill = "00AAAA"
s2.graphicalProperties.line.dashStyle = "sysDot"
s2.graphicalProperties.line.width = 100050 # width in EMUs

s2 = c1.series[2]
s2.smooth = True # Make the line smooth

ws.add_chart(c1, "A10")

from copy import deepcopy
stacked = deepcopy(c1)
stacked.grouping = "stacked"
stacked.title = "Stacked Line Chart"
ws.add_chart(stacked, "A27")

percent_stacked = deepcopy(c1)
percent_stacked.grouping = "percentStacked"
percent_stacked.title = "Percent Stacked Line Chart"
ws.add_chart(percent_stacked, "A44")

# Chart with date axis
c2 = LineChart()
c2.title = "Date Axis"
c2.style = 12
c2.y_axis.title = "Size"
c2.y_axis.crossAx = 500
c2.x_axis = DateAxis(crossAx=100)
c2.x_axis.number_format = 'd-mmm'
c2.x_axis.majorTimeUnit = "days"
c2.x_axis.title = "Date"

c2.add_data(data, titles_from_data=True)
dates = Reference(ws, min_col=1, min_row=2, max_row=7)
c2.set_categories(dates)

ws.add_chart(c2, "A61")

wb.save("line.xlsx")

Load multiple Excel

import openpyxl
import pathlib
path = pathlib.Path('Directory name/')
for path_obj in path.iterdir():
    wb = openpyxl.load_workbook('file name.xlsx')
    for sh in wb:
        #Operation for each sheet

Change log

--2019/12/30 Newly created