A little update on @alecxe response for Python 3 (io.BytesIO instead of StringIO.StringIO) and Django >= 1.5 (content_type instead of mimetype), with the fully in-memory file assembly that has since been implemented by @jmcnamara ({‘in_memory’: True}) ! Here is the full example : import io from django.http.response import HttpResponse from xlsxwriter.workbook import Workbook def your_view(request): … Read more
You can use with: with pd.ExcelWriter(‘test.xlsx’, engine=”openpyxl”, mode=”a”) as writer: d1.to_excel(writer,sheet_name=”d1″) d2.to_excel(writer,sheet_name=”d2″) writer.save() writer.close() update This should work just note that the a blank file needs to be created before hand. You can just create a blank file using python if you want. I created a simple loop to, in some ways, mimic the essence … Read more
Use load_book from openpyxl – see xlsxwriter and openpyxl docs: import pandas as pd from openpyxl import load_workbook book = load_workbook(‘test.xlsx’) writer = pd.ExcelWriter(‘test.xlsx’, engine=”openpyxl”) writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) df.to_excel(writer, sheet_name=”tab_name”, other_params) writer.save()
[NOTE: as of Jan 2023 xslxwriter added a new method called autofit. See jmcnamara’s answer below] As a general rule, you want the width of the columns a bit larger than the size of the longest string in the column. The with of 1 unit of the xlsxwriter columns is about equal to the width … Read more
You could use set_column as follows: worksheet1.set_column(1, 1, 25) This is defined as follows: set_column(first_col, last_col, width, cell_format, options) You would need to determine a suitable width, perhaps based on the longest length of text in the whole column. Care though would be needed to base this on the font and size being used. Also … Read more
To create the Worksheet in advance, you need to add the created sheet to the sheets dict: writer.sheets[‘Validation’] = worksheet Using your original code: # Creating Excel Writer Object from Pandas writer = pd.ExcelWriter(‘test.xlsx’,engine=”xlsxwriter”) workbook=writer.book worksheet=workbook.add_worksheet(‘Validation’) writer.sheets[‘Validation’] = worksheet df.to_excel(writer,sheet_name=”Validation”,startrow=0 , startcol=0) another_df.to_excel(writer,sheet_name=”Validation”,startrow=20, startcol=0) Explanation If we look at the pandas function to_excel, it uses … Read more
Pandas expects a filename path to the ExcelWriter constructors although each of the writer engines support StringIO. Perhaps that should be raised as a bug/feature request in Pandas. In the meantime here is a workaround example using the Pandas xlsxwriter engine: import pandas as pd import StringIO io = StringIO.StringIO() # Use a temp filename … Read more
Your logic is almost correct, however i approached your problem through a slightly different approach: 1) Sort the column, make sure that all the values are grouped together. 2) Reset the index (using reset_index() and maybe pass the arg drop=True). 3) Then we have to capture the rows where the value is new. For that … Read more
Quote from xlsxwriter module documentation: This module cannot be used to modify or write to an existing Excel XLSX file. If you want to modify existing xlsx workbook, consider using openpyxl module. See also: Modify an existing Excel file using Openpyxl in Python Use openpyxl to edit a Excel2007 file (.xlsx) without changing its own … Read more
I think you need first reset default header style, then you can change it: pd.core.format.header_style = None All together: import pandas as pd data = pd.DataFrame({‘test_data’: [1,2,3,4,5]}) writer = pd.ExcelWriter(‘test.xlsx’, engine=”xlsxwriter”) pd.core.format.header_style = None data.to_excel(writer, sheet_name=”test”, index=False) workbook = writer.book worksheet = writer.sheets[‘test’] font_fmt = workbook.add_format({‘font_name’: ‘Arial’, ‘font_size’: 10}) header_fmt = workbook.add_format({‘font_name’: ‘Arial’, ‘font_size’: 10, … Read more