I want to color a part of an Excel string in Python
Introduction
As the title says, it reads an Excel file using Python and outputs a part of the character string written in the cell with color. At first, I was planning to use openpyxl, but it seemed that I couldn't do what I wanted, so I decided to use ** write_rich_string ** of xlsxwriter to do it. However, xlsxwriter cannot read, so I will use openpyxl for the reading part and xlsxwriter for the writing part. I chose ~~ Python because I haven't touched it much. ~~
things to do
- Load Excel with Python
openpyxl
import openpyxl
#Open the file with openpyxl
iptbook = openpyxl.load_workbook(filename='test.xlsx')
#Sheet selection
iptsheet = iptbook.worksheets[0]
#Get cell string
cellvalue = iptsheet.cell(row=1, column=1).value
#End
iptbook.close()
- Color a part of the character string in the cell and output
xlsxwriter
import xlsxwriter
#Create a book with xlsxwriter
optbook = xlsxwriter.Workbook('opt.xlsx')
#Add sheet with xlsxwriter
optsheet = optbook.add_worksheet()
#Define format
red = optbook.add_format({'color': 'red'})
#Split characters
splitvalue = cellvalue.split()
#Write in rich text
optsheet.write_rich_string('A1', red, splitvalue[0], splitvalue[1])
#End
optbook.close()
result
The files are before and after execution.
Articles that I used as a reference
Example: Writing “Rich” strings with multiple formats
I want to handle things in Excel where multiple formats (fonts) are mixed in one cell
in conclusion
With rich text, you can change not only the color but also the line weight and font. This time, it was realized by using two libraries in combination. If anyone knows a better way, I would be grateful if you could let me know.
Recommended Posts