Python output not formatting correctly with output to LibreOffice by openpyxl

Question

Attempting to format output to a LibreOffice calc (ie, linux-based Excel) document but the python format syntax isn't applied as expected.

Using the new string formatting, running the below:

import openpyxl as xl
wb = xl.Workbook()
ws = wb.active

ws['A3'] = '{:>}'.format('rundate:')
ws['A4'] = '{:>}'.format('name:')
ws['A5'] = '{:>}'.format('comments:')

wb.save('test.xlsx')

Unexpectedly gives this, which appears to be left rather than right aligned:

Trying the left align char on the off chance I had them reversed:

import openpyxl as xl
wb = xl.Workbook()
ws = wb.active

ws['A3'] = '{:<}'.format('rundate:')
ws['A4'] = '{:<}'.format('name:')
ws['A5'] = '{:<}'.format('comments:')

wb.save('test.xlsx')

Returns the same:

And another version (specifying total number of chars), makes it clear the text isn't being right aligned. If anything, right aligned text should spill over the left side of the cell, not the right.

import openpyxl as xl
wb = xl.Workbook()
ws = wb.active

ws['A3'] = '{:>12}'.format('rundate:')
ws['A4'] = '{:>12}'.format('name:')
ws['A5'] = '{:>12}'.format('comments:')

wb.save('test.xlsx')

To the downvoter, what was the issue?

Chris
– Chris

2020-02-03 11:01:44 +00:00
Commented Feb 3, 2020 at 11:01 — Chris
– Chris, Commented Feb 3, 2020 at 11:01

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

From the alignment module:

Alignment options for use in styles.

horizontal Value must be one of {‘left’, ‘centerContinuous’, ‘center’, ‘distributed’, ‘fill’, ‘justify’, ‘right’, ‘general’}

I think this will work:

from openpyxl.styles import Alignment
alignment=Alignment(horizontal='left')

import openpyxl as xl
wb = xl.Workbook()
ws = wb.active

for row in ws.iter_rows(min_col=1, max_col=1, min_row=3, max_row=5):
    for cell in row:
        cell.alignment = alignment

wb.save('test.xlsx')

or simply:

ws['A3'].alignment = Alignment(horizontal='left')
ws['A4'].alignment = Alignment(horizontal='left')
ws['A5'].alignment = Alignment(horizontal='left')

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 3, 2020 at 3:17

APhillips

1,19111 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

Ugh, so janky...already needed two other sets of loops to format cells, this would be a cherry on top. Try in a few, thanks.

Collectives™ on Stack Overflow

Python output not formatting correctly with output to LibreOffice by openpyxl

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related