def load(file_name, sheetname=None, **keywords):
"""Constructs an instance :class:`Sheet` from a sheet of an excel file
except csv, most excel files has more than one sheet.
Hence sheetname is required here to indicate from which sheet the instance
should be constructed. If this parameter is omitted, the first sheet, which
is indexed at 0, is used. For csv, sheetname is always omitted because csv
file contains always one sheet.
:param str sheetname: which sheet to be used for construction
:param int name_colmns_by_row: which row to give column names
:param int name_rows_by_column: which column to give row names
:param dict keywords: other parameters
"""
if isinstance(file_name, tuple):
sheet = get_sheet(
file_type=file_name[0],
file_content=file_name[1],
sheet_name=sheetname,
**keywords
)
else:
sheet = get_sheet(
file_name=file_name, sheet_name=sheetname, **keywords
)
return sheet
def merge_files(file_array, outfilename=DEFAULT_OUT_FILE):
"""merge many files horizontally column after column
:param str outfilename: save the sheet as
"""
if os.path.exists(outfilename):
raise NotImplementedError(MESSAGE_WARNING)
content = []
for file_name in file_array:
sheet = get_sheet(file_name=file_name)
content.extend(list(sheet.columns()))
merged_sheet = get_sheet(array=content)
merged_sheet.transpose()
merged_sheet.save_as(outfilename)
return outfilename
def merge_files(file_array, outfilename=DEFAULT_OUT_FILE):
"""merge many files horizontally column after column
:param str outfilename: save the sheet as
"""
if os.path.exists(outfilename):
raise NotImplementedError(MESSAGE_WARNING)
content = []
for f in file_array:
sheet = get_sheet(file_name=f)
content.extend(list(sheet.columns()))
merged_sheet = get_sheet(array=content)
merged_sheet.transpose()
merged_sheet.save_as(outfilename)
return outfilename
def load_from_dict(the_dict, with_keys=True, **keywords):
"""Return a sheet from a dictionary of one dimensional arrays
:param dict the_dict: its value should be one dimensional array
:param bool with_keys: indicate if dictionary keys should be
appended or not
"""
return get_sheet(adict=the_dict, with_keys=with_keys, **keywords)
def load_from_dict(the_dict, with_keys=True, **keywords):
"""Return a sheet from a dictionary of one dimensional arrays
:param dict the_dict: its value should be one dimensional array
:param bool with_keys: indicate if dictionary keys should be
appended or not
"""
return get_sheet(adict=the_dict, with_keys=with_keys, **keywords)
def Reader(file_name=None, sheetname=None, **keywords):
"""
A single sheet excel file reader
Default is the sheet at index 0. Or you specify one using sheet index
or sheet name. The short coming of this reader is: column filter is
applied first then row filter is applied next
use as class would fail though
changed since 0.0.7
"""
if isinstance(file_name, tuple):
return get_sheet(file_type=file_name[0],
file_content=file_name[1],
sheet_name=sheetname,
**keywords)
else:
return get_sheet(file_name=file_name, sheet_name=sheetname, **keywords)
def load_from_records(records, **keywords):
"""Return a sheet from a list of records
Sheet.to_records() would produce a list of dictionaries. All dictionaries
share the same keys.
:params list records: records are likely to be produced by
Sheet.to_records() method.
"""
return get_sheet(records=records, **keywords)
def merge_csv_to_a_book(filelist, outfilename):
merged = Book()
for file_name in filelist:
sheet = get_sheet(file_name=file_name, delimiter=';')
_, tail = os.path.split(
file_name.replace("result_", '').replace('.csv', ''))
sheet.name = tail
merged += sheet
merged.save_as(outfilename)
def load_from_records(records, **keywords):
"""Return a sheet from a list of records
Sheet.to_records() would produce a list of dictionaries. All dictionaries
share the same keys.
:params list records: records are likely to be produced by
Sheet.to_records() method.
"""
return get_sheet(records=records, **keywords)
def Reader(file_name=None, sheetname=None, **keywords):
"""
A single sheet excel file reader
Default is the sheet at index 0. Or you specify one using sheet index
or sheet name. The short coming of this reader is: column filter is
applied first then row filter is applied next
use as class would fail though
changed since 0.0.7
"""
if isinstance(file_name, tuple):
return get_sheet(file_type=file_name[0],
file_content=file_name[1],
sheet_name=sheetname,
**keywords)
else:
return get_sheet(file_name=file_name, sheet_name=sheetname,
**keywords)
def load(file_name, sheetname=None, **keywords):
"""Constructs an instance :class:`Sheet` from a sheet of an excel file
except csv, most excel files has more than one sheet.
Hence sheetname is required here to indicate from which sheet the instance
should be constructed. If this parameter is omitted, the first sheet, which
is indexed at 0, is used. For csv, sheetname is always omitted because csv
file contains always one sheet.
:param str sheetname: which sheet to be used for construction
:param int name_colmns_by_row: which row to give column names
:param int name_rows_by_column: which column to give row names
:param dict keywords: other parameters
"""
if isinstance(file_name, tuple):
sheet = get_sheet(file_type=file_name[0],
file_content=file_name[1],
sheet_name=sheetname,
**keywords)
else:
sheet = get_sheet(file_name=file_name, sheet_name=sheetname,
**keywords)
return sheet
def merge_csv_to_a_book(filelist, outfilename=DEFAULT_OUT_XLS_FILE):
"""merge a list of csv files into a excel book
:param list filelist: a list of accessible file path
:param str outfilename: save the sheet as
"""
merged = Book()
for file_name in filelist:
sheet = get_sheet(file_name=file_name)
_, tail = os.path.split(file_name)
sheet.name = tail
merged += sheet
merged.save_as(outfilename)
def merge_csv_to_a_book(filelist, outfilename=DEFAULT_OUT_XLS_FILE):
"""merge a list of csv files into a excel book
:param list filelist: a list of accessible file path
:param str outfilename: save the sheet as
"""
merged = Book()
for file_name in filelist:
sheet = get_sheet(file_name=file_name)
head, tail = os.path.split(file_name)
sheet.name = tail
merged += sheet
merged.save_as(outfilename)
def load_from_memory(file_type, file_content, sheetname=None, **keywords):
"""Constructs an instance :class:`Sheet` from memory
:param str file_type: one value of these: 'csv', 'tsv', 'csvz',
'tsvz', 'xls', 'xlsm', 'xslm', 'ods'
:param iostream file_content: file content
:param str sheetname: which sheet to be used for construction
:param dict keywords: any other parameters
"""
return get_sheet(file_type=file_type,
file_content=file_content,
sheet_name=sheetname,
**keywords)
def load_from_memory(file_type,
file_content,
sheetname=None,
**keywords):
"""Constructs an instance :class:`Sheet` from memory
:param str file_type: one value of these: 'csv', 'tsv', 'csvz',
'tsvz', 'xls', 'xlsm', 'xslm', 'ods'
:param iostream file_content: file content
:param str sheetname: which sheet to be used for construction
:param dict keywords: any other parameters
"""
return get_sheet(file_type=file_type,
file_content=file_content,
sheet_name=sheetname,
**keywords)
def ColumnSeriesReader(file_name=None, sheetname=None, series=0, **keywords):
"""A single sheet excel file reader and it has row headers in a selected column
use as class would fail
changed since 0.0.7
"""
if isinstance(file_name, tuple):
return get_sheet(file_type=file_name[0],
file_content=file_name[1],
name_rows_by_column=series,
**keywords)
else:
return load(file_name,
sheetname=sheetname,
name_rows_by_column=series,
**keywords)
def ColumnSeriesReader(file_name=None, sheetname=None, series=0, **keywords):
"""A single sheet excel file reader and it has row headers in a selected column
use as class would fail
changed since 0.0.7
"""
if isinstance(file_name, tuple):
return get_sheet(file_type=file_name[0],
file_content=file_name[1],
name_rows_by_column=series,
**keywords)
else:
return load(file_name,
sheetname=sheetname,
name_rows_by_column=series,
**keywords)
def update_rows(infilename, row_dicts, outfilename=None):
"""Update one or more rows of a data file with series
datastucture: key should an integer of the row to be updated
value should be an array of the data
:param str infilename: an accessible file name
:param dict row_dicts: dictionaries of rows
:param str outfilename: save the sheet as
"""
default_out_file = OUT_FILE_FORMATTER % infilename
if outfilename:
default_out_file = outfilename
if os.path.exists(default_out_file):
raise NotImplementedError(MESSAGE_WARNING)
sheet = get_sheet(file_name=infilename, name_rows_by_column=0)
series = sheet.rownames
for k in row_dicts.keys():
index = series.index(str(k))
sheet.set_row_at(index, row_dicts[k])
sheet.save_as(default_out_file)
def update_rows(infilename, row_dicts, outfilename=None):
"""Update one or more rows of a data file with series
datastucture: key should an integer of the row to be updated
value should be an array of the data
:param str infilename: an accessible file name
:param dict row_dicts: dictionaries of rows
:param str outfilename: save the sheet as
"""
default_out_file = OUT_FILE_FORMATTER % infilename
if outfilename:
default_out_file = outfilename
if os.path.exists(default_out_file):
raise NotImplementedError(MESSAGE_WARNING)
sheet = get_sheet(file_name=infilename, name_rows_by_column=0)
series = sheet.rownames
for k in row_dicts.keys():
index = series.index(str(k))
sheet.set_row_at(index, row_dicts[k])
sheet.save_as(default_out_file)
def update_columns(infilename, column_dicts, outfilename=None):
"""Update one or more columns of a data file with series
The data structure of column_dicts should be:
key should be first row of the column
the rest of the value should an array
:param str infilename: an accessible file name
:param dict column_dicts: dictionaries of columns
:param str outfilename: save the sheet as
"""
default_out_file = OUT_FILE_FORMATTER % infilename
if outfilename:
default_out_file = outfilename
if os.path.exists(default_out_file):
raise NotImplementedError(MESSAGE_WARNING)
sheet = get_sheet(file_name=infilename, name_columns_by_row=0)
series = sheet.colnames
for k in column_dicts.keys():
index = series.index(str(k))
sheet.set_column_at(index, column_dicts[k])
sheet.save_as(default_out_file)
def update_columns(infilename, column_dicts, outfilename=None):
"""Update one or more columns of a data file with series
The data structure of column_dicts should be:
key should be first row of the column
the rest of the value should an array
:param str infilename: an accessible file name
:param dict column_dicts: dictionaries of columns
:param str outfilename: save the sheet as
"""
default_out_file = OUT_FILE_FORMATTER % infilename
if outfilename:
default_out_file = outfilename
if os.path.exists(default_out_file):
raise NotImplementedError(MESSAGE_WARNING)
sheet = get_sheet(file_name=infilename, name_columns_by_row=0)
series = sheet.colnames
for k in column_dicts.keys():
index = series.index(str(k))
sheet.set_column_at(index, column_dicts[k])
sheet.save_as(default_out_file)