class Sheet() & DataframeSheet()
class Sheet()
Base Excel Sheet Class which contains all the methods that can be applied to a sheet
merge_cells(ref)
Merge several cells
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
ref |
str
|
Cell range reference e.g. 'A1:C5' |
required |
Example
sheet1.merge_cells(ref='A1:C5')
group_columns(ref)
Group several columns
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
ref |
str
|
Excel column reference e.g. 'A:C' |
required |
Example
sheet1.group_columns(ref='1:2')
group_rows(ref)
Group several rows
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
ref |
str
|
Excel row reference e.g. '1:3' |
required |
Example
sheet1.group_rows(ref='1:5')
add_data_validation(ref, type, props=None, operator=None, **kwargs)
Add data validation to worksheet
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
ref |
str or list of str
|
Cell range reference e.g. "A1:C5" or ["A1:C5", "A21:Z55"] |
required |
type |
str
|
Data valiation type. Options: list, whole, decimal, date, time, textLength, formula |
required |
operator |
str
|
Validation mathematical operator: "between", "greaterThan", "greaterThanOrEqual", "equal", "notEqual", "lessThan", "lessThanOrEqual" |
None
|
props |
any
|
Depending on vaildation type the properties: list = ["Option 1", "Option 2"], numeric = single number OR upper & lower boundary (1, 100), formla |
None
|
kwargs |
dict
|
Any futher kwarg specified in openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.datavalidation.html |
{}
|
Examples:
sheet1.add_data_validation(ref="A1:C5", type="whole")sheet1.add_data_validation(ref="A1:C5", type="decimal", operator="greaterThan", props=0)sheet1.add_data_validation(ref="A1:C5", type="textLength", props=10)sheet1.add_data_validation(ref="A1:C5", type="list", props=["Yes", "No"])sheet1.add_data_validation(ref="A1:C5", type="whole", operator="between", props=[0, 100])
class DataframeSheet()
Bases: Sheet
DataFrame Excel Sheet class containing all logic specific to dataframe exports
Note: All methods/function of the class Sheet also work for this class DataframeSheet
add_data_validation(ref, type, props=None, operator=None, **kwargs)
Add data validation to worksheet
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
ref |
str or list of str
|
Cell range reference e.g. "A1:C5" or "employees" or ["A1:C5", "A21:Z55", "employees:RoE"] |
required |
type |
str
|
Data valiation type. Options: list, whole, decimal, date, time, textLength, formula |
required |
operator |
str
|
Validation mathematical operator: "between", "greaterThan", "greaterThanOrEqual", "equal", "notEqual", "lessThan", "lessThanOrEqual" |
None
|
props |
any
|
Depending on vaildation type the properties: list = ["Option 1", "Option 2"], numeric = single number OR upper & lower boundary (1, 100), formla |
None
|
kwargs |
dict
|
Any futher kwarg specified in openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.datavalidation.html |
{}
|
Examples:
sheet1.add_data_validation(ref="employees", type="whole")sheet1.add_data_validation(ref="A1:C5", type="whole")sheet1.add_data_validation(ref="RoE", type="decimal", operator="greaterThan", props=0)sheet1.add_data_validation(ref="A1:C5", type="textLength", props=10)sheet1.add_data_validation(ref="A1:C5", type="list", props=["Yes", "No"])sheet1.add_data_validation(ref="A1:C5", type="whole", operator="between", props=[0, 100])