I have used pandas to convert my data into dataframes and I am saving the data to separate sheets within one excel file. I would like to hide certain columns in each excel sheet, but I haven't found any functions within pandas that will translate over to the excel file. I apologize if I may not have looked hard enough or it is just not possible.
Here is a simplified version of what it looks like now:
import pandas as pd
sheets = {"Sheet 1":[1,2,3], "Sheet 2":[3,5,6]}
header = ["a", "b", "c"]
with pd.ExcelWriter('test.xlsx') as writer:
for s in list(sheets.keys()):
df = pd.DataFrame(sheets[s], columns=header)
df.to_excel(writer, sheet_name=s, index=False)
It might be possible I need to change to using xlsxwriter or another package like that. I am not using pandas for anything else other that saving the data to an excel file in separate sheets. Additionally, I would like to hide the same columns in each sheet.
EDIT: I figured it out. You can use xlsxwriter as the engine when writing the excel file.
Here is what worked:
import pandas as pd
sheets = {"Sheet 1":[1,2,3], "Sheet 2":[3,5,6]}
header = ["a", "b", "c"]
with pd.ExcelWriter('test.xlsx', engine="xlsxwriter") as writer:
for s in list(sheets.keys()):
df = pd.DataFrame(sheets[s], columns=header)
sheet = writer.sheets(s)
hidden_column_ndx = 0
sheet.set_column(hidden_column_ndx, hidden_column_ndx, None, None, {"hidden":1})
df.to_excel(writer, sheet_name=s, index=False)