Reading an excel spreadsheet and formatting the text from the cells

Solution for Reading an excel spreadsheet and formatting the text from the cells
is Given Below:

I have an Excel Spreadsheet that I want to output to a Word doc based on an ID.

So here is what it looks like(a small portion):

Dungeon_Room_ID  |  Monster  |  Treasure
----------------------------------------------
       1            Orc         10 Copper
       1            Orc         2 Silver
       1            Orc         1 Iron Sword
       2            Mad Elf     Staff of Fire
       2            Mad Elf     150 Gold
       2            Mad Elf     Steel Mace
       2            Mad Elf     Cloak of Shadows
       2            Mad Elf     Glass Sword
       3            Wolf        5 Bronze
       3            Wolf        Ice Wand
   

I want to print out to a text file based on the Dungeon_Room_ID.

So, for example, based on the Dungeon_Room_ID, the text file would have this:

Monster: Orc

Treasure(s):
* 10 Copper
* 2 Silver
* 1 Iron Sword


Monster: Mad Elf

Treasure(s):
* Staff of Fire
* 150 Gold
* Steel Mace
* Cloak of Shadows
* Glass Sword


Monster: Wolf

Treasure(s):
* 5 Bronze
* Ice Wand

Here is my Python code:

from openpyxl import load_workbook
wb = load_workbook(
    filename="G:/Games/Python/MonsterHunterDungeonCrawl/MonsterList.xlsx", read_only=True)
ws = wb['Sheet1']

for row in ws.rows:
    for cell in row:
        if cell.value:
            print(cell.value)

wb.close()

But when I print it out, it shows this(here is just the Orc part):

1

Orc

10 Copper

1

Orc

2 Silver

1

Orc

1 Iron Sword

So is there a way to code it so that it does not repeat the monster Name and to format it nicely like in my example?

Thanks!

The below code will print and export as a .txt file the example code you provided. If you need a docx, this can be tweaked to achieve that using the docx package — you might be able to write the output_str as one paragraph, or you could loop trough the output_list list and write each line as a paragraph.

I included the logic in the comments, but essentially, it just iterates over the rows and if its the first time encountering the monster id, it prints the whole row, else just the treasure (with formatting).

from openpyxl import load_workbook
wb = load_workbook(
    filename="monsters.xlsx", read_only=True)
ws = wb['Sheet1']


# easily format the treasure item line 
def treasure_line(item):
    return f"* {item}"

# list of monster_ids whose first row has been read
listed_monster_ids = []

# list to hold output for exporting to .txt
output_lines = []


# loop through excel rows
for x, row in enumerate(ws.rows):
    # skip header row
    if x > 0:
        # easily reference cell values
        id = row[0].value
        monster = row[1].value
        treasure = row[2].value

        # if the monster_id isn't in the list, we need to write the 
        # the "header info" and list the treasure from this row
        # and if monster_id is in the list, we just need the treasure from that row
        if id not in listed_monster_ids:
            # add newlines between monsters, but not before first one
            if x == 1:
                print(f'Monster: {monster}n')
                output_lines.append(f'Monster: {monster}n')
            
            else:
                print(f'nnMonster: {monster}n')
                output_lines.append(f'nnMonster: {monster}n')
            
            print('Treasure(s):')
            output_lines.append('Treasure(s):')
            
            print(treasure_line(treasure))
            output_lines.append(treasure_line(treasure))
            
            listed_monster_ids.append(id)
        
        else:
            print(treasure_line(treasure))
            output_lines.append(treasure_line(treasure))


# join the output lines for nice formatting 
glue = "n"
output_str = glue.join(output_lines)

# export to .txt
with open('monsters.txt', 'w') as out:
    out.write(output_str)

Output:

Monster: Orc

Treasure(s):
* 10 Copper
* 2 Silver
* 1 Iron Sword


Monster: Mad Elf

Treasure(s):
* Staff of Fire
* 150 Gold
* Steel Mace
* Cloak of Shadows
* Glass Sword


Monster: Wolf

Treasure(s):
* 5 Bronze
* Ice Wand