def __bypass_for_spec_col(self, sheet: Sheet, row_num: int) -> None:
"""
A private function that is executed when you specify a dictionary
of certain fields.
:param sheet: Book
:param row_num: Number row
:return:
"""
_ = sheet.row_values(row_num, self._comparison_col,
self._comparison_col + 1)[0]
if self.out_data.get(_):
if self._info_name:
print(_)
self._number_count += 1
return
self.out_data[_] = {}
for k, v in self._dict_col.items():
value = sheet.row_values(row_num, v, v + 1)[0]
self.out_data[_][k] = value
if self._dict_col_category:
self.__get_category(_, sheet, row_num)
def __bypass_default(self, sheet: Sheet, row_num: int) -> None:
"""
Private function, executed without specifying a dictionary of
certain fields.
:param sheet: Book
:param row_num: Number row
:return:
"""
_ = self.out_data.get(
sheet.row_values(row_num, self._comparison_col,
self._comparison_col + 1))[0]
if _:
if self._info_name:
print(_)
self._number_count += 1
return
self.out_data[_] = {}
values = sheet.row_values(row_num)
num = 0
for val in values:
self.out_data[_][num] = val
num += 1
def process_row(self, row_index:int, sheet:Sheet):
values = sheet.row_values(row_index)
values = self.fix_floats(values)
if values == [''] * len(values):
self.add_table()
self.state_process()
else:
self.current_table.add_row(values)
def Get_Excel_Row_Values(filepath,sheetName,uniqueValue):
Book = xlrd.open_workbook(filepath)
Sheet = Book.sheet_by_name(sheetName)
row_count = Sheet.nrows
col_count = Sheet.ncols
for i in range(0,row_count):
for j in range(0,col_count):
value = Sheet.cell_value(i, j)
if value == uniqueValue:
row_values = Sheet.row_values(i, 0)
return row_values
def __get_category(self, key: str, sheet: Sheet, row_num: int) -> None:
"""
The private function is executed if the dictionary of certain category
fields is specified.
:param key: Key record
:param sheet: Book
:param row_num: Number row
:return:
"""
if self._join:
cat = ''
for v in self._dict_col_category.values():
_ = sheet.row_values(row_num, v, v + 1)[0]
if cat:
cat += f'{self._delimiter}{_}'
else:
cat = _
self.out_data[key]['category'] = cat
else:
for k, v in self._dict_col_category:
value = sheet.row_values(row_num, v, v + 1)[0]
self.out_data[key][k] = value
def __init__(self, sheet: Sheet, keys: Iterable[str] = ..., key_row=0):
self.conn = sqlite3.connect(':memory:')
self.cur = self.conn.cursor()
self.where = None
self.orders = {}
# create table
stm = []
col_keys = []
for i in (self.ARGS if keys is ... else keys):
key, ktype, col = i.split()
stm.append('{} {}'.format(key, ktype))
col_keys.append((int(col), key))
stm = 'CREATE TABLE TEMP({});'.format(','.join(stm))
self.cur.execute(stm)
# add records
klist = ','.join([i[1] for i in col_keys])
stm = 'INSERT INTO TEMP({}) VALUES({})'
for i in range(key_row + 1, sheet.nrows):
row = sheet.row_values(i)
if (len(row) > 1) and (not row[1]):
continue
vlist = ','.join([repr(row[j[0]]) for j in col_keys])
self.cur.execute(stm.format(klist, vlist))
self.conn.commit()
# add extra order
if keys is not ...:
return
order = ['肉', '菜', '油料干货', '调料制品', '杂货类']
self.add_order('kind', order)
order = ['营养餐', '非营养餐', '幼儿餐', '教师餐']
self.add_order('meal', order)
cur = self.select('DISTINCT NAME, KIND')
order = self.orders['kind']
names = [(order.get(k, len(order)), n) for n, k in cur]
names.sort()
self.add_order('name', [i[1] for i in names])
def process_cols(self, row_index:int, sheet:Sheet):
self.current_table.cols = sheet.row_values(row_index)