Excel table creation with Python [Progress management table]
Introduction
I suddenly thought that I had created a simple progress management table for myself. ... Isn't it annoying to make this every time? ??
That's why I wrote the code that creates a table when I start it after studying python.
↓ The finished product looks like this
Let's explain each part.
File creation
import openpyxl.utils
import os
from openpyxl.styles import PatternFill
from openpyxl.styles.borders import Border, Side
from openpyxl.formatting.rule import FormulaRule
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = 'Progress management table'
First, create an Excel file. Create a new file with openpyxl.Workbook (). active targets the currently selected sheet. Since the file is launched, sheet1 is selected. You can change the name of the sheet selected by title.
Cell sizing
def cellwidth(start, end, wide):
for length in range(start, end):
alpha = sheet.cell(row=1, column=length).coordinate[:-1]
sheet.column_dimensions['{}'.format(alpha)].width = wide
cellwidth(1, 7, 8.38)
cellwidth(2, 3, 17.38)
cellwidth(8, 9, 0.38)
cellwidth(9, 335, 4.88)
for rows in range(1, 100):
sheet.row_dimensions[rows].height = 19
You can set the column width with colomn_dimensions ['specified column'] .width and the row height with row_dimensions ['specified row'] .height. I wanted to specify each, so I made it a function and used the for statement. It is difficult to understand the column, but it is converted to A = 1, B = 2, C = 3 ... (Example: B1 if cell (row = 1, column = 2)) Since I wanted to put an alphabet in the specified column, I used cell (row =, column =). Coordinate to get the cell position, and [: -1] to get the last -1 character from the beginning. (Example: If it is AAA1 in the cell, get AAA) Since the line width is uniform this time, it is not a function.
Frame border
side1 = Side(style='thin', color='000000')
side2 = Side(style='double', color='000000')
side3 = Side(style='hair', color='000000')
for rows in sheet.iter_rows(min_row=1, min_col=1, max_row=100, max_col=9):
for cell in rows:
if cell.row == 5:
cell.border = Border(right=side1, bottom=side2)
else:
cell.border = Border(top=None, right=side1, bottom=None, left=None)
for rows in sheet.iter_rows(min_row=1, min_col=9, max_row=100, max_col=335):
for cell in rows:
if cell.row == 5:
cell.border = Border(right=side3, bottom=side2)
else:
cell.border = Border(top=side3, right=side3, bottom=side3, left=side3)
Define the border with Side (style ='Border style', color ='Color'). Border (top =, right =, bottom =, left =) allows you to set the top, bottom, left, and right borders of a cell. Here, a for statement is used to set a border for each cell in the range specified by min to max. I wanted to put a line under the frame of only the 5th line, so I branched it with an if statement. Note that if you set top and bottom and right and left to different types of lines, the adjacent parts will be overwritten with the borders that were changed later.
Insert numbers and expressions
startYear = 2020
startMonth = 3
startDay = 1
sheet["B5"].value = "task"
sheet["C4"].value = "Start date"
sheet["C5"].value = "plans"
sheet["D5"].value = "Performance"
sheet["E4"].value = "Completion date"
sheet["E5"].value = "plans"
sheet["F5"].value = "Performance"
sheet["G5"].value = "Remarks"
sheet["I2"].value = startYear
sheet["J2"].value = "Year"
sheet["I3"].value = startMonth
sheet["J3"].value = "Month"
sheet["I4"].number_format = "d"
sheet["I4"].value = '=DATE(I2, I3, {})'.format(startDay)
sheet["I5"].number_format = "aaa"
sheet["I5"].value = '=I4'
for rows in sheet.iter_rows(min_row=4, min_col=10, max_row=5, max_col=334):
for cell in rows:
if cell.row == 4:
cell.number_format = 'd'
cell.value = '={}+1'.format(sheet.cell(row=cell.row, column=cell.column-1).coordinate)
else:
cell.number_format = 'aaa'
cell.value = '={}'.format(sheet.cell(row=cell.row-1, column=cell.column).coordinate)
for rows in sheet.iter_rows(min_row=3, min_col=11, max_row=3, max_col=334):
for cell in rows:
cell.number_format = "m"
cell.value = '=IF(DAY({0})=1, {1},"")'.format(sheet.cell(column=cell.column, row=cell.row + 1).coordinate,
sheet.cell(column=cell.column, row=cell.row + 1).coordinate)
The value is entered in the specified cell in cell .value. If you want to enter an expression, use'= expression'. If you enter a simple calculation formula, the calculation result will only be entered, so you need to be careful. You can also change the display format with number_format. You can see the display format in Excel or check it in print (cell.number_format). By the way, d is the date and aaa is the day of the week. I decide the starting month and add the date from there, and if it is one day, I also display the month. With this, I have roughly entered the months, days, and days of the week for 11 months.
Conditional formatting
def colorMake(types, start, end):
return PatternFill(fill_type=types, start_color=start, end_color=end)
grayfill = colorMake('solid', 'd3d3d3', 'd3d3d3')
goldfill = colorMake('solid', 'ffd700', 'ffd700')
bluefill = colorMake('solid', '1e90ff', '1e90ff')
for rows in sheet.iter_rows(min_row=6, min_col=2, max_row=100, max_col=7):
for cell in rows:
cell.number_format = "m/d"
sheet.conditional_formatting.add('C6:G100', FormulaRule(formula=['NOT($F6="")'], stopIfTrue=True, fill=grayfill))
sheet.conditional_formatting.add('I4:ME100', FormulaRule(formula=['OR(WEEKDAY(I$5)=1, WEEKDAY(I$5)=7)'], stopIfTrue=True, fill=grayfill))
sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($D6<=I$4, $F6>=I$4)'], stopIfTrue=True, fill=goldfill))
sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($C6<=I$4, $E6>=I$4)'], stopIfTrue=True, fill=bluefill))
You can set the conditions in the order of description in the selection sheet .conditional_formatting.add ('Applicable cell range', FormulaRule (formula = [Applicable condition], stopIfTrue = True or False, fill = color)). I stumbled upon this, but formula = [applicable conditions] and the content of the conditional expression should not start with =. I don't get an error, but why is there no conditional formatting in Excel? ?? ?? It will be. stopIfTrue is a conditional format "stop if met". What is it if you haven't used Excel in the first place? It feels like ... I also set the cell color when the condition is met with fill. PatternFill (filltype = fill pattern, start_color =, end_color =) If you don't use gradients, start_color and end_color should be the same.
Save
desktop_path = os.getenv("HOMEDRIVE") + os.getenv("HOMEPATH") + "\\Desktop"
wb.save(desktop_path + '\\Progress management table.xlsx')
It is the end. You can save it as workbook .save (filename). Please note that without this, no matter how much you make it, it will not be saved. This time, the absolute path to the desktop is specified and saved. If it is just the file name, it will be saved in the current directory.
At the end
This time, the range of cells is too fixed, so if you create a property file for variables and customize it as you like by adjusting the numerical values there, the degree of freedom will increase. It was a lot of study because it was packed with various elements. (Small feeling)
Whole code
import openpyxl.utils
import os
from openpyxl.styles import PatternFill
from openpyxl.styles.borders import Border, Side
from openpyxl.formatting.rule import FormulaRule
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = 'Progress management table'
def cellwidth(start, end, wide):
for length in range(start, end):
alpha = sheet.cell(row=1, column=length).coordinate[:-1]
sheet.column_dimensions['{}'.format(alpha)].width = wide
cellwidth(1, 7, 8.38)
cellwidth(2, 3, 17.38)
cellwidth(8, 9, 0.38)
cellwidth(9, 335, 4.88)
for rows in range(1, 100):
sheet.row_dimensions[rows].height = 19
side1 = Side(style='thin', color='000000')
side2 = Side(style='double', color='000000')
side3 = Side(style='hair', color='000000')
for rows in sheet.iter_rows(min_row=1, min_col=1, max_row=100, max_col=9):
for cell in rows:
if cell.row == 5:
cell.border = Border(right=side1, bottom=side2)
else:
cell.border = Border(top=None, right=side1, bottom=None, left=None)
for rows in sheet.iter_rows(min_row=1, min_col=9, max_row=100, max_col=335):
for cell in rows:
if cell.row == 5:
cell.border = Border(right=side3, bottom=side2)
else:
cell.border = Border(top=side3, right=side3, bottom=side3, left=side3)
startYear = 2020
startMonth = 2
startDay = 1
sheet["B5"].value = "task"
sheet["C4"].value = "Start date"
sheet["C5"].value = "plans"
sheet["D5"].value = "Performance"
sheet["E4"].value = "Completion date"
sheet["E5"].value = "plans"
sheet["F5"].value = "Performance"
sheet["G5"].value = "Remarks"
sheet["I2"].value = startYear
sheet["J2"].value = "Year"
sheet["I3"].value = startMonth
sheet["J3"].value = "Month"
sheet["I4"].number_format = "d"
sheet["I4"].value = '=DATE(I2, I3, {})'.format(startDay)
sheet["I5"].number_format = "aaa"
sheet["I5"].value = '=I4'
for rows in sheet.iter_rows(min_row=4, min_col=10, max_row=5, max_col=334):
for cell in rows:
if cell.row == 4:
cell.number_format = 'd'
cell.value = '={}+1'.format(sheet.cell(row=cell.row, column=cell.column-1).coordinate)
else:
cell.number_format = 'aaa'
cell.value = '={}'.format(sheet.cell(row=cell.row-1, column=cell.column).coordinate)
for rows in sheet.iter_rows(min_row=3, min_col=11, max_row=3, max_col=334):
for cell in rows:
cell.number_format = "m"
cell.value = '=IF(DAY({0})=1, {1},"")'.format(sheet.cell(column=cell.column, row=cell.row + 1).coordinate,
sheet.cell(column=cell.column, row=cell.row + 1).coordinate)
def colorMake(types, start, end):
return PatternFill(fill_type=types, start_color=start, end_color=end)
grayfill = colorMake('solid', 'd3d3d3', 'd3d3d3')
goldfill = colorMake('solid', 'ffd700', 'ffd700')
bluefill = colorMake('solid', '1e90ff', '1e90ff')
for rows in sheet.iter_rows(min_row=6, min_col=2, max_row=100, max_col=7):
for cell in rows:
cell.number_format = "m/d"
sheet.conditional_formatting.add('C6:G100', FormulaRule(formula=['NOT($F6="")'], stopIfTrue=True, fill=grayfill))
sheet.conditional_formatting.add('I4:ME100', FormulaRule(formula=['OR(WEEKDAY(I$5)=1, WEEKDAY(I$5)=7)'], stopIfTrue=True, fill=grayfill))
sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($D6<=I$4, $F6>=I$4)'], stopIfTrue=True, fill=goldfill))
sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($C6<=I$4, $E6>=I$4)'], stopIfTrue=True, fill=bluefill))
desktop_path = os.getenv("HOMEDRIVE") + os.getenv("HOMEPATH") + "\\Desktop"
wb.save(desktop_path + '\\Progress management table.xlsx')
Recommended Posts