问如何使用python或JavaScript提取文本并将其保存为excel文件

EN

pip install pdfplumber

pattern = r'Our Ref.*?Name.*?Ref 1.*?Ref 2.*?'

import pdfplumber
import re

# regex pattern for keys in line1 of data entity
my_regex_dict_line1 = {
    'Our Ref' : r'Our Ref :(.*?)Name',
    'Name' : r'Name:(.*?)Ref 1',
    'Ref 1' : r'Ref 1 :(.*?)Ref 2',
    'Ref 2' : r'Ref 2:(.*?)$'
}

# regex pattern for keys in line2 of data entity
my_regex_dict_line2 = {
    'Amount' : r'Amount:(.*?)Total Paid',
    'Total Paid' : r'Total Paid:(.*?)Balance',
    'Balance' : r'Balance:(.*?)Date of A/C',
    'Date of A/C' : r'Date of A/C:(.*?)Date Received',
    'Date Received' : r'Date Received:(.*?)$'
}

# regex pattern for keys in line3 of data entity
my_regex_dict_line3 ={
    'Last Paid' : r'Last Paid:(.*?)Amt Last Paid',
    'Amt Last Paid' : r'Amt Last Paid:(.*?)A/C\s+Status',
    'A/C Status': r'A/C\s+Status:(.*?)Collector',
    'Collector' : r'Collector :(.*?)$'
}

def preprocess_data(data):
    return [el.strip() for el in data.splitlines() if el.strip()]
    
def get_header_data(text, json_data = {}):
    header_data_list = preprocess_data(text)
    # third line in text of header contains Date Created field
    json_data['Date Created'] = re.search(r'Date Created:(.*?)$', header_data_list[2]).group(1).strip()
    # fourth line in text contains Number of Pages, Client Code, Client Name
    json_data['Number of Pages'] = re.search(r'Number of Pages:(.*?)$', header_data_list[3]).group(1).strip()
    # fifth line in text contains Client Code and ClientName
    json_data['Client Code'] = re.search(r'Client Code - (.*?)Client Name', header_data_list[4]).group(1).strip()
    json_data['ClientName'] = re.search(r'Client Name - (.*?)$', header_data_list[4]).group(1).strip()
    
def iterate_through_regex_and_populate_dictionaries(data_dict, regex_dict, text):
    ''' For the given pattern of regex_dict, this function iterates through each regex pattern and adds the key value to regex_dict dictionary '''
    for key, regex in regex_dict.items():
            matched_value = re.search(regex, text)
            if matched_value is not None:
                data_dict[key] = matched_value.group(1).strip()

def populate_date_notes(data_dict, text):
    ''' This function populates date and Notes in the data chunk in the form of list to data_dict dictionary '''
    data_dict['Date'] = []
    data_dict['Notes'] = []
    iter = 4
    while(iter < len(text)):
        date_match = re.search(r'(\d{2}/\d{2}/\d{4})',text[iter])
        data_dict['Date'].append(date_match.group(1).strip())
        notes_match = re.search(r'\d{2}/\d{2}/\d{4}\s*(.*?)$',text[iter])
        data_dict['Notes'].append(notes_match.group(1).strip())
        iter += 1   

data_index = 1
json_data = {}
pdf_path = r'C:\Users\hpoddar\Desktop\Temp\sample3.pdf' # ENTER YOUR PDF PATH HERE
pdf_text = ''
data_entity_sep_pattern = r'(?=Our Ref.*?Name.*?Ref 1.*?Ref 2)'

if(__name__ == '__main__'):
    with pdfplumber.open(pdf_path) as pdf:
        index = 0
        while(index < len(pdf.pages)):
            page = pdf.pages[index]
            pdf_text += '\n' + page.extract_text()
            index += 1
            
    split_on_data_entity = re.split(data_entity_sep_pattern, pdf_text.strip())
    # first data in the split_on_data_entity list will contain the header information
    get_header_data(split_on_data_entity[0], json_data)
    while(data_index < len(split_on_data_entity)):
        data_entity = {}
        data_processed = preprocess_data(split_on_data_entity[data_index])
        iterate_through_regex_and_populate_dictionaries(data_entity, my_regex_dict_line1, data_processed[0])
        iterate_through_regex_and_populate_dictionaries(data_entity, my_regex_dict_line2, data_processed[1])
        iterate_through_regex_and_populate_dictionaries(data_entity, my_regex_dict_line3, data_processed[2])
        if(len(data_processed) > 3 and data_processed[3] != None and 'Date' in data_processed[3] and 'Notes' in data_processed[3]):
            populate_date_notes(data_entity, data_processed)
            json_data['data_entity' + str(data_index)] = data_entity
        data_index += 1
            
    print(json_data)

{'Date Created': '18/04/2022', 'Number of Pages': '4', 'Client Code': '110203', 'ClientName': 'AWS PTE. LTD.', 'data_entity1': {'Our Ref': '2118881115', 'Name': 'Sky Blue', 'Ref 1': '12-34-56789-2021/2', 'Ref 2': 'F2021004444', 'Amount': '$100.11', 'Total Paid': '$0.00', 'Balance': '$100.11', 'Date of A/C': '01/08/2021', 'Date Received': '10/12/2021', 'Last Paid': '', 'Amt Last Paid': '', 'A/C Status': 'CLOSED', 'Collector': 'Sunny Jane', 'Date': ['04/03/2022'], 'Notes': ['Letter Dated 04 Mar 2022.']}, 'data_entity2': {'Our Ref': '2112221119', 'Name': 'Green Field', 'Ref 1': '98-76-54321-2021/1', 'Ref 2': 'F2021001111', 'Amount': '$233.88', 'Total Paid': '$0.00', 'Balance': '$233.88', 'Date of A/C': '01/08/2021', 'Date Received': '10/12/2021', 'Last Paid': '', 'Amt Last Paid': '', 'A/C Status': 'CURRENT', 'Collector': 'Sam Jason', 'Date': ['11/03/2022', '11/03/2022', '08/03/2022', '08/03/2022', '21/02/2022', '18/02/2022', '18/02/2022'], 'Notes': ['Email for payment', 'Case Status', 'to send a Letter', '845***Ringing, No reply', 'Letter printed - LET: LETTER 2', 'Letter sent - LET: LETTER 2', '845***Line busy']}}

pip install xlsxwriter

import xlsxwriter
workbook = xlsxwriter.Workbook('Sample.xlsx')
worksheet = workbook.add_worksheet("Sheet 1")
row = 0
col = 0

# write columns
columns = ['Account History Report', 'All Notes'] + [ key for key in json_data.keys() if 'data_entity' not in key ] + list(json_data['data_entity1'].keys())
worksheet.write_row(row, col,  tuple(columns))
row += 1

column_index_map = {}
for index, col in enumerate(columns):
    column_index_map[col] =  index

# write the header
worksheet.write(row, column_index_map['Date Created'],  json_data['Date Created'])
worksheet.write(row, column_index_map['Number of Pages'],  json_data['Number of Pages'])
worksheet.write(row, column_index_map['Client Code'],  json_data['Client Code'])
worksheet.write(row, column_index_map['ClientName'],  json_data['ClientName'])
data_entity_index = 1


#iterate through each data entity and for each key insert the values in the sheet
while True:
    data_entity_key = 'data_entity' + str(data_entity_index)
    row_size = 1
    if(json_data.get(data_entity_key) != None):
        for key, value in json_data.get(data_entity_key).items():
            if(type(value) == list):
                worksheet.write_column(row, column_index_map[key],  tuple(value))
                row_size = len(value)
            else:
                worksheet.write(row, column_index_map[key], value)
    else:
        break
    data_entity_index += 1
    row += row_size
    
workbook.close()