Search code examples
pythonexceldataframexlwings

Xlwings write text in-between and at the end of a cell

I have a data like as below.

enter image description here

ABC 1   300 500 "He is a good student and has scored lot of marks in 2nd term during
                 He also focuses on extracurricular activities"
DEF 2   400 600 "He is a good student and has scored lot of marks in 2nd term during
                 He also focuses on extracurricular activities"

I would like to two keywords/sentence to each student based on a criteria like below

ABC - "2nd term during Aug, 2022", "Needs Improvement on Maths"

DEF - "2nd term during Aug, 2022","Needs Improvement on Science"

So, I tried the below

wb = xw.Book('foo.xlsx')
ws = wb["input"]
for i in range(A1:A1048756):
    s = ws.cell(i).value
    if s == 'ABC': 
        ws.cell(i).value = s.replace(
            "He is a good student and has scored lot of marks in 2nd term during",
            "He is a good student and has scored lot of marks in 2nd term during Aug,2022. Needs improvement on Maths")

However, am not sure whether this is the best way to do this. Moreover, I don't know how can I set formatting for certain part of the text in a cell.

I expect my output to be like as below

enter image description here

Solution

  • Example code concentrating on the first student only.
    I'm assuming the comments section uses a template since the initial text for both students is the same. Therefore just working on adding the unique highlighted text. Either way obviously the other text can also be written at the same time if necessary.

    Depending on how common the text is you can hard set your indexes or calculate from the length of the text. The date text position is calculated, the improvement text position is counted.

    import xlwings as xw

filename = "foo.xlsx"
date_text = 'Aug 2022'
improve_text = 'Needs improvement on'
line1_date_pos = 67  # Length of the text that precedes the date entry

with xw.App() as app:
    wb = app.books.open(filename)
    ws = wb.sheets('Sheet1')

    date_offset = line1_date_pos + len(date_text)+1
    # Insert the date text into the cell, need to use '.api.Text' and just use
    # the index of the position where the text is to be inserted
    ws['E2'].characters[line1_date_pos].api.Text = " " + date_text + "\n"
    # Setting date text formatting requires start and end index, the end index is
    # calculated from the date text length added to the start index +1
    ws['E2'].characters[line1_date_pos:date_offset].font.bold = True
    ws['E2'].characters[line1_date_pos:date_offset].font.color = (255, 0, 0)

    # If the text template is common, the indexes might be able to be hard set.
    # Be aware text inserted by the code could change index positions 
    ws['E2'].characters[123].api.Text = "\n" + improve_text + " Maths"
    ws['E2'].characters[144:149].font.name = 'Calibri'
    ws['E2'].characters[144:149].font.size = '14'
    ws['E2'].characters[144:149].font.italic = True
    ws['E2'].characters[123:149].font.bold = True
    ws['E2'].characters[123:149].font.color = (255, 0, 0)

    wb.save(filename)
    wb.close()