Untitled

from tkinter import Tk
from tkinter.filedialog import askdirectory
import pdfplumber
import re
import os
#import xlsxwriter
from openpyxl import Workbook
from openpyxl import load_workbook


def get_path():
    path = askdirectory(title='Selecione a pasta com as faturas') # shows dialog box and return the path
    dir_path = path.replace("/","\\")
    #print(dir_path)
    path_files = []
    files = os.listdir(dir_path)
    for file in files:
        if file.endswith(".pdf"):
            path_files.append(os.path.join(dir_path , file))
    return(path_files)

all_pdfs_path = get_path()


#Importação das planilhas e setup da active sheet

wb = load_workbook(filename = 'TEMPLATE - DMG I TIM.xlsx')
ws1 = wb.active


row1 = 7
row2 = 7
row3 = 7
row4 = 1
col2 = 2
col3 = 5
col_cred = 9
consumo_total = 0
creditos_values = 0
v_creditos = [];
v_consumos = [];
v_precos = [];
compensado_total = [];
compensadas = 0
iterador_vetores = 0


for single_pdf_path in all_pdfs_path:
    with pdfplumber.open(single_pdf_path) as pdf:
        page = pdf.pages[0]
        text = page.extract_text()
        print(text)


    numero_fatura_re = re.compile(r'^Ouvidoria:.* (\S+)') 
    consumo_only_re = re.compile(r'Consumo (\S+) kWh a (\S+) +(\S+) +(\S+)')
    credito_re = re.compile(r'Credito .* (\S+)')
    total_pagar_re = re.compile(r'^\S+ \S+ \S+ \S+ \d{2}\/\d{2}\/\d{4} (\S+)')
    consumo_ponta_fora_ponta_re = re.compile(r'(Consumo (?:(?:F/)?Ponta|Interm)) (\S+) kWh a (\S+) +(\S+) +(\S+)')
    reversa_acum_re = re.compile(r'Reversa Acum\. F\/Ponta - (\S+)')
    data_fatura_re = re.compile(r'\d{2}\/\d{2}\/\d{4} \S+ \S+ \S+ \S+ \S+ (\S+)')
    base_aliquota_icms_re = re.compile(r'^(\d+.*,\d{2}) (\d{2},\d{2}) (\d+.*,\d{2})')
    qual_consumo = len(re.findall(r'(Consumo (?:(?:F/)?Ponta|Interm)) (\S+) kWh a (\S+) +(\S+) +(\S+)', text))
    pis_re = re.compile(r'Pis (\d+,\d+)')
    cofins_re = re.compile(r'Cofins (\d+,\d+)')
    endereco_re = re.compile(r'(.*\n){5}')

    
    for line in text.split('\n'):
        #line1 = line
        line = numero_fatura_re.match(line)
        if line:
           ws1.cell(column=4, row=row1, value= int(line.group(1).replace('-','')))
           row1+=1

    if qual_consumo!=0:
        for line in text.split('\n'):
            line = consumo_ponta_fora_ponta_re.search(line)
            #line2 = credito_re.search(line)
            if line:
                v_consumos.append(float(line.group(2).replace('.', '').replace(',', '.')))
                v_precos.append(float(line.group(5).replace('.', '').replace(',', '.')))
                consumo_total+=float(line.group(2).replace('.', '').replace(',', '.'))
                ws1.cell(column=16, row=row2, value=consumo_total)
                col2 +=1
                col3 +=1
    
    if qual_consumo==0:
        for line in text.split('\n'):
            line = consumo_only_re.search(line)
            if line:
                    
                v_consumos.append(float(line.group(1).replace('.', '').replace(',', '.')))
                v_precos.append(float(line.group(4).replace('.', '').replace(',', '.')))
                consumo_total+=float(line.group(1).replace('.', '').replace(',', '.'))
                ws1.cell(column=16, row=row2, value=consumo_total)
                col2 +=1
                col3 +=1
                
    for line in text.split('\n'):
        line = credito_re.search(line)
        if line:
            #worksheet.write(row3, col_cred, line.group(1))
            #col_cred+=1
            v_creditos.append(float(line.group(1).replace('-','').replace('.', '').replace(',', '.')))
            #ws1.cell(column=18, row=row3, value=consumo_total)
            
    for line in text.split('\n'):
        line = reversa_acum_re.search(line)
        if line:
            ws1.cell(column=18, row=row3, value=int(line.group(1).replace(',00', '')))
            
##    for line in text.split('\n'):
##        line = data_fatura_re.match(line)
##        if line:
##            worksheet.write(row3, 14, line.group(1))
    
    for credito in v_creditos:
        for i in range(len(v_precos)):
            if credito == v_precos[i]:
                compensadas+=v_consumos[i]
    #worksheet.write(row3, 8, compensadas)
    ws1.cell(column=17, row=row3, value=compensadas)

         
    for line in text.split('\n'):
        line = total_pagar_re.match(line)
        if line:
            ws1.cell(column = 9, row=row3, value=float(line.group(1).replace('.', '').replace(',', '.')))

    for line in text.split('\n'):
        line = base_aliquota_icms_re.search(line)
        if line:
            ws1.cell(column = 10, row = row3, value=float(line.group(3).replace('.', '').replace(',', '.')))
            ws1.cell(column = 11, row = row3, value=float(line.group(2).replace('.', '').replace(',', '.'))/100)
##            print(line.group(1))
##            print(ws1.cell(row3, 16).value)
##            print(ws1.cell(row3,17).value)
##            print((float(line.group(1).replace('.', '').replace(',', '.'))/(ws1.cell(row3, 16).value - ws1.cell(row3,17).value)))
            ws1.cell(column = 19, row = row3, value=(float(line.group(1).replace('.', '').replace(',', '.'))/(ws1.cell(row3, 16).value - ws1.cell(row3,17).value)))
            
    for line in text.split('\n'):
        line = pis_re.search(line)
        if line:
            ws1.cell(column = 12, row = row3, value=float(line.group(1).replace('.', '').replace(',', '.'))/100)


    for line in text.split('\n'):
        line = cofins_re.search(line)
        if line:
            ws1.cell(column = 13, row = row3, value=float(line.group(1).replace('.', '').replace(',', '.'))/100)        
##    #print(v_creditos)
##    #print(v_consumos)
##    #print(v_precos)
##
    for line in text.split('\n'):
        line = endereco_re.search(line)
        if line:
            ws1.cell(column = 5, row = row3, value = line.group(1))
            print(str(line.group(1)))



            
    compensadas = 0
    v_creditos = [];
    v_consumos = [];
    v_precos = [];
    consumo_total=0
    col_cred=9
    col3 = 5
    col2 = 2  
    row2 +=1   
    row3 +=1
    row4 +=1
            
##workbook.close()
##
####print(v_creditos)
####print(v_consumos)
####print(v_precos)
##
##

wb.save("teste.xlsx")