I tried to find out how to streamline the work flow with Excel x Python ③

Purpose of this time

Format when outputting an Excel file in Python.

• As in the example, the explanation is given in the comments of the program.

Program flow

① Import the sample.xlsx file ② Set a lot of formats ③ Output to sample2.xlsx file

Last reference

I tried to find out how to streamline the work flow with Excel x Python ② https://qiita.com/t20190127/items/023e5dd0128252f46378

Program execution result confirmation

• The program is at the bottom

Excel to import (sample.xlsx)

Output file (sample2.xlsx)

program

file_edit.py

import pathlib
import openpyxl
from openpyxl.styles import Alignment, PatternFill, Font, Border, Side
import csv

#Get excel file
wb = openpyxl.load_workbook("./data/sample.xlsx")

#Get sheet "Name: Sheet1"
ws = wb["Sheet1"]

#Output data in cell A1 * Pattern 1
print(ws["A1"].value)  #Get by specifying cell A1

#Output data in cell A1 * Pattern 2
row1 = ws[1]           #Get the first line of sheet "Name: Sheet1"
print(row1[0].value)   #Get the 1st row and 1st column ⇒ As a result, cell A1

#Output all data
print("All data>")
for row in ws.rows:
	for cell in row:
	    print(cell.value)

#Rewrite data in cell A1
ws["A1"].value = "★ A1"

#Change font in cell A1 (Gothic, 12 size, thick, blue)
font_header = Font(name="Gothic", size=12,bold=True,color="0000FF")
ws["A1"].font = font_header

#Cell A1 fill (gray)
ws["A1"].fill = PatternFill(patternType="solid", fgColor="808080")

#Filling multiple cells A5 to D6 (green)
for rows in ws["A5":"D6"]:
    for cell in rows:
        cell.fill = PatternFill(patternType="solid", fgColor="99FFCC")

#Note
#Change the color depending on the condition (CellIsRule/ conditional_formatting.add）
#ColorScaleRule/ conditional_formatting.add）

#Value position in cell A1 (centered, bottom)
ws["A1"].alignment = Alignment(horizontal="center", vertical="bottom")

#Format A column (column width)
ws.column_dimensions["A"].width= 30

#Format cell B2 (3 digit comma separated)
ws["B2"].number_format = "#,##0"![capture.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/350277/64e2c4b3-8faa-fd1e-4dac-0054fffa42a7.jpeg)


#Set the frame of cell B2 (thin, black)
side = Side(style="thin", color="000000")
border = Border(left=side,right=side,top=side,bottom=side)
ws["B2"].border = border

#Cell A2,A3 cell merge
ws.merge_cells("A2:A3")

#Save the loaded Excel with a different name
#* In case of overwriting, specify the read file path
wb.save("./data/sample2.xlsx")

Summary

How about that. Compared to the last time, I feel that it has become a form that can be used at work. Also, I would like to try Graph / PDF next time.

That's it (*'▽')