def test1():
dbf = dbfpy.dbf.Dbf(dbfName, readOnly=True)
header_style = easyxf('font: name Arial, bold True, height 200;')
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
for (i, name) in enumerate(dbf.fieldNames):
sheet1.write(0, i, name, header_style)
for (i, thecol) in enumerate(dbf.fieldDefs):
name, thetype, thelen, thedec = str(thecol).split()
colwidth = max(len(name), int(thelen))
sheet1.col(i).width = colwidth * 310
for row in range(1, len(dbf)):
for col in range(len(dbf.fieldNames)):
sheet1.row(row).write(col, dbf[row][col])
fileName, extentsion = dbfName.split('.')
book.save(fileName + ".xls")
wb = open_workbook(fileName + ".xls")
save(wb, fileName + ".xls")
def test_save_stringio(self,c):
wb = object()
s = StringIO()
save(wb,s)
self.assertEqual(len(c.call_args_list),1)
args = c.call_args_list[0][0]
self.assertEqual(len(args),2)
r = args[0]
self.failUnless(isinstance(r,XLRDReader))
self.failUnless(r.wb is wb)
self.assertEqual(r.filename,'unknown.xls')
w = args[1]
self.failUnless(isinstance(w,StreamWriter))
self.failUnless(w.stream is s)
def test_save_stringio(self, c):
wb = object()
s = StringIO()
save(wb, s)
self.assertEqual(len(c.call_args_list), 1)
args = c.call_args_list[0][0]
self.assertEqual(len(args), 2)
r = args[0]
self.failUnless(isinstance(r, XLRDReader))
self.failUnless(r.wb is wb)
self.assertEqual(r.filename, 'unknown.xls')
w = args[1]
self.failUnless(isinstance(w, StreamWriter))
self.failUnless(w.stream is s)
def test_save_tempfile(self,c):
wb = object()
ef = TemporaryFile()
save(wb,ef)
self.assertEqual(len(c.call_args_list),1)
args = c.call_args_list[0][0]
self.assertEqual(len(args),2)
r = args[0]
self.failUnless(isinstance(r,XLRDReader))
self.failUnless(r.wb is wb)
self.assertEqual(r.filename,'unknown.xls')
w = args[1]
self.failUnless(isinstance(w,StreamWriter))
af = w.stream
self.failUnless(af is ef)
self.assertEqual(ef.closed,False)
def lists2excel(lists, output_filename, template_filename, sheetname = None, sheetindex = 0, offset = [0, 0]):
rb = open_workbook(template_filename, formatting_info = True)
#print rb.sheet_names()
if (sheetname != None):
worksheet = rb.sheet_by_name(sheetname)
else:
worksheet = rb.sheet_by_index(sheetindex)
for y, row in enumerate(lists):
for x,item in enumerate(row):
#print item, type(item)
if(type(item) is int):
worksheet.put_cell(y, x, XL_CELL_NUMBER, item, worksheet.cell_xf_index(y, x))
else:
worksheet.put_cell(y, x, XL_CELL_TEXT, item, worksheet.cell_xf_index(y, x))
save(rb, output_filename)
def test_save_tempfile(self, c):
wb = object()
ef = TemporaryFile()
save(wb, ef)
self.assertEqual(len(c.call_args_list), 1)
args = c.call_args_list[0][0]
self.assertEqual(len(args), 2)
r = args[0]
self.failUnless(isinstance(r, XLRDReader))
self.failUnless(r.wb is wb)
self.assertEqual(r.filename, 'unknown.xls')
w = args[1]
self.failUnless(isinstance(w, StreamWriter))
af = w.stream
self.failUnless(af is ef)
self.assertEqual(ef.closed, False)
def test_save_path(self,c,d):
wb = object()
path = os.path.join(d.path,'path.xls')
save(wb,path)
self.assertEqual(len(c.call_args_list),1)
args = c.call_args_list[0][0]
self.assertEqual(len(args),2)
r = args[0]
self.failUnless(isinstance(r,XLRDReader))
self.failUnless(r.wb is wb)
self.assertEqual(r.filename,'path.xls')
w = args[1]
self.failUnless(isinstance(w,StreamWriter))
f = w.stream
self.assertEqual(f.name, path)
self.assertEqual(f.mode, 'wb')
self.assertEqual(f.closed, True)
def test_save_path(self, c, d):
wb = object()
path = os.path.join(d.path, 'path.xls')
save(wb, path)
self.assertEqual(len(c.call_args_list), 1)
args = c.call_args_list[0][0]
self.assertEqual(len(args), 2)
r = args[0]
self.failUnless(isinstance(r, XLRDReader))
self.failUnless(r.wb is wb)
self.assertEqual(r.filename, 'path.xls')
w = args[1]
self.failUnless(isinstance(w, StreamWriter))
f = w.stream
self.assertEqual(f.name, path)
self.assertEqual(f.mode, 'wb')
self.assertEqual(f.closed, True)
def pfile(f_in, f_out):
from dbmis_connect2 import DBMIS
from xlrd import open_workbook # http://pypi.python.org/pypi/xlrd
from xlutils.save import save # http://pypi.python.org/pypi/xlutils
import time
localtime = time.asctime( time.localtime(time.time()) )
log.info('------------------------------------------------------------')
log.info('RVT Processing Start {0}'.format(localtime))
i1 = fname.find("VT22M")
if i1 < 0:
sout = "Wrong file name <{0}>".format(fname)
log.warn(sout)
return
s_mcod = fname[i1+5:i1+11]
mcod = int(s_mcod)
try:
mo = modb[mcod]
clinic_id = mo.mis_code
sout = "clinic_id: {0} MO Code: {1}".format(clinic_id, mcod)
log.info(sout)
except:
sout = "Clinic not found for mcod = {0}".format(s_mcod)
log.warn(sout)
return
dbc = DBMIS(clinic_id = clinic_id, mis_host = MIS_HOST, mis_db = MIS_DB)
cursor = dbc.con.cursor()
cname = dbc.name.encode('utf-8')
sout = "cod_mo: {0} clinic_id: {1} clinic_name: {2} ".format(mcod, clinic_id, cname)
log.info(sout)
inBook = open_workbook(f_in)
inSheets = inBook.sheet_names()
wshn0 = inSheets[0]
inSheet = inBook.sheet_by_name(wshn0)
rb = open_workbook(f_t, formatting_info=True)
r_sheet = rb.sheet_by_index(0) # read only copy to introspect the file
# copy header
value = inSheet.cell_value(0, 0)
cell = r_sheet.cell(0,0)
r_sheet.put_cell(0,0,cell.ctype,value,cell.xf_index)
# value = inSheet.cell_value(1, 0)
# cell = r_sheet.cell(1,0)
# r_sheet.put_cell(1,0,cell.ctype,value,cell.xf_index)
curr_row = 2
num_rows = inSheet.nrows - 1
out_row = 2
numb_in = 0
numb_out = 0
while curr_row < num_rows:
curr_row += 1
numb_in += 1
row = inSheet.row(curr_row)
# Cell Types: 0=Empty, 1=Text, 2=Number, 3=Date, 4=Boolean, 5=Error, 6=Blank
c1_type = inSheet.cell_type(curr_row, 0)
if c1_type != 2: continue
code = inSheet.cell_value(curr_row, 4)
people_id_s = inSheet.cell_value(curr_row, 11)
people_id = int(people_id_s)
if numb_in % STEP == 0:
sout = " {0} people_id: {1}".format(numb_in, people_id)
log.info(sout)
if not code in (53, 54, 57): continue
s_sqlt = """SELECT clinical_checkup_id
FROM clinical_checkups
WHERE (clinic_id_fk = {0}) AND (people_id_fk = {1});"""
s_sql = s_sqlt.format(clinic_id, people_id)
cursor.execute(s_sql)
rec = cursor.fetchone()
if rec == None: continue
out_row += 1
numb_out += 1
out_col = 0
for col in range(2,17):
in_cell = inSheet.cell(curr_row, col)
value = inSheet.cell_value(curr_row, col)
cell = r_sheet.cell(out_row,out_col)
try:
r_sheet.put_cell(out_row,out_col,in_cell.ctype,value,cell.xf_index)
except:
sout = "{0} put_cell error: ({1}, {2})".format(f_out, out_row, out_col)
log.warn(sout)
out_col += 1
save(rb,f_out)
sout = "Input lines number: {0}".format(numb_in)
log.info(sout)
sout = "Output lines number: {0}".format(numb_out)
log.info(sout)
dbc.close()
localtime = time.asctime( time.localtime(time.time()) )
log.info('VT Processing Finish '+localtime)
# 6: I61.0-I61.9, I62.0, I62.1, I62.9
if (((ds5 >= 'I61.0') and (ds5 <= 'I61.9')) \
or (ds5 in ('I62.0', 'I62.1', 'I62.9'))) \
and (p_count_arr[6] ==0):
p_count_arr[6] = 1
# 7: I63.0-I63.9
if (ds5 >= 'I63.0') and (ds5 <= 'I63.9') and (p_count_arr[7] ==0):
p_count_arr[7] = 1
# 8: I64
if (ds3 == 'I64') and (p_count_arr[8] ==0):
p_count_arr[8] = 1
# 9: J00-J98
if (ds3 >= 'J00') and (ds3 <= 'J98') and (p_count_arr[9] ==0):
p_count_arr[9] = 1
# 10: J12-J16, J18
if (((ds3 >= 'J12') and (ds3 <= 'J18')) \
or (ds3 == 'J18')) and (p_count_arr[10] ==0):
p_count_arr[10] = 1
save(rb,fout_fname)
dbc.close()
sys.exit(0)
import os
from xlrd import open_workbook, XL_CELL_NUMBER
from xlwt import easyxf
from xlutils.copy import copy # http://pypi.python.org/pypi/xlutils
from xlutils.save import save
print "start"
rb = open_workbook('mo_equipment.xls', formatting_info = True)
print rb.sheet_names()
worksheet = rb.sheet_by_name('NBG Devices')
worksheet.put_cell(0, 0, XL_CELL_NUMBER, 2.6, worksheet.cell_xf_index(0, 0))
save(rb,'mo_equipment_rd.xls')
wb = copy(rb)
w_sheet = wb.get_sheet(0)
#w_sheet.put_cell(0, 0, XL_CELL_NUMBER, 2.6, w_sheet.cell_xf_index(0, 0))
print type(w_sheet.row(0))
print type(worksheet.cell(0,0))
#._Row__cells.get(0))
#.xf_idx
#old = w_sheet.get_cell(0,0).xf_idx
w_sheet.write(0,0, 'ou yeah2')
#w_sheet._Worksheet__rows.get(0)._Row__cells.get(0).xf_idx = old
wb.save('mo_equipment_wt.xls')
def update():
# Config, filenames
key_list = [
'readypro_excel_filename', # TODO: more consistency in <filename> vs <fully qualified path> stuff
'mepa_excel_filename',
'new_excel_filename',
'target_path',
'login_url',
'user',
'user_css',
'password',
'password_css',
'login_butt_css',
'login_popup_css',
'file_input_css',
'upload_butt_css',
'catalogo_css',
'sostituzione_css'
]
mepa = Supplier(supplier_name, key_list)
print(mepa) # Title
[
readypro_excel_filename, mepa_excel_filename, new_excel_filename,
target_path, login_url, user, user_css, password, password_css,
login_butt_css, login_popup_css, file_input_css, upload_butt_css,
catalogo_css, sostituzione_css
] = mepa.val_list
# TODO: add some info prints
# Open and re-save excel file to fix mildly malformed files
readypro_xls = open_workbook(readypro_excel_filename)
save(readypro_xls, readypro_excel_filename + '.f')
# Copy data from generated spreadsheet to downloaded to-edit one
readypro_xls = open_workbook(readypro_excel_filename + '.f')
mepa_xls_model = open_workbook(mepa_excel_filename, formatting_info=True)
mepa_xls_new = copy(mepa_xls_model)
mepa_sheet = mepa_xls_new.get_sheet(1)
readypro_sheet = readypro_xls.sheet_by_index(
0) # get_sheet() throws an error
# TODO: avoid empty rows when an invalid entry is skipped
for i in range(1, readypro_sheet.nrows): # rows (skip header)
for j in range(0, 20): # cols
marca = False
for m in range(
14, 18): # Marca (A-D) = campo 14 (14 0-based with offset)
marca = marca or readypro_sheet.cell_value(i, m)
if marca and readypro_sheet.cell_value(
i, 1): # CodArt Produttore = campo 1
mepa_sheet.write(i, j, readypro_sheet.cell_value(
i, j + 1)) # offset read, column 0 is empty
mepa_xls_new.save(os.path.join(target_path, new_excel_filename))
chromedriver_autoinstaller.install()
options = webdriver.ChromeOptions()
options.add_argument('--headless')
options.add_argument('--window-size=3840,2160')
with webdriver.Chrome(options=options) as driver:
# Login
driver.get(login_url)
pop_up_butt = driver.find_element_by_css_selector(login_popup_css)
pop_up_butt.click()
user_input = driver.find_element_by_css_selector(user_css)
user_input.send_keys(user)
pass_input = driver.find_element_by_css_selector(password_css)
pass_input.send_keys(password)
login_butt = driver.find_element_by_css_selector(login_butt_css)
login_butt.click()
catalogo_butt = WebDriverWait(driver, 5).until(
ec.element_to_be_clickable((By.CSS_SELECTOR, catalogo_css)))
catalogo_butt.click()
sost_butt = WebDriverWait(driver, 5).until(
ec.presence_of_element_located(
(By.CSS_SELECTOR, sostituzione_css)))
sost_butt.click()
file_input = WebDriverWait(driver, 10).until(
ec.presence_of_element_located((By.CSS_SELECTOR, file_input_css)))
file_input.send_keys(os.path.join(target_path, new_excel_filename))
upload_butt = WebDriverWait(driver, 5).until(
ec.presence_of_element_located((By.CSS_SELECTOR, upload_butt_css)))
upload_butt.click()
time.sleep(
10
) # dirty, switch with something that checks portal output message
s_sql = SQLT_CLINIC.format(clinic_id)
cursor.execute(s_sql)
rec = cursor.fetchone()
if rec is None:
sout = "Clinic {0} has not got clinic_key".format(clinic_id)
log.warn(sout)
continue
key = rec[0]
s_sql = SQLT_USER1.format(clinic_id, key)
cursor.execute(s_sql)
recs = cursor.fetchall()
n1 = 0
for rec in recs:
user_id = rec[0]
s_sql = SQLT_USER2.format(user_id)
cursor2.execute(s_sql)
results = cursor2.fetchall()
nn = len(results)
if nn == 1: n1 += 1
r_sheet.put_cell(i,4,2,n1,cell1.xf_index)
sout = "{0} key: {1} id: {2} n1: {3}".format(i, key, clinic_id, n1)
log.info(sout)
save(rb,FOUT)
dbc.close()
sys.exit(0)
