mugglecode / Excel Magic
Programming Languages
Labels
Projects that are alternatives of or similar to Excel Magic
Excel MAGIC
Simplify common Excel operations.
Installation
pip install excelmagic
Usage
Opening an Excel file
from excel_magic.dataset import open_file
file = open_file('test.xlsx')
Also supports with statement:
from excel_magic.dataset import open_file
with open_file('test.xlsx') as file:
pass
Query rows
Example data:
Id | Name | Age | Score |
---|---|---|---|
1 | John | 22 | 89 |
2 | David | 23 | 93 |
3 | Emma | 22 | 95 |
Query rows of a sheet in an excel file with specific cell value:
from excel_magic.dataset import open_file
with open_file('test.xlsx') as excel:
# select a sheet by index or sheet name
sheet = excel.get_sheet_by_index(0)
# find rows containing the name 'David'
rows = sheet.find(Name='David')
Or query rows with callback function:
from excel_magic.dataset import open_file
def score_over_90(rows):
if rows['Score'].value > 90:
return True
with open_file('test.xlsx') as excel:
sheet = excel.get_sheet_by_index(0)
# find rows with the score column greater than 90
rows = sheet.filter(score_over_90)
Getting cell values from a row
You can use key: value
method to get the cell object in a rod, like operating a dict.
cell = row['Score']
And get the value of the cell object through the value
attribute.
score_num = cell.value
Split sheets
Split multiple sheets of excel file to independent excel files.
from excel_magic.dataset import open_file
file = open_file('test.xlsx')
file.split_sheets_to_files()
Merge files
Combine sheets from files into a new excel file.
from excel_magic.dataset import open_file
excel_files = ['01.xlsx', '02.xlsx', '03.xlsx']
new_excel = open_file('test.xlsx')
for file in excel_files:
new_excel.merge_file(file)
new_excel.save()
Or
from excel_magic.dataset import open_file
excel_files = ['01.xlsx', '02.xlsx', '03.xlsx']
with open_file('test.xlsx') as new_excel:
for file in excel_files:
new_excel.merge_file(file)
API Reference
The hierarchical relationship in the excel file is:
Excel (sheets) → Sheet → Row → Cell
And excelmagic provides similar hierarchical object API:
Dataset Object → Sheet Object → Row Object → Cell Object
Dataset Object
Example:
from excel_magic.dataset import open_file
dataset = open_file('test.xlsx')
Methods:
Search Sheet
-
get_sheet_by_index(index: int) -> Sheet
- get a sheet object by sheet index.
-
get_sheet_by_name(name: str) -> Sheet
- get a sheet object by sheet name.
-
does_exist(name: str) -> bool
- check if sheet name exists in your Dataset.
Create Sheet
-
add_sheet(name: str, fields: List[str]) -> Sheet
- append new sheet with sheet name and column headers.
Delete Sheet
-
remove_sheet(sheet: Sheet) -> None
- remove a sheet by passing a sheet object.
Others
-
save() -> None
- save changes.
-
split_sheets_to_files() -> None
- split multiple sheets to independent excel files.
-
merge_file(path: str) -> None
- merge another excel file to the current file.
-
export_json(out: str) -> None
- export all sheets to a json file.
Sheet Object
Example:
from excel_magic.dataset import open_file
dataset = open_file('test.xlsx')
sheet = dataset.get_sheet_by_index(0)
Methods:
Search rows
-
find(**kwargs: dict[str, Any]) -> List[dict]
- return list of row which is essentially a dict.
-
filter(callback: Callable[[dict], Union[None, bool]]) -> List[dict]
- return list of row, filter by the callback function with which return True. And the callback receives row object (a dict) as parameter.
-
get_rows() -> List[dict]
- return a list of all rows.
Create row
-
append_row(content: Union[dict, List[str]]) -> None
- append a row to your file. If you use dict-type parameter, the keys should be same as your column headers.
Delete row
-
remove_row(row: dict) -> None
- find and delete a row according to dict key and value.
Export and Import sheet
-
to_csv(out: str = '') -> None
- export the sheet to csv file.
-
to_json(out: str = '') -> None
- export the sheet to json file.
-
import_json(path: str)
- Import a json file and insert into the sheet.
Others:
-
print_row(index: int) -> str
- return a string of a row ready to be print.
-
beautify(by: str) -> List[dict]
- group data by column header.
-
set_header_style(style: Style) -> None
- set style of the header.
-
set_row_style(row: Union[dict, int], style: Style) -> None
- set style of a row.
Row Object
Example:
from excel_magic.dataset import open_file
dataset = open_file('test.xlsx')
sheet = dataset.get_sheet_by_index(0)
rows = sheet.find(Name='David') # return a list of found row object
Methods:
The row object is dict-type, with column headers as its key and cell object as the value.
So you can get the cell object of a row with row[key]
or row.get(key)
, like dict type dose.
Read cell
row[key].value
Update cell
row[key].value = new_value
Delete cell
row[key].value = ''
Cell Object
Example:
from excel_magic.dataset import open_file
dataset = open_file('test.xlsx')
sheet = dataset.get_sheet_by_index(0)
rows = sheet.find(Name='David') # return a list of found row object
cell = rows[0].get('Score') # then use value attribute to get the value of a cell
score = cell.value
Attributes:
-
value
- get the value of cell object
Methods:
-
set_style(style: Style) -> None
- passing style object, set the style of the cell
Style Object
Create the style object for cells.
Example:
from excel_magic.dataset import Style
my_style = Style()
my_style.fill_color = '#52de97'
my_style.font_size = 20
my_style.bold = True
cell.set_style(my_style)
The following attributes have been supported:
Attribute | Optional Value | Default Value |
---|---|---|
font_color | 'red' or '(255, 0, 0)' or '#FF0000' ... | 'black' |
fill_color | 'red' or '(255, 0, 0)' or '#FF0000' ... | '' |
font_name | 'Calibri' or 'Times New Roman' or 'Arial' ... | 'Calibri' |
font_size | 12 or '12' ... | 12 |
bold | True or False | False |
underline | True or False | False |
horizontal_alignment | 'left' or 'center' or 'right' | 'left' |
vertical_alignment | 'top' or 'center' or 'bottom' | 'top' |
Built With
Authors
Kelly
See also the list of contributors who participated in this project.