/excel-magic

Do magic to your excel file!

Primary LanguagePythonMIT LicenseMIT

Excel MAGIC

Do magic to your excel file!

Use Case

CRUD using Dataset

let's say we have a file like this:

id name age
1 John Doe 12
2 Kelly 18 
from excel_magic.dataset import Dataset
ds = Dataset('a_file.xlsx')
table = ds.get_sheet(0)
search_results = table.find(name='John Doe')
table.remove(search_results[0])
search_results[1]['name'] = 'Vicky'
# we can leave age empty if we do it like this!
table.append({'id': '3', 'name': 'Dick'})
# we can use filter if we have even more complex conditions
filter_results = table.filter(lambda row: row['age'] is '')
# don't forget to save!
ds.save()

API Reference

dataset Module

Style

represents the style of a cell

attribute description default
horizontal_alignment how text align in a cell left
vertical_alignment how text align vertically in a cell top
bold is text bold False
underline is text underlined False
font_color color of the font black
font_name name of the font Calibri
font_size font size 12
fill_color fill color ''

Cell

  • set_style(style: Style)

    • se style of the cell

Dataset

  • get_sheet(index: int)

    • get sheet by index

  • get_sheet_by_name(name: str)

    • get sheet by name

  • does_exist(name: str)

    • check if a sheet exists

  • merge_file(path: str)

    • merge another file to the current file

  • create_sheet_by_json(name: str, data: str) -> Sheet

    • Create a sheet by json file

  • import_json(path: str)

    • import multiple sheets from a json file

  • export_json(out: str)

    • export all sheets to a json file

  • remove_sheet(sheet: Sheet)

    • remove a sheet

  • save()

    • save your stuff

Sheet

  • find(**kwargs)

    • find a list of rows

  • filter(callback)

    • return a list of rows, filter by the callback function. return True if you want it

  • append_row(content: Union[dict, List[str]])

    • add a row to your file, dict keys should be your headers

  • remove(row: dict)

    • remove a row

  • set_header_style(style: Style)

    • set style of the header

  • get_col(col: str)

    • get rows of a column

  • get_rows() -> List[dict]

    • get all rows

  • print_row(index: int) -> str

    • return a string of a row ready to be print

  • set_row_style(row: Union[dict, int], style: Style)

    • set style of a row

  • to_csv(out: str = '')

    • Convert sheet to csv

  • to_json(out: str = '')

    • Convert sheet to json

  • import_json(path: str)

    • Import data from a json file

  • group_by(by: str) -> List[dict]

    • Group data by a column

utils Module

Document

  • split_sheets(out='', out_prefix='')

    • split file by sheets

  • split_rows(row_count: int, out='', out_prefix='')

    • split file by rows