115 lines
3.9 KiB
Python
115 lines
3.9 KiB
Python
import os
|
|
import fdb
|
|
import csv,time,pandas as pd
|
|
import openpyxl
|
|
|
|
|
|
PathToSaveCSV=r"u:\NextCloudOrdinace\Reporty\testy"
|
|
timestr = time.strftime("%Y-%m-%d %H-%M-%S ")
|
|
CSVname="Výkony pojišťovna.xlsx"
|
|
# PathToSaveCSV=r"//tower/tempspeed"
|
|
|
|
con = fdb.connect(
|
|
host='localhost', database=r'u:\MEDICUS 3\data\medicus.FDB',
|
|
user='sysdba', password='masterkey',charset='WIN1250')
|
|
|
|
# Create a Cursor object that operates in the context of Connection con:
|
|
cur = con.cursor()
|
|
|
|
# import openpyxl module
|
|
import openpyxl
|
|
import xlwings as xw
|
|
wb = openpyxl.Workbook()
|
|
sheet = wb.active
|
|
# wb.save("sample.xlsx")
|
|
|
|
|
|
#Načtení dokladů pojišťovny
|
|
# cur.execute("select davka, rok, typ, poj,dokladh.rodcis,prijmeni||', '||jmeno, datose,kod,pocvyk,ddgn from dokladh "
|
|
# "join dokladd on dokladd.idhlav=dokladh.idhlav "
|
|
# "join kar on kar.rodcis=dokladh.rodcis "
|
|
# " order by datose desc;") # where dokladh.rodcis=7401270052 Schořálek
|
|
cur.execute("select davka, CAST(rok AS FLOAT) as rok, dokladh.typ, dokladh.poj,dokladh.rodcis,kar.prijmeni||', '||kar.jmeno as jmenokomplet,datose,kod,pocvyk,ddgn from dokladh "
|
|
"join dokladd on dokladh.idhlav=dokladd.idhlav "
|
|
"join kar on kar.rodcis=dokladh.rodcis "
|
|
"order by datose desc;") # Schořálek where dokladh.rodcis=7401270052
|
|
|
|
nacteno=cur.fetchall()
|
|
pocet_radku=len(nacteno)
|
|
print(len(nacteno))
|
|
|
|
sheet.title="Doklady pojišťovny"
|
|
sheet.append(["Dávka","Rok","Typ","Pojišťovna","Rodné číslo","Jméno","Datum výkonu","Kód","Počet","Dg."])
|
|
#nacteno jsou výkony pro pojišťovnu
|
|
print("Zapisuji data do excelu")
|
|
for row in nacteno:
|
|
sheet.append(row)
|
|
wb.save(os.path.join(PathToSaveCSV ,timestr+CSVname))
|
|
|
|
# autofilter
|
|
print("Aplikuji autofilter")
|
|
for ws in wb.worksheets:
|
|
# Get the maximum number of rows and columns
|
|
max_row = ws.max_row
|
|
max_column = ws.max_column
|
|
ws.auto_filter.ref = f"A1:{openpyxl.utils.get_column_letter(max_column)}{max_row}"
|
|
# ws.auto_filter.ref = ws.dimensions
|
|
|
|
print("Aplikuji format datumu dd.mm.yyyy")
|
|
for row in ws.iter_rows(min_col=6, max_col=6):
|
|
for cell in row:
|
|
if cell.value: # Only format cells with values
|
|
cell.number_format = 'DD.MM.YY'
|
|
|
|
print("aplikuji ohraničení")
|
|
from openpyxl.styles import Border,Side,Alignment,PatternFill,Font,numbers
|
|
#Definition of border
|
|
thin_border = Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))
|
|
#Definition of yellow fill
|
|
yellow_fill = PatternFill(start_color='FFFF00', # Yellow color code
|
|
end_color='FFFF00',
|
|
fill_type='solid')
|
|
|
|
|
|
min_row=1
|
|
max_row=pocet_radku+1
|
|
min_column=1
|
|
max_column=10
|
|
for row in ws.iter_rows(min_row=min_row,max_row=max_row,min_col=min_column,max_col=max_column): #(min_row=1, max_col=3, max_row=2):
|
|
for cell in row:
|
|
cell.border = Border(left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin'))
|
|
cell.alignment=Alignment(horizontal='center', vertical='center')
|
|
#Aplikace yellow line a bold jen na první řádek
|
|
if cell.row==1:
|
|
cell.fill=yellow_fill
|
|
cell.font = Font(bold=True)
|
|
# if cell.column in (2,4,5,7): #Aplikuji číselný formát '0'
|
|
# cell.number_format = '0'
|
|
if cell.column in (2,4,5,8): #Aplikuji textový formát '@'
|
|
cell.number_format = numbers.FORMAT_TEXT
|
|
cell.value=cell.value
|
|
|
|
|
|
|
|
wb.save(os.path.join(PathToSaveCSV ,timestr+CSVname))
|
|
|
|
|
|
|
|
|
|
# Tento modul je pouze na autofit jednotlivych sloupcu na vsech listech workbooku
|
|
print("Aplikuji autofit")
|
|
file = os.path.join(PathToSaveCSV ,timestr+CSVname)
|
|
with xw.App(visible=False) as app:
|
|
wb = xw.Book(file)
|
|
for sheet in range(len(wb.sheets)):
|
|
ws = wb.sheets[sheet]
|
|
ws.autofit()
|
|
|
|
wb.save()
|
|
wb.close()
|
|
|
|
|