How to write an Excel file with pandas

Spreadsheet handoffs often need worksheet names and display controls that a flat text file cannot preserve. pandas can write a DataFrame directly to an .xlsx workbook while selecting the columns that belong in the handoff.

The DataFrame.to_excel() method handles the table export, while openpyxl supplies the workbook engine used for both writing and inspection. The autofilter argument requires pandas 3.0 or newer, and the pandas[excel] extra installs the supported Excel dependencies.

Excel write mode replaces any workbook already at the destination path. A dependable handoff reopens the saved file to check both the DataFrame values and workbook settings because a completed write call alone cannot prove that the intended worksheet is usable.

1. Install the pandas Excel dependencies in the active Python environment.

   $ python3 -m pip install "pandas[excel]"

   Related: How to install pandas with pip

2. Create write_orders_excel.py with the imports and output-path setup.

   write_orders_excel.py

   from pathlib import Path
    
   import pandas as pd
   from openpyxl import load_workbook
   from pandas.testing import assert_frame_equal
    
   output = Path("exports/orders.xlsx")
   output.parent.mkdir(parents=True, exist_ok=True)

   Running the completed script replaces any workbook already stored at exports/orders.xlsx.

3. Append the source DataFrame below the output-path setup in write_orders_excel.py.

   orders = pd.DataFrame(
       {
           "order_id": pd.Series(["A100", "A101", "A102"], dtype="string"),
           "customer": ["Ada Lovelace", "Lin Chen", "Maya Patel"],
           "region": ["EMEA", "APAC", "AMER"],
           "total_usd": [149.5, None, 212.0],
           "ordered_at": pd.to_datetime(
               ["2026-06-01", "2026-06-02", "2026-06-03"]
           ),
           "internal_note": ["priority", "review", "standard"],
       }
   )

4. Append the Excel export block below the DataFrame definition in write_orders_excel.py.

   export_columns = ["order_id", "customer", "region", "total_usd", "ordered_at"]
    
   orders.to_excel(
       output,
       sheet_name="Orders",
       columns=export_columns,
       index=False,
       na_rep="",
       float_format="%.2f",
       freeze_panes=(1, 0),
       autofilter=True,
       engine="openpyxl",
   )

   columns leaves internal_note out of the workbook, index=False omits row labels, and the worksheet opens with a frozen header row and filters over the exported table.

5. Append the workbook read-back checks below the export call in write_orders_excel.py.

   round_trip = pd.read_excel(
       output,
       sheet_name="Orders",
       dtype={"order_id": "string"},
       parse_dates=["ordered_at"],
       engine="openpyxl",
   )
   assert_frame_equal(
       round_trip,
       orders.loc[:, export_columns],
       check_dtype=False,
   )
    
   workbook = load_workbook(output, data_only=True)
   worksheet = workbook["Orders"]
    
   assert workbook.sheetnames == ["Orders"]
   assert worksheet.freeze_panes == "A2"
   assert worksheet.auto_filter.ref == "A1:E4"
    
   print(f"Wrote {output}")
   print(f"Sheet: {worksheet.title}")
   print(f"Rows: {len(round_trip)}")
   print(f"Headers: {', '.join(round_trip.columns)}")
   print(f"Freeze panes: {worksheet.freeze_panes}")
   print(f"Auto filter: {worksheet.auto_filter.ref}")

6. Run the completed write_orders_excel.py script to produce the Excel workbook.

   $ python3 write_orders_excel.py
   Wrote exports/orders.xlsx
   Sheet: Orders
   Rows: 3
   Headers: order_id, customer, region, total_usd, ordered_at
   Freeze panes: A2
   Auto filter: A1:E4

   The assertions stop execution if the worksheet name, row values, column order, frozen header, or filter range differs from the intended export.
   Related: How to read an Excel file with pandas
   Tool: XLSX to CSV Converter