New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Worksheet.set_column() sets the column width only once #82

Closed
croshchupkin opened this Issue Dec 18, 2013 · 14 comments

Comments

2 participants
@croshchupkin

croshchupkin commented Dec 18, 2013

Inside the method:

    out = StringIO.StringIO()
    workbook = xlsxwriter.workbook.Workbook(out)
    worksheet = workbook.add_worksheet()

    worksheet.set_column('A:A', 1)
    worksheet.set_column('A:A', 100)

    workbook.close()
    out.seek(0)
    return out

Expected outcome:

snapshot2

Actual outcome:

snapshot3

I don't think it's a LibreOffice issue this time, because the width is, in fact, set - but only once.

@croshchupkin

This comment has been minimized.

Show comment
Hide comment
@croshchupkin

croshchupkin Dec 18, 2013

I just found out that set_column() works properly if I specify an actual letter range (e.g. 'A:C' instead of 'A:A'). But this doesn't help me, because I want to be able to first set the default width for all of the columns involved - and then set the custom width for the select few.

croshchupkin commented Dec 18, 2013

I just found out that set_column() works properly if I specify an actual letter range (e.g. 'A:C' instead of 'A:A'). But this doesn't help me, because I want to be able to first set the default width for all of the columns involved - and then set the custom width for the select few.

@ghost ghost assigned jmcnamara Dec 18, 2013

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 18, 2013

Owner

That is a bug.

I'll post a fix.

Owner

jmcnamara commented Dec 18, 2013

That is a bug.

I'll post a fix.

@croshchupkin

This comment has been minimized.

Show comment
Hide comment
@croshchupkin

croshchupkin Dec 18, 2013

👍 Thanks! I''l be waiting.

croshchupkin commented Dec 18, 2013

👍 Thanks! I''l be waiting.

@croshchupkin

This comment has been minimized.

Show comment
Hide comment
@croshchupkin

croshchupkin Dec 18, 2013

Another detail: when the width was previously set for a range of columns, it won't change even if it's later set to another value for the range which intersects the first one.

croshchupkin commented Dec 18, 2013

Another detail: when the width was previously set for a range of columns, it won't change even if it's later set to another value for the range which intersects the first one.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 18, 2013

Owner

Another detail: when the width was previously set for a range of columns, it won't change even if it's later set to another value for the range which intersects the first one.

That isn't going to work even after the bug fix.

XlsxWriter doesn't track which ranges overlap or intersect and I have no plans to add that as a feature.

If you want that behaviour I'd suggest calculating the ranges in your program (using sets or otherwise) before writing them to the Excel file.

Owner

jmcnamara commented Dec 18, 2013

Another detail: when the width was previously set for a range of columns, it won't change even if it's later set to another value for the range which intersects the first one.

That isn't going to work even after the bug fix.

XlsxWriter doesn't track which ranges overlap or intersect and I have no plans to add that as a feature.

If you want that behaviour I'd suggest calculating the ranges in your program (using sets or otherwise) before writing them to the Excel file.

@croshchupkin

This comment has been minimized.

Show comment
Hide comment
@croshchupkin

croshchupkin Dec 18, 2013

So, will I not be able to, say, set the same width for the a range - and then set the width for an individual column within that range?

croshchupkin commented Dec 18, 2013

So, will I not be able to, say, set the same width for the a range - and then set the width for an individual column within that range?

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 18, 2013

Owner

So, will I not be able to, say, set the same width for the a range - and then set the width for an individual column within that range?

No.

It might work in Excel but probably not in LibreOffice.

When you split a column range in Excel you get n+1 ranges. For example this:

worksheet.set_column('A:E', 20)
worksheet.set_column('C:C', 30)

In Excel would be:

worksheet.set_column('A:B', 20)
worksheet.set_column('C:C', 30)
worksheet.set_column('D:E', 20)

If you have a small number of columns (<1000) then I would recommend just setting each one individually. Like this:

import xlsxwriter

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

for col in range(50):
    worksheet.set_column(col, col, 20)

# Then override any that you want.
worksheet.set_column(4, 4, 50)

workbook.close()

Note, this won't work in LibreOffice until after the bug fix for this issue but it currently works with Excel.

Owner

jmcnamara commented Dec 18, 2013

So, will I not be able to, say, set the same width for the a range - and then set the width for an individual column within that range?

No.

It might work in Excel but probably not in LibreOffice.

When you split a column range in Excel you get n+1 ranges. For example this:

worksheet.set_column('A:E', 20)
worksheet.set_column('C:C', 30)

In Excel would be:

worksheet.set_column('A:B', 20)
worksheet.set_column('C:C', 30)
worksheet.set_column('D:E', 20)

If you have a small number of columns (<1000) then I would recommend just setting each one individually. Like this:

import xlsxwriter

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

for col in range(50):
    worksheet.set_column(col, col, 20)

# Then override any that you want.
worksheet.set_column(4, 4, 50)

workbook.close()

Note, this won't work in LibreOffice until after the bug fix for this issue but it currently works with Excel.

@croshchupkin

This comment has been minimized.

Show comment
Hide comment
@croshchupkin

croshchupkin Dec 18, 2013

I see. I don't think I'll have more than 100 cols in a sheet, so I guess I'll manage.

croshchupkin commented Dec 18, 2013

I see. I don't think I'll have more than 100 cols in a sheet, so I guess I'll manage.

@croshchupkin

This comment has been minimized.

Show comment
Hide comment
@croshchupkin

croshchupkin Dec 18, 2013

By the way, what'll happen if try to do this vise versa, first assigning default styles to each column individually - and then applying the custom styles to a range of columns?

croshchupkin commented Dec 18, 2013

By the way, what'll happen if try to do this vise versa, first assigning default styles to each column individually - and then applying the custom styles to a range of columns?

@croshchupkin

This comment has been minimized.

Show comment
Hide comment
@croshchupkin

croshchupkin Dec 18, 2013

Also, is it just me - or am I unable to first set the column width - and then set the styles for a range of cells, which partially belong to this column? E.g.:

worksheet.set_column('A:A', 20)
worksheet.write('A1:C6', None, workbook.add_format({'border': 1, 'border_color': 'green'}))

croshchupkin commented Dec 18, 2013

Also, is it just me - or am I unable to first set the column width - and then set the styles for a range of cells, which partially belong to this column? E.g.:

worksheet.set_column('A:A', 20)
worksheet.write('A1:C6', None, workbook.add_format({'border': 1, 'border_color': 'green'}))
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 18, 2013

Owner

By the way, what'll happen if try to do this vise versa, first assigning default styles to each column individually - and then applying the custom styles to a range of columns?

The last range will show up in Excel. The first in LibreOffice.

Instead you should do something like the following, which will work without a bug fix in LibreOffice/Excel:

import xlsxwriter

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

colwidths = {}

# Store the defaults.
for col in range(50):
    colwidths[col] = 5

# Then override any that you want.
colwidths[4] = 15

# Then set the column widths.
for col_num, width in colwidths.items():
    worksheet.set_column(col_num, col_num, width)

workbook.close()

Owner

jmcnamara commented Dec 18, 2013

By the way, what'll happen if try to do this vise versa, first assigning default styles to each column individually - and then applying the custom styles to a range of columns?

The last range will show up in Excel. The first in LibreOffice.

Instead you should do something like the following, which will work without a bug fix in LibreOffice/Excel:

import xlsxwriter

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

colwidths = {}

# Store the defaults.
for col in range(50):
    colwidths[col] = 5

# Then override any that you want.
colwidths[4] = 15

# Then set the column widths.
for col_num, width in colwidths.items():
    worksheet.set_column(col_num, col_num, width)

workbook.close()

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 18, 2013

Owner

Also, is it just me - or am I unable to first set the column width - and then set the styles for a range of cells, which partially belong to this column? E.g.:

worksheet.set_column('A:A', 20)
worksheet.write('A1:C6', None, workbook.add_format({'border': 1, 'border_color': 'green'}))

The second line is a syntax error. The write() method only works for individual cells.

Also, a cell format will override a column or row format (this is Excel's behaviour) but not the width or height.

Owner

jmcnamara commented Dec 18, 2013

Also, is it just me - or am I unable to first set the column width - and then set the styles for a range of cells, which partially belong to this column? E.g.:

worksheet.set_column('A:A', 20)
worksheet.write('A1:C6', None, workbook.add_format({'border': 1, 'border_color': 'green'}))

The second line is a syntax error. The write() method only works for individual cells.

Also, a cell format will override a column or row format (this is Excel's behaviour) but not the width or height.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 18, 2013

Owner

The second line is a syntax error.

Hmm, actually it isn't but it should be.

Owner

jmcnamara commented Dec 18, 2013

The second line is a syntax error.

Hmm, actually it isn't but it should be.

@croshchupkin

This comment has been minimized.

Show comment
Hide comment
@croshchupkin

croshchupkin Dec 19, 2013

Instead you should do something like the following, which will work without a bug fix in LibreOffice/Excel:

Yes, I'll do just that.

croshchupkin commented Dec 19, 2013

Instead you should do something like the following, which will work without a bug fix in LibreOffice/Excel:

Yes, I'll do just that.

@jmcnamara jmcnamara closed this in 91ba665 Dec 28, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment