This is my second day of the August Challenge

preface

Today I’m going to automate the generation of my favorite bean calendar with a Python script.

Results show

The development tools

Python version: 3.9.4

Related modules:

Openpyxl module

The calendar module

And some modules that come with Python

Knowledge supplement

An Excel spreadsheet document is called a workbook. A workbook is saved in a file with the.xlsx extension.

Note OpenPyXL only supports the. XLSX format. Therefore, for Excel2003 “. XLS “format is not supported.

Each workbook can contain multiple tables, also known as worksheets. This information is generated in a workbook, 12 worksheets.

The Calendar library mainly generates calendar information.

The source code

import calendar
Set the start date code of the week to Sunday
calendar.setfirstweekday(firstweekday=6)
Return to the 2019 calendar
print(calendar.calendar(2019, w=2, l=1, c=6))
Copy the code

The output is as follows

Returns information for each column in January

Return information for each column of the month
print(calendar.monthcalendar(2019.1))
# Output results
[[0.0.1.2.3.4.5], [6.7.8.9.10.11.12], [13.14.15.16.17.18.19], [20.21.22.23.24.25.26], [27.28.29.30.31.0.0]]
Copy the code

Compare this to the information in January, and you’ll notice that there’s an extra zero.

Everything else is exactly the same, so we’re done traversing the list information.

Implement the Love bean calendar

from openpyxl.styles import Alignment, PatternFill, Font
from openpyxl.utils import get_column_letter
from openpyxl.drawing.image import Image
import openpyxl
import calendar

Set the first day to Sunday
calendar.setfirstweekday(firstweekday=6)
# Create a job 䈬
wb = openpyxl.Workbook()

Traversal 12 months
for i in range(1.13) :# Add worksheet
    sheet = wb.create_sheet(index=0, title=str(i) + 'month')
    # Get date and time
    for j in range(len(calendar.monthcalendar(2019, i))):
        for k in range(len(calendar.monthcalendar(2019, i)[j])):
            value = calendar.monthcalendar(2019, i)[j][k]
            # Make the value 0 null
            if value == 0:
                value = ' '
                sheet.cell(row=j + 9, column=k + 1).value = value
            else:
                sheet.cell(row=j + 9, column=k + 1).value = value
                # Set font
                sheet.cell(row=j + 9, column=k + 1).font = Font(U 'Microsoft Yahei', size=11)
    Cell text Settings, right aligned, vertical center
    align = Alignment(horizontal='right', vertical='center')
    # Set the cell fill color property
    fill = PatternFill("solid", fgColor="B9EBF7")
    # Fill the cells with color
    for k1 in range(1.100) :for k2 in range(1.100):
            sheet.cell(row=k1, column=k2).fill = fill
    # Add day of the week line
    days = ['Sunday'.'Monday'.'Tuesday'.'Wednesday'.'Thursday'.'Friday'.'Saturday']
    num = 0
    for k3 in range(1.8):
        sheet.cell(row=8, column=k3).value = days[num]
        sheet.cell(row=8, column=k3).alignment = align
        sheet.cell(row=8, column=k3).font = Font(U 'Microsoft Yahei', size=11)
        Set the column width to 12
        c_char = get_column_letter(k3)
        sheet.column_dimensions[get_column_letter(k3)].width = 12
        num += 1
    Set row height to 30
    for k4 in range(8.14):
        sheet.row_dimensions[k4].height = 30
    # Merge cells
    sheet.merge_cells('I1:P20')
    # Add image
    img = Image('huge_2.jpg')
    sheet.add_image(img, 'I1')

    # Add year and month
    sheet.cell(row=3, column=1).value = '2019'
    sheet.cell(row=4, column=1).value = str(i) + 'month'
    Set the year and month text properties
    sheet.cell(row=3, column=1).font = Font(U 'Microsoft Yahei', size=16, bold=True, color='FF7887')
    sheet.cell(row=4, column=1).font = Font(U 'Microsoft Yahei', size=16, bold=True, color='FF7887')
    sheet.cell(row=3, column=1).alignment = align
    sheet.cell(row=4, column=1).alignment = align
# Save the document
wb.save('Love bean calendar. XLSX')
Copy the code

That’s the end of this article. Thanks for watching. The next article in this series of Useful Scripts in Python will share how to create real-time screenshot recognition OCR in Python

To thank the readers, I’d like to share some of my recent programming gems to give back to each and every one of you.

Dry goods are mainly:

① More than 2000 Python ebooks (both mainstream and classic)

Python Standard Library (Chinese version)

(3) project source code (forty or fifty interesting and classic practice projects and source code)

④Python basic introduction, crawler, Web development, big data analysis videos (suitable for small white learning)

⑤Python Learning Roadmap

⑥ Two days of Python crawler boot camp live permissions

All done ~ see profile for complete source code.

Review past

Python10 line script to create real-time screenshot recognition OCR

Python script to play with emoji

Python script to achieve quick cartoon character avatar

Python scripts for weather query applications

Python implementation automatically monitors Github projects and opens web pages

Python implements automatic file categorization

The Python implementation helps you choose the double ball number

Python to achieve daily change “Bing picture” to “desktop wallpaper”

Python to achieve batch watermark

Python implementation to decipher the ZIP package

Python to achieve batch download Baidu pictures