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
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 xlsxwriterworkbook = 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 defaultdef 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_wrapdef 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))
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
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()
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.
Problem through the process
- Requirements changes over time. But it handled because we use Agile Methodology for the development phase
- 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
- 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 - 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
- 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.