def display_basic_info(self):
cell1 = self.sheet.range('A1')
cell1.value = 'Report'
cell1.api.font.bold = True
cell1.api.font.color = rgb_to_int((255, 255, 255))
cell1.color = rgb_to_int((153, 0, 204))
self.sheet.range('B1').formula = '=TODAY()'
def thread_run(arg_dict: dict):
ticker = arg_dict.get("ticker")
index = arg_dict.get("index")
stock = LiveStock(stock_ticker=ticker)
stock.update_stock(ticker)
stock_vals = [
"price", "bid", "ask", "regularMarketDayLow", "regularMarketDayHigh", "regularMarketOpen", "regularMarketVolume"
]
tmp_char = "B"
try:
for i, type_val in enumerate(stock_vals):
xw.Book(arg_dict.get("file_name")).sheets[arg_dict.get("name")].range(f"B{index}").api.Font.Color = \
[rgb_to_int((107, 142, 35)), rgb_to_int((139, 0, 0))][getattr(stock, "color") == "red"]
xw.Book(arg_dict.get("file_name")).sheets[arg_dict.get("name")].range(f"{chr(ord(tmp_char) + i)}{index}").value = [
getattr(stock, type_val)
]
if xw.Book(arg_dict.get("file_name")).sheets[arg_dict.get("name")].range(f"M{index}").value is True:
mail_content = [str(obj) for obj in
xw.Book(arg_dict.get("file_name")).sheets[arg_dict.get("name")].range(f"K{index}:L{index}").options(
numbers=int).value]
dst_address = xw.Book(arg_dict.get("file_name")).sheets[arg_dict.get("name")].range(f"{MAIL_COL}2").value
if dst_address is not None:
send_mail(getattr(stock, 'displayName'), ' '.join(mail_content), dst_address)
xw.Book(arg_dict.get("file_name")).sheets[arg_dict.get("name")].range(f"K{index}:M{index}").clear_contents()
xw.Book(arg_dict.get("file_name")).sheets[arg_dict.get("name")].range(f"{MAIL_COL}2").color = (255, 255, 255)
elif dst_address is None:
xw.Book(arg_dict.get("file_name")).sheets[arg_dict.get("name")].range(f"{MAIL_COL}2").color = (139, 0, 0)
interval_date = xw.Book(arg_dict.get("file_name")).sheets[arg_dict.get("name")].range(f"I{index}:J{index}").value
# move it to object oriented
if interval_date[0]:
df_stock_interval = StockInterval().get_interval(tk=ticker, interval=interval_date[0], period=interval_date[1])
shape_len = df_stock_interval.shape[1] + 1
col = ((index-INDEX_TO_START_STOCK_VAL) * (shape_len + 1))
TK_NAME_LINE = 1
STOCK_INTERVAL_LINE = 2
xw.Book(arg_dict.get("file_name")).sheets[INTERVAL_SHEET].range(
(TK_NAME_LINE, col + 2)).value = getattr(stock, 'displayName') if hasattr(stock, 'displayName') else ticker
xw.Book(arg_dict.get("file_name")).sheets[INTERVAL_SHEET].range(
(TK_NAME_LINE, col + 2)).color = ORANGE_HEADER
xw.Book(arg_dict.get("file_name")).sheets[INTERVAL_SHEET].range(
(STOCK_INTERVAL_LINE, col + 2)).value = df_stock_interval
xw.Book(arg_dict.get("file_name")).sheets[INTERVAL_SHEET].range(
(STOCK_INTERVAL_LINE, col + 2), (STOCK_INTERVAL_LINE, col + 1 + shape_len)).color = BLUE_HEADER
xw.Book(arg_dict.get("file_name")).sheets[arg_dict.get("name")].range(f"I{index}:J{index}").clear_contents()
except Exception as ex:
return None
class Color:
GLOBAL_INPUT_TAB = 3243501
INPUT = 11389944
CALCULATION = 15652797
INDEX = 12566463
TXL = utils.rgb_to_int((53, 81, 133))
BACKGROUND = utils.rgb_to_int((237, 242, 244))
LIGHT_TEXT = BACKGROUND
GREEN = utils.rgb_to_int((196, 215, 155))
USED_IDENTIFIER = GREEN
def insert_chart(self, source_data, chart_type, switch_row_col,
chart_title):
chart = self.sheet.charts.add()
chart.set_source_data(source_data)
chart.chart_type = chart_type
if switch_row_col == True:
chart.api[1].plotby = 1
else:
chart.api[1].plotby = 2
chart.left = source_data.left
chart.top = source_data.top
chart.width = min(max(500, source_data.width), 1000)
chart.height = min(source_data.height * 3, 400)
chart.api[1].setelement(2)
title_set = chart.api[1].charttitle
title_set.text = chart_title
title_set.font.bold = False
title_set.font.color = rgb_to_int((153, 0, 255))
chart.api[1].legend.font.size = 12
chart.name = chart_title
self.position = (int(
(chart.top + chart.height) / self.cell_height) + 3, 1)
def WritSheet(sheetName,df,idx=False):
"""寫入Excel"""
#檢查Sheet 是否存在
flag=False
for i in range(wb.sheets.count):
if wb.sheets[i].name==sheetName:
flag=True
wb.sheets[i].clear()
break
if not flag :
ws=wb.sheets.add(sheetName)
ws=wb.sheets[sheetName]
ws.range("A1").options(index=idx).value=df
#調整表頭顏色
ws.range(ws.range('A1').expand('right').address).color = rgb_to_int((0, 73, 134))
ws.range(ws.range('A1').expand('right').address).api.Font.Color = rgb_to_int((255, 255, 255))
def show_status_message(sheet, coords, message, background=None, color=None):
"""Show a status message in an Excel spreadsheet.
Args:
sheet (Sheet): The sheet where the status message will be written.
coords (str): The location of the status message.
message (str): A status message to write to Excel.
background (tuple): Optional background color.
color (tuple): Optional font color.
"""
sheet.range(coords).value = message
if background:
sheet.range(coords).color = literal_eval(background)
if color:
sheet.range(coords).api.Font.Color = rgb_to_int(literal_eval(color))
import xlwings as xw
from xlwings.utils import rgb_to_int
import datetime as dt
import pandas as pd
from xlwingsfunctions.basicfunctions import *
from pandasfunctions.pandastringmanip import *
import numpy as np
# creating a dataframe
sht = xw.Book("sample.xlsx").sheets["Sheet1"]
sht.range("A2,A5").api.Font.Color = rgb_to_int((205, 92, 92))
# https://www.toogit.com/proposals/create/need-to-marry-information-onto-1-spreadsheet
import xlwings as xw
from xlwings.utils import rgb_to_int
import pandas as pd
import numpy as np
import os
dir_path = os.path.dirname(os.path.realpath(__file__))
current_directory = "\\".join(dir_path.split("\\"))
df1 = pd.read_csv(os.path.join(current_directory, "data1.csv"))
df2 = pd.read_csv(os.path.join(current_directory, "data2.csv"))
common = df1.merge(df2, on=['Order#', 'Cost'])
highlight = df2[(~df2["Order#"].isin(common["Order#"]))].index.values
newhighlight = ["A" + str(i + 2) for i in highlight]
a1notation = ",".join(newhighlight)
df2.to_excel(os.path.join(current_directory, "df2.xlsx"),
sheet_name="df2",
index=False)
sht = xw.Book(os.path.join(current_directory, "df2.xlsx")).sheets["df2"]
sht.range(a1notation).api.Font.Color = rgb_to_int((205, 92, 92))
#sht.range("A1").value = df
sht.api.rows('1:1').insert #新增第一ROW當作column heading
sht.range('a1').value = ['seq', 'name_object']
#sht2.api.rows('1:1').insert #新增第一ROW當作column heading
sht2.range('a1').value = [
'seq', 'name_sql', 'name_createtable', 'label_table', 'primary_key'
]
sht3.range('a1').value = [
'seq', 'name_sql', 'name_createtable', 'name_column', 'datatype'
]
rng = sht.range('a1:z1')
rng.color = (180, 198, 230)
rng.api.Font.Color = rgb_to_int((20, 20, 255))
rng = sht2.range('a1:z1')
rng.color = (180, 198, 230)
rng.api.Font.Color = rgb_to_int((20, 20, 255))
rng = sht3.range('a1:z1')
rng.color = (180, 198, 230)
rng.api.Font.Color = rgb_to_int((20, 20, 255))
# sht.range('A1:A2').api.merge #合并单元格
# 返回和设置当前格子的高度和宽度
#print(rng.width)
#print(rng.height)
#rng.row_height=40
def ChangeColor(ws,RowCel,FontColor,BakColor):
#調整表頭顏色,背景與自行顏色
print(RowCel)
ws.range(ws.range(RowCel).expand('right').address).color = rgb_to_int(BakColor)
ws.range(ws.range(RowCel).expand('right').address).api.Font.Color = rgb_to_int(FontColor)
def main():
global py_filename_without_extension
global py_filename
global xl_filename
global active_devices
global sht_maqlab_config
py_filename = os.path.basename(__file__)
# check .py extension
py_filename_without_extension = py_filename.split(".")[0:-1][0]
# find the excel file in the current dir
# we are looking for xlsx and xlsm extension
# as result we get the first occurrence of one of this
# excel-files, so make sure that you will not have both
# of them in the directory
files = os.listdir(os.path.dirname(__file__))
for f in files:
fn = f.split(".")[0:-1][0]
ex = f.split(".")[-1:][0]
if fn == py_filename_without_extension:
if ex == 'xlsx' or ex == 'xlsm':
xl_filename = f
break
# print("UDF-Server started")
# print("Filepath:", __file__)
# print("Python-Filename:", py_filename)
# print("Excel-Filename:", xl_filename)
'''
source.range('A1').expand().clear_contents()
source.range('A1').value = cursor.fetchall()
'''
wb = xw.Book.caller()
try:
wb.sheets.add("maqlab.conf", "xlwings.conf")
except:
pass
try:
sht_maqlab_config = wb.sheets["maqlab.conf"]
except:
sht_maqlab_config = None
sht = wb.sheets["Automatic"]
sht.api.OLEObjects("Command1").Object.Visible = True # nur test
sht.api.OLEObjects("MessageBox").Object.Visible = True # nur test
sht.range(status_connection_cell_address).color = xwu.rgb_to_int(
(200, 200, 200)) # cell color
sht.range(status_connection_cell_address).api.Font.Color = xwu.rgb_to_int(
(0, 0, 0)) # font color of text
if maqlab is not None and maqlab.is_connected:
sht.range(status_connection_cell_address).color = xwu.rgb_to_int(
(0, 200, 10)) # cell color
sht.range(
status_connection_cell_address).api.Font.Color = xwu.rgb_to_int(
(0, 0, 0)) # font color of text
sht.range(status_connection_cell_address).value = "Connected"
else:
sht.range(status_connection_cell_address).color = xwu.rgb_to_int(
(200, 0, 10)) # cell color
sht.range(
status_connection_cell_address).api.Font.Color = xwu.rgb_to_int(
(255, 255, 255)) # font color of text
sht.range(
status_connection_cell_address).value = "MAQlab not Connected"
return
reload()
def measure(t, count):
global stop_thread
global accessnr
global wertzahl
# accessing the sheet with index 0
sht = xw.Book(xl_filename).sheets[0] # sheet 0 = 1.Tabelle
# Status zelle Text ändern und umfärben
sht.range(status_measure_cell_address).api.Font.Color = xwu.rgb_to_int(
(0, 0, 0)) # font color of text
sht.range(status_measure_cell_address).color = xwu.rgb_to_int(
(0, 200, 10)) # cell color
sht.range(status_measure_cell_address).value = "Messung läuft" # cell text
print("Thread Running...")
i = 0
while i < count:
print(i)
sht.range("J5").value = str(i)
sht.range("J6").value = str(count)
# Index der Messung in Spalte A (=1)
# das ist eine weitere Möglichkeit eine Zelle zu adressieren
# ( Zeilennummer, Spaltennummer)
cell = (i + 3, 1)
sht.range(cell).value = str(i)
# vorherige Zelle färben
if i > 0:
sht.range(i + 2, 1).color = xwu.rgb_to_int((100, 100, 100))
# actuelle Zelle färben
sht.range(cell).color = xwu.rgb_to_int((0, 200, 10))
# -------------------------------------------
global run_once
if run_once:
run_once = False
Voltage = 0
Current = 0
Zeile = 3
# sht.range('E32').value = [['TabKopfX', 'TabKopfY'], [1, 2], [10, 20]]
# sht.range('C22').value = 'Verfuegbar'
# sht.range('C23').value = active_devices
accessnr = sht.range('N11').value
accessnr = int(accessnr)
# ----------------------------------------------------------------------
command = str(sht["N14"].value)
print("Sending " + command + " to " + str(accessnr))
wertzahl = maqlab.send_and_receive(accessnumber=accessnr,
command=command).payload
print("Received " + wertzahl)
# convert into real value from string with unit
# format is for instance "0.543 VDC"
wertzahl = re.sub("[A-Za-z ]", "", wertzahl)
cell = (i + 16, 14)
sht.range(cell).value = wertzahl
# ----------------------------------------------------------------------
accessnr = sht.range('O11').value
accessnr = int(accessnr)
command = str(sht["O14"].value)
print("Sending " + command + " to " + str(accessnr))
wertzahl = maqlab.send_and_receive(accessnumber=accessnr,
command=command).payload
print("Received " + wertzahl)
# convert into real value from string with unit
# format is for instance "0.543 VDC"
wertzahl = re.sub("[A-Za-z ]", "", wertzahl)
cell = (i + 16, 15) # COL O is 15
sht.range(cell).value = wertzahl
# ------------------------------------------------------------------------
# timing
timer = 0
while timer < t:
if stop_thread:
break
time.sleep(0.01)
timer = timer + 0.01
if stop_thread:
stop_thread = False
break
i += 1 # nächste Zeile
# Statuszelle Text ändern und umfärben
sht.range(status_measure_cell_address).api.Font.Color = xwu.rgb_to_int(
(255, 255, 255))
sht.range(status_measure_cell_address).color = xwu.rgb_to_int(
(200, 10, 10))
sht.range(status_measure_cell_address).value = "Messung fertig"
print("Thread Stopped")
wb = xw.books.add() #新建一张workbook
sht = wb.sheets.add(name="Data") #新建一张sheet
wb.app.visible = True
# %%
sht.range('A1').value = 1
sht.range('A1').value = 2
sht.range('C1').formula = "=A1+C1"
sht.range('C1').formula = "=A1+C1"
# %%
xw.sheets[0].range('A1:A5').value = [[i] for i in range(5)]
xw.sheets[0].range('B1:C5').formula = [['=A1+1', '=B1*10']]
xw.sheets[0].range('A1:C5').value
#Cell Color
xw.sheets[0].range('A1:C5').api.Font.Color = rgb_to_int((20, 20, 255))
xw.sheets[0].range('B1:C1').color = rgb_to_int((20, 20, 255))
#%%
# formula
ws = xw.sheets[0]
rng_to_paste = ws.range('C1').options(ndim=1).formula
ws.range('D1').options(ndim=1).formula = rng_to_paste
# %%
# 加入超鏈接
sht = xw.sheets[1]
sht.range('A1').value = [[1, 2], [3, 4]]
rng1 = sht.range('A1').options(expand='table', transpose=True).value
rng1 = sht.range('B1')
rng1.add_hyperlink('www.baidu.com', '百度', '提示：點擊即鏈接到百度')