openpyxl しましま模様にする方法【初心者のためのpython #6】

pythonでエクセル操作

2021.05.05 2020.07.09

この記事は約12分で読めます。

python でのセルの塗りつぶし方を応用して、

しましま模様にしてみましょう

大量データの場合に行の区別がつきやすくなり、視認性が向上します

しましま模様の表を作る
実際の現場で求められること
まとめ

しましま模様の表を作る

pythonでopenpyxlを使う事前準備

こちらの記事でわかりやすくまとめたので参考にしてほしい

セルの塗りつぶし方

下記記事にてシンプルにまとめているので、こちらを参考にしてほしい

記述方法

shimashima = 1, 0　により色を持ち替えることで実現可能だ。

サンプルコード

# -*- coding: utf-8 -*-
import openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.styles import borders
from openpyxl.styles.fills import PatternFill

wb = load_workbook("sample.xlsx")
ws = wb["Sheet1"]

shimashima = 0
for i in range(1,11):
        ws.cell(row=i, column=1).border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         right=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         left=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         bottom=borders.Side(style=borders.BORDER_HAIR, color='008E77'))
        ws.cell(row=i, column=2).border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         right=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         bottom=borders.Side(style=borders.BORDER_HAIR, color='008E77'))
        ws.cell(row=i, column=3).border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         right=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         bottom=borders.Side(style=borders.BORDER_HAIR, color='008E77'))
        ws.cell(row=i, column=4).border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         right=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         bottom=borders.Side(style=borders.BORDER_HAIR, color='008E77'))
        ws.cell(row=i, column=5).border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         right=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         bottom=borders.Side(style=borders.BORDER_HAIR, color='008E77'))
        ws.cell(row=i, column=6).border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         right=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         bottom=borders.Side(style=borders.BORDER_HAIR, color='008E77'))
        ws.cell(row=i, column=7).border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         right=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         bottom=borders.Side(style=borders.BORDER_HAIR, color='008E77'))
        ws.cell(row=i, column=8).border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         right=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         bottom=borders.Side(style=borders.BORDER_HAIR, color='008E77'))
        ws.cell(row=i, column=9).border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         right=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         bottom=borders.Side(style=borders.BORDER_HAIR, color='008E77'))
        ws.cell(row=i, column=10).border = borders.Border(top=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         right=borders.Side(style=borders.BORDER_HAIR, color='008E77'),
                                                         bottom=borders.Side(style=borders.BORDER_HAIR, color='008E77'))
        if (shimashima == 0):
            shimashima = 1
        else:
            ws.cell(row = i, column=1).fill = PatternFill(patternType='solid', start_color='f9f9f9', end_color='f9f9f9')
            ws.cell(row = i, column=2).fill = PatternFill(patternType='solid', start_color='f9f9f9', end_color='f9f9f9')
            ws.cell(row = i, column=3).fill = PatternFill(patternType='solid', start_color='f9f9f9', end_color='f9f9f9')
            ws.cell(row = i, column=4).fill = PatternFill(patternType='solid', start_color='f9f9f9', end_color='f9f9f9')
            ws.cell(row = i, column=5).fill = PatternFill(patternType='solid', start_color='f9f9f9', end_color='f9f9f9')
            ws.cell(row = i, column=6).fill = PatternFill(patternType='solid', start_color='f9f9f9', end_color='f9f9f9')
            ws.cell(row = i, column=7).fill = PatternFill(patternType='solid', start_color='f9f9f9', end_color='f9f9f9')
            ws.cell(row = i, column=8).fill = PatternFill(patternType='solid', start_color='f9f9f9', end_color='f9f9f9')
            ws.cell(row = i, column=9).fill = PatternFill(patternType='solid', start_color='f9f9f9', end_color='f9f9f9')
            ws.cell(row = i, column=10).fill = PatternFill(patternType='solid', start_color='f9f9f9', end_color='f9f9f9')
            shimashima = 0

wb.save('sample_rewirte.xlsx')

見やすくおしゃれな縞々が完成した