Is it possible to create an excel file with hidden columns in Python using pandas?

Viewed 121

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)
0 Answers
Related