Cellbase v0.1.2
Abstraction layer for accessing spreadsheet as database, built on top of openpyxl.
Usage
Read, write or edit spreadsheet in database like environment, for example:
cellbase = Cellbase().load('simple.xlsx')
dao = SimpleDAO(cellbase) # Object inherits from DAO
entity = Simple(id=1, name='jp') # Object inherits from Entity
# Basic database operations
dao.insert(entity)
dao.query({'row_idx': entity.row_idx})
entity.name = 'imjp'
dao.update(entity)
dao.delete({'row_idx': entity.row_idx})
# Format cells' font, fill, border, etc...
dao.format({'row_idx': entity.row_idx},
fill=PatternFill(fill_type="solid", fgColor="00FFFF00"))
# Access openpyxl.cell.Cell directly
dao.traverse(lambda cell: do_something(cell),
{'row_idx': entity.row_idx}, select=['id'])
cellbase.save()Installing
Install from pypi:
pip install cellbaseFor Your Information
There are some rules/concepts being followed by Cellbase, not necessary to know but it is nice to be awared of them.
-
Cellbase = Workbook = Database
-
Celltable = Worksheet = Table
-
DAO is the helper to access data from Cellbase
-
Entity is resposible to convert data to/from dict
-
Cellbase named 'load' for reading file instead of 'open' as currently it does not open connection/stream to file, which means any changes made are not saved or updated until save/save_as is called
-
Implemetation of DAO & Entity are optional
-
'where' argument in most methods expect dict in format as below:
where = {'col_name_1', value_1, 'col_name_2': value_2}
-
'select' argument in traverse & format expect list in format as below:
select = ['col_name_1', 'col_name_2']
-
'row_idx' is the actual row index in spreadsheet
-
'row_idx' starts from 2 as 1st row is taken by header, which means:
dao.query({'row_idx', 1}) # Will raise KeyError -
Cellbase doesn't expect input values(dict) consist of 'row_idx' but values returned by query() will definitely consist 'row_idx'
-
Cellbase expect variable names declared in first row.
Empty variable will caused whole column to be ignored(column 3).
It doesn't really matter for rows, empty row as row 3 is still a valid row.
var_1 var_2 (empty) var_3 data data data data (empty) (empty) (empty) (empty) data data (empty) data
Getting Started
Cellbase is made to be easily picked up, you may start right away in python console or implement DAO & Entity to simplify the codes in your scripts.
from cellbase import Cellbase
# Without specifying filename, it will save as 'cellbase.xlsx' by default
cellbase = CellBase()
# Register the format of worksheet to deal with(only for new worksheet)
# 'Simple' is the worksheet name, while 'id' and 'name' are column names
cellbase.register({'Simple': ['id', 'name']})-
Without DAO & Entity:
row_idx = cellbase.insert('Simple', {'id': 1, 'name': 'jp'}) values = cellbase.query('Simple', {'row_idx': row_idx}) cellbase.update('Simple', {'row_idx': row_idx, 'id': 1, 'name': 'imjp'}) cellbase.delete('Simple', {'row_idx': row_idx})
-
With DAO & Entity:
First create DAO,
dao = SimpleDAO(cellbase)then do what the last example did,
except saving declaration of table name & access data from object inherits Entity
entity = Simple(id=1, name='jp') dao.insert(entity) dao.query({'row_idx': entity.row_idx}) entity.name = 'imjp' dao.update(entity) dao.delete({'row_idx': entity.row_idx})
Finally, save it to file
cellbase.save()More
Cellbase load, save, save_as, drop, register
Load from file
cellbase.load('filename.xlsx')Save to filename used in load, otherwise, current working directory as 'cellbase.xlsx'
cellbase.save()Save as another file, will raise FileExistsError if overwrite is False
cellbase.save_as('another_filename.xlsx', overwrite=True)Drop worksheet
cellbase.drop('worksheet_name')
# or drop with DAO
dao.drop()Register structure of worksheet to deal with(only required for new worksheet), otherwise, ValueError will be raised when creating worksheet as Cellbase doesn't know what are the title of worksheet and column names to create.
cellbase.register({'TABLE_NAME_1': ['COL_NAME_1', 'COL_NAME_2']})Example of DAO & Entity
DAO
from cellbase import DAO
class SimpleDAO(DAO):
# Optional, just to make life easier
TABLE_NAME = 'Simple'
COL_ID = 'id'
COL_NAME = 'name'
def worksheet_name(self):
return SimpleDAO.TABLE_NAME
def new_entity(self):
return Simple() # New instance of entity for query to return resultEntity
from cellbase import Entity
class Simple(Entity):
def __init__(self, id=0, name=""):
super().__init__() # Declare row_idx
self.id = id
self.name = name
def from_dict(self, values):
super().from_dict(values) # Inherits to handle row_idx
self.id = values[SimpleDAO.COL_ID]
self.name = values[SimpleDAO.COL_NAME]
return self
def to_dict(self):
values = super().to_dict() # Inherits to handle row_idx
values[SimpleDAO.COL_ID] = self.id
values[SimpleDAO.COL_NAME] = self.name
return valuesLambda
After getting used with Cellbase you might find that simple equality search like this is not enough:
dao.query({'id': 1, 'name': 'imjp'})For example, if you need to access all records where name contains 'jp', you might find lambda useful:
dao.query({'name': lambda value: 'jp' in value})
dao.update(entity, {'name': lambda value: 'jp' in value})
cellbase.query(worksheet_name, {'name': lambda value: 'jp' in value})
cellbase.update(worksheet_name, data, {'name': lambda value: 'jp' in value})
# So as traverse & format...or find with row_idx
dao.query({'row_idx': lambda row_idx: 3 <= row_idx <= 9})
dao.update(entity, {'row_idx': lambda row_idx: 3 <= row_idx <= 9})
cellbase.query(worksheet_name, {'row_idx': lambda row_idx: 3 <= row_idx <= 9})
cellbase.update(worksheet_name, data, {'row_idx': lambda row_idx: 3 <= row_idx <= 9})
# So as traverse & format...Magic method(Must implement DAO & Entity)
# Magic method only works with row_idx
total_row_number = len(dao) # __len__
entity = dao[row_idx] # __getitem__
dao[row_idx] = entity # __setitem__
contains = row_idx in dao # __contains__
del dao[row_idx] # __delitem
# Of course it works with lambda/callable too
entity = dao[lambda row_idx: 3 <= row_idx <= 9] # __getitem__
contains = lambda row_idx: 3 <= row_idx <= 9 in dao # __contains__
del dao[lambda row_idx: 3 <= row_idx <= 9] # __delitem
# Exception
# __setitem__ only support update, insertion will raise warning
if lambda row_idx: 3 <= row_idx <= 9 in dao:
dao[lambda row_idx: 3 <= row_idx <= 9] = entity # update
else:
dao[lambda row_idx: 3 <= row_idx <= 9] = entity # no effect at allFormatting
Other than setting value, you may format cells as well:
dao.format(where, select, fill, font, border...)
# or wrap all formats in CellFormatter
dao.format(where, select, cell_formatter)See CellFormatter, for more information.
Low Level Access
Low level might be a strong word, but you can have direct access to cells(openpyxl.cell.Cell) through traverse:
dao.traverse(lambda cell: do_something(cell), where, select)For more example, checkout Tests
License
This project is licensed under the MIT License - see the LICENSE.md file for details
