def has_sheet(self): if self.sheetname == '': return True try: pyexcel.get_sheet(file_name=self.filename, sheet_name=self.sheetname) return True except: return False
def convert(): #Definir formato de localização dos dados locale.setlocale(locale.LC_ALL, 'en_US') #Planilha - GND # Importa dados da planilha de Grandes Grupos de Despesa raw_sheet_gnd = pyexcel.get_sheet(file_name="/home/romulofloresta/server_app/xls2py/gnd-raw.xls") #Remove linhas e colunas desnecessárias raw_sheet_gnd.delete_rows(list(range(4))) #deleta linhas acima for i in range(3): raw_sheet_gnd.delete_rows([-1]) #deleta linhas abaixo raw_sheet_gnd.delete_columns([0, 2, 4, 5, 7]) #Converte para tipo de dados python raw_sheet_gnd.name_columns_by_row(0) py_records_gnd = raw_sheet_gnd.to_records() #formata os valores de moeda for record in py_records_gnd: record['Autorizado'] = locale.currency(record["Autorizado"], symbol=None) record['Pago'] = locale.currency(record["Pago"], symbol=None) # Funções # Importa dados da planilha de Funções raw_sheet_func = pyexcel.get_sheet(file_name="/home/romulofloresta/server_app/xls2py/funcoes-raw.xls") #Remove linhas e colunas desnecessárias raw_sheet_func.delete_rows(list(range(4))) #deleta linhas acima for i in range(4): raw_sheet_func.delete_rows([-1]) #deleta linhas abaixo raw_sheet_func.delete_columns([1, 3, 4, 6]) #Alterar título da coluna raw_sheet_func[0,0] = 'Funcao' #Converte para tipo de dados python raw_sheet_func.name_columns_by_row(0) py_records_func = raw_sheet_func.to_records() # Formata os campos for record in py_records_func: record['Funcao'] = record['Funcao'][4:] record['Autorizado'] = locale.currency(record["Autorizado"], symbol=None) record['Pago'] = locale.currency(record["Pago"], symbol=None) #Pega a versão do banco de dados with open('server_app/version.json') as f: data_version = json.load(f) f.close() # Retorna json com os dados response = json.dumps({'Funcao': py_records_func, 'GND': py_records_gnd, 'version': data_version['version'], 'updated':data_version['date']}) return response
def test_upload_and_download(self): for upload_file_type in FILE_TYPE_MIME_TABLE.keys(): file_name = 'test.%s' % upload_file_type for download_file_type in FILE_TYPE_MIME_TABLE.keys(): print("Uploading %s Downloading %s" % (upload_file_type, download_file_type)) sheet = pe.Sheet(self.data) io = sheet.save_to_memory(upload_file_type).getvalue() if not PY2: if isinstance(io, bytes): content = io else: content = io.encode('utf-8') else: content = io response = self.app.post( '/switch/%s' % download_file_type, upload_files=[('file', file_name, content)], ) eq_(response.content_type, FILE_TYPE_MIME_TABLE[download_file_type]) sheet = pe.get_sheet(file_type=download_file_type, file_content=response.body) sheet.format(int) array = sheet.to_array() assert array == self.data
def test_get_sheet_from_sql(self): sheet = pe.get_sheet(session=Session(), table=Signature) assert sheet.to_array() == [ ["X", "Y", "Z"], [1, 2, 3], [4, 5, 6] ]
def parse_bounds(file_path): if not (os.path.isfile(file_path)): print (file_path + " File not found!!!") return {"bounds" : []} sheet = pyexcel.get_sheet(file_name=file_path) bound_dict = { "record" : sheet.column[1][0], "channel" : sheet.column[1][1], "neuron" : sheet.column[1][2], "bounds" : [], } for idx in range(len(sheet.column[0][3:])): idx += 3 tmp_bounds = sheet.column[1][idx].split(" ") if (len(tmp_bounds) == 1 and tmp_bounds[0] == ""): continue if (len(tmp_bounds) != 2): tmp_bounds = [] tmp_bounds.append( sheet.column[1][idx] ) tmp_bounds.append( sheet.column[2][idx] ) #tmp_bounds[0] = min(tmp_bounds) #tmp_bounds[1] = max(tmp_bounds) tmp_dict = { 'name' : sheet.column[0][idx], 'lower_bound' : float(tmp_bounds[0]), 'upper_bound' : float(tmp_bounds[1]), } bound_dict["bounds"].append(tmp_dict) return (bound_dict)
def test_auto_detect_float_false(self): expected = [[ '2014-12-25', '2014-12-25 11:11:11', '2014-12-25 11:11:11.000010']] sheet = pe.get_sheet(file_name=self.excel_filename, auto_detect_datetime=False) self.assertEqual(sheet.to_array(), expected)
def test_writing_multiline_ods(): content = "2\n3\n4\n993939\na" testfile = "writemultiline.ods" array = [[content, "test"]] pyexcel.save_as(array=array, dest_file_name=testfile) sheet = pyexcel.get_sheet(file_name=testfile) assert sheet[0, 0] == content os.unlink(testfile)
def main(base_dir): # "example.csv","example.xlsx","example.ods", "example.xlsm" spreadsheet = pyexcel.get_sheet(file_name=os.path.join(base_dir, "example.xls")) # rows() returns row based iterator, meaning it can be iterated row by row for row in spreadsheet.rows(): print(row)
def test_get_sheet_from_file(self): data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]] sheet = pe.Sheet(data) testfile = "testfile.xls" sheet.save_as(testfile) sheet = pe.get_sheet(file_name=testfile) assert sheet.to_array() == data os.unlink(testfile)
def test_auto_detect_int(self): sheet = pe.get_sheet(file_name=self.test_file) expected = dedent(""" pyexcel_sheet1: +---+---+-----+ | 1 | 2 | 3.1 | +---+---+-----+""").strip() self.assertEqual(str(sheet), expected)
def test_issue_30(): test_file = "issue_30.ods" sheet = pe.Sheet() sheet[0, 0] = 999999999999999 sheet.save_as(test_file) sheet2 = pe.get_sheet(file_name=test_file) eq_(sheet[0, 0], sheet2[0, 0]) os.unlink(test_file)
def addExcel(path, nowtime): sheet = pe.get_sheet(file_name = path) oneRow = [nowtime] for i in range(1, len(sys.argv)): oneRow.append(sys.argv[i]) sheet.row += oneRow sheet.save_as(path)
def test_auto_detect_int(self): sheet = pe.get_sheet(file_name=self.test_file, library="pyexcel-xls") expected = dedent(""" pyexcel_sheet1: +---+---+-----+ | 1 | 2 | 3.1 | +---+---+-----+""").strip() eq_(str(sheet), expected)
def open_existing(self, the_file): if not self.finished: sheet = get_sheet(file_name=the_file) for row in sheet.rows(): self.row += row self.name_columns_by_row(0) self.name_rows_by_column(0) self._finished = True
def test_auto_detect_int_false(self): sheet = pe.get_sheet(file_name=self.test_file, auto_detect_int=False) expected = dedent(""" test_auto_detect_init.csv: +-----+-----+-----+ | 1.0 | 2.0 | 3.1 | +-----+-----+-----+""").strip() self.assertEqual(str(sheet), expected)
def test_ods_output_stringio(self): data = [[1, 2, 3], [4, 5, 6]] io = pyexcel.save_as(dest_file_type="ods", array=data) r = pyexcel.get_sheet( file_type="ods", file_content=io.getvalue(), library="pyexcel-ods3" ) result = [1, 2, 3, 4, 5, 6] actual = list(r.enumerate()) eq_(result, actual)
def load_single_sheet(self, field_name=None, sheet_name=None, **keywords): file_type, file_handle = self.get_file_tuple(field_name) if file_type is not None and file_handle is not None: return pe.get_sheet(file_type=file_type, file_content=file_handle.read(), sheet_name=sheet_name, **keywords) else: return None
def test_load_sheet_from_django_model(self): model=FakeDjangoModel() sheet = pe.Sheet(self.data, name_columns_by_row=0) sheet.save_to_django_model(model) assert model.objects.objs == self.result model._meta.update(["X", "Y", "Z"]) sheet2 = pe.get_sheet(model=model) sheet2.name_columns_by_row(0) assert sheet2.to_records() == sheet.to_records()
def test_download(self): for file_type in FILE_TYPE_MIME_TABLE.keys(): print(file_type) response = self.client.get("/polls/download/"+file_type) assert response['Content-Type'] == FILE_TYPE_MIME_TABLE[file_type] sheet = pe.get_sheet(file_type=file_type, file_content=response.content) sheet.format(int) array = sheet.to_array() assert array == self.data
def test_get_sheet_from_array(self): data = [ ["X", "Y", "Z"], [1, 2, 3], [4, 5, 6] ] sheet = pe.get_sheet(array=data) result = sheet.to_array() assert data == result
def test_get_sheet_from_memory_compatibility(self): data = [ ["X", "Y", "Z"], [1, 2, 3], [4, 5, 6] ] content = pe.save_as(dest_file_type="xls", array=data) sheet = pe.get_sheet(content=content.getvalue(), file_type="xls") assert sheet.to_array() == data
def create_dictionary(sheet_list): ''' creates allsheet_dict and ezsheet_dict ''' for i in sheet_list: if i == './resources/All_output.xlsx': allsheet = pyexcel.get_sheet(file_name = sheet_list[0], name_columns_by_row=0) allsheet_od = allsheet.to_dict()#makes ordered dict allsheet_dict = dict(allsheet_od)#makes ordinary dict #print "ALL: ",allsheet_dict elif i == './resources/Easy_output.xlsx': ezsheet = pyexcel.get_sheet(file_name = sheet_list[1], name_columns_by_row=0) ezsheet_dict = dict(ezsheet.to_dict()) #print "EZ: ",ezsheet_dict else: print "You don't have the appropriate sheet names" return (allsheet_dict, ezsheet_dict)
def main(base_dir): sheet = pe.get_sheet(file_name=os.path.join(base_dir, "tutorial_datatype_01.xls"), name_columns_by_row=0) print(sheet.to_dict()) # {u'userid': [10120.0, 10121.0, 10122.0], # u'name': [u'Adam', u'Bella', u'Cedar']} sheet.column.format(0, str) print(sheet.to_dict())
def test_auto_detect_float_false(self): sheet = pe.get_sheet(file_name=self.test_file, auto_detect_float=False) self.assertEqual(sheet.to_array(), [[1, '2.0', '3.1']]) expected = dedent(""" test_auto_detect_init.csv: +---+-----+-----+ | 1 | 2.0 | 3.1 | +---+-----+-----+""").strip() self.assertEqual(str(sheet), expected)
def test_get_sheet_from_file_stream(self): data = [ ["X", "Y", "Z"], [1, 2, 3], [4, 5, 6] ] content = pe.save_as(dest_file_type="xls", array=data) sheet = pe.get_sheet(file_stream=content, file_type="xls") assert sheet.to_array() == data
def __init__(self, filename): self.sheet = pyexcel.get_sheet(file_name=filename) self.filename = filename self.get_index_start_var_def() self.get_index_end_gatts() self.get_index_start_data() self.get_index_end_var_def() self.get_index_end_data() self.max_data_column()
def main(date_start=datetime.datetime.today(), date_end=None): running_date = date_start # answered = defaultdict(list) # answered = namedtuple('answered', 'lessthan5 morethan5 morethan10') # answered(lessthan5=[], morethan5=[], morethan10=[]) # # answered.__new__.__defaults__ = ([],) * len(answered._fields) # lost = namedtuple('lost', 'lessthan5 morethan5 morethan10') # lost(lessthan5=[], morethan5=[], morethan10=[]) # # lost.__new__.__defaults__ = ([],) * len(lost._fields) # print(answered) # print(lost) answered = Counter() lost = Counter() while date_end >= running_date: month_date = running_date.strftime('%m%d%Y') # file = r'C:\Users\mscales\Desktop\Development\Attachment Archive\{0}\Call Details.xlsx'.format(month_date) file = r'C:\Users\mscales\Desktop\Development\Daily SLA Parser - Automated Version\Archive\{0}\{0}_Call Details.xlsx'.format(month_date) # print(file) try: sheet = pe.get_sheet(file_name=file) data = sheet.to_array() # print(data) for row in data: if row[6] == 7592: call_duration = get_sec(row[12]) date = row[3] if row[11] is True: print("found true call") if call_duration <= 30: answered.morethan10.append(convert_time_stamp(call_duration) + '.' + date) # elif call_duration >= 1800: # answered.morethan5.append(convert_time_stamp(call_duration) + '.' + date) # else: # answered.lessthan5.append(convert_time_stamp(call_duration) + '.' + date) # else: # if call_duration >= 600: # lost.morethan10.append(convert_time_stamp(call_duration) + '.' + date) # elif call_duration >= 300: # lost.morethan5.append(convert_time_stamp(call_duration) + '.' + date) # else: # lost.lessthan5.append(convert_time_stamp(call_duration) + '.' + date) else: pass except Exception as e: print(e) pass running_date = running_date + timedelta(days=1) # print("Answered: less than 5 min: {}".format(len(answered.lessthan5))) # for duration in answered.lessthan5: # print(duration) # print("Answered: more than 5 min: {}".format(len(answered.morethan5))) # for duration in answered.morethan5: # print(duration) print("Answered: more than 60 min: {}".format(len(answered.morethan10))) for duration in answered.morethan10: print(duration.split('.'))
def do_write_stringio(file_type): data = [ [1, 2, 3], [4, 5, 6] ] io = pe.save_as(dest_file_type=file_type, array=data) r = pe.get_sheet(file_type=file_type, file_content=io.getvalue()) result=[1, 2, 3, 4, 5, 6] actual = pe.utils.to_array(r.enumerate()) assert actual == result
def test_get_sheet_from_query_sets(self): session = Session() objects = session.query(Signature).all() column_names = ["X", "Y", "Z"] sheet = pe.get_sheet(column_names=column_names, query_sets=objects) assert sheet.to_array() == [ ["X", "Y", "Z"], [1, 2, 3], [4, 5, 6] ]
def test_save_a_dict3(self): adict = { "X": [1, 4], "Y": [2, 5], "Z": [3, 6] } sheet = pe.get_sheet(adict=adict, name_columns_by_row=0) sheet.save_to_database(self.session, Signature) result = pe.get_dict(session=self.session, table=(Signature)) assert adict == result
def make_response_from_a_table( model, file_type, status=200, file_name=None, **keywords ): """ Produce a single sheet Excel book of *file_type* :param model: a Django model :param file_type: same as :meth:`~django_excel.make_response` :param status: same as :meth:`~django_excel.make_response` """ sheet = pe.get_sheet(model=model, **keywords) return make_response( sheet, file_type, status, file_name=file_name, **keywords )
def test_array(self): for struct_type in ["array", "dict", "records"]: print("Testing %s" % struct_type) io = pe.save_as(dest_file_type="xls", array=self.data) io.seek(0) response = self.app.post('/exchange/%s' % struct_type, buffered=True, data={"file": (io, "test.xls")}, content_type="multipart/form-data") assert response.content_type == "application/vnd.ms-excel" sheet = pe.get_sheet(file_type='xls', file_content=response.data) assert sheet.to_array() == self.data eq_(sheet.name, 'test_array')
def __init__(self, archivo): ## Obtiene los registros en el archivo de carga masiva proporcionado por el usuario en un arreglo, en caso de un archivo csv la longitud de cada arreglo es de 1 elemento datos = pyexcel.get_sheet(file_name=archivo) for dat in datos: if dat.__len__() == 0: # Es un archivo csv else: # Esta en otro formato ods, xls, xlsx, etc pass #def validar_string()
def test_issue_92_incomplete_records(): records = [{"a": 1, "b": 2, "c": 3}, {"b": 2}, {"c": 3}] sheet = pe.get_sheet(records=records) content = dedent(""" +---+---+---+ | a | b | c | +---+---+---+ | 1 | 2 | 3 | +---+---+---+ | | 2 | | +---+---+---+ | | | 3 | +---+---+---+""").strip("\n") eq_(str(sheet.content), content)
def test_url_source_via_file_suffix_get_sheet(self, mock_open): m = MagicMock() x = MagicMock() x.type.return_value = "text" m.info.return_value = x m.read.return_value = "1,2,3" mock_open.return_value = m sheet = pe.get_sheet(url="xx.csv") content = dedent(""" csv: +---+---+---+ | 1 | 2 | 3 | +---+---+---+""").strip('\n') self.assertEqual(str(sheet), content)
def test_auto_detect_int_false(self): sheet = pe.get_sheet( file_name=self.test_file, auto_detect_int=False, library="pyexcel-ods", ) expected = dedent( """ pyexcel_sheet1: +-----+-----+-----+ | 1.0 | 2.0 | 3.1 | +-----+-----+-----+""" ).strip() eq_(str(sheet), expected)
def test_greek_db(self): # old_row_names = greek_example_sheet.row[1] process_greek_db(self.fields_correspondences) greek_db_to_test = pe.get_sheet(file_name="data/processed-greek-db.xlsx") new_row_names = greek_db_to_test.row[0] # Check first and last name fields self.assertEqual(new_row_names[0], "food-name", "The content of the fields doesn't match") self.assertEqual(new_row_names[-1], "copper-mg", "The content of the fields doesn't match") pass
def test_download_attachment(self): test_file_name = "test" for file_type in FILE_TYPE_MIME_TABLE.keys(): print(file_type) response = self.client.get("/polls/download_attachment/" + file_type + "/" + test_file_name) assert response['Content-Type'] == FILE_TYPE_MIME_TABLE[file_type] assert response['Content-Disposition'] == ( "attachment; filename=%s.%s" % (test_file_name, file_type)) sheet = pe.get_sheet(file_type=file_type, file_content=response.content) sheet.format(int) array = sheet.to_array() assert array == self.data
def test_issue_92_non_uniform_records(): records = [{"a": 1}, {"b": 2}, {"c": 3}] sheet = pe.get_sheet(records=records, custom_headers=['a', 'b', 'c']) content = dedent(""" +---+---+---+ | a | b | c | +---+---+---+ | 1 | | | +---+---+---+ | | 2 | | +---+---+---+ | | | 3 | +---+---+---+""").strip("\n") eq_(str(sheet.content), content)
def xsl_to_json(url): sheet = pe.get_sheet(url=url) sheet_array = sheet.array new_data = [] col_name = sheet_array[0] i = 0 for c in sheet_array: if i != 0: data = {} for i in range(len(col_name)): data[col_name[i]] = c[i] new_data.append(data) i = i + 1 return new_data
def do_read_stringio(file_name): create_sample_file1(file_name) file_type = file_name.split('.')[-1] open_flag = 'rb' if file_type in ['csv', 'tsv']: open_flag = 'r' with open(file_name, open_flag) as f: content = f.read() r = pe.get_sheet(file_type=file_type, file_content=content) result = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 1.1, 1] actual = pe.utils.to_array(r.enumerate()) assert result == actual if os.path.exists(file_name): os.unlink(file_name)
def do_read_stringio(file_name): create_sample_file1(file_name) file_type = file_name.split(".")[-1] open_flag = "rb" if file_type in ["csv", "tsv"]: open_flag = "r" with open(file_name, open_flag) as f: content = f.read() r = pe.get_sheet(file_type=file_type, file_content=content) result = ["a", "b", "c", "d", "e", "f", "g", "h", "i", "j", 1.1, 1] actual = list(r.enumerate()) eq_(result, actual) if os.path.exists(file_name): os.unlink(file_name)
def test_issue_29(self): a=[ ['2016-03-31 10:59', '0123', 'XS360_EU', '04566651561653122'], # error case [datetime(2016, 4, 15, 17, 52, 11), 123, False, 456193284757] # python types ] s=pe.get_sheet(array=a) content = dedent(""" pyexcel_sheet1: +------------------+------+----------+-------------------+ | 2016-03-31 10:59 | 0123 | XS360_EU | 04566651561653122 | +------------------+------+----------+-------------------+ | 15/04/16 | 123 | false | 456193284757 | +------------------+------+----------+-------------------+""").strip('\n') self.assertEqual(str(s), content)
def test_merge_two_csv_files_2(self): file1 = os.path.join("tests", "fixtures", "test_cook_book_merge_numbers_2.csv") file2 = os.path.join("tests", "fixtures", "test_cook_book_merge_alphabets_2.csv") file_list = [file1, file2] pe.cookbook.merge_files(file_list) r = pe.get_sheet(file_name="pyexcel_merged.csv") actual = [ [1, 2, 3, "", "", "", "", "a", "b", "c", "", ""], [4, 5, 6, 10, 11, 12, "", "d", "e", "f", "m", "q"], [7, 8, 9, "", "", "", 9, "g", "h", "I", "n", "p"], ] eq_(actual, r.array)
def test_writing_date_format(self): excel_filename = "testdateformat.xls" data = [[datetime.date(2014, 12, 25), datetime.time(11, 11, 11), datetime.datetime(2014, 12, 25, 11, 11, 11)]] pe.save_as(dest_file_name=excel_filename, array=data) r = pe.get_sheet(file_name=excel_filename) assert isinstance(r[0, 0], datetime.date) is True assert r[0, 0].strftime("%d/%m/%y") == "25/12/14" assert isinstance(r[0, 1], datetime.time) is True assert r[0, 1].strftime("%H:%M:%S") == "11:11:11" assert isinstance(r[0, 2], datetime.date) is True assert r[0, 2].strftime("%d/%m/%y %H:%M:%S") == "25/12/14 11:11:11" os.unlink(excel_filename)
def test_url_source_via_file_suffix_get_sheet(self, mock_open): m = MagicMock() x = MagicMock() x.type.return_value = "text" m.info.return_value = x m.read.return_value = "1,2,3" mock_open.return_value = m book = pe.get_sheet(url="xx.csv") content = dedent(""" Sheet Name: csv +---+---+---+ | 1 | 2 | 3 | +---+---+---+""").strip('\n') assert str(book) == content
def generate_hash_tables(self, file_path: str, excel_filepath: str, sheet_name: str = None, header: bool = True) -> None: """ This function processes the wikified output file uploaded by the user to build self.other dictionary :param file_path: :param excel_filepath: :param sheet_name: :param header: :return: """ cell_to_qnode = dict() value_to_qnode = dict() with open(file_path, encoding='utf-8') as file: csv_reader = csv.reader(file, delimiter=',') for row in csv_reader: if header: header = False continue if not check_if_string_is_invalid( row[0]) and not check_if_string_is_invalid(row[1]): cell_to_qnode[(int(row[0]), int(row[1]))] = row[3] if row[2] is not None: value_to_qnode[str(row[2]).strip()] = row[3] sheet = pyexcel.get_sheet(sheet_name=sheet_name, file_name=excel_filepath) for cell, qnode in cell_to_qnode.items(): try: cell_value = str(sheet[cell[1], cell[0]]).strip() if not check_if_string_is_invalid( cell_value) and cell_value not in value_to_qnode: value_to_qnode[cell_value] = qnode except IndexError: pass for row in range(len(sheet)): for col in range(len(sheet[0])): try: cell_value = str(sheet[row, col]).strip() if value_to_qnode.get(cell_value, None): cell_to_qnode[(col, row)] = value_to_qnode[cell_value] except IndexError: pass cell_to_qnode = self.serialize_cell_to_qnode(cell_to_qnode) self.other["qnodes"] = cell_to_qnode self.other["region"] = list(cell_to_qnode.keys())
def upload(): if request.method == 'POST': # Check if the post request has the file part if 'file' not in request.files: return 'No file received.' input_file = request.files['file'] # If the user doesn't select a file, the browser also # submit an empty part without a filename. if input_file.filename == '': return 'No file selected.' if not input_file.filename.lower().endswith('.xls'): return 'File must be XLS format.' try: sheet = pyexcel.get_sheet(file_content=input_file.read(), file_type='xls', name_columns_by_row=1) del sheet.row[filter_row] # Delete empty rows records = sheet.to_records() except XLRDError as e: return 'File is corrupt.' cal = vobject.iCalendar() for record in records: event = build_event(record) vevent = cal.add('vevent') vevent.add('uid').value = str(uuid.uuid4()).upper() vevent.add('summary').value = event['title'] vevent.add('description').value = event['description'] vevent.add('location').value = event['location'] vevent.add('dtstart').value = event['event_start'] vevent.add('dtend').value = event['event_end'] ruleset = rruleset() ruleset.rrule( rrule(WEEKLY, byweekday=event['day_index'], until=event['until'])) for exdate in event['excludes']: ruleset.exdate(exdate) vevent.rruleset = ruleset return cal.serialize() return ''
def read_create(): wb = openpyxl.Workbook() wb.save("src/tmp/baskets/" + "left_over" + ".xlsx") file_name = "src/data/Courselist.ods" sheet = pyexcel.get_sheet(file_name=file_name) name = [] i = 0 for row in sheet: lists = str(row[3]) lists = lists.split('-') if (lists[0] != "0"): check = 0 i += 1 if (i == 2): continue if (i > 186): break # print(row) # print("sdjhfjdsgf hjdshf hds ddvfdvhghjd d f ",i) for file in os.listdir(file_path): if file.endswith( '.xlsx' ) and file != ".xlsx" and file != "course_faculty_main.xlsx" and file != 'course_faculty_optional.xlsx': a = str(os.path.join(file_path, file)) if (check == 1 or row[1] != ""): break wb = openpyxl.load_workbook(a) ws = wb.worksheets[0] j = 0 for j in range(2, ws.max_row + 1): # print(row[1],ws.cell(j,1).value) if (ws.cell(j, 1).value == row[1]): check = 1 break wb.save(a) if (check == 0): wb = openpyxl.load_workbook("src/tmp/baskets/" + "left_over" + ".xlsx") ws = wb.worksheets[0] rows = ws.max_row + 1 # print(rows) for l in range(1, 6): ws.cell(rows, l).value = row[l] wb.save("src/tmp/baskets/" + "left_over" + ".xlsx")
def load_sheet(self): """ Return a pyexcel sheet read from the uploaded file. """ f = self.files['spreadsheet'] # Remove BOM if f.read(len(codecs.BOM_UTF8)).startswith(codecs.BOM_UTF8): f = io.TextIOWrapper(f, encoding='utf-8-sig') # Convert byte-stream to strings elif self.extension == 'csv': f = io.TextIOWrapper(f) return pyexcel.get_sheet(file_type=self.extension, file_stream=f)
def importar_gene(var): if 'username' in session: form = ImportExcel(request.form) if request.method=='POST': archivo = Manejo_archivos(request.files["archivo"]) archivo.guardar() success_message = 'Tu archivo se ha guardado exitosamente' flash(success_message) cantidad = archivo.contar_lineas() leidos = pyexcel.get_sheet(file_name=archivo.archivo.filename, name_columns_by_row=0) return render_template("table.html", datos = leidos, cantidad = cantidad, tipo = "0", variables = var) else: return render_template('importar.html', form = form) else: return redirect('/users/login')
def main(base_dir): # Simple give the file name to **Reader** # "example.xls","example.xlsx","example.ods", "example.xlsm" spreadsheet = pe.get_sheet(file_name=os.path.join(base_dir, "example.csv")) # row_range() gives [0 .. number of rows] for r in spreadsheet.row_range(): # column_range() gives [0 .. number of ranges] for c in spreadsheet.column_range(): # cell_value(row_index, column_index) # return the value at the specified # position # please note that both row_index # and column_index starts from 0 print(spreadsheet.cell_value(r, c))
def test_simple_option(): runner = CliRunner() file_fixture = os.path.join("tests", "fixtures", "multiple-sheets.xls") prefix = "output" result = runner.invoke( split, ['--output-file-type', 'xlsx', file_fixture, prefix]) eq_(result.exit_code, 0) book = get_book(file_name=file_fixture) count = 0 for sheet in book: splitted_file = "%s_%s_%s.xlsx" % (prefix, sheet.name, count) count += 1 written_sheet = get_sheet(file_name=splitted_file) eq_(str(sheet), str(written_sheet)) os.unlink(splitted_file)
def test_save_file_as_another_one(self): data = [ ["X", "Y", "Z"], [1, 2, 3], [4, 5, 6] ] sheet = pe.Sheet(data) testfile = "testfile.xls" testfile2 = "testfile2.csv" sheet.save_as(testfile) pe.isave_as(file_name=testfile, dest_file_name=testfile2) sheet = pe.get_sheet(file_name=testfile2) eq_(sheet.to_array(), data) os.unlink(testfile) os.unlink(testfile2)
def process(self): file_type = self.excel_file.name.split(".")[1] if file_type not in self.file_types: self.errors.append('Файл недопустимого формата, допустимые форматы - ' + str(self.file_types)) return sheet = pyexcel.get_sheet(file_type=file_type, file_stream=self.excel_file.read()) logger.info('***********************************************************') logger.info('Начинаем обработку файла %s %s' % (self.excel_file.name, self.start_log_message)) logger.info('***********************************************************') self.process_business_logic(sheet.rows()) logger.info('*******************************************************') logger.info('Обработка файла %s %s' % (self.excel_file.name, self.end_log_message)) logger.info('*******************************************************')
def _download_and_verify_file_name(self, file_name): for file_type in FILE_TYPE_MIME_TABLE.keys(): url_encoded_file_name = urllib_quote(file_name) response = self.client.get(("/polls/download_attachment/%s/%s" % (file_type, url_encoded_file_name))) assert response["Content-Type"] == FILE_TYPE_MIME_TABLE[file_type] assert response["Content-Disposition"] == ( "attachment; filename=%s.%s;filename*=utf-8''%s.%s" % (url_encoded_file_name, file_type, url_encoded_file_name, file_type)) sheet = pe.get_sheet(file_type=file_type, file_content=response.content) sheet.format(int) array = sheet.to_array() assert array == self.data
def test_sql_sheet(self): sheet = pe.get_sheet(session=Session(), table=Pyexcel, sheet_name='custom sheet', export_columns=['weight', 'birth']) content = dedent(""" custom sheet: +--------+------------+ | weight | birth | +--------+------------+ | 11.25 | 2014-11-11 | +--------+------------+ | 12.25 | 2014-11-12 | +--------+------------+""").strip('\n') self.assertEqual(str(sheet), content)
def get_data(file: str) -> dict: """ Will return a simple set of data, without too much validation. Deduplicates data per unique category. data{'category': set('url1, url2')} Compound records are decompounded and placed in the correct categories. Below might result in four additions: category, category - url, url :param file: :return: """ data: Dict[str, set] = {} try: sheet = p.get_sheet(file_name=file, name_columns_by_row=0) except XLRDError: # xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'thisfile' return data # Skips the first entry for row in sheet: # Data is parsed to python-like datatype. In this case we only expect strings and cast them as such. found_categories = str(row[0]).lower().strip().split(',') found_urls = str(row[1]).lower().strip().split(',') for found_category in found_categories: found_category = found_category.strip() for found_url in found_urls: found_url = found_url.strip() # create new category if found_category not in data: data[found_category] = set() data[found_category].add(found_url) # During editing, it might happen there are some 'left over' cells that are also added. # These left overs contain no urls. If they do, and something has been attempted to be added to # 'empty', it is discarded. We require urls to be in a list / category. if '' in data: data.pop('') p.free_resources() return data
def exportarDatosExcel( request): ##Se exportan los datos a un archivo de excel organizado lock.acquire() sheet = pyexcel.get_sheet(array=[ vaca1[10], vaca1[6], vaca1[7], vaca1[0], vaca1[1], vaca1[2], vaca1[3], vaca1[4], vaca1[5], vaca1[8], Vacio, vaca2[10], vaca2[6], vaca2[7], vaca2[0], vaca2[1], vaca2[2], vaca2[3], vaca2[4], vaca2[5], vaca2[8], Vacio, vaca3[10], vaca3[6], vaca3[7], vaca3[0], vaca3[1], vaca3[2], vaca3[3], vaca3[4], vaca3[5], vaca3[8] ]) lock.release() sheet.transpose() return excel.make_response(sheet, "xlsx")
def test_write_irregular_arrays_to_file(self): r = pe.get_sheet(file_name=self.testfile, skip_empty_rows=False) actual = [ [1, "", "", "", "", ""], ["", 2, "", "", "", ""], ["", 2, 3, "", "", ""], ["", 2, 3, 4, "", ""], ["", 2, 3, 4, "", ""], ["", 2, 3, 4, 5, 6], ["", "", "", "", 5, ""], ["", "", "", "", "", ""], ["", "", 3, "", "", ""], ["", "", 3, 4, "", ""], ["", "", 3, 4, "", ""], ] eq_(actual, r.array)