About 4 years ago, I wrote an article about prime numbers in Excel and their visualisation with VBA – vba-function-in-excel-to-check-prime-numbers. Pretty much, it displays the prime numbers in a 10 x N matrix, starting from 1 and finishing at N:
So, in this article, I will do exactly the same thing with Python and the xlsxwriter library. So, let’s try to summarize the task:
- write a matrix with 10 cells per row
- format these cells as follows:
- centre them
- color them in green and bold them, if the cell contains prime number
That’s all. For the writing of the matrix, it is quite important to note, that in Python the first column and first row of Excel is with index 0. Which is not the case in the Excel library of VBA. Thus, the following code, writing to 0th row and 0th column in Excel through Python is actually working:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
def Main(): wbk_name = 'vitoshacademy.xlsx' wbk = xlsxwriter.Workbook(wbk_name) wks = wbk.add_worksheet() wks.set_column(0,9,3.22) wks.write(0,0, 'A') wks.write(0,1, 'B') wks.write(1,0, 'C') wks.write(1,1, 'D') wbk.close() |
And providing what is expected, with “A1” being on the 0th column and 0th row:
Once this is fixed in our task, the only problem left is the format. To be honest, although the documentation of the xlswriter is quite well written, it still took me some time to fix it. Anyway, the format is presented as a dictionary with the format parameters being the keys:
|
1 2 |
cell_format_default = wbk.add_format({'bold': False, 'font_color': 'black', 'bg_color':'white','align':'center'}) cell_format_prime = wbk.add_format({'bold': True, 'font_color': 'white', 'bg_color': 'green','align': 'center'}) |
At the end, fixing the columns at a specific width in excel is a 1 liner as expected – wks.set_column(0,9,3.22). Of course, starting from the 0th column. And 3.22 is the width, that is quite ok to display a 3 digit number.
This is how the complete python code looks like:
|
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 32 33 34 35 36 37 38 39 |
import xlsxwriter import math def Main(): wbk_name = 'vitoshacademy.xlsx' wbk = xlsxwriter.Workbook(wbk_name) wks = wbk.add_worksheet() wks.set_column(0,9,3.22) cell_format_default = wbk.add_format({'bold': False, 'font_color': 'black', 'bg_color':'white','align':'center'}) cell_format_prime = wbk.add_format({'bold': True, 'font_color': 'white', 'bg_color': 'green','align': 'center'}) for number in range(1, 151): if (number % 10 == 0): column = 9 row = number // 10 - 1 else: row = number // 10 column = number % 10 - 1 if is_prime(number): wks.write(row,column, number, cell_format_prime) else: wks.write(row,column, number, cell_format_default) wbk.close() def is_prime(number): if number == 1 or number == 2 or number == 3: return True if number < 1: return False for i in range(2, int(math.sqrt(number))+1): if number % i == 0: return False return True if __name__== "__main__": Main() |