Python 2.7 xlsxwriter isn't formatting data properly with valign

Question

I have horizontal and vertical alignment set to center, but only the first two columns are centering the data.

The third column appears to be setting the data with a 'top' vertical alignment:

#!/usr/bin/env python

import xlsxwriter

# Create our spreadsheet.
workbook = xlsxwriter.Workbook('stats.xlsx')
worksheet = []

# Open our data file to extract data.
data = open('server_results','r')

# Create a bold centered font for our column headers.
format = workbook.add_format()
format.set_bold()
format.set_align('center')
format.set_align('vcenter')
format.set_text_wrap()

# Non-headers will be centered.
format1 = workbook.add_format()
format1.set_align('center')
format1.set_align('vcenter')
format1.set_text_wrap()

# Provider label/name for worksheet.
worksheet.append(workbook.add_worksheet('Stats'))

# Adjust column widths for data.
worksheet[0].set_column(0,2,25)
#worksheet[0].set_column(1,1,25)
#worksheet[0].set_column(1,2,25)
#worksheet[0].set_row(0,40)

# Start from the first cell. Rows and columns are zero indexed.
worksheet[0].write('A1', 'Column 1', format)
worksheet[0].write('B1', 'Column 2', format)
worksheet[0].write('C1', 'Column 3', format)

row = 1
col = 0

# Count lines
linelist = data.readlines()
count = len(linelist)

# Populate spreadsheet.
for num in range (0, count):
    line = linelist[num]
    splitline = line.split("\t")
    worksheet[0].write(row, col, splitline[0], format1)
    worksheet[0].write(row, col + 1, splitline[1], format1)
    worksheet[0].write(row, col + 2, splitline[2], format1)
    row += 1

#close workbook
workbook.close()
EOF

I've tried modifying how I format the data with following code:

format = workbook.add_format({'bold': True, 'bg_color': 'yellow', 'border': 5, 'align': 'center', 'valign': 'middle', 'text_wrap': True})

but when I do this, then NONE of the columns are centered -- with this format the first two columns are bottom aligned and third column is top aligned

Answer 1

Using the following:

splitline = [l.rstrip() for l in line.split("\t")]

resolved the issue.

Answer 2

The third column appears to be setting the data with a 'top' vertical alignment

I ran a version of your program with some sample data and I don't see that behaviour:

import xlsxwriter

# Create our spreadsheet.
workbook = xlsxwriter.Workbook('stats.xlsx')
worksheet = []

# Create a bold centered font for our column headers.
format = workbook.add_format()
format.set_bold()
format.set_align('center')
format.set_align('vcenter')
format.set_text_wrap()

# Non-headers will be centered.
format1 = workbook.add_format()
format1.set_align('center')
format1.set_align('vcenter')
format1.set_text_wrap()

# Provider label/name for worksheet.
worksheet.append(workbook.add_worksheet('Stats'))

# Adjust column widths for data.
worksheet[0].set_column(0,2,25)

# Start from the first cell. Rows and columns are zero indexed.
worksheet[0].write('A1', 'Column 1', format)
worksheet[0].write('B1', 'Column 2', format)
worksheet[0].write('C1', 'Column 3', format)

row = 1
col = 0

# Count lines
linelist = ['foo\tbar\tbaz\tbing'] * 5
count = len(linelist)

# Populate spreadsheet.
for num in range (0, count):
    line = linelist[num]
    splitline = line.split("\t")
    worksheet[0].write(row, col, splitline[0], format1)
    worksheet[0].write(row, col + 1, splitline[1], format1)
    worksheet[0].write(row, col + 2, splitline[2], format1)
    row += 1

#close workbook
workbook.close()

The output shows horizontal and vertical centering as expected:

Could you clarify what the issue is.