def test_autofit_col(self): # TODO: compare col/row widths before/after - not implemented yet Range('Sheet1', 'A1:D4').value = 'test_string' Range('Sheet1', 'A:D').autofit() Range('Sheet1', 'A:D').autofit('r') Range('Sheet1', 'A:D').autofit('c') Range('Sheet1', 'A:D').autofit('rows') Range('Sheet1', 'A:D').autofit('columns')
def set_book(ticker, line): Range('A' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";bid_num_1\'" Range('B' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";bid\'" Range('C' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";ask\'" Range('D' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";ask_num_1\'" Range('E' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";last\'" Range('F' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";volume\'" #Add ref close for simplicity Range('G' + str(line + 1)).value = "=FDF|Q!\'" + ticker + ";close\'" return 0
def lluvia_max(): muestra = Range('LLUVIA', 'C5').table.value #numero de datos DataNr = len(muestra) #cambiamos tipo de dato a 'array' muestra = np.asarray(muestra) #todos valores de los parametrod de Gumbel hasta 58 datos Yn = Range('+', 'C4:C29').value Sigma = Range('+', 'D4:D29').value Indeks = Range('+', 'B4:B29').value.index(DataNr) #Valores adoptados por los parametros de la funcion Gumbel: Yn = Yn[Indeks] Sigma = Sigma[Indeks] ### empty lists creation s = [] m = [] alfa = [] mu = [] P = [] Prob = [] #los parametros de la funcion de Gumbel para cada estacion #ddof: "Delta Degrees of Freedom" by default 0 for i in range(0, 4): columna = muestra[:, i] s.append(np.std(columna, ddof=1)) m.append(np.mean(columna)) alfa.append(s[i] / Sigma) mu.append(m[i] - Yn * alfa[i]) P.append(mu[i] - alfa[i] * np.log(np.log(T / (T - 1)))) Prob.append(1 - 1 / T) ### PESOS dist = np.asarray([3968, 14046, 13251, 12596]) #distancias... pesos = 1 / dist**2 sq = [1 / i**2 for i in dist] pesos = pesos / sum(sq) ### Pmax24 return sum(P * pesos)
def test_unicode_path(self): # pip3 seems to struggle with unicode filenames src = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'unicode_path.xlsx') dst = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx') shutil.move(src, dst) wb = Workbook(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx'), app_visible=False, app_target=APP_TARGET) Range('A1').value = 1 wb.close() shutil.move(dst, src)
def test_hyperlink(self): address = 'www.xlwings.org' # Naked address Range('A1').add_hyperlink(address) assert_equal(Range('A1').value, address) if sys.platform.startswith('darwin'): assert_equal(Range('A1').hyperlink, 'http://' + address) else: assert_equal(Range('A1').hyperlink, 'http://' + address + '/') # Address + FriendlyName Range('A2').add_hyperlink(address, 'test_link') assert_equal(Range('A2').value, 'test_link') if sys.platform.startswith('darwin'): assert_equal(Range('A2').hyperlink, 'http://' + address) else: assert_equal(Range('A2').hyperlink, 'http://' + address + '/')
def test_cells(): cells = Range('C6:C441').value count = 0 master_text = [] while count < len(cells): if isinstance(cells[count], basestring): current_cell = cells[count].split(" ") current_text_count = 0 while current_text_count < len(current_cell): master_text.append(current_cell[current_text_count]) current_text_count += 1 count += 1 else: count += 1 return cell_word_count(master_text)
def combobox(): """ This populates the ComboBox with the values from the database """ # Database Query cursor.execute("SELECT PlaylistId, Name FROM Playlist") # Write IDs and Names to hidden sheet Range('Source', 'A1').table.clear_contents() Range('Source', 'A1').value = cursor.fetchall() # Format and fill the ComboBox to show Names (Text) and give back IDs (Values) # TODO: implement natively in xlwings combo = "ComboBox1" wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ListFillRange = \ 'Source!{}'.format(str(Range('Source', 'A1').table.xl_range.Address)) wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.BoundColumn = 1 wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnCount = 2 wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnWidths = 0 # Close cursor and connection cursor.close() con.close()
def playlist(): """ Get the playlist content based on the ID from the Dropdown """ # Get PlaylistId from ComboBox playlist_id = wb.xl_workbook.ActiveSheet.OLEObjects( "ComboBox1").Object.Value # Database query cursor.execute( """ SELECT t.Name AS Track, alb.Title AS Album, art.Name AS Artist, t.Composer FROM PlaylistTrack pt INNER JOIN Track t ON pt.TrackId = t.TrackId INNER JOIN Album alb ON t.AlbumId = alb.AlbumId INNER JOIN Artist art ON alb.ArtistId = art.ArtistId WHERE PlaylistId = ? """, (playlist_id, )) # Get the result and column names col_names = [col[0] for col in cursor.description] rows = cursor.fetchall() # Clear the sheet and write the column names and result to Excel Range('A9').table.clear_contents() Range('A9').value = col_names if len(rows): Range('A10').value = rows else: Range('A10').value = 'Empty Playlist!' # Close cursor and connection cursor.close() con.close()
def combobox(): """ This populates the ComboBox with the values from the database """ # Make a connection to the calling Excel file wb = Workbook.caller() # Place the database next to the Excel file db_file = os.path.join(os.path.dirname(wb.fullname), 'chinook.sqlite') # Database connection and creation of cursor con = sqlite3.connect(db_file, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES) cursor = con.cursor() # Database Query cursor.execute("SELECT PlaylistId, Name FROM Playlist") # Write IDs and Names to hidden sheet Range('Source', 'A1').table.clear_contents() Range('Source', 'A1').value = cursor.fetchall() # Format and fill the ComboBox to show Names (Text) and give back IDs (Values) # TODO: implement natively in xlwings combo = "ComboBox1" wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ListFillRange = \ 'Source!{}'.format(str(Range('Source', 'A1').table.xl_range.Address)) wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.BoundColumn = 1 wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnCount = 2 wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnWidths = 0 # Close cursor and connection cursor.close() con.close()
def test_integers(self): """Covers GH 227""" Range('A99').value = 2147483647 # max SInt32 assert_equal(Range('A99').value, 2147483647) Range('A100').value = 2147483648 # SInt32 < x < SInt64 assert_equal(Range('A100').value, 2147483648) Range('A101').value = 10000000000000000000 # long assert_equal(Range('A101').value, 10000000000000000000)
def test_unicode_path(self): # pip3 seems to struggle with unicode filenames src = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'unicode_path.xlsx') if sys.platform.startswith('darwin'): dst = os.path.join( os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/', 'ünicödé_päth.xlsx') else: dst = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx') shutil.copy(src, dst) wb = Workbook(dst, app_visible=False, app_target=APP_TARGET) Range('A1').value = 1 wb.close() os.remove(dst)
def test_calculation(self): Range('A1').value = 2 Range('B1').formula = '=A1 * 2' app = Application(wkb=self.wb) app.calculation = Calculation.xlCalculationManual Range('A1').value = 4 assert_equal(Range('B1').value, 4) app.calculation = Calculation.xlCalculationAutomatic app.calculate() # This is needed on Mac Excel 2016 but not on Mac Excel 2011 (changed behaviour) assert_equal(Range('B1').value, 8) Range('A1').value = 2 assert_equal(Range('B1').value, 4)
def summarize_sales(): """ Retrieve the account number and date ranges from the Excel sheet Read in the data from the sqlite database, then manipulate and return it to excel """ # Make a connection to the calling Excel file wb = Workbook.caller() # Connect to sqlite db db_file = os.path.join(os.path.dirname(wb.fullname), 'pbp_proj.db') engine = create_engine(r"sqlite:///{}".format(db_file)) # Retrieve the account number from the excel sheet as an int account = Range('B2').options(numbers=int).value # Get our dates - in real life would need to do some error checking to ensure # the correct format start_date = Range('D2').value end_date = Range('F2').value # Clear existing data Range('A5:F100').clear_contents() # Create SQL query sql = 'SELECT * from sales WHERE account="{}" AND date BETWEEN "{}" AND "{}"'.format( account, start_date, end_date) # Read query directly into a dataframe sales_data = pd.read_sql(sql, engine) # Analyze the data however we want summary = sales_data.groupby(["sku"])["quantity", "ext-price"].sum() total_sales = sales_data["ext-price"].sum() # Output the results if summary.empty: Range('A5').value = "No Data for account {}".format(account) else: Range('A5').options(index=True).value = summary Range('E5').value = "Total Sales" Range('F5').value = total_sales
def get_spot_purchase_values(year): ROOT_DIR = os.path.dirname(os.getcwd()) if year < 2005: raise ValueError('Data only goes back to 2005') elif year < 2015: wb = Workbook(os.path.join(ROOT_DIR, 'results/MomPop{0}.xlsm'.format(year)), app_visible=False) else: wb = Workbook(os.path.join( ROOT_DIR, 'results/notgrapefruit{0}.xlsm'.format(year)), app_visible=False) raw_price_df = pd.DataFrame(np.array( Range('grove', 'C5:N10', atleast_2d=True).value), columns=Range('grove', 'C4:N4').value, index=Range('grove', 'B5:B10').value) exchange_rate_df = pd.DataFrame(np.array( Range('grove', 'C14:N15', atleast_2d=True).value), columns=Range('grove', 'C13:N13').value, index=Range('grove', 'B14:B15').value) quantity_mat_weekly = np.array( Range('grove', 'C38:AX43', atleast_2d=True).value) # The matrix retrieved has values for each week. We want an average weekly # quantity for the given month. quantity_mat_av = np.zeros((6, 12)) for i in xrange(0, 12): this_month = quantity_mat_weekly[:, (4 * i):(4 * i + 4)] quantity_mat_av[:, i] = np.mean(this_month, 1) quantity_df = pd.DataFrame(quantity_mat_av, index=Range('grove', 'B38:B43').value, columns=Range('grove', 'C4:N4').value) wb.close() return (raw_price_df, exchange_rate_df, quantity_df)
def test(): credentials = authorization.get_credentials(SCOPES, CLIENT_SECRET_FILE, APPLICATION_NAME) service = build('gmail', 'v1', http=credentials.authorize(Http())) results = service.users().labels().list(userId='me').execute() labels = results.get('labels', []) if not labels: print 'No labels found.' else: print 'Labels:' i = 1 for label in labels: try: wb = Workbook('Book1') Range((i, 1)).value = label['name'].encode("shift-jis") except: print "error" i += 1
def insert_one_dataframe(self, df, cell_specification): """ Populate the current workbook given a single :class=:`pandas.DataFrame` object. """ if not len(df): return index = cell_specification.get('index', False) header = cell_specification.get('header', False) top_left_cell = cell_specification.get('top_left_cell', 'A0') logger.debug( "insert %d by %d rows/cols dataframe " "at cell '%s' in sheet '%s'", len(df), len(df.columns), str(top_left_cell), Sheet.active(self.wkb).name) Range(top_left_cell, index=index, header=header).value = df
def refresh(): """ Refreshes the tables in Excel given the input parameters. """ # Connect to the Workbook wb = Workbook.caller() # Read input start_date = Range(sheet_dashboard, 'start_date').value end_date = Range(sheet_dashboard, 'end_date').value account_name = Range(sheet_dashboard, 'account').value property_name = Range(sheet_dashboard, 'property').value profile_name = Range(sheet_dashboard, 'view').value max_results = Range(sheet_dashboard, 'max_rows').value # Clear Content Range(sheet_dashboard, 'behavior').clear_contents() Range(sheet_dashboard, 'effective').clear_contents() # Behavior table behavior(start_date, end_date, account_name, property_name, profile_name, max_results)
def test_resize(self): r = Range('A1').resize(4, 5) assert_equal(r.shape, (4, 5)) r = Range('A1').resize(row_size=4) assert_equal(r.shape, (4, 1)) r = Range('A1:B4').resize(column_size=5) assert_equal(r.shape, (4, 5)) r = Range('A1:B4').resize(row_size=5) assert_equal(r.shape, (5, 2)) r = Range('A1:B4').resize() assert_equal(r.shape, (4, 2)) assert_raises(AssertionError, Range('A1:B4').resize, row_size=0) assert_raises(AssertionError, Range('A1:B4').resize, column_size=0)
def parse_variable_values(workbook, start_cell, end_cell): """ Given the workbook reference and START-END index pair, this function parses the values in the variable row and saves it as a list of the same name. input ----- workbook: Workbook xlwings object start_cell: Start cell dictionary end_cell: End cell dictionary returns: lists of variables, formulas, comments """ workbook.set_current() # sets the workbook as the current working workbook variables = dict() formulas = dict() comments = dict() start = (start_cell['row'], start_cell['col']) end = (end_cell['row'], start_cell['col']) start_column = Range(get_sheet(), start, end).value # [1:] excludes 'START' element start_column = start_column[1:] for relative_index, element in enumerate(start_column): current_index = start_cell['row'] + relative_index + 1 if element: # if non-empty if not isinstance(element, str): raise ValueError( "The column below START can contain only strings") # print(element) element = element.strip() if '=' in element: formulas[element] = current_index elif '#' == element[0]: comments[element] = current_index else: variables[element] = current_index return variables, formulas, comments
def test_array(self): _skip_if_no_numpy() # 1d array Range('Sheet6', 'A1').value = array_1d cells = Range('Sheet6', 'A1:D1', asarray=True).value assert_array_equal(cells, array_1d) # 2d array Range('Sheet6', 'A4').value = array_2d cells = Range('Sheet6', 'A4', asarray=True).table.value assert_array_equal(cells, array_2d) # 1d array (atleast_2d) Range('Sheet6', 'A10').value = array_1d cells = Range('Sheet6', 'A10:D10', asarray=True, atleast_2d=True).value assert_array_equal(cells, np.atleast_2d(array_1d)) # 2d array (atleast_2d) Range('Sheet6', 'A12').value = array_2d cells = Range('Sheet6', 'A12', asarray=True, atleast_2d=True).table.value assert_array_equal(cells, array_2d)
def test_array(self): _skip_if_no_numpy() # 1d array Range('Sheet6', 'A1').value = array_1d cells = Range('Sheet6', 'A1:D1').options(np.array).value assert_array_equal(cells, array_1d) # 2d array Range('Sheet6', 'A4').value = array_2d cells = Range('Sheet6', 'A4').options(np.array, expand='table').value assert_array_equal(cells, array_2d) # 1d array (atleast_2d) Range('Sheet6', 'A10').value = array_1d cells = Range('Sheet6', 'A10:D10').options(np.array, ndim=2).value assert_array_equal(cells, np.atleast_2d(array_1d)) # 2d array (atleast_2d) Range('Sheet6', 'A12').value = array_2d cells = Range('Sheet6', 'A12').options(np.array, ndim=2, expand='table').value assert_array_equal(cells, array_2d)
def check_cell(self, address, value): # Active Sheet Range(address).value = value cell = Range(address).value assert_equal(cell, value) # SheetName Range('Sheet2', address).value = value cell = Range('Sheet2', address).value assert_equal(cell, value) # SheetIndex Range(3, address).value = value cell = Range(3, address).value assert_equal(cell, value)
def insert_one_series(self, series, cell_specification): """ Populate the current workbook given a single :class=:`pandas.Series` object. """ if not len(series): return # contiguous cells #TODO: (use vertical and horizontal properties of xlwings) # non-contiguous user-defined cells for cs in cell_specification.get('cells', []): ws = cs.get('worksheet') or Sheet.active(self.wkb).name content = self.jinja_env.from_string( cs['content']).render(**series) logger.debug("insert content '%s' at cell '%s' in sheet '%s'", content, cs['cell'], ws) Range(ws, cs['cell']).value = content
def test_list(self): # 1d List Row Range('Sheet4', 'A27').value = list_row_1d cells = Range('Sheet4', 'A27:C27').value assert_equal(list_row_1d, cells) # 2d List Row Range('Sheet4', 'A29').value = list_row_2d cells = Range('Sheet4', 'A29:C29', atleast_2d=True).value assert_equal(list_row_2d, cells) # 1d List Col Range('Sheet4', 'A31').value = list_col cells = Range('Sheet4', 'A31:A33').value assert_equal([i[0] for i in list_col], cells) # 2d List Col cells = Range('Sheet4', 'A31:A33', atleast_2d=True).value assert_equal(list_col, cells)
def importMethod(self): print("import button clicked!") self.filepath = str(QFileDialog.getOpenFileName()) self.wb = Workbook(self.filepath) self.data = Range('Sheet1','A2', asarray=True).table.value # print self.data for m in np.arange(0,len(self.data)): self.dataTable.insertRow(self.dataTable.rowCount()) for n in np.arange(0,len(self.colnames)-1): newitem = QTableWidgetItem(str(self.data[m,n])) self.dataTable.setItem(m,n,newitem) # Enable convert button self.convertPushButton.setEnabled(True) self.plotPushButton.setEnabled(False)
def summarize_sales(): """ Retrieve the account number and date ranges from the Excel sheet """ # Make a connection to the calling Excel file wb = Workbook.caller() # Retrieve the account number and dates account = Range('B2').value start_date = Range('D2').value end_date = Range('F2').value # Output the data just to make sure it all works Range('A5').value = account Range('A6').value = start_date Range('A7').value = end_date
def get_excel_formula_as_string(right_side_expression, time_period, variables): """ Using the right-hand side of a math expression (e.g. a(t)=a(t-1)*a_rate(t)), converted to sympy expression, and substituting the time index variable (t) in it, the function finds the Excel formula corresponding to the right-hand side expression. input ----- right_side_expression: sympy expression, e.g. a(t-1)*a_rate(t) time_period: value of time index variable (t) for time_periodtitution output: formula_string: a string of excel formula, e.g. '=A20*B21' """ right_dict = simplify_expression(right_side_expression, time_period, variables) for right_key, right_coords in right_dict.items(): excel_index = str( Range(get_sheet(), tuple(right_coords)).get_address(False, False)) right_side_expression = right_side_expression.subs( right_key, excel_index) formula_str = '=' + str(right_side_expression) return formula_str
def test_hyperlink(self): address = 'www.xlwings.org' # Naked address Range('A1').add_hyperlink(address) assert_equal(Range('A1').value, address) hyperlink = Range('A1').hyperlink if not hyperlink.endswith('/'): hyperlink += '/' assert_equal(hyperlink, 'http://' + address + '/') # Address + FriendlyName Range('A2').add_hyperlink(address, 'test_link') assert_equal(Range('A2').value, 'test_link') hyperlink = Range('A2').hyperlink if not hyperlink.endswith('/'): hyperlink += '/' assert_equal(hyperlink, 'http://' + address + '/')
def test_get_address(self): res = Range((1,1),(3,3)).get_address() assert_equal(res, '$A$1:$C$3') res = Range((1,1),(3,3)).get_address(False) assert_equal(res, '$A1:$C3') res = Range((1,1),(3,3)).get_address(True, False) assert_equal(res, 'A$1:C$3') res = Range((1,1),(3,3)).get_address(False, False) assert_equal(res, 'A1:C3') res = Range((1,1),(3,3)).get_address(include_sheetname=True) assert_equal(res, 'Sheet1!$A$1:$C$3') res = Range('Sheet2', (1,1),(3,3)).get_address(include_sheetname=True) assert_equal(res, 'Sheet2!$A$1:$C$3') res = Range((1,1),(3,3)).get_address(external=True) assert_equal(res, '[test_range_1.xlsx]Sheet1!$A$1:$C$3')
def to_excel(self): """ Create an Excel spreadsheet from the device's point histories. """ his = {} for name in list(self.points_name): try: his[name] = self._findPoint(name, force_read=False).history.replace( ['inactive', 'active'], [0, 1]).resample('1ms') except TypeError: his[name] = self._findPoint( name, force_read=False).history.resample('1ms') his['Notes'] = self.notes df = pd.DataFrame(his).fillna(method='ffill').fillna(method='bfill') if _XLWINGS: wb = Workbook() Range('A1').value = df else: df.to_csv()