def __init__(self, in_file, sheet_name=None): """ :param str|Path in_file: can be a folder, *.xlsx or *.zip :param str sheet_name: """ if sheet_name is None: sheet_name = 'flashcards' self.modified = time() self._sheet_name = sheet_name if not isinstance(in_file, Path): in_file = Path(in_file) if in_file.exists(): if in_file.suffix != '': self.excel = in_file self.all_sheets = pyexcel.get_book_dict(file_name=str(self.excel)) self.data = self._load_raw_data(self.all_sheets, self._sheet_name) else: self.excel = in_file.joinpath(in_file.stem + '.xlsx') self.all_sheets = pyexcel.get_book_dict(str(self.excel)) self.data = self._load_raw_data(self.all_sheets, self._sheet_name) else: self.excel = in_file self.data = OrderedDict()
def test_issue_9_hidden_sheet_2(): test_file = get_fixture("hidden_sheets.xls") book_dict = pe.get_book_dict(file_name=test_file, skip_hidden_sheets=False) assert "hidden" in book_dict eq_(book_dict['shown'], [['A', 'B']]) eq_(book_dict['hidden'], [['a', 'b']])
def search(): for file in fList: try: book = p.get_book_dict(file_name=file) iterateOverCells(book, file) except KeyboardInterrupt: # print('KeyboardInterrupt exception is caught') sys.exit(0) except: print(f"Error:\tUnsupported format, password protected or corrupted file: {file}", file=sys.stderr) pass if count == True: print("Total matches: ", len(countMatches), "Cells, ", len(strMatches), "Strings") if showFileAndSheetName or filename == True: for x in Counter(matchFILES): d = Counter(matchFILES) print(str(x) + ": " + str(d[x]) + " Rows") else: pass elif files_with_match: MYset = list(set(matchFILES)) MYset.sort() [print(fx) for fx in MYset] elif files_without_match: MYset = list(set(NONmatchFILES)) MYset.sort() [print(fx) for fx in MYset]
def test_book_file(self): data = { "Category": [["id", "name"], [1, "News"], [2, "Sports"]], "Post": [ ["id", "title", "body", "pub_date", "category"], [1, "Title A", "formal", datetime(2015, 1, 20, 23, 28, 29), "News"], [2, "Title B", "informal", datetime(2015, 1, 20, 23, 28, 30), "Sports"], ], } for upload_file_type in ["xls"]: print("Uploading %s" % upload_file_type) file_name = "test.%s" % upload_file_type io = pe.save_book_as(bookdict=data, dest_file_type=upload_file_type) if not PY2: if isinstance(io, BytesIO): content = io.getvalue() else: content = io.getvalue().encode("utf-8") else: content = io.getvalue() response = self.app.post("/upload/all", upload_files=[("file", file_name, content)]) ret = pe.get_book_dict(file_type="xls", file_content=response.body) assert data["Category"] == ret["category"] sheet = pe.Sheet(data["Post"], name_columns_by_row=0) sheet.column.format("pub_date", lambda d: d.isoformat()) sheet2 = pe.Sheet(ret["post"], name_columns_by_row=0) for key in sheet.colnames: if key == "category": continue assert sheet.column[key] == sheet2.column[key] assert sheet2.column["category_id"] == [1, 2]
def excel_data(file_path): fl=pyexcel.get_book_dict(file_name=file_path) data=None for sheet in fl: if isinstance(fl[sheet], list): data=fl[sheet] return data
def test_book_file(self): data = { "Category":[ ["id", "name"], [1, "News"], [2, "Sports"] ], "Post":[ ["id", "title", "body", "pub_date", "category"], [1, "Title A", "formal", datetime(2015,1,20,23,28,29), "News"], [2, "Title B", "informal", datetime(2015,1,20,23,28,30), "Sports"] ] } for upload_file_type in ['xls']: with app.app_context(): db.drop_all() db.create_all() print("Uploading %s" % upload_file_type) file_name = "test.%s" % upload_file_type io = pe.save_book_as(bookdict=data, dest_file_type=upload_file_type) response = self.app.post('/upload/all', buffered=True, data={"file": (io, file_name)}, content_type="multipart/form-data") ret = pe.get_book_dict(file_type="xls", file_content=response.data) assert data['Category'] == ret['category'] sheet = pe.Sheet(data['Post'], name_columns_by_row=0) sheet.column.format("pub_date", lambda d: d.isoformat()) sheet2 = pe.Sheet(ret['post'], name_columns_by_row=0) for key in sheet.colnames: if key == "category": continue assert sheet.column[key] == sheet2.column[key] assert sheet2.column['category_id'] == [1, 2]
def test_save_book_to_memory_from_sql(self): test_file = pe.save_book_as(dest_file_type="xls", session=Session(), tables=[Signature, Signature2]) book_dict = pe.get_book_dict(file_content=test_file.getvalue(), file_type="xls") expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
def excel_to_json(file_path: str, sheet_name: str = None, want_sheet_names: bool = False) -> dict: """ This function reads the excel file and converts it to JSON :param file_path: :param sheet_name: :param want_sheet_names: :return: """ sheet_data = {'columnDefs': [{'headerName': "", 'field': "^", 'pinned': "left"}], 'rowData': []} column_index_map = {} result = dict() if not sheet_name or want_sheet_names: result['sheetNames'] = list() book_dict = pyexcel.get_book_dict(file_name=file_path) for sheet in book_dict.keys(): result['sheetNames'].append(sheet) if not sheet_name: sheet_name = result['sheetNames'][0] sheet = book_dict[sheet_name] else: result["sheetNames"] = None sheet = pyexcel.get_sheet(sheet_name=sheet_name, file_name=file_path) result["currSheetName"] = sheet_name for i in range(len(sheet[0])): column = get_column_letter(i+1) column_index_map[i+1] = column sheet_data['columnDefs'].append({'headerName': column_index_map[i + 1], 'field': column_index_map[i + 1]}) for row in range(len(sheet)): r = {'^': str(row + 1)} for col in range(len(sheet[row])): r[column_index_map[col+1]] = str(sheet[row][col]).strip() sheet_data['rowData'].append(r) result['sheetData'] = sheet_data return result
def _load_pyexcel_xlsx(self): updated_data = pyexcel.get_book_dict( file_name=str(self.in_file.absolute())) self.meta.setdefault('_styles', dict())['excel'] = ExcelFormatter( self.in_file).data return self._set_updated_data(updated_data)
def test_issue_9_hidden_sheet_2(self): test_file = os.path.join("tests", "fixtures", "hidden_sheets.xls") book_dict = pe.get_book_dict(file_name=test_file, skip_hidden_sheets=False) assert "hidden" in book_dict eq_(book_dict['shown'], [['A', 'B']]) eq_(book_dict['hidden'], [['a', 'b']])
def test_get_book_from_sql(self): book_dict = pe.get_book_dict(session=Session(), tables=[Signature, Signature2]) expected = OrderedDict() expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]}) expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
def test_issue_8_hidden_sheet_2(): test_file = get_fixtures("hidden_sheets.xlsx") book_dict = pe.get_book_dict(file_name=test_file, skip_hidden_sheets=False, library="pyexcel-xlsx") assert "hidden" in book_dict eq_(book_dict["shown"], [["A", "B"]]) eq_(book_dict["hidden"], [["a", "b"]])
def get_object(self, queryset=None): obj = super().get_object() obj = obj.upload try: obj = pyexcel.get_book_dict(file_type='xlsx', file_content=obj.read()) except Exception: raise Http404('Некорректный загружаемый файл') return obj
def test_get_book_dict(self): content = OrderedDict() content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) io = pe.save_book_as(dest_file_type="xls", bookdict=content) adict = pe.get_book_dict(file_content=io.getvalue(), file_type="xls") assert adict == content
def test_issue_8_hidden_sheet_2(): test_file = get_fixtures("hidden_sheets.xlsx") book_dict = pe.get_book_dict(file_name=test_file, skip_hidden_sheets=False, library="pyexcel-xlsx") assert "hidden" in book_dict eq_(book_dict['shown'], [['A', 'B']]) eq_(book_dict['hidden'], [['a', 'b']])
def test_get_book_from_sql(self): book_dict = pe.get_book_dict(session=Session(), tables=[Signature, Signature2]) expected = OrderedDict() expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]}) expected.update( {'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
def get_book_dict(self, **keywords): """Get a dictionary of two dimensional array from the file :param keywords: additional key words :returns: A dictionary of two dimensional arrays """ params = self.get_params(**keywords) return pe.get_book_dict(**params)
def test_get_book_from_sql(self): book_dict = pe.get_book_dict(session=Session(), tables=[Signature, Signature2]) expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update( {"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
def excel_load(file_path: str): import pyexcel # load workbook wb = pyexcel.get_book_dict(file_name=file_path) for name, item in wb.items(): # ensure from array to odict and filter empty values wb[name] = filteritems(arrtodict(item)) return wb
def excel_to_records(input_path: str) -> Workbook: """Reads rows to a excel file at the specified path. Args: input_path (str): The path where the excel file will be read. """ return pyexcel.get_book_dict(file_name=input_path)
def get_first_sheet_name(file_path: str): """ This function returns the first sheet name of the excel file :param file_path: :return: """ book_dict = pyexcel.get_book_dict(file_name=file_path) for sheet in book_dict.keys(): return sheet
def test_get_book_dict(self): content = OrderedDict() content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) content.update( {"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) io = pe.save_book_as(dest_file_type="xls", bookdict=content) adict = pe.get_book_dict(file_content=io.getvalue(), file_type="xls") assert adict == content
def get_list(xlsxPath, sheetname, startrow): single_dict = p.get_book_dict(file_name=xlsxPath, sheet_name=sheetname, start_row=startrow, skip_empty_rows=True) row_list = single_dict[sheetname] # for key,item in single_dict.items(): # print(json.dumps(item, ensure_ascii=False, sort_keys=False, indent=1)) return row_list
def test_force_file_type_for_save_book_as(self): pe.save_as( bookdict={"sheet1": [[1, 2]]}, dest_file_name="a.bin", dest_force_file_type="xls", ) actual = pe.get_book_dict(file_name="a.bin", force_file_type="xls") eq_({"sheet1": [[1, 2]]}, actual) os.unlink("a.bin")
def showfile(request, file_name): fs = FileSystemStorage('loanboard/files') file_path=fs.path('')+'/'+file_name fl=pyexcel.get_book_dict(file_name=file_path) data=None for sheet in fl: if isinstance(fl[sheet], list): data=fl[sheet] return render(request, 'lbfile.html', {'data':data, 'filename': file_name})
def test_save_book_as_file_from_sql(self): test_file = "book_from_sql.xls" pe.save_book_as(out_file=test_file, session=Session(), tables=[Signature, Signature2]) book_dict = pe.get_book_dict(file_name=test_file) expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected os.unlink(test_file)
def loadExcelInDict(): import pyexcel from pyexcel._compact import OrderedDict file = 'C:\\Users\\r103co62\\Desktop\\Template.xlsx' # Get your data in an ordered dictionary of lists my_dict = pyexcel.get_dict(file_name=file, name_columns_by_row=0) # Get your data in a dictionary of 2D arrays book_dict = pyexcel.get_book_dict(file_name=file) # Retrieve the records of the file records = pyexcel.get_records(file_name=file)
def test_should_convert_json_to_excel(self): with open( os.path.join(self.test_data, 'household_without_fieldset_MSI_expected.json'), 'r') as input_json_file: input_json = json.load(input_json_file, object_pairs_hook=OrderedDict) expected_book_dict = pe.get_book_dict(file_name=os.path.join( self.test_data, "household_without_fieldset_MSI.xlsx")) actual_excel_output = convert_json_to_excel(input_json) # io.BufferedRandom() # actual_excel_output.read = lambda n=0 : actual_excel_output.getvalue() # actual_excel_output.readable = lambda: True # # bufferedReader = io.BufferedReader(actual_excel_output) # self.assertIsNotNone(bufferedReader) actual_book_dict = pe.get_book_dict( file_type='xlsx', file_content=actual_excel_output) self.assertEqual(actual_book_dict, expected_book_dict)
def test_save_book_as_file_from_sql(self): test_file="book_from_sql.xls" pe.save_book_as(out_file=test_file, session=Session(), tables=[Signature, Signature2]) book_dict = pe.get_book_dict(file_name=test_file) expected = OrderedDict() expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]}) expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected os.unlink(test_file)
def test_save_book_to_memory_from_sql(self): test_file = pe.save_book_as(dest_file_type="xls", session=Session(), tables=[Signature, Signature2]) book_dict = pe.get_book_dict(file_content=test_file.getvalue(), file_type="xls") expected = OrderedDict() expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]}) expected.update( {'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
def test_save_book_to_memory_from_sql(self): test_file = pe.save_book_as(dest_file_type="xls", session=Session(), tables=[Signature, Signature2]) book_dict = pe.get_book_dict( file_content=test_file.getvalue(), file_type="xls" ) expected = OrderedDict() expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]}) expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
def test_save_book_as_file_from_sql(self): test_file = "book_from_sql.xls" pe.save_book_as(dest_file_name=test_file, session=Session(), tables=[Signature, Signature2]) book_dict = pe.get_book_dict(file_name=test_file) expected = OrderedDict() expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]}) expected.update( {'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected os.unlink(test_file)
def read_excel_sheets(sheet_name=None, file_url=None, all_sheets=False): #if True: try: book_dict = pyexcel.get_book_dict(file_name=file_url) if all_sheets: dict_all_sheets = {} for name_sheet in book_dict: records = book_dict[name_sheet] if records: header = records.pop(0) else: header = [] try: header = [ str(col).lower().replace(u'\xa0', u' ').strip().replace( ' ', '_') for col in header ] except UnicodeEncodeError: header = [ col.lower().replace(u'\xa0', u' ').strip().replace(' ', '_') for col in header ] dict_all_sheets[name_sheet.lower().replace(' ', '_')] = { 'header': header, 'records': records } return dict_all_sheets if book_dict.get(sheet_name): records = book_dict[sheet_name] header = records.pop(0) try: header = [ str(col).lower().replace(u'\xa0', u' ').strip().replace(' ', '_') for col in header ] except UnicodeEncodeError: header = [ col.lower().replace(u'\xa0', u' ').strip().replace(' ', '_') for col in header ] return header, records except Exception as e: print( json.dumps({ 'error': 'Ocurrio un error al leer el documento Excel', 'msg': str(e) }))
def test_save_book_to_memory_from_sql(self): test_file = pe.save_book_as( dest_file_type="xls", session=Session(), tables=[Signature, Signature2], ) book_dict = pe.get_book_dict(file_content=test_file.getvalue(), file_type="xls") expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update( {"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
def test_save(in_file, config, request): if isinstance(config, str): config = Path("tests/input").joinpath(config) assert config.exists() config = str(config) data = pyexcel.get_book_dict(file_name=str(Path("tests/input").joinpath(in_file))) pyexcel_xlsxwx.save_data( str(Path("tests/output").joinpath(request.node.name).with_suffix(".xlsx")), data, config=config, )
def add_annul_propusk_to_db(file, request): print('add_annul_propusk_to_db') our_annul_propuska = [] annul = pyexcel.get_book_dict(file_name='workstation/static/fileupload/' + file.name) step = 0 print('Взяли файл') print(len(annul['Sheet'])) for row in annul['Sheet']: print("Проверенно {} строк из {}".format(step, len(annul['Sheet']))) if row[1] != '': car = obj_search_in_db_car(what=row[1]) if car: # Если машина есть if car.is_our: propusk = {'grz': row[1], 'date_from': row[2], 'date_to': row[3], 'status': row[4], 'proverka': datetime.today().strftime("%Y-%m-%d")} print('Машина наша') our_annul_propuska.append(propusk) prop = Propusk.objects.filter(car=car) if not prop: print("Нет пропуска на " + car.grz) date_from = datetime.strptime(row[2], "%d.%m.%Y").date().strftime("%Y-%m-%d") date_to = datetime.strptime(row[3], "%d.%m.%Y").date().strftime("%Y-%m-%d") propusk = Propusk(car=car, date_from=date_from, date_to=date_to, status=row[4]) propusk.save() else: #Если машина есть но не наша print('Машина не наша') date_from = datetime.strptime(row[2], "%d.%m.%Y").date().strftime("%Y-%m-%d") date_to = datetime.strptime(row[3], "%d.%m.%Y").date().strftime("%Y-%m-%d") propusk = Propusk(car=car, date_from=date_from, date_to=date_to, status=row[4]) propusk.save() else: # Если машины нет car = Car(user_id=request.user, grz=row[1], is_our=False) car.save() date_from = datetime.strptime(row[2], "%d.%m.%Y").date().strftime("%Y-%m-%d") date_to = datetime.strptime(row[3], "%d.%m.%Y").date().strftime("%Y-%m-%d") propusk = Propusk(car=car, date_from=date_from, date_to=date_to, status=row[4]) propusk.save() else: # Если пустая ячейка print('пустая ячейка') step += 1 print("Записанно в propuska {}".format(len(our_annul_propuska))) save_to_file_annul(our_annul_propuska) return our_annul_propuska
def excel_to_json(file_path: str, sheet_name: str = None) -> str: """ This function reads the excel file and converts it to JSON :param file_path: :param sheet_name: :return: """ book_dict = pyexcel.get_book_dict(file_name=file_path) sheet_data = { 'columnDefs': [{ 'headerName': "", 'field': "^", 'pinned': "left" }], 'rowData': [] } column_index_map = {} file_path = file_path.lower() is_first_excel = ( False, True)[(file_path.endswith(".xls") or file_path.endswith(".xlsx")) and (sheet_name == None)] result = dict() if not sheet_name: result['sheetNames'] = list() for sheet in book_dict.keys(): result['sheetNames'].append(sheet) sheet_name = result['sheetNames'][0] sheet = book_dict[sheet_name] for i in range(len(sheet[0])): column = get_column_letter(i + 1) column_index_map[i + 1] = column sheet_data['columnDefs'].append({ 'headerName': column_index_map[i + 1], 'field': column_index_map[i + 1] }) for row in range(len(sheet)): r = {'^': str(row + 1)} for col in range(len(sheet[row])): r[column_index_map[col + 1]] = str(sheet[row][col]).strip() sheet_data['rowData'].append(r) if is_first_excel: result['sheetData'] = dict() result['sheetData'][sheet_name] = sheet_data return result else: return sheet_data
def test_save_book_as_file_from_sql(self): test_file = "book_from_sql.xls" pe.save_book_as( dest_file_name=test_file, session=Session(), tables=[Signature, Signature2], ) book_dict = pe.get_book_dict(file_name=test_file) expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update( {"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected os.unlink(test_file)
def main(mdd_path): book = pyexcel.get_book_dict(file_name=os.fspath(mdd_path), skip_empty_rows=True) # Encontrar para cada sheetname un fichero llamado FECHA_sheetname.shp coincidiendo mayúsculas, minúsculas y todo. Pensar si tiene sentido ignorar caracteres especiales. # No estaría de más poder sacar un listado de todos los .shp en el root_path por esto de comparar con los sheetnames # Chequear los nombres de campos y tipos del mdd frente a los de las capas # Generar tablas a partir del mdd/capas for b in book: if b in ['Fontes', 'Barragens', 'Loc_Prov', 'Loc_Dis', 'Loc_PosAdm']: continue # Convertir a rutas absolutas sin necesidad de iniciar la búsqueda con una ruta relativa tablename = b.lower() shp_path = find_all_by_pattern('*' + b + '.shp', './08_BD_Unica/01_BD_Cartografia_Base')[0] compare_fields(book, b, shp_path) encoding = calculate_encoding.main(shp_path) command='shp2pgsql -p -I -s 32737 -g geom -W {encoding} -N abort {shp_path} cbase.{tablename} >> /tmp/foo.sql'.format(tablename=tablename, shp_path=shp_path, encoding=encoding) os.system(command) command='shp2pgsql -a -D -s 32737 -g geom -W {encoding} -N abort {shp_path} cbase.{tablename} >> /tmp/foo_data.sql'.format(tablename=tablename, shp_path=shp_path, encoding=encoding) os.system(command) rewrite_shp2pgsql_ddl_output('/tmp/foo.sql') rewrite_shp2pgsql_data_output('/tmp/foo_data.sql')
import pyexcel import scipy.optimize as opt import numpy as np import xlsxwriter cafe_average = {'domestic':60, 'Asian': 100, 'European':70} #book now contains the entire excel workbook dict = pyexcel.get_book_dict(file_name="Input_Data.xls") tgroup = np.asarray(dict['tGROUP']) fgroup = np.asarray(dict['fGROUP']) bgroup = np.asarray(dict['bGROUP']) scenario = np.asarray(dict['Scenario']) book = pyexcel.get_book(file_name="Input_Data.xls") startYear = 2015 endYear = 2017 num_of_iterations = 50 gamma = 0.1 #order by row 0 temp = book['Scenario_2'] temp.name_columns_by_row(0) scenario_rec = temp.to_records() def calculate_shares(scenario_rec): #s0 = sum of each sj s0 = 0.0 for eachrec in scenario_rec: s0+=eachrec['sj']
TempNewNamePath = os.path.join(dstroot,tempName) os.rename(TempOldNamePath,TempNewNamePath) #pathstring="C:\Python2.7.10\project_database\excel_database" pathstring='C:\temp_read_Excel' filepath_data=unicode(pathinfile) print filepath_data, "database path" FileNamestring=name print dstroot,tempName, "read" os.chdir(dstroot) print os.getcwd() while True: try: book_dict = pyexcel.get_book_dict(file_name=tempName, path=dstroot) break except ValueError: os.remove(TempNewNamePath) print "Oops! That was no valid number. Try again..." #book_dict = pyexcel.get_book_dict(file_name=tempName, path=dstroot) #isinstance(book_dict, OrderedDict) con = lite.connect('database.db') with con: cur=con.cursor() sheetnum=0 for key, val in book_dict.items(): #sheetName_ID sheetnum+=1 print(key) sql_datacheck="SELECT * FROM excel_file WHERE PATH=? AND FileName=? AND File_SheetName=?"
def test_get_book_dict(self): pe.get_book_dict(x="something")
def test_get_book_dict(self): content = _produce_ordered_dict() io = pe.save_book_as(dest_file_type="xls", bookdict=content) adict = pe.get_book_dict(file_content=io.getvalue(), file_type="xls") assert adict == content
def test_issue_9_hidden_sheet(): test_file = get_fixture("hidden_sheets.xls") book_dict = pe.get_book_dict(file_name=test_file) assert "hidden" not in book_dict eq_(book_dict['shown'], [['A', 'B']])
def WriteInDatabase(pathinfile,name,tempName): dstroot ='C:\\temp_read_Excel' pathstring='C:\temp_read_Excel' filepath_data=unicode(pathinfile) FileNamestring=name.decode("BIG5") TempNewNamePath = os.path.join(dstroot,tempName) os.chdir(dstroot) print os.getcwd() while True: try: book_dict = pyexcel.get_book_dict(file_name=tempName, path=dstroot) break except ValueError: os.remove(TempNewNamePath) print "Oops! That was no valid number. Try again..." #book_dict = pyexcel.get_book_dict(file_name=tempName, path=dstroot) #isinstance(book_dict, OrderedDict) con = lite.connect('database.db') with con: cur=con.cursor() sheetnum=0 for key, val in book_dict.items(): #sheetName_ID sheetnum+=1 sql_datacheck="SELECT * FROM excel_file WHERE PATH=? AND FileName=? AND File_SheetName=?" sql_pathnamesheet="INSERT INTO excel_file(PATH,FileName,File_SheetName,File_Datetime) VALUES(?,?,?,?)" now = datetime.datetime.now() PathNameSheet_para=[filepath_data,FileNamestring,key,now] fileCheck_Para=[filepath_data,FileNamestring,key] for checker in con.execute(sql_datacheck, fileCheck_Para): rownum=0 for valrow in val: rownum+=1 colnum=0 for valcol in valrow: colnum+=1 if valcol!='': sql_check="SELECT * FROM excel_content WHERE Sheet_Name=? and Row_Number=? and Col_Number =?" sql_update="UPDATE excel_content SET Cell_Data=? WHERE Sheet_Name=? and Row_Number=? and Col_Number =?" sql_insert="INSERT INTO excel_content(Sheet_Name,Row_Number,Col_Number,Cell_Data) VALUES(?,?,?,?)" content_check_para=[checker[0],rownum,colnum] insert_para=[checker[0],rownum,colnum,valcol] update_para=[valcol,checker[0],rownum,colnum] #cell exist for celldataval in con.execute(sql_check, content_check_para): con.execute(sql_update, update_para) break else: con.execute(sql_insert, insert_para) #os.remove(TempNewNamePath) break else: con.execute(sql_pathnamesheet, PathNameSheet_para) for thisID in con.execute(sql_datacheck, fileCheck_Para): rownum=0 for valrow in val: rownum+=1 colnum=0 for valcol in valrow: colnum+=1 if valcol!='': sql_check="SELECT * from excel_content" sql_insert="INSERT INTO excel_content(Sheet_Name,Row_Number,Col_Number,Cell_Data) VALUES(?,?,?,?)" insert_para=[thisID[0],rownum,colnum,valcol] con.execute(sql_insert, insert_para)
def test_get_book_from_sql(self): book_dict = pe.get_book_dict(session=Session(), tables=[Signature, Signature2]) expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
def test_get_book_dict(self): expected = pe.get_book_dict(x="something") assert expected == None