Skip to content

Instantly share code, notes, and snippets.

@gdemir

gdemir/README.txt

Last active August 15, 2023 13:44
Show Gist options
  • Save gdemir/5fb050822ae919ad6674 to your computer and use it in GitHub Desktop.
Save gdemir/5fb050822ae919ad6674 to your computer and use it in GitHub Desktop.
Download ZIP
PDKS-excel üzerinde işlemler, excel ve pdf'e dökme
Raw
README.txt
# README
source : https://bobbyhadz.com/blog/choco-is-not-recognized-as-internal-or-external-command
# run powershell
[BAŞLAT + R]
Powershell yönetici olarak çalıştır
# choco installer install
Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1'))
# curl install
choco install curl -y
# pip install
curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py
cd C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310\
python.exe get-pip.py
# autopytoexe.exe install
cd C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310\Scripts
pip.exe install auto-py-to-exe
# autopytoexe.exe run
cd C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310\
python.exe C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310\Scripts\autopytoexe.exe
python.exe -m C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310\Scripts\autopytoexe.exe
cd C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310\output\yazici_bul.exe
# run auto-py-to-exe.exe
C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310>python.exe Scripts\auto-py-to-exe.exe
C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310>python.exe -m Scripts\auto-py-to-exe.exe
Raw
pdks-ct-p.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Alignment, Border, Font, Side, Style, PatternFill, Color
from openpyxl.cell import get_column_letter
import datetime
import os, pipes
PDF_DIR = "pdf"
EXCEL_DIR = "excel"
if not os.path.isdir(os.path.join(os.getcwd(), EXCEL_DIR)):
os.mkdir(os.path.join(os.getcwd(), EXCEL_DIR))
if not os.path.isdir(os.path.join(os.getcwd(), PDF_DIR)):
os.mkdir(os.path.join(os.getcwd(), PDF_DIR))
# OKUMA ##########################################################################################
def excel_to_hash(xlsx_filename):
departments = []
# example departments
# departments = [{
# "name" : "Bilgi İşlem",
# "users" : [{
# "first_name" : "Gökhan",
# "last_name" : "Demir",
# "days" : [{ "date" : "2015-01-01", "i": "08:08:00", "o" : "08:08:08" }]
# }]
# }]
wb = load_workbook(filename=xlsx_filename, read_only=True)
ws = wb.active
user_state = False
department = ""
for row in ws.rows:
if user_state == False:
if row[3].value is not None: # Bölüm ismini belirle yoksa ekle
if "Bölüm" in row[3].value.encode(encoding = 'UTF-8', errors = 'strict').split():
department_name = row[3].value[8:].encode(encoding = 'UTF-8', errors = 'strict')
department_search_state = False # Önceki kayıtlarda böyle bir bölüm kaydı var mı ?
if departments != []:
for department in departments:
if department["name"] == department_name:
department_search_state = True
break
if not department_search_state:
departments.append({ "name" : department_name })
continue
if row[1].value == "Ad": # Ad ise kişi bilgisi başladı
user_state = True
continue
if user_state == True:
if row[9].value is not None: # string ise ve Toplam Süre ise kişi bilgisi bitti
if row[9].value.encode(encoding = 'UTF-8', errors = 'strict') == "Toplam Süre":
user_state = False
continue
if row[1].value is not None and row[1].value != "Ad":
for department in departments:
if department["name"] == department_name:
if "users" in department.keys(): # Bölüm'de hiç kullanıcı var mı ?
user_search_state = False
for user in department["users"]:
if user["first_name"] == row[1].value and user["last_name"] == row[4].value:
user_search_state = True
user["days"].append({ "date" : row[5].value, "i" : row[7].value, "o" : row[8].value })
break
if not user_search_state:
department["users"].append({
"first_name" : row[1].value,
"last_name" : row[4].value,
"days" : [{ "date" : row[5].value, "i": row[7].value, "o" : row[8].value }]
})
else:
department["users"] = [{
"first_name" : row[1].value,
"last_name" : row[4].value,
"days" : [{ "date" : row[5].value, "i": row[7].value, "o" : row[8].value }]
}]
break
return departments
# YAZMA ##########################################################################################
def hash_to_excel_departments_io(departments):
print ">>> EXCEL Yazma işlemleri başlıyor"
# general text styles
text_border = Border(left = Side(style = 'thin'), right = Side(style = 'thin'), top = Side(style = 'thin'), bottom = Side(style = 'thin'))
text_aligment = Alignment(horizontal = 'center', vertical = 'center')
# comment colors and background colors
comment_colors = {
"danger" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('dca7a7')),
"textcolor" : Font(name = 'Calibri', size = 11, bold = True, color = 'a94442')
},
"warning" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('fcf8e3')),
"textcolor" : Font(name = 'Calibri', size = 11, bold = True, color = '8a6d3b')
},
"success" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('ffffff')),
"textcolor" : Font(name = 'Calibri', size = 11, bold = True, color = '000000')
}
}
# header
_header = ["Ad", "Soyad", "Tarih", "Giriş Saati", "Giriş Durumu", "Çıkış Saati", "Çıkış Durumu", "İzin Durum"]
column_count = len(_header)
# column and row width
COLUMN_WIDTHS = [ 22, 20, 11, 9, 16, 9, 16, 10 ]
ROW_WIDTH = 16.2 # 14 x 16.2 = 226.8 page height
# excel filenames for pdf
excel_filenames = []
for department in departments:
wb = Workbook()
ws = wb.active
# title
ws.merge_cells(start_row = 1, start_column = 1, end_row = 4, end_column = column_count)
title = ws['A1']
title.value = department["name"]
title.font = Font(name = 'Calibri', size = 14, bold = True, color = 'ffffff')
title.alignment = text_aligment
title.fill = PatternFill(patternType = 'solid', fgColor = Color('0099ff'))
# header font and styles
for col in range(1, column_count + 1):
ws.cell(row = 5, column = col).value = _header[col - 1]
ws.cell(row = 5, column = col).font = Font(name = 'Calibri', size = 8, bold = True, color = 'ffffff')
ws.cell(row = 5, column = col).border = text_border
ws.cell(row = 5, column = col).alignment = text_aligment
ws.cell(row = 5, column = col).fill = PatternFill(patternType = 'solid', fgColor = Color('3072b3'))
row = 6
department_users = department["users"]
# department_users = sorted(department["users"], key = lambda k: k['first_name']) # sort hash list
for user in department_users:
first_row = row
for day in user["days"]:
if day["i"] is not None:
i_time = day["i"].strftime("%H:%M")
i_hour = int(day["i"].strftime("%H"))
i_minute = int(day["i"].strftime("%M"))
if (i_hour < 7) or (i_hour == 7 and i_minute <= 45):
# i_color_key = "warning"
# i_comment = "Erken Geldi"
i_color_key = "success"
i_comment = ""
elif (i_hour > 8) or (i_hour == 8 and i_minute >= 15):
i_color_key = "warning"
i_comment = "Geç Geldi"
else:
i_color_key = "success"
i_comment = "" # Sorun yok
else:
i_time = ""
i_color_key = "danger"
i_comment = "Kart Kullanmadı"
if day["o"] is not None:
o_time = day["o"].strftime("%H:%M")
o_hour = int(day["o"].strftime("%H"))
o_minute = int(day["o"].strftime("%M"))
if (o_hour < 16) or (o_hour == 16 and o_minute <= 59):
o_color_key = "warning"
o_comment = "Erken Çıktı"
elif (o_hour > 17) or (o_hour == 17 and o_minute >= 15):
# o_color_key = "warning"
# o_comment = "Geç Çıktı"
o_color_key = "success"
o_comment = ""
else:
o_color_key = "success"
o_comment = "" # Sorun yok
else:
o_time = ""
o_color_key = "danger"
o_comment = "Kart Kullanmadı"
ws.append([user["first_name"], user["last_name"], day["date"].strftime("%d-%m-%Y"), i_time, i_comment, o_time, o_comment, ""])
# color set for comments
i_color = comment_colors[i_color_key]
ws.cell(row = row, column = 5).fill = i_color["bgcolor"]
ws.cell(row = row, column = 5).font = i_color["textcolor"]
o_color = comment_colors[o_color_key]
ws.cell(row = row, column = 7).fill = o_color["bgcolor"]
ws.cell(row = row, column = 7).font = o_color["textcolor"]
for col in range(1, column_count + 1):
ws.cell(row = row, column = col).border = text_border
ws.cell(row = row, column = col).alignment = text_aligment
row = row + 1
# ad soyad | row : merge and center
ws.merge_cells(start_row = first_row, end_row = row - 1, start_column = 1, end_column = 1)
ws.merge_cells(start_row = first_row, end_row = row - 1, start_column = 2, end_column = 2)
# EXCEL column with
for col, width in enumerate(COLUMN_WIDTHS):
ws.column_dimensions[get_column_letter(col + 1)].width = width
# EXCEL row height
for row in range(1, len(ws.rows)):
ws.row_dimensions[row].height = ROW_WIDTH
# PRINT auto only page
# ws.page_setup.orientation = ws.ORIENTATION_PORTRAIT
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
ws.page_setup.horizontalCentered = True
ws.page_setup.verticalCentered = True
filename = os.path.join(os.getcwd(), EXCEL_DIR, department["name"] + ".xlsx")
print "-" + filename
wb.save(filename = filename)
excel_filenames.append(filename)
return excel_filenames
def excel_to_pdf(excel_filenames):
print ">>> PDF yazma işlemleri başlıyor"
os.chdir(PDF_DIR)
for excel_filename in excel_filenames:
filename = pipes.quote(excel_filename)
os.system("libreoffice -headless -convert-to pdf " + filename)
# os.system("soffice --headless --convert-to pdf:\"impress_pdf_Export\"" + filename)
departments = excel_to_hash('pdks.xlsx')
excel_filenames = hash_to_excel_departments_io(departments)
excel_to_pdf(excel_filenames)
Raw
pdks-for-windows.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Font, Side, Style, PatternFill, Color
from openpyxl.cell import get_column_letter
import inspect
import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook, FileFormatType, PdfSaveOptions
import datetime
import os, pipes
PDF_DIR = "pdf"
EXCEL_DIR = "excel"
if not os.path.isdir(os.path.join(os.getcwd(), EXCEL_DIR)):
os.mkdir(os.path.join(os.getcwd(), EXCEL_DIR))
if not os.path.isdir(os.path.join(os.getcwd(), PDF_DIR)):
os.mkdir(os.path.join(os.getcwd(), PDF_DIR))
# OKUMA ##########################################################################################
def excel_to_hash(xlsx_filename):
departments = []
# example departments
# departments = [{
# "name" : "Bilgi İşlem",
# "users" : [{
# "first_name" : "Gökhan",
# "last_name" : "Demir",
# "days" : [{ "date" : "2015-01-01", "i": "08:08:00", "o" : "08:08:08" }]
# }]
# }]
wb = load_workbook(filename=xlsx_filename, read_only=True)
ws = wb.active
user_state = False
department = ""
for row in ws.rows:
if user_state == False:
if row[3].value is not None: # Bölüm ismini belirle yoksa ekle
if "Bölüm" in row[3].value.split():
department_name = row[3].value[8:]
department_search_state = False # Önceki kayıtlarda böyle bir bölüm kaydı var mı ?
if departments != []:
for department in departments:
if department["name"] == department_name:
department_search_state = True
break
if not department_search_state:
departments.append({ "name" : department_name })
continue
if row[1].value == "Ad": # Ad ise kişi bilgisi başladı
user_state = True
continue
if user_state == True:
if row[9].value is not None: # string ise ve Toplam Süre ise kişi bilgisi bitti
if row[9].value == "Toplam Süre":
user_state = False
continue
if row[1].value is not None and row[1].value != "Ad":
for department in departments:
if department["name"] == department_name:
if "users" in department.keys(): # Bölüm'de hiç kullanıcı var mı ?
user_search_state = False
for user in department["users"]:
if user["first_name"] == row[1].value and user["last_name"] == row[4].value:
user_search_state = True
user["days"].append({ "date" : row[5].value, "i" : row[7].value, "o" : row[8].value })
break
if not user_search_state:
department["users"].append({
"first_name" : row[1].value,
"last_name" : row[4].value,
"days" : [{ "date" : row[5].value, "i": row[7].value, "o" : row[8].value }]
})
else:
department["users"] = [{
"first_name" : row[1].value,
"last_name" : row[4].value,
"days" : [{ "date" : row[5].value, "i": row[7].value, "o" : row[8].value }]
}]
break
return departments
# YAZMA ##########################################################################################
def hash_to_excel_departments_io(departments):
print(">>> EXCEL Yazma işlemleri başlıyor")
# general text styles
text_border = Border(left = Side(style = 'thin'), right = Side(style = 'thin'), top = Side(style = 'thin'), bottom = Side(style = 'thin'))
text_aligment = Alignment(horizontal = 'center', vertical = 'center')
# comment colors and background colors
comment_colors = {
"danger" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('dca7a7')),
"textcolor" : Font(name = 'Calibri', size = 11, bold = True, color = 'a94442')
},
"warning" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('fcf8e3')),
"textcolor" : Font(name = 'Calibri', size = 11, bold = True, color = '8a6d3b')
},
"success" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('ffffff')),
"textcolor" : Font(name = 'Calibri', size = 11, bold = True, color = '000000')
}
}
# header
_header = ["Ad", "Soyad", "Tarih", "Giriş Saati", "Giriş Durumu", "Çıkış Saati", "Çıkış Durumu", "İzin Durum"]
column_count = len(_header)
# column and row width
COLUMN_WIDTHS = [ 22, 20, 11, 9, 16, 9, 16, 10 ]
ROW_WIDTH = 16.2 # 14 x 16.2 = 226.8 page height
# excel filenames for pdf
excel_filenames = []
for department in departments:
wb = openpyxl.Workbook()
ws = wb.active
# title
ws.merge_cells(start_row = 1, start_column = 1, end_row = 4, end_column = column_count)
title = ws['A1']
title.value = department["name"]
title.font = Font(name = 'Calibri', size = 14, bold = True, color = 'ffffff')
title.alignment = text_aligment
title.fill = PatternFill(patternType = 'solid', fgColor = Color('0099ff'))
# header font and styles
for col in range(1, column_count + 1):
ws.cell(row = 5, column = col).value = _header[col - 1]
ws.cell(row = 5, column = col).font = Font(name = 'Calibri', size = 8, bold = True, color = 'ffffff')
ws.cell(row = 5, column = col).border = text_border
ws.cell(row = 5, column = col).alignment = text_aligment
ws.cell(row = 5, column = col).fill = PatternFill(patternType = 'solid', fgColor = Color('3072b3'))
row = 6
department_users = department["users"]
# department_users = sorted(department["users"], key = lambda k: k['first_name']) # sort hash list
for user in department_users:
first_row = row
for day in user["days"]:
if day["i"] is not None:
i_time = day["i"].strftime("%H:%M")
i_hour = int(day["i"].strftime("%H"))
i_minute = int(day["i"].strftime("%M"))
if (i_hour < 7) or (i_hour == 7 and i_minute <= 45):
# i_color_key = "warning"
# i_comment = "Erken Geldi"
i_color_key = "success"
i_comment = ""
elif (i_hour > 8) or (i_hour == 8 and i_minute >= 15):
i_color_key = "warning"
i_comment = "Geç Geldi"
else:
i_color_key = "success"
i_comment = "" # Sorun yok
else:
i_time = ""
i_color_key = "danger"
i_comment = "Kart Kullanmadı"
if day["o"] is not None:
o_time = day["o"].strftime("%H:%M")
o_hour = int(day["o"].strftime("%H"))
o_minute = int(day["o"].strftime("%M"))
if (o_hour < 16) or (o_hour == 16 and o_minute <= 59):
o_color_key = "warning"
o_comment = "Erken Çıktı"
elif (o_hour > 17) or (o_hour == 17 and o_minute >= 15):
# o_color_key = "warning"
# o_comment = "Geç Çıktı"
o_color_key = "success"
o_comment = ""
else:
o_color_key = "success"
o_comment = "" # Sorun yok
else:
o_time = ""
o_color_key = "danger"
o_comment = "Kart Kullanmadı"
ws.append([user["first_name"], user["last_name"], day["date"].strftime("%d-%m-%Y"), i_time, i_comment, o_time, o_comment, ""])
# color set for comments
i_color = comment_colors[i_color_key]
ws.cell(row = row, column = 5).fill = i_color["bgcolor"]
ws.cell(row = row, column = 5).font = i_color["textcolor"]
o_color = comment_colors[o_color_key]
ws.cell(row = row, column = 7).fill = o_color["bgcolor"]
ws.cell(row = row, column = 7).font = o_color["textcolor"]
for col in range(1, column_count + 1):
ws.cell(row = row, column = col).border = text_border
ws.cell(row = row, column = col).alignment = text_aligment
row = row + 1
# ad soyad | row : merge and center
ws.merge_cells(start_row = first_row, end_row = row - 1, start_column = 1, end_column = 1)
ws.merge_cells(start_row = first_row, end_row = row - 1, start_column = 2, end_column = 2)
# EXCEL column with
for col, width in enumerate(COLUMN_WIDTHS):
ws.column_dimensions[get_column_letter(col + 1)].width = width
# EXCEL row height
for row in range(1, len(ws.rows)):
ws.row_dimensions[row].height = ROW_WIDTH
# PRINT(auto only page)
# ws.page_setup.orientation = ws.ORIENTATION_PORTRAIT
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
ws.page_setup.horizontalCentered = True
ws.page_setup.verticalCentered = True
filename = os.path.join(os.getcwd(), EXCEL_DIR, department["name"] + ".xlsx")
print("-" + filename)
wb.save(filename = filename)
excel_filenames.append(filename)
return excel_filenames
def excel_to_pdf(excel_filenames):
print(">>> PDF yazma işlemleri başlıyor")
os.chdir(PDF_DIR)
for excel_filename in excel_filenames:
filename = pipes.quote(excel_filename)
# os.system("cd C:\Program Files\Microsoft Office\Office16")
# os.system("EXCEL.EXE -headless -convert-to pdf " + filename)
workbook = Workbook(excel_filename)
# Convert Excel to PDF
# workbook.save(filename + ".pdf", SaveFormat.PDF)
# workbook.save(filename + ".pdf", asposecells.SaveFormat.PDF);
saveOptions = PdfSaveOptions()
workbook.save(excel_filename + ".pdf", saveOptions)
# unix için os.system("libreoffice -headless -convert-to pdf " + filename)
# os.system("soffice --headless --convert-to pdf:\"impress_pdf_Export\"" + filename)
departments = excel_to_hash('pdks.xlsx')
print(departments)
excel_filenames = hash_to_excel_departments_io(departments)
excel_to_pdf(excel_filenames)
jpype.shutdownJVM()
Raw
pdks.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Alignment, Border, Font, Side, Style, PatternFill, Color
from openpyxl.cell import get_column_letter
import datetime
import os, pipes
PDF_DIR = "pdf"
EXCEL_DIR = "excel"
if not os.path.isdir(os.path.join(os.getcwd(), EXCEL_DIR)):
os.mkdir(os.path.join(os.getcwd(), EXCEL_DIR))
if not os.path.isdir(os.path.join(os.getcwd(), PDF_DIR)):
os.mkdir(os.path.join(os.getcwd(), PDF_DIR))
# OKUMA ##########################################################################################
def excel_to_hash(xlsx_filename):
departments = []
# example departments
# departments = [{
# "name" : "Bilgi İşlem",
# "users" : [{
# "first_name" : "Gökhan",
# "last_name" : "Demir",
# "days" : [{ "date" : "2015-01-01", "i": "08:08:00", "o" : "08:08:08" }]
# }]
# }]
wb = load_workbook(filename=xlsx_filename, read_only=True)
ws = wb.active
user_state = False
department = ""
for row in ws.rows:
if user_state == False:
if row[3].value is not None: # Bölüm ismini belirle yoksa ekle
if "Bölüm" in row[3].value.encode(encoding = 'UTF-8', errors = 'strict').split():
department_name = row[3].value[8:].encode(encoding = 'UTF-8', errors = 'strict')
department_search_state = False # Önceki kayıtlarda böyle bir bölüm kaydı var mı ?
if departments != []:
for department in departments:
if department["name"] == department_name:
department_search_state = True
break
if not department_search_state:
departments.append({ "name" : department_name })
continue
if row[1].value == "Ad": # Ad ise kişi bilgisi başladı
user_state = True
continue
if user_state == True:
if row[9].value is not None: # string ise ve Toplam Süre ise kişi bilgisi bitti
if row[9].value.encode(encoding = 'UTF-8', errors = 'strict') == "Toplam Süre":
user_state = False
continue
date = row[5].value
weekend = None
if date is not None and date != "Tarih":
day = date.strftime("%A")
if day == "Saturday" or day == "Sunday":
weekend = True
if row[1].value is not None and row[1].value != "Ad" and weekend == None:
for department in departments:
if department["name"] == department_name:
if "users" in department.keys(): # Bölüm'de hiç kullanıcı var mı ?
user_search_state = False
for user in department["users"]:
if user["first_name"] == row[1].value and user["last_name"] == row[4].value:
user_search_state = True
user["days"].append({ "date" : row[5].value, "i" : row[7].value, "o" : row[8].value })
break
if not user_search_state:
department["users"].append({
"first_name" : row[1].value,
"last_name" : row[4].value,
"days" : [{ "date" : row[5].value, "i": row[7].value, "o" : row[8].value }]
})
else:
department["users"] = [{
"first_name" : row[1].value,
"last_name" : row[4].value,
"days" : [{ "date" : row[5].value, "i": row[7].value, "o" : row[8].value }]
}]
break
return departments
# YAZMA ##########################################################################################
def hash_to_excel_departments_io(departments):
print ">>> EXCEL Yazma işlemleri başlıyor"
# general text styles
text_border = Border(left = Side(style = 'thin'), right = Side(style = 'thin'), top = Side(style = 'thin'), bottom = Side(style = 'thin'))
text_aligment = Alignment(horizontal = 'center', vertical = 'center')
# comment colors and background colors
comment_colors = {
"danger" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('dca7a7')),
"textcolor" : Font(name = 'Calibri', size = 11, bold = True, color = 'a94442')
},
"warning" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('fcf8e3')),
"textcolor" : Font(name = 'Calibri', size = 11, bold = True, color = '8a6d3b')
},
"success" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('ffffff')),
"textcolor" : Font(name = 'Calibri', size = 11, bold = True, color = '000000')
}
}
# header
_header = ["Ad", "Soyad", "Tarih", "Giriş Saati", "Giriş Durumu", "Çıkış Saati", "Çıkış Durumu", "İzin Durum"]
column_count = len(_header)
# column and row width
COLUMN_WIDTHS = [ 22, 20, 11, 9, 16, 9, 16, 10 ]
ROW_WIDTH = 16.2 # 14 x 16.2 = 226.8 page height
# excel filenames for pdf
excel_filenames = []
for department in departments:
wb = Workbook()
ws = wb.active
# title
ws.merge_cells(start_row = 1, start_column = 1, end_row = 4, end_column = column_count)
title = ws['A1']
title.value = department["name"]
title.font = Font(name = 'Calibri', size = 14, bold = True, color = 'ffffff')
title.alignment = text_aligment
title.fill = PatternFill(patternType = 'solid', fgColor = Color('0099ff'))
# header font and styles
for col in range(1, column_count + 1):
ws.cell(row = 5, column = col).value = _header[col - 1]
ws.cell(row = 5, column = col).font = Font(name = 'Calibri', size = 8, bold = True, color = 'ffffff')
ws.cell(row = 5, column = col).border = text_border
ws.cell(row = 5, column = col).alignment = text_aligment
ws.cell(row = 5, column = col).fill = PatternFill(patternType = 'solid', fgColor = Color('3072b3'))
row = 6
department_users = department["users"]
# department_users = sorted(department["users"], key = lambda k: k['first_name']) # sort hash list
for user in department_users:
first_row = row
for day in user["days"]:
if day["i"] is not None:
i_time = day["i"].strftime("%H:%M")
i_hour = int(day["i"].strftime("%H"))
i_minute = int(day["i"].strftime("%M"))
if (i_hour < 7) or (i_hour == 7 and i_minute <= 45):
# i_color_key = "warning"
# i_comment = "Erken Geldi"
i_color_key = "success"
i_comment = ""
elif (i_hour > 8) or (i_hour == 8 and i_minute >= 15):
i_color_key = "warning"
i_comment = "Geç Geldi"
else:
i_color_key = "success"
i_comment = "" # Sorun yok
else:
i_time = ""
i_color_key = "danger"
i_comment = "Kart Kullanmadı"
if day["o"] is not None:
o_time = day["o"].strftime("%H:%M")
o_hour = int(day["o"].strftime("%H"))
o_minute = int(day["o"].strftime("%M"))
if (o_hour < 16) or (o_hour == 16 and o_minute <= 59):
o_color_key = "warning"
o_comment = "Erken Çıktı"
elif (o_hour > 17) or (o_hour == 17 and o_minute >= 15):
# o_color_key = "warning"
# o_comment = "Geç Çıktı"
o_color_key = "success"
o_comment = ""
else:
o_color_key = "success"
o_comment = "" # Sorun yok
else:
o_time = ""
o_color_key = "danger"
o_comment = "Kart Kullanmadı"
ws.append([user["first_name"], user["last_name"], day["date"].strftime("%d-%m-%Y"), i_time, i_comment, o_time, o_comment, ""])
# color set for comments
i_color = comment_colors[i_color_key]
ws.cell(row = row, column = 5).fill = i_color["bgcolor"]
ws.cell(row = row, column = 5).font = i_color["textcolor"]
o_color = comment_colors[o_color_key]
ws.cell(row = row, column = 7).fill = o_color["bgcolor"]
ws.cell(row = row, column = 7).font = o_color["textcolor"]
for col in range(1, column_count + 1):
ws.cell(row = row, column = col).border = text_border
ws.cell(row = row, column = col).alignment = text_aligment
row = row + 1
# ad soyad | row : merge and center
ws.merge_cells(start_row = first_row, end_row = row - 1, start_column = 1, end_column = 1)
ws.merge_cells(start_row = first_row, end_row = row - 1, start_column = 2, end_column = 2)
# EXCEL column with
for col, width in enumerate(COLUMN_WIDTHS):
ws.column_dimensions[get_column_letter(col + 1)].width = width
# EXCEL row height
for row in range(1, len(ws.rows)):
ws.row_dimensions[row].height = ROW_WIDTH
# PRINT auto only page
# ws.page_setup.orientation = ws.ORIENTATION_PORTRAIT
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
ws.page_setup.horizontalCentered = True
ws.page_setup.verticalCentered = True
filename = os.path.join(os.getcwd(), EXCEL_DIR, department["name"] + ".xlsx")
print "-" + filename
wb.save(filename = filename)
excel_filenames.append(filename)
return excel_filenames
def excel_to_pdf(excel_filenames):
print ">>> PDF yazma işlemleri başlıyor"
os.chdir(PDF_DIR)
for excel_filename in excel_filenames:
filename = pipes.quote(excel_filename)
os.system("libreoffice -headless -convert-to pdf " + filename)
# os.system("soffice --headless --convert-to pdf:\"impress_pdf_Export\"" + filename)
departments = excel_to_hash('pdks.xlsx')
excel_filenames = hash_to_excel_departments_io(departments)
excel_to_pdf(excel_filenames)
Raw
pdks_v2.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Font, Side, Style, PatternFill, Color
from openpyxl.cell import get_column_letter
#import inspect
#import jpype
#import asposecells
#jpype.startJVM(classpath="jars/*")
#print(jpype.java.lang.System.getProperty("java.class.path"))
#jpype.startJVM()
#from asposecells.api import Workbook, FileFormatType, PdfSaveOptions
import datetime
import os, pipes
#PDF_DIR = "pdf"
EXCEL_DIR = "excel"
#if not os.path.isdir(os.path.join(os.getcwd(), PDF_DIR)):
# os.mkdir(os.path.join(os.getcwd(), PDF_DIR))
# OKUMA ##########################################################################################
def excel_to_hash(xlsx_filename):
departments = []
# example departments
# departments = [{
# "name" : "Bilgi İşlem",
# "users" : [{
# "first_name" : "Gökhan",
# "last_name" : "Demir",
# "days" : [{ "date" : "2015-01-01", "i": "08:08:00", "o" : "08:08:08" }]
# }]
# }]
wb = load_workbook(filename=xlsx_filename, read_only=True)
ws = wb.active
user_state = False
department = ""
for row in ws.rows:
if user_state == False:
if row[3].value is not None: # Bölüm ismini belirle yoksa ekle
if "Bölüm" in row[3].value.split():
department_name = row[3].value[8:]
department_search_state = False # Önceki kayıtlarda böyle bir bölüm kaydı var mı ?
if departments != []:
for department in departments:
if department["name"] == department_name:
department_search_state = True
break
if not department_search_state:
departments.append({ "name" : department_name })
continue
if row[1].value == "Ad": # Ad ise kişi bilgisi başladı
user_state = True
continue
if user_state == True:
if row[9].value is not None: # string ise ve Toplam Süre ise kişi bilgisi bitti
if row[9].value == "Toplam Süre":
user_state = False
continue
if row[1].value is not None and row[1].value != "Ad":
for department in departments:
if department["name"] == department_name:
if "users" in department.keys(): # Bölüm'de hiç kullanıcı var mı ?
user_search_state = False
for user in department["users"]:
if user["first_name"] == row[1].value and user["last_name"] == row[4].value:
user_search_state = True
user["days"].append({ "date" : row[5].value, "i" : row[7].value, "o" : row[8].value })
break
if not user_search_state:
department["users"].append({
"first_name" : row[1].value,
"last_name" : row[4].value,
"days" : [{ "date" : row[5].value, "i": row[7].value, "o" : row[8].value }]
})
else:
department["users"] = [{
"first_name" : row[1].value,
"last_name" : row[4].value,
"days" : [{ "date" : row[5].value, "i": row[7].value, "o" : row[8].value }]
}]
break
return departments
# YAZMA ##########################################################################################
def hash_to_excel_departments_io(departments):
print(">>> EXCEL Yazma işlemleri başlıyor")
FONT_NAME = 'Monda'
# general text styles
text_border = Border(left = Side(style = 'thin'), right = Side(style = 'thin'), top = Side(style = 'thin'), bottom = Side(style = 'thin'))
text_aligment = Alignment(horizontal = 'center', vertical = 'center')
# comment colors and background colors
comment_colors = {
"danger" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('dca7a7')),
"textcolor" : Font(name = FONT_NAME, size = 8, bold = True, color = 'a94442')
},
"warning" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('fcf8e3')),
"textcolor" : Font(name = FONT_NAME, size = 8, bold = True, color = '8a6d3b')
},
"success" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('ffffff')),
"textcolor" : Font(name = FONT_NAME, size = 8, bold = True, color = '000000')
}
}
# header
_header = ["Ad", "Soyad", "Tarih", "Giriş Saati", "Giriş Durumu", "Çıkış Saati", "Çıkış Durumu", "İzin Durum"]
column_count = len(_header)
# column and row width
COLUMN_WIDTHS = [ 22, 20, 11, 9, 16, 9, 16, 10 ]
ROW_WIDTH = 16.2 # 14 x 16.2 = 226.8 page height
# excel filenames for pdf
excel_filenames = []
for department in departments:
wb = openpyxl.Workbook()
ws = wb.active
# TITLE font and styles
ws.merge_cells(start_row = 1, start_column = 1, end_row = 4, end_column = column_count)
title = ws['A1']
title.value = department["name"]
title.font = Font(name = FONT_NAME, size = 14, bold = True, color = 'ffffff')
title.alignment = text_aligment
title.fill = PatternFill(patternType = 'solid', fgColor = Color('0099ff'))
# HEADER font and styles
for col in range(1, column_count + 1):
ws.cell(row = 5, column = col).value = _header[col - 1]
ws.cell(row = 5, column = col).font = Font(name = FONT_NAME, size = 8, bold = True, color = 'ffffff')
ws.cell(row = 5, column = col).border = text_border
ws.cell(row = 5, column = col).alignment = text_aligment
ws.cell(row = 5, column = col).fill = PatternFill(patternType = 'solid', fgColor = Color('3072b3'))
row = 6
department_users = department["users"]
# department_users = sorted(department["users"], key = lambda k: k['first_name']) # sort hash list
for user in department_users:
first_row = row
for day in user["days"]:
if day["i"] is not None:
i_time = day["i"].strftime("%H:%M")
i_hour = int(day["i"].strftime("%H"))
i_minute = int(day["i"].strftime("%M"))
if (i_hour < 7) or (i_hour == 7 and i_minute <= 45):
# i_color_key = "warning"
# i_comment = "Erken Geldi"
i_color_key = "success"
i_comment = ""
elif (i_hour > 8) or (i_hour == 8 and i_minute >= 15):
i_color_key = "warning"
i_comment = "Geç Geldi"
else:
i_color_key = "success"
i_comment = "" # Sorun yok
else:
i_time = ""
i_color_key = "danger"
i_comment = "Kart Kullanmadı"
if day["o"] is not None:
o_time = day["o"].strftime("%H:%M")
o_hour = int(day["o"].strftime("%H"))
o_minute = int(day["o"].strftime("%M"))
if (o_hour < 16) or (o_hour == 16 and o_minute <= 59):
o_color_key = "warning"
o_comment = "Erken Çıktı"
elif (o_hour > 17) or (o_hour == 17 and o_minute >= 15):
# o_color_key = "warning"
# o_comment = "Geç Çıktı"
o_color_key = "success"
o_comment = ""
else:
o_color_key = "success"
o_comment = "" # Sorun yok
else:
o_time = ""
o_color_key = "danger"
o_comment = "Kart Kullanmadı"
data = [ user["first_name"], user["last_name"], day["date"].strftime("%d-%m-%Y"), i_time, i_comment, o_time, o_comment, "" ]
ws.append(data)
# ws.append([user["first_name"], user["last_name"], day["date"].strftime("%d-%m-%Y"), i_time, i_comment, o_time, o_comment, ""])
# Font set for input-output
for col in range(1, column_count + 1):
ws.cell(row = row, column = col).font = Font(name = FONT_NAME, size = 8)
# COLOR set for comments
i_color = comment_colors[i_color_key]
ws.cell(row = row, column = 5).fill = i_color["bgcolor"]
ws.cell(row = row, column = 5).font = i_color["textcolor"]
o_color = comment_colors[o_color_key]
ws.cell(row = row, column = 7).fill = o_color["bgcolor"]
ws.cell(row = row, column = 7).font = o_color["textcolor"]
for col in range(1, column_count + 1):
ws.cell(row = row, column = col).border = text_border
ws.cell(row = row, column = col).alignment = text_aligment
row = row + 1
# ad soyad | row : merge and center
ws.merge_cells(start_row = first_row, end_row = row - 1, start_column = 1, end_column = 1)
ws.merge_cells(start_row = first_row, end_row = row - 1, start_column = 2, end_column = 2)
# EXCEL column with
for col, width in enumerate(COLUMN_WIDTHS):
ws.column_dimensions[get_column_letter(col + 1)].width = width
# EXCEL row height
for row in range(1, len(ws.rows)):
ws.row_dimensions[row].height = ROW_WIDTH
# PRINT(auto only page)
# ws.page_setup.orientation = ws.ORIENTATION_PORTRAIT
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
ws.page_setup.horizontalCentered = True
ws.page_setup.verticalCentered = True
filename = os.path.join(os.getcwd(), EXCEL_DIR, department["name"] + ".xlsx")
print("-" + filename)
wb.save(filename = filename)
excel_filenames.append(filename)
return excel_filenames
def excel_to_pdf(excel_filenames):
print(">>> PDF yazma işlemleri başlıyor")
os.chdir(PDF_DIR)
for excel_filename in excel_filenames:
filename = pipes.quote(excel_filename)
# os.system("cd C:\Program Files\Microsoft Office\Office16")
# os.system("EXCEL.EXE -headless -convert-to pdf " + filename)
workbook = Workbook(excel_filename)
# Convert Excel to PDF
# workbook.save(filename + ".pdf", SaveFormat.PDF)
# workbook.save(filename + ".pdf", asposecells.SaveFormat.PDF);
saveOptions = PdfSaveOptions()
workbook.save(excel_filename + ".pdf", saveOptions)
# unix için os.system("libreoffice -headless -convert-to pdf " + filename)
# os.system("soffice --headless --convert-to pdf:\"impress_pdf_Export\"" + filename)
def program_detail():
print("# PDKS Excel Parser\n")
print("→ Description : PDKS'den alınan .xls dosyasını PDKS.xlsx şeklinde kaydettikten sonra parçalar.")
print("→ Author : Gökhan Demir")
print("→ Version : v2")
print("→ Realese Date : 21.06.2023\n")
# Program Detayı
program_detail()
# PDKS.xslx dosya kontrolü
INPUT_FILE = 'pdks.xlsx'
INPUT_FILE_PATH = os.path.join(os.getcwd(), INPUT_FILE)
if not os.path.exists(INPUT_FILE_PATH):
print("Dosya yok : " + INPUT_FILE_PATH)
input("Bir tuşa basıp programı kapatınız.")
exit()
# EXCEL_DIR dizin kontrolü
if not os.path.isdir(os.path.join(os.getcwd(), EXCEL_DIR)):
os.mkdir(os.path.join(os.getcwd(), EXCEL_DIR))
# Ana Excel -> Hash olarak doldurma
departments = excel_to_hash(INPUT_FILE_PATH)
print("> Bulunan Deparment sayısı : %s", len(departments))
# Hash -> Excel Department olarak yazma
excel_filenames = hash_to_excel_departments_io(departments)
# Excel Department -> PDF Department olarak yazma
# excel_to_pdf(excel_filenames)
# jpype.shutdownJVM()
Raw
pdks_v3.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Font, Side, Style, PatternFill, Color
from openpyxl.cell import get_column_letter
#import inspect
#import jpype
#import asposecells
#jpype.startJVM(classpath="jars/*")
#print(jpype.java.lang.System.getProperty("java.class.path"))
#jpype.startJVM()
#from asposecells.api import Workbook, FileFormatType, PdfSaveOptions
import datetime
import os, pipes
#PDF_DIR = "pdf"
EXCEL_DIR = "excel"
#if not os.path.isdir(os.path.join(os.getcwd(), PDF_DIR)):
# os.mkdir(os.path.join(os.getcwd(), PDF_DIR))
# OKUMA ##########################################################################################
def excel_to_hash(xlsx_filename):
departments = []
# example departments
# departments = [{
# "name" : "Bilgi İşlem",
# "users" : [{
# "first_name" : "Gökhan",
# "last_name" : "Demir",
# "days" : [{ "date" : "2015-01-01", "i": "08:08:00", "o" : "08:08:08" }]
# }]
# }]
wb = load_workbook(filename=xlsx_filename, read_only=True)
ws = wb.active
user_state = False
department = ""
for row in ws.rows:
if user_state == False:
if row[3].value is not None: # Bölüm ismini belirle yoksa ekle
if "Bölüm" in row[3].value.split():
department_name = row[3].value[8:]
department_search_state = False # Önceki kayıtlarda böyle bir bölüm kaydı var mı ?
if departments != []:
for department in departments:
if department["name"] == department_name:
department_search_state = True
break
if not department_search_state:
departments.append({ "name" : department_name })
continue
if row[1].value == "Ad": # Ad ise kişi bilgisi başladı
user_state = True
continue
if user_state == True:
if row[9].value is not None: # string ise ve Toplam Süre ise kişi bilgisi bitti
if row[9].value == "Toplam Süre":
user_state = False
continue
if row[1].value is not None and row[1].value != "Ad":
for department in departments:
if department["name"] == department_name:
if "users" in department.keys(): # Bölüm'de hiç kullanıcı var mı ?
user_search_state = False
for user in department["users"]:
if user["first_name"] == row[1].value and user["last_name"] == row[4].value:
user_search_state = True
user["days"].append({ "date" : row[5].value, "i" : row[7].value, "o" : row[8].value })
break
if not user_search_state:
department["users"].append({
"first_name" : row[1].value,
"last_name" : row[4].value,
"days" : [{ "date" : row[5].value, "i": row[7].value, "o" : row[8].value }]
})
else:
department["users"] = [{
"first_name" : row[1].value,
"last_name" : row[4].value,
"days" : [{ "date" : row[5].value, "i": row[7].value, "o" : row[8].value }]
}]
break
return departments
# YAZMA ##########################################################################################
def hash_to_excel_departments_io(departments):
print(">>> EXCEL Yazma işlemleri başlıyor")
FONT_NAME = 'Calibri'
# general text styles
text_border = Border(left = Side(style = 'thin'), right = Side(style = 'thin'), top = Side(style = 'thin'), bottom = Side(style = 'thin'))
text_aligment = Alignment(horizontal = 'center', vertical = 'center')
# comment colors and background colors
comment_colors = {
"danger" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('dca7a7')),
"textcolor" : Font(name = FONT_NAME, size = 8, bold = True, color = 'a94442')
},
"warning" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('fcf8e3')),
"textcolor" : Font(name = FONT_NAME, size = 8, bold = True, color = '8a6d3b')
},
"success" : {
"bgcolor" : PatternFill(patternType = 'solid', fgColor = Color('ffffff')),
"textcolor" : Font(name = FONT_NAME, size = 8, bold = True, color = '000000')
}
}
# header
_header = ["Ad", "Soyad", "Tarih", "Giriş Saati", "Giriş Durumu", "Çıkış Saati", "Çıkış Durumu", "İzin Durum"]
column_count = len(_header)
# column and row width
COLUMN_WIDTHS = [ 22, 20, 11, 9, 16, 9, 16, 10 ]
ROW_WIDTH = 16.2 # 14 x 16.2 = 226.8 page height
# excel filenames for pdf
excel_filenames = []
for department in departments:
wb = openpyxl.Workbook()
ws = wb.active
# TITLE font and styles
ws.merge_cells(start_row = 1, start_column = 1, end_row = 4, end_column = column_count)
title = ws['A1']
title.value = department["name"]
title.font = Font(name = FONT_NAME, size = 14, bold = True, color = 'ffffff')
title.alignment = text_aligment
title.fill = PatternFill(patternType = 'solid', fgColor = Color('0099ff'))
# HEADER font and styles
for col in range(1, column_count + 1):
ws.cell(row = 5, column = col).value = _header[col - 1]
ws.cell(row = 5, column = col).font = Font(name = FONT_NAME, size = 8, bold = True, color = 'ffffff')
ws.cell(row = 5, column = col).border = text_border
ws.cell(row = 5, column = col).alignment = text_aligment
ws.cell(row = 5, column = col).fill = PatternFill(patternType = 'solid', fgColor = Color('3072b3'))
row = 6
department_users = department["users"]
# department_users = sorted(department["users"], key = lambda k: k['first_name']) # sort hash list
for user in department_users:
first_row = row
for day in user["days"]:
if day["i"] is not None:
i_time = day["i"].strftime("%H:%M")
i_hour = int(day["i"].strftime("%H"))
i_minute = int(day["i"].strftime("%M"))
if (i_hour < 7) or (i_hour == 7 and i_minute <= 45):
# i_color_key = "warning"
# i_comment = "Erken Geldi"
i_color_key = "success"
i_comment = ""
elif (i_hour > 8) or (i_hour == 8 and i_minute >= 15):
i_color_key = "warning"
i_comment = "Geç Geldi"
else:
i_color_key = "success"
i_comment = "" # Sorun yok
else:
i_time = ""
i_color_key = "danger"
i_comment = "Kart Kullanmadı"
if day["o"] is not None:
o_time = day["o"].strftime("%H:%M")
o_hour = int(day["o"].strftime("%H"))
o_minute = int(day["o"].strftime("%M"))
if (o_hour < 16) or (o_hour == 16 and o_minute <= 59):
o_color_key = "warning"
o_comment = "Erken Çıktı"
elif (o_hour > 17) or (o_hour == 17 and o_minute >= 15):
# o_color_key = "warning"
# o_comment = "Geç Çıktı"
o_color_key = "success"
o_comment = ""
else:
o_color_key = "success"
o_comment = "" # Sorun yok
else:
o_time = ""
o_color_key = "danger"
o_comment = "Kart Kullanmadı"
data = [ user["first_name"], user["last_name"], day["date"].strftime("%d-%m-%Y"), i_time, i_comment, o_time, o_comment, "" ]
ws.append(data)
# ws.append([user["first_name"], user["last_name"], day["date"].strftime("%d-%m-%Y"), i_time, i_comment, o_time, o_comment, ""])
# Font set for input-output
for col in range(1, column_count + 1):
ws.cell(row = row, column = col).font = Font(name = FONT_NAME, size = 8)
# COLOR set for comments
i_color = comment_colors[i_color_key]
ws.cell(row = row, column = 5).fill = i_color["bgcolor"]
ws.cell(row = row, column = 5).font = i_color["textcolor"]
o_color = comment_colors[o_color_key]
ws.cell(row = row, column = 7).fill = o_color["bgcolor"]
ws.cell(row = row, column = 7).font = o_color["textcolor"]
for col in range(1, column_count + 1):
ws.cell(row = row, column = col).border = text_border
ws.cell(row = row, column = col).alignment = text_aligment
row = row + 1
# ad soyad | row : merge and center
ws.merge_cells(start_row = first_row, end_row = row - 1, start_column = 1, end_column = 1)
ws.merge_cells(start_row = first_row, end_row = row - 1, start_column = 2, end_column = 2)
# EXCEL column with
for col, width in enumerate(COLUMN_WIDTHS):
ws.column_dimensions[get_column_letter(col + 1)].width = width
# EXCEL row height
for row in range(1, len(ws.rows)):
ws.row_dimensions[row].height = ROW_WIDTH
# PRINT(auto only page)
# ws.page_setup.orientation = ws.ORIENTATION_PORTRAIT
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
ws.page_setup.horizontalCentered = True
ws.page_setup.verticalCentered = True
filename = os.path.join(os.getcwd(), EXCEL_DIR, department["name"] + ".xlsx")
print("-" + filename)
wb.save(filename = filename)
excel_filenames.append(filename)
return excel_filenames
def excel_to_pdf(excel_filenames):
print(">>> PDF yazma işlemleri başlıyor")
os.chdir(PDF_DIR)
for excel_filename in excel_filenames:
filename = pipes.quote(excel_filename)
# os.system("cd C:\Program Files\Microsoft Office\Office16")
# os.system("EXCEL.EXE -headless -convert-to pdf " + filename)
workbook = Workbook(excel_filename)
# Convert Excel to PDF
# workbook.save(filename + ".pdf", SaveFormat.PDF)
# workbook.save(filename + ".pdf", asposecells.SaveFormat.PDF);
saveOptions = PdfSaveOptions()
workbook.save(excel_filename + ".pdf", saveOptions)
# unix için os.system("libreoffice -headless -convert-to pdf " + filename)
# os.system("soffice --headless --convert-to pdf:\"impress_pdf_Export\"" + filename)
def program_detail():
print("# PDKS Excel Parser\n")
print("→ Description : PDKS'den alınan .xls dosyasını PDKS.xlsx şeklinde kaydettikten sonra parçalar.")
print("→ Author : Gökhan Demir")
print("→ Version : v3")
print("→ Realese Date : 15.08.2023\n")
# Program Detayı
program_detail()
# PDKS.xslx dosya kontrolü
INPUT_FILE = 'pdks.xlsx'
INPUT_FILE_PATH = os.path.join(os.getcwd(), INPUT_FILE)
if not os.path.exists(INPUT_FILE_PATH):
print("Dosya yok : " + INPUT_FILE_PATH)
input("Bir tuşa basıp programı kapatınız.")
exit()
# EXCEL_DIR dizin kontrolü
if not os.path.isdir(os.path.join(os.getcwd(), EXCEL_DIR)):
os.mkdir(os.path.join(os.getcwd(), EXCEL_DIR))
# Ana Excel -> Hash olarak doldurma
departments = excel_to_hash(INPUT_FILE_PATH)
print("> Bulunan Deparment sayısı :", len(departments))
# Hash -> Excel Department olarak yazma
excel_filenames = hash_to_excel_departments_io(departments)
# Excel Department -> PDF Department olarak yazma
# excel_to_pdf(excel_filenames)
# jpype.shutdownJVM()
@gdemir
Copy link
Author

gdemir commented Jan 7, 2019

pip install -Iv openpyxl==2.3.3

@gdemir
Copy link
Author

gdemir commented Jun 7, 2023 •
edited
Loading

README

source : https://bobbyhadz.com/blog/choco-is-not-recognized-as-internal-or-external-command

run powershell

[BAŞLAT + R]
Powershell yönetici olarak çalıştır

choco installer install

Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1'))

curl install

choco install curl -y

pip install

curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py
cd C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310
python.exe get-pip.py

autopytoexe.exe install

cd C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310\Scripts
pip.exe install auto-py-to-exe

autopytoexe.exe run

cd C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310
python.exe C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310\Scripts\autopytoexe.exe
python.exe -m C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310\Scripts\autopytoexe.exe
cd C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310\output\yazici_bul.exe

run auto-py-to-exe.exe

C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310>python.exe Scripts\auto-py-to-exe.exe
C:\Users\gokhan.demir2\AppData\Local\Programs\Python\Python310>python.exe -m Scripts\auto-py-to-exe.exe

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment