使用xlsxwriter加粗和着色行

huangapple go评论80阅读模式
英文:

Bolding and coloring rows using xlsxwriter

问题

以下是您当前的代码:

import xlsxwriter

user_input = [["10002",'01/04/23','','300','','300','','','','44.44','','','','','34232','','','','34','','','2312'],['10001','01/30/2023','63','15','12345','gatorade','0.1234','a0001','4','50','50','115.4','123','33456','34543','34234','3432','34.22','1800','1800','0','0']]
# 在这里输入列表

column_titles = ['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22']

user_input.insert(0, column_titles)
# 将列标题插入到Excel的第一行

workbook = xlsxwriter.Workbook('workbook.xlsx')
worksheet = workbook.add_worksheet()

for row_num, data in enumerate(user_input):
    worksheet.write_row(row_num, 0, data)
# 添加到Excel文档

如果您想要将第一行文本加粗,并将第一行的前5列单元格高亮显示为蓝色,可以使用XlsxWriter的格式功能来实现。以下是示例代码:

import xlsxwriter

user_input = [["10002",'01/04/23','','300','','300','','','','44.44','','','','','34232','','','','34','','','2312'],['10001','01/30/2023','63','15','12345','gatorade','0.1234','a0001','4','50','50','115.4','123','33456','34543','34234','3432','34.22','1800','1800','0','0']]
# 在这里输入列表

column_titles = ['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22']

user_input.insert(0, column_titles)
# 将列标题插入到Excel的第一行

workbook = xlsxwriter.Workbook('workbook.xlsx')
worksheet = workbook.add_worksheet()

# 创建用于加粗文本的格式
bold_format = workbook.add_format({'bold': True})

# 创建用于蓝色背景的格式
blue_format = workbook.add_format({'bg_color': 'blue'})

for row_num, data in enumerate(user_input):
    worksheet.write_row(row_num, 0, data)

# 将第一行文本加粗
worksheet.set_row(0, None, bold_format)

# 将第一行的前5列单元格高亮显示为蓝色
for col in range(5):
    worksheet.set_column(col, col, None, blue_format)

workbook.close()

这将帮助您实现所需的效果。希望对您有所帮助。

英文:

Here is my current code:

import xlsxwriter

user_input = [["10002",'01/04/23','',"300",'',"300",'','','',"44.44",'','','','',"34232",'','','',"34",'','',"2312"],["10001","01/30/2023","63","15","12345","gatorade","0.1234","a0001","4","50","50","115.4","123","33456","34543","34234","3432","34.22","1800","1800","0","0"]]
#Lists are entered here

column_titles = ['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22']

user_input.insert(0, column_titles)
#Adds column titles to be in first row of Excel


workbook = xlsxwriter.Workbook('workbook.xlsx')
worksheet = workbook.add_worksheet()

for row_num, data in enumerate(user_input):
    worksheet.write_row(row_num, 0, data)
#Adds to Excel doc

I have tried to follow <https://xlsxwriter.readthedocs.io/tutorial02.html> and <https://stackoverflow.com/questions/28966420/how-to-set-formatting-for-entire-row-or-column-in-xlsxwriter-python> , but every time I try and edit those to work for my own code, my workbook just comes back blank. Doesn't error out or anything.

This is my first time using xlsxwriter, so I'm not quite sure how to do much yet. I'm trying to take the first row in the spreadsheet, and put it all in bold. (My attempts of this are not in my example code). As well as putting the first 5 columns in the first row, and highlighting those boxes to be blue. Can anybody help me with this?

I'm thinking maybe the way I have the column titles list being appended into the original list may be part of what's complicating this? But I'm unsure. Thank you in advance for any help.

答案1

得分: 1

你可能需要根据所需的格式将数据写入不同的部分。
要添加格式,您可以创建一个格式,并在写入时应用该格式,或者将其设置为行或列。
只考虑标题行,给定两个要求:

  1. 加粗所有值
  2. 前5个单元格以蓝色高亮显示

在下面的示例代码中,有两种格式,即加粗和单元格高亮。
在这种情况下,加粗设置为第一行(0),即第一行。此行可以在列表编写之前或之后添加。'set_row'将格式'header_row_format'应用于从A1到表格可能包含的最后一列的整行。
尽管加粗所有第一行的单元格可能没问题,但高亮可能不适用,尽管您的要求只是高亮前5个单元格。因此,在这种情况下,我们可以创建另一种格式,即'cell_format',并在写入单元格值时仅将其添加到前5个单元格。
如果您只想加粗写入数据的单元格,可以将加粗包括在'cell_format'的一部分中,如已注释的格式行所示。但在这种情况下,您需要两个单元格格式,一个带有背景颜色,一个不带背景颜色。

import xlsxwriter

column_titles = ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22']

workbook = xlsxwriter.Workbook('workbook.xlsx')
worksheet = workbook.add_worksheet()

header_row_format = workbook.add_format({'bold': True})
worksheet.set_row(0, None, header_row_format)

cell_format = workbook.add_format()
# cell_format.set_bold(True)
cell_format.set_bg_color('blue')

for col_num, data in enumerate(column_titles):
    if col_num < 5:
        worksheet.write(0, col_num, data, cell_format)
    else:
        worksheet.write(0, col_num, data)

workbook.close()
英文:

You may need to separate the data writes depending on formatting required.
To add formatting you can create a format and apply that when writing or set to a row or column.<br>
Looking at the header row only given the two requirements;<br>

  1. Bold all values
  2. First 5 cells highlighted in blue

In the example code below there a two formats the bolding and the cell highlight.<br>
In this case bolding is set to the row (0) i.e. row 1, this line can be added before or after the list is written. The 'set_row' applies the format 'header_row_format' to the whole row from A1 to the last possible column the sheet can contain.<br>
While bolding all the cells in the first row may be OK, higlighting probably wouldn't be notwithstanding your requirement is to only highlight the first 5 anyway. Therefore in this case we can create another format, 'cell_format' and only add this to the first 5 cells as we write the cell values.<br>
If you did only want to bold those cells that you write data to you could include bold as part of the 'cell_format' see commented format line. However in this case you'd need two cell formats one with the bg colour and one without.<br>

import xlsxwriter

column_titles = [&#39;1&#39;,&#39;2&#39;,&#39;3&#39;,&#39;4&#39;,&#39;5&#39;,&#39;6&#39;,&#39;7&#39;,&#39;8&#39;,&#39;9&#39;,&#39;10&#39;,&#39;11&#39;,&#39;12&#39;,&#39;13&#39;,&#39;14&#39;,&#39;15&#39;,&#39;16&#39;,&#39;17&#39;,&#39;18&#39;,&#39;19&#39;,&#39;20&#39;,&#39;21&#39;,&#39;22&#39;]

workbook = xlsxwriter.Workbook(&#39;workbook.xlsx&#39;)
worksheet = workbook.add_worksheet()

header_row_format = workbook.add_format({&#39;bold&#39;: True})
worksheet.set_row(0, None, header_row_format)

cell_format = workbook.add_format()
# cell_format.set_bold(True)
cell_format.set_bg_color(&#39;blue&#39;)

for col_num, data in enumerate(column_titles):
    if col_num &lt; 5:
        worksheet.write(0, col_num, data, cell_format)
    else:
        worksheet.write(0, col_num, data)


workbook.close()

huangapple
  • 本文由 发表于 2023年2月8日 23:52:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/75388349.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定