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.

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

Excel to import (sample.xlsx)

キャプチャ.JPG

Output file (sample2.xlsx)

キャプチャ.JPG

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 (*'▽')

Recommended Posts

I tried to find out how to streamline the work flow with Excel x Python ②
I tried to find out how to streamline the work flow with Excel x Python ④
I tried to find out how to streamline the work flow with Excel x Python ⑤
I tried to find out how to streamline the work flow with Excel x Python ①
I tried to find out how to streamline the work flow with Excel x Python ③
I tried to find the entropy of the image with python
I tried to simulate how the infection spreads with Python
I tried to find out if ReDoS is possible with Python
I tried to improve the efficiency of daily work with Python
Excel X Python The fastest way to work
I tried to streamline the standard role of new employees with Python
I tried to touch the CSV file with Python
I tried to solve the problem with Python Vol.1
I tried to find out the outline about Big Gorilla
I tried to find the average of the sequence with TensorFlow
I tried to divide the file into folders with Python
python beginners tried to find out
The 15th offline real-time I tried to solve the problem of how to write with python
How to write offline real time I tried to solve the problem of F02 with Python
I tried to solve the ant book beginner's edition with python
A story that didn't work when I tried to log in with the Python requests module
[Python] I tried to visualize the night on the Galactic Railroad with WordCloud!
I tried to refer to the fun rock-paper-scissors poi for beginners with Python
How to write offline real time I tried to solve E11 with python
Mayungo's Python Learning Episode 2: I tried to put out characters with variables
I tried to get the authentication code of Qiita API with Python.
I tried with the top 100 PyPI packages> I tried to graph the packages installed on Python
I tried to get the movie information of TMDb API with Python
How to write offline real time I tried to solve E12 with python
I tried "smoothing" the image with Python + OpenCV
I tried "differentiating" the image with Python + OpenCV
I tried to save the data with discord
[Python] How to read excel file with pandas
I tried to get CloudWatch data with Python
I tried to output LLVM IR with Python
I tried "binarizing" the image with Python + OpenCV
[Algorithm x Python] How to use the list
I tried to automate sushi making with python
I tried to open the latest data of the Excel file managed by date in the folder with Python
I tried to output the rpm list of SSH login destination to an Excel sheet with Python + openpyxl.
[Circuit x Python] How to find the transfer function of a circuit using Lcapy
I tried to find out as much as possible about the GIL that you should know if you are doing parallel processing with Python
When I tried to create a virtual environment with Python, it didn't work
I tried to easily visualize the tweets of JAWS DAYS 2017 with Python + ELK
I tried to automatically send the literature of the new coronavirus to LINE with Python
[Python & SQLite] I tried to analyze the expected value of a race with horses in the 1x win range ①
I tried to explain how to get the article content with MediaWiki API in an easy-to-understand manner with examples (Python 3)
I tried to learn the sin function with chainer
I tried to graph the packages installed in Python
I tried to summarize how to use matplotlib of python
[Introduction to Python] How to iterate with the range function?
I tried to get started with blender python script_Part 01
I tried to draw a route map with Python
[Python] How to specify the download location with youtube-dl
I tried to get started with blender python script_Part 02
I tried to implement an artificial perceptron with python
I want to inherit to the back with python dataclass
I want to work with a robot in python.
[Python] I tried to graph the top 10 eyeshadow rankings
I tried to automatically generate a password with Python3
I tried to summarize how to use pandas in python