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

• xlrd
• xlsxwriter

Authors

Kelly

See also the list of contributors who participated in this project.