How to write XLSX Excel file row by row?

Python Help
1
  1. Hi I’m new to Python. I have not yet finished a 70 hour tutorial.
  2. My home system: Python 3.12 on MS Windows 11 Home.

I’m looking to learn how to write an Excel XLSX file using Pandas, or perhaps another writer but I have special requirements.

What I’ve researched and my notes.

  1. Searches used: 3
  2. AI searches: 2
  3. Web Pages read: 8
  4. Since I’m new to Python I didn’t understand the Pandas documentation.
  5. Most examples did not have any code so I could write each row out column by column.
  6. If you have a web page that meets my requirements I would be happy to read it. I’m still in the learning curve of Python though.
  7. I already installed a bunch of modules that pandas uses, like xlsxwriter and several others.

Requirements

  1. I need to write an Excel spreadsheet row-by-row and cell-by-cell as some numeric cells will need to be formatted as dollars (with 2 decimal points), some numeric cells will need to have no decimal points. And I need to do calculations on other data to determine the data that goes into a cell. Some cells will need to be formatted as a percent. And as I write the data I need to check it for possible errors.
  2. I will also need to know how to put a formula into a cell that adds up 2 other cells.
  3. I do not need the index commonly seen in column A in most examples. I’m trying to minimize the amount of manual handling of the file after it’s made.
  4. Spreadsheets I write are normally under 200 lines but can be 10,000 lines on occasion. This will eventually run on an Azure virtual machine with only 1GB of memory so I’m concerned about memory usage.
  5. Optional: I will need to format each cell with a different background color based on the numeric value of the cell. For example, if the value is > 100, make the cell background light red.

In this example below, I get an error AttributeError: ‘XlsxWriter’ object has no attribute ‘sheet_name’. None of the examples I found explain this or how to use sheet_name like this. The default sheet_name seems to be “Sheet1” but that’s all I know.

def writexlsx_pandas(filename,outdata):
    procname = str(inspect.stack()[0][3])
    print(f"\n{procname}: Writing {filename}")
    #sheet_name="Mytest"
    pdb.set_trace() # Breakpoint
    
    df = pandas.DataFrame(outdata, columns=["Name", "Age", "Float"]) # Create dataframe
    writer = pandas.ExcelWriter(filename, engine='xlsxwriter') # use write engine "xlsxwriter"
    for row in df.itertuples():
        # Extract data from the row tuple
        name, age, value = row[1:]  # Skip the index

        # Write the row data to the Excel sheet
        writer.sheet_name.write(row.Index - 1, 0, name)  # Adjust row index for clarity
        writer.sheet_name.write(row.Index - 1, 1, age)
        writer.sheet_name.write(row.Index - 1, 2, value)

    # Save the Excel file
    writer.save()

# Main program.
outfile="out_pandas.xlsx"
outdata = [["Name1", 10, 3.14],
    ["Name2", 20, 2.86],
    ["Name3", 22, 1.05]
    ]
writexlsx_pandas2(outfile,outdata)

A sample spreadsheet would look like this which I will put as CSV format:

Name,Address,City,State,Zip
John Smith,123 Pine St,Grand Forks,IA,12345
Dave Jones,2800 8 Mile Rd,Detroit,MI,49555
Sam Green,1550 Mumbling Brook,Hastings,MI,49333

Same data as, I think this is a list of lists:

outdata = [
    [Name,Address,City,State,Zip],
    [John Smith,123 Pine St,Grand Forks,IA,12345],
    [Dave Jones,2800 8 Mile Rd,Detroit,MI,49555],
    [Sam Green,1550 Mumbling Brook,Hastings,MI,49333]
]

Thank you for your help.

2

This might help:

# Open a new speadsheet
writer = pandas.ExcelWriter(filename, engine='xlsxwriter')

# Add a new sheet
sheet_name = "Mytest"
sheet = writer.book.add_worksheet(sheet_name)

for row in df.itertuples():
    # Extract data from the row tuple
    name, age, value = row[1:]  # Skip the index
    # Write the row data to the Excel sheet
    writer.sheet_name.write(row.Index - 1, 0, name)  # Adjust row index for clarity
    writer.sheet_name.write(row.Index - 1, 1, age)
    writer.sheet_name.write(row.Index - 1, 2, value)

# Close the spreadsheet
writer.close()
1 Like
3

I had to add a line to create the data frame “df” variable like this:

	sheet = writer.book.add_worksheet(sheet_name)
	df = pandas.DataFrame(outdata, columns=["Name", "Age", "Float"])

but then I got an error “AttributeError: ‘XlsxWriter’ object has no attribute ‘sheet_name’” in this line:

writer.sheet_name.write(row.Index - 1, 0, name)  # Adjust row index for clarity

I’m not sure what went wrong here.

4

Hi !

Have you read this documentation page ?

https://xlsxwriter.readthedocs.io/working_with_pandas.html

Seems to cover some of the use cases described in your first message :slightly_smiling_face:

5

Ok I did get it working. This does not format any headers or data but here’s the working code for others to take a look at. One must have some modules installed first: pandas, xlsxwriter, inspect to get the function name into “procname”.

#########################################################
# writexlsx_pandas3: Write out columnar data to XLSX file
""" In: Excel XLSX filename, include path if not in directory
with python file. Outdata = list of lists.
Out: nothing.
"""
def writexlsx_pandas3():
    procname = str(inspect.stack()[0][3])
    print("")
    outfile="out_pandas.xlsx"
    outdata = [["Name", "Amt", "Float"], # Headers
    ["Name1", 10, 3.14],
    ["Name2", 20, 2.86],
    ["Name3", 22, 1.05]
    ]
    filename=outfile
    # Open a new speadsheet
    writer = pandas.ExcelWriter(filename, engine='xlsxwriter')

    # Add a new sheet
    sheet_name = "Mytest"
    sheet = writer.book.add_worksheet(sheet_name)
    df = pandas.DataFrame(outdata, columns=["Name", "Age", "Float"])
    for row in df.itertuples():
        # Extract data from the row tuple
        name, age, value = row[1:]  # Skip the index
        # Write the row data to the Excel sheet
        sheet.write(row.Index, 0, name)  # Adjust row index for clarity
        sheet.write(row.Index, 1, age)
        sheet.write(row.Index, 2, value)

    # Close the spreadsheet
    writer.close()
    print(f"{procname} Done writing {filename}")