1

I have a file like so that I am reading from excel:

Year  Month  Day
1     2      1
2     1      2

I want to specify the column width that excel recognizes. I would like to do it in pandas but I don't see a option. I have tried to do it with the module StyleFrame.

This is my code:

from StyleFrame import StyleFrame
import pandas as pd

df=pd.read_excel(r'P:\File.xlsx')
excel_writer = StyleFrame.ExcelWriter(r'P:\File.xlsx')
sf=StyleFrame(df)
sf=sf.set_column_width(columns=['Year', 'Month'], width=4.0)
sf=sf.set_column_width(columns=['Day'], width=6.00)
sf=sf.to_excel(excel_writer=excel_writer)
excel_writer.save()

but the formatting isn't saved when I open the new file.

Is there a way to do it in pandas? I would even take a pure python solution to this, pretty much anything that works.

Stefano Potter
  • 3,467
  • 10
  • 45
  • 82
  • 1
    This works ok for me. What version of StyleFrame are you using? `import StyleFrame ; print(StyleFrame._version_)` – DeepSpace Oct 20 '16 at 18:52
  • version 0.1.5 is what I am using – Stefano Potter Oct 20 '16 at 18:55
  • I guess it looks like when I open the excel file the column width is visually changed. But when I right click the column and select `column width` my specification is not what it says – Stefano Potter Oct 20 '16 at 18:59
  • 1
    Yeah, the actual width values in Excel seem to consistently be 0.62 less than the desired. You can try to exaggerate and set a very large width just to verify that your changes are actually being saved to the file. I'll look into it and fix it for the next version :) – DeepSpace Oct 20 '16 at 19:03
  • Yep, for me it is .76 less than expected, but still consistent. – Stefano Potter Oct 20 '16 at 19:23
  • This is related to the way Excel stores column widths: https://bitbucket.org/openpyxl/openpyxl/issues/293/column-width-issue#comment-10400299 Unfortunately there's not something that openpy (which styleframe uses) or styleframe can do. – DeepSpace Oct 20 '16 at 19:52
  • What about [this approach](http://xlsxwriter.readthedocs.io/example_pandas_column_formats.html)? – MaxU - stand with Ukraine Oct 20 '16 at 20:33
  • any ideas on how to remove the column headers when saving the file with the method I posted? – Stefano Potter Oct 20 '16 at 21:33

1 Answers1

3

As for your question on how to remove the headers, you can simply pass header=False to to_excel:

sf.to_excel(excel_writer=excel_writer, header=False).

Note that this will still result with the first line of the table being bold. If you don't want that behavior you can update to 0.1.6 that I just released.

DeepSpace
  • 78,697
  • 11
  • 109
  • 154