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
[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
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
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
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
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
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
first of all, this post is the first piece of the solution, where you should specify startrow=: Append existing excel sheet with new dataframe using python pandas you might also consider header=False. so it should look like: df1.to_excel(writer, startrow = 2,index = False, Header = False) if you want it to automatically get to the … Read more
You cannot append to an existing xlsx file with xlsxwriter. There is a module called openpyxl which allows you to read and write to preexisting excel file, but I am sure that the method to do so involves reading from the excel file, storing all the information somehow (database or arrays), and then rewriting when … Read more
Thank you. I believe that a complete example could be good for anyone else who have the same issue: import pandas as pd import numpy as np path = r”C:\Users\fedel\Desktop\excelData\PhD_data.xlsx” x1 = np.random.randn(100, 2) df1 = pd.DataFrame(x1) x2 = np.random.randn(100, 2) df2 = pd.DataFrame(x2) writer = pd.ExcelWriter(path, engine=”xlsxwriter”) df1.to_excel(writer, sheet_name=”x1″) df2.to_excel(writer, sheet_name=”x2″) writer.save() writer.close() Here … Read more