Creating named range for with Python based on the string inside the cells

Question

I am not really sure where to begin with this question.

I know that I have two options for Pandas to_excel engines, either openpyxl or XlsxWriter.

I'm new to both.

I would like to created named range that will export to the Excel output based on the string in the cells, not the cell range (ie A1:A4). I basically have multiple sub-tables that I'm exporting from Pandas to one worksheet, and I want each table to have Ann Excel named range.

I know both openpyxl and XlsxWriter have defined name methods.

Does anyone have any suggestions?

jmcnamara · Answer

The question isn't 100% clear around the "string in the cells" but here is how you could add named ranges to the data in dataframes exported to Excel, using xlsxwriter:

import pandas as pd
from xlsxwriter.utility import xl_range_abs

# Create some Pandas dataframes from some data.
df1 = pd.DataFrame({'Data1': [11, 12, 13, 14],
                    'Data2': [11, 12, 13, 14]})
df2 = pd.DataFrame({'Data1': [11, 12, 13, 14],
                    'Data2': [11, 12, 13, 14]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas.xlsx', engine='xlsxwriter')

# Position the dataframes in the worksheet.
df1.to_excel(writer, sheet_name='Sheet1')  # Default position, cell A1.
df2.to_excel(writer, sheet_name='Sheet1', startrow=5)

# Get the xlsxwriter workbook object.
workbook  = writer.book

# Set a defined name range.
(max_row, max_col) = df1.shape
cell_range = xl_range_abs(1, 1, max_row, max_col)
cell_range = '=Sheet1!' + cell_range
workbook.define_name('Sheet1!Range1', cell_range)

# Set another range.
previous_max_row = max_row + 1
(max_row, max_col) = df2.shape
cell_range = xl_range_abs(previous_max_row + 1, 1,
                          previous_max_row + max_row, max_col)
cell_range = '=Sheet1!' + cell_range
workbook.define_name('Sheet1!Range2', cell_range)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

Creating named range for with Python based on the string inside the cells

1 Answers