Automate Your Excel File Generation with Python

Life Much Easier with that!

Creating a template for your repetitive document could save you a lot of time. But, could you imagine when you have the template and data inside it can be fulfilled automatically?

Let’s automate excel file generation with Python!

For example in this article, I will create a sample grading system sheet that looks like this

Sample grading worksheet

Step 1 — Installing library that you need

I use xlsxwriter to help me through the process, don’t forget to import it to the Python code after installation complete

pip install xlsxwriter

Step 2 — Create a workbook

The workbook itself will contains one or more worksheets (the place that we will work on). We creating a file named grading.xlsx. This process will generate an empty workbook with a single worksheet

import xlsxwriter
workbook = xlsxwriter.Workbook('grading.xlsx')

Step 3 — Create a worksheet

We will create a worksheet to work on the template that we have above

worksheet = workbook.add_worksheet('Assignment 1')

Step 4 — Create the template

Defining row and col (zero-indexed)

row = 0
col = 0

That means that we have a pointer to cell A1 of the sheets, this will help us through the template-making process.

Column name in the template wider than other. We need to adjust it so the column gets bigger

worksheet.set_column('B:B', 15)

Before writing something in the sheet, I create some function to help me format the text that will be written

def default_format(workbook):
    default = workbook.add_format({
        'font_name':REGULAR_FONT,
        'font_size':REGULAR_SIZE,
        'valign':'top',
    })
    return default
def text_box_wrap_format(workbook):
    text_box_wrap = workbook.add_format({
        'font_name':REGULAR_FONT,
        'font_size':REGULAR_SIZE,
        'align':'justify',
        'valign':'vcenter',
        'border':True,
        'text_wrap':True
    })
    return text_box_wrap
def text_box_center_wrap_format(workbook):
    text_box_center_wrap = workbook.add_format({
        'font_name':REGULAR_FONT,
        'font_size':REGULAR_SIZE,
        'align':'center',
        'valign':'vcenter',
        'border':True,
        'text_wrap':True
    })
    return text_box_center_wrap

Creating the header of the template. For the first two columns, it’s easy. We need the merge_range function from xlsxwriter.worksheet.

worksheet.merge_range(row, col, row+3, col, "No.", text_box_center_wrap_format(workbook))
worksheet.merge_range(row, col+1, row+3, col+1, "Name", text_box_center_wrap_format(workbook))

For the Grading Indicators column, it’s trickier. We need some python manipulation

indicators = [
    ['Correctness', 40],
    ['Doccumentation', 40],
    ['Demo', 20]
]
tmp_col = col+2+len(indicators)-1
worksheet.merge_range(
    row, col+2, row, tmp_col, "Grading Indicators", 
    text_box_center_wrap_format(workbook)
)
for idx, indicator in enumerate(indicators):
    worksheet.write(row+1, col+2+idx, idx+1, text_box_center_wrap_format(workbook))
    worksheet.write(row+2, col+2+idx, indicator[1], text_box_center_wrap_format(workbook))

For the last column

worksheet.merge_range(row, tmp_col+1, row+2, tmp_col+1, "Total", text_box_center_wrap_format(workbook))

The current worksheet looks like

Updating the row so it pointing cell A4

row += 3

Now, we need to fill the form. I need to import something below to convert row, coll to cell notation

from xlsxwriter.utility import xl_rowcol_to_cell

I add a dictionary of the mark

students_grade = {
    "Nanda" : [40, 40, 20],
    "Ryaas" : [28, 32, 15],
    "Absar" : [38, 32, 5]
}

Write down the grade to the cell

for idx, (name, grades) in enumerate(sorted(students_grade.items())):
    worksheet.write(row, col, idx+1, text_box_center_wrap_format(workbook))
    worksheet.write(row, col+1, name, text_box_center_wrap_format(workbook))
    st_cell_grade = xl_rowcol_to_cell(row, col+2)
    tmp_col = col+2
    for grade in grades:
        worksheet.write(row, tmp_col, grade, text_box_center_wrap_format(workbook))
        tmp_col += 1
    end_cell_grade = xl_rowcol_to_cell(row, tmp_col-1)
    worksheet.write(
        row, tmp_col, "=SUM({}:{})".format(st_cell_grade, end_cell_grade),
        text_box_center_wrap_format(workbook)
    )
    row += 1

Now, it looks like this

yay! looks nice

To finalize it, we need to write the indicators explanation

row += 1
worksheet.write(row, col, "Indicators:", default_format(workbook))
row += 1
for idx, indicator in enumerate(indicators):
    placeholder = "{}:{}".format(idx+1, indicator[0])
    worksheet.write(row, col, placeholder, default_format(workbook))
    row += 1

Lastly, we need to close the workbook

workbook.close()

Final result

We are done!

How I Implement it in PPL?

In my Software Engineering Projects, I requested to finish the task about generating excel files for SISIDANG.

Sample template that I need to implement

Problem through the process

1. Requirements changes over time. But it handled because we use Agile Methodology for the development phase
2. The task includes inserting the signature of the lecturer in the template. It’s hard because you need to resize and change the property of the signature image so it can fit the cell were given. It takes me a whole day to find something that can fix that

Things that I learned from it

1. Creating a template with excel, not about hard-coded everything in the template. It’s necessary to create a function that can be used several times for another template. It could help you to save development time and try to change or debug something
   For example, I used it to create format (given in the sample above) and give signature in the sheets
2. Separating files makes me more focused while I implemented the task. For example, I create berkas_helper.py to manage all about cell formatting and genereate_berkas.py for the main logic for the template generation
3. It helped a lot because the data we need to fulfill the template placed in a database. So, automation can be done with this code. It could reduce human participation to fill the template.

Conclusion

Automate generating excel files helps a lot so the process can be done efficiently.

References

1. https://xlsxwriter.readthedocs.io/