def create_merged_state_spreadsheet(state): state_data = merge_state_data(state) reports = rows.import_from_dicts(state_data["reports"]) cases = rows.import_from_dicts(state_data["cases"]) data = io.BytesIO() rows.export_to_xlsx(reports, data, sheet_name=google_data.BOLETIM_SPREADSHEET) data.seek(0) rows.export_to_xlsx(cases, data, sheet_name=google_data.CASOS_SPREADSHEET) data.seek(0) return data
def test_issue_168(self): temp = tempfile.NamedTemporaryFile(delete=False) filename = "{}.{}".format(temp.name, self.file_extension) self.files_to_delete.append(filename) table = rows.Table(fields=OrderedDict([("jsoncolumn", rows.fields.JSONField)])) table.append({"jsoncolumn": '{"python": 42}'}) rows.export_to_xlsx(table, filename) table2 = rows.import_from_xlsx(filename) self.assert_table_equal(table, table2)
def test_export_to_xlsx_fobj(self): temp = tempfile.NamedTemporaryFile() filename = temp.name + '.xlsx' temp.close() fobj = open(filename, 'wb') self.files_to_delete.append(filename) rows.export_to_xlsx(utils.table, fobj) fobj.close() table = rows.import_from_xlsx(filename) self.assert_table_equal(table, utils.table)
def test_issue_168(self): temp = tempfile.NamedTemporaryFile(delete=False) filename = '{}.{}'.format(temp.name, self.file_extension) self.files_to_delete.append(filename) table = rows.Table(fields= OrderedDict([('jsoncolumn', rows.fields.JSONField)])) table.append({'jsoncolumn': '{"python": 42}'}) rows.export_to_xlsx(table, filename) table2 = rows.import_from_xlsx(filename) self.assert_table_equal(table, table2)
def test_export_to_xlsx_filename(self): temp = tempfile.NamedTemporaryFile() filename = temp.name + '.xlsx' temp.close() self.files_to_delete.append(filename) rows.export_to_xlsx(utils.table, filename) table = rows.import_from_xlsx(filename) self.assert_table_equal(table, utils.table) export_in_memory = rows.export_to_xlsx(utils.table, None) result_fobj = BytesIO() result_fobj.write(export_in_memory) result_fobj.seek(0) result_table = rows.import_from_xlsx(result_fobj) self.assert_table_equal(result_table, utils.table)
def test_export_to_xlsx_uses_prepare_to_export(self, mocked_prepare_to_export): temp = tempfile.NamedTemporaryFile() filename = temp.name + ".xlsx" temp.file.close() self.files_to_delete.append(filename) kwargs = {"test": 123, "parameter": 3.14} mocked_prepare_to_export.return_value = iter([utils.table.fields.keys()]) rows.export_to_xlsx(utils.table, temp.name, **kwargs) self.assertTrue(mocked_prepare_to_export.called) self.assertEqual(mocked_prepare_to_export.call_count, 1) call = mocked_prepare_to_export.call_args self.assertEqual(call[0], (utils.table,)) self.assertEqual(call[1], kwargs)
def test_export_to_xlsx_uses_prepare_to_export(self, mocked_prepare_to_export): temp = tempfile.NamedTemporaryFile() filename = temp.name + ".xlsx" temp.file.close() self.files_to_delete.append(filename) kwargs = {"test": 123, "parameter": 3.14} mocked_prepare_to_export.return_value = iter( [utils.table.fields.keys()]) rows.export_to_xlsx(utils.table, temp.name, **kwargs) self.assertTrue(mocked_prepare_to_export.called) self.assertEqual(mocked_prepare_to_export.call_count, 1) call = mocked_prepare_to_export.call_args self.assertEqual(call[0], (utils.table, )) self.assertEqual(call[1], kwargs)
for item in itens_dados: match = pattern.match(str(item)) if match: row_ver[fields.slug(match.group(1))] = parse_data(match.group(3)) row_ver["frentes"] = URL_FRENTES.format(id=id_ver) row_ver["votacoes"] = URL_VOTACOES.format(id=id_ver) row_ver["mandatos"] = URL_MANDATOS.format(id=id_ver) def pegar_dados_do_brasilio(cpf_ver, row_ver): try: data = api.dataset_table_data("documentos-brasil", 'documents', document_type='CPF', document=cpf_ver) result = list(data) row_ver['docs_brasilio'] = result[0]['sources'] except: row_ver['docs_brasilio'] = '' data = api.dataset_table_data("socios-brasil", 'socios', search="\"{}\"".format(row_ver['nome_civil'].strip())) result = list(data) row_ver['socios_brasilio'] = ' /// '.join([r['cnpj'] + ' ' + r['razao_social'] + ' (' + r['qualificacao_socio'] + ')' for r in result]) for id_ver, cpf_ver in map(lambda i: i.strip().split('\t'), IDS_CPFS): row_ver = {} pegar_dados_da_camara_municipal(id_ver, row_ver) pegar_dados_do_brasilio(cpf_ver, row_ver) vereadores.append(row_ver) export_to_xlsx(vereadores, "vereadores.xlsx")
for day in days_brasil: if day['city'] == 'Joinville' and day['is_last'] == True: day['date'] = rows_dados[0].data_iso day['last_available_confirmed'] = rows_dados[0].confirmados day['last_available_confirmed_per_100k_inhabitants'] = rows_dados[0].conf_por_100k day['new_confirmed'] = rows_dados[0].confirmados - rows_dados[1].confirmados day['last_available_deaths'] = rows_dados[0].obitos day['new_deaths'] = rows_dados[0].obitos - rows_dados[1].obitos day['last_available_death_rate'] = rows_dados[0].taxa_conf_obito break rows_brasil = rows.import_from_dicts(days_brasil) #%% Exportando os dados das cidades brasileiras para .CSV e .XLSX rows.export_to_csv(rows_brasil, "covid_brasil.csv") rows.export_to_xlsx(rows_brasil, "covid_brasil.xlsx") #%% Exportando os dados sobre joinville para .CSV e .XLSX rows.export_to_csv(rows_dados, "covid_joinville.csv") rows.export_to_xlsx(rows_dados, "covid_joinville.xlsx") #%% Exportando os dados sobre ocupacao dos leitos para .CSV e .XLSX rows.export_to_csv(rows_leitos, "leitos_uti_enfermaria.csv") rows.export_to_xlsx(rows_leitos, "leitos_uti_enfermaria.xlsx")
def teladduser(file, time_sleep): """ Log in on a Telegram account and add a users in a Supergroup from a SpreadSheet which the account logged in is admin. """ # Verify if the Excel SpreadSheet was give! if not file: print('Need to pass the Excel SpreadSheet Filename!\n') click.Context(teladduser).exit(code=1) # Login on a Telegram account try: api_id = config('API_ID') api_hash = config('API_HASH') phone = config('PHONE') client = TelegramClient(phone, api_id, api_hash) client.connect() if not client.is_user_authorized(): client.send_code_request(phone) login_code = click.prompt( 'Enter the Login Code that was send to yor Telegram app', type=int) client.sign_in(phone, login_code) except UndefinedValueError: print( 'The environment variables API_ID, API_HASH or PHONE were not defined. ' 'Please create a .env file with they!\n') click.Context(teladduser).exit(code=1) # Get all Groups of the logged user chats = [] last_date = None chunk_size = 100 groups = [] result = client( GetDialogsRequest(offset_date=last_date, offset_id=0, offset_peer=InputPeerEmpty(), limit=chunk_size, hash=0)) # Get only the super group of the logged user chats.extend(result.chats) for chat in chats: try: if chat.megagroup: groups.append(chat) except: continue # Select a group to add users for i, g in enumerate(groups): print(f"{i + 1} - {g.title}") g_index = click.prompt("\nEnter Number of Group you want add users", type=int) try: target_group = groups[int(g_index) - 1] except IndexError: print( '\nThe number selected was not of a valid Group number! Please try again!\n' ) click.Context(teladduser).exit(code=1) target_group_entity = InputPeerChannel(target_group.id, target_group.access_hash) print(f'\nReading the file {file}, this will take a while ...\n') users_to_add = rows.import_from_xlsx(file) # Create a new Rows Table to save processed data fields = OrderedDict([('username_normal', rows.fields.TextField), ('nome', rows.fields.TextField), ('grupocanal', rows.fields.TextField), ('conta_de_envio', rows.fields.IntegerField), ('log', rows.fields.TextField)]) users_added = rows.Table(fields=fields) n = 0 for i, user in enumerate(users_to_add): if user.log: users_added.append({ 'username_normal': user.username_normal, 'nome': user.nome, 'grupocanal': user.grupocanal, 'cont_a_de_envio': user.conta_de_envio, 'log': user.log, }) elif i >= 45: try: print(f'Adicionando usuário: {i} - {user.nome}') user_to_add = client.get_input_entity(user.username_normal) client( InviteToChannelRequest(target_group_entity, [user_to_add])) log = f"Usuário inserido em: {datetime.strftime(datetime.today(), '%Y-%m-%d às %H:%M:%S')}" users_added.append({ 'username_normal': user.username_normal, 'nome': user.nome, 'grupocanal': target_group.title, 'cont_a_de_envio': user.conta_de_envio, 'log': log, }) n += 1 if n % 20 == 0: print( f'\nWaiting {time_sleep / 60} minutes to avoid Flood Error.\n' ) time.sleep(time_sleep) else: time.sleep(time_sleep / 15) except PeerFloodError: print( "\nGetting Flood Error from telegram. Script is stopping now. Please try again after some time.\n" ) try: rows.export_to_xlsx(users_added, "usersAddedBeforeFloodError.xlsx") except: print('\nCould not write to the file provided!\n') click.Context(teladduser).exit(code=1) except UserPrivacyRestrictedError: print( "\nThe user's privacy settings do not allow you to do this. Skipping.\n" ) except ValueError as err: print(f'\n{err} - Skipping.\n') except UserChannelsTooMuchError: print( f'\nThe user {user.username_normal} you tried to add is already in too many channels/supergroups\n' ) except FloodWaitError as err: print('\nHave to sleep', err.seconds, 'seconds\n') time.sleep(err.seconds) except KeyboardInterrupt: print('\nExecution was interrupted by user.\n') click.Context(teladduser).exit(code=1) except: traceback.print_exc() print("\nUnexpected Error\n") continue else: users_added.append({ 'username_normal': user.username_normal, 'nome': user.nome, 'grupocanal': user.grupocanal, 'cont_a_de_envio': user.conta_de_envio, 'log': user.log, }) try: rows.export_to_xlsx(users_added, file) except: traceback.print_exc() print('\nCould not write to the file provided!\n')