Lifehacks, Python

Openpyxl – how to change font and color in a cell?

Hello to all! In continuation of articles on working with Openpyxl today, let’s try to figure out: how to change the font and color in a cell?

We’ll begin to understand – and for practical purposes, create an Excel file in which we will create a cell with text that will be:

  • Have some size
  • Colour
  • Underline
  • Incline
  • The text will be bold

We begin to code: 

  1. import openpyxl #Connect the library
  2. from openpyxl import Workbook
  3. from openpyxl.styles import PatternFill#Connect cell styles
  4. from openpyxl.workbook import Workbook
  5. from openpyxl.styles import Font, Fill#Connect styles for text
  6. from openpyxl.styles import colors#Connect colors for text and cells
  7. wb = openpyxl.Workbook() #Create book
  8. work_sheet = wb.create_sheet(title=’Test sheet’) #Created a sheet with a name and made it active
  9.  
  10. work_sheet['A1'] = 'Test text'
  11. work_sheet_a1 = work_sheet['A5']#Created a variable that contains cell A1 with the existing text
  12. work_sheet_a1.font = Font(size=23, underline='single', color='FFBB00', bold=True, italic=True) #We apply the following parameters to the text: size - 23, underline, color = FFBB00 (text color is specified in RGB), bold, oblique. If we do not need a bold font, we use the construction: bold = False. We act similarly if we do not need an oblique font: italic = False.
  13.  
  14. Important: if necessary, the possibility of using standard colors is included in the styles, but the code in this case will look different:
  15. work_sheet_a1.font = Font(size=23, underline='single', color = colors.RED, bold=True, italic=True) #what color = colors.RED — color prescribed in styles
  16. work_sheet_a1.fill = PatternFill(fill_type='solid', start_color='ff8327', end_color='ff8327')#This code allows you to do design color cells

Important: to work with the design of columns or rows, another algorithm is used, which we will talk about later.

By the way, below is the code for a simple program that creates a page and writes text in cells from a unit to the specified one.

  1. import openpyxl #Подключаем библиотеку
  2. from openpyxl.styles import Font, Fill
  3. wb = openpyxl.Workbook() #Создали книгу
  4. work_sheet = wb.create_sheet(title='Test sheet') #Создали лист с названием и сделали его активным
  5. a = int(input('Введите а: '))
  6. cell_a = 'A'
  7. i = 1
  8. while i <= a:
  9.     i = str(i)
  10.     cell = cell_a + i
  11.     work_sheet[cell] = 'Test text'
  12.     work_sheet_a1 = work_sheet[cell]
  13.     work_sheet_a1.font = Font(size=i)
  14.     i = int(i)
  15.     i = i + 1
  16. wb.save('ok.xlsx')

Thanks for attention! On a topic that sounds like “Openpyxl – how to change the font and color in a cell” – there is nothing more to say ? Do not forget to repost ?