PDKS-excel üzerinde işlemler, excel ve pdf'e dökme

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

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)

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()

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)

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()

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()

pip install -Iv openpyxl==2.3.3

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