Using python, how to open an existing excel file, change a row/column formatting then save the file-CodePudding

Anyone knows how to open an existing excel (workbook), then on a specified sheet, then change the format of either row(s) or column(s)? I tried following:

import xlsxwriter
wb = xlsxwriter.Workbook("An_existing_excel.xlsx")

But it returns an empty one.

I also tried openpyxl, but cannot figure out how to modify the format of a particular sheet:

from openpyxl import load_workbook
wb = load_workbook(file_name)
for sheet_name in wb.get_sheet_names():
   sheet = wb[sheet_name]
   sheet.set_column(0, 0, None, None, {'hidden': True}) // this is not available

Anyone has suggestions?

CodePudding user response：

Why don't you use Pandas?

install Pandas in your terminal:

pip install pandas

Example usage for sample.xlsx with 2 sheets, and header is on 2nd row in each:

In [1]: import pandas as pd
In [2]: pd.read_excel("sample.xlsx", sheet_name="Sheet 2", header=1)
Out[2]: 
    D   E   F
0   7   8   9
1  10  11  12

In [3]: pd.read_excel("sample.xlsx", sheet_name="Sheet 1", header=1)
Out[3]: 
   A  B  C
0  1  2  3

Then, use pd.ExcelWriter() to change sheet/cell formatting and save it.

More here https://xlsxwriter.readthedocs.io/example_pandas_column_formats.html

CodePudding user response：

With openpyxl, you could readily achieve what you are looking for.

wb = openpyxl.load_workbook(filename)
# select the sheet of interest
ws = wb['Sheet1']
# for example, hide column 'A'
ws.column_dimensions['A'].hidden = True
# save
wb.save(filename)

Check this SO thread for some variations.