excelmagic2
excelmagic2
pypi i excelmagic2
excelmagic2

excelmagic2

Do magic to your excel file!

by mugglecode

2.0.10 (see all)License:MIT
pypi i excelmagic2
Readme

Excel MAGIC

Simplify common Excel operations.

Logo
PyPI version

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:

IdNameAgeScore
1John2289
2David2393
3Emma2295

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:

AttributeOptional ValueDefault 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_size12 or '12' ...12
boldTrue or FalseFalse
underlineTrue or FalseFalse
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.

VersionTagPublished
2.0.10
3yrs ago
2.0.8
3yrs ago
2.0.7
3yrs ago
2.0.6
3yrs ago
No alternatives found
No tutorials found
Add a tutorial
No dependencies found

Rate & Review

100
No reviews found
Be the first to rate