How can I add a delimiter to the print function to and print within a specific cell in an excel file-CodePudding

The goal of the script is to iterate through all the rows in an excel file and extract a specific part of the comments and out put them vertically starting from that cell I assign.

So far I've gotten it to print out the comments within the IDE but I don't know how to go about setting a condition to delimit the comments or rather extract only the needed portion of the comment only and output it to the desired cell.

As an example the comments I'm trying to extract look something like this

Your submission conflicts with 'FASKLS89WYA' in the catalog.

I want to extract only the characters within the quotation marks, and the characters before the first quotation mark are always the same.

Appreciate any guidance, I'm still learning Python so it's new to me and I'm trying to make this script on my own as much as I can to learn.

Thanks in advance!

import openpyxl as xl
from openpyxl import load_workbook
from tkinter import filedialog
import tkinter as tk
import os

root = tk.Tk()
root.withdraw()

folder = filedialog.askdirectory()

paths = filedialog.askopenfilename(title="Select file", filetypes=(("Excel files", "*.xlsx"), ("Excel files", "*.xls")))
folder = '/'.join(paths.split('/')[0:-1])  # remove filename from path
sample_data = os.path.basename(paths)  # save filename for later use


def process_workbook(filename):
    if len(filename) != 0:  # in case cancel was pressed in filedialog
        wb = xl.load_workbook(filename)
        first_sheet = wb.get_sheet_names()[0]
        worksheet = wb.get_sheet_by_name(first_sheet)

        for row in worksheet.iter_rows(): # iterate through each cell in every row
            for cell in row:
                if cell.comment:
                    print(cell.comment.text)

    wb.save(os.path.join(folder, f'up_{sample_data}'))

process_workbook(paths)

CodePudding user response：

You can use the split method specifying the separator in a string containing the data of a row, for each row.

This method splits the string whenever it finds the specified character. Then it is a matter of taking the part you want.

Example


string = "Your submission conflicts with 'FASKLS89WYA' in the catalog."

splitted_string = string.split('\'')

the_part_you_want = splitted_string[1]

Note that this the character is escaped with a \ because it is a special character.