Reading & writing workbooks
Writing Excel files
to_excel, ExcelWriter and appending to workbooks that already exist.
Start with the full guides
The long-form treatment of writing excel files, with the reasoning the shorter pages assume.
- GuideWriting DataFrames to Excel with PandasExport pandas DataFrames to Excel: single and multi-sheet workbooks, number and date formats, appending to existing files, and choosing the right engine.
- GuideBuilding Excel Reports with xlsxwriterThe write-only engine that formats fastest: reusable format objects, column-level formatting, charts, conditional formats, panes and constant-memory mode — and exactly when openpyxl is still the right choice.
- OverviewGetting Started with Python Excel AutomationLearn how to read, transform, and write Excel files with Python using pandas and openpyxl — the libraries, the pipeline pattern, and runnable starter code.
- GuideUsing openpyxl for Excel File ManipulationA runnable, step-by-step guide to creating, reading, editing, styling, and saving .xlsx files with openpyxl — workbooks, sheets, cells, formatting, and images.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toWrite Multiple DataFrames to One Excel FileWrite several pandas DataFrames into one .xlsx: separate sheets via one ExcelWriter, stacking on a single sheet with startrow, and appending with mode='a'.
- How-toWrite a Formatted Excel Report with xlsxwriterBuild a complete styled report in one pass: a title block, a header row you control, column formats and widths, a totals row, banded rows, an autofilter and frozen panes.
- How-toFind and Replace Text in Excel with openpyxlReplace a company name, a product code or a year across every sheet of a workbook without losing formatting: plain and regex replacement, formulas left intact, rich text preserved, and a log of every change.
- How-toInsert and Delete Rows and Columns with openpyxlAdd and remove rows and columns in an existing Excel file with openpyxl — insert_rows, delete_cols, why formulas and merges do not move, and safe deletion in reverse order.
- How-toopenpyxl: Append Data to an Existing Excel SheetAdd new rows to an existing .xlsx file with openpyxl — using ws.append(), explicit row indexing, dict-based sparse appends, type-safe values, and atomic saves.
- How-toAppend a DataFrame to an Existing Excel File with pandasAdd rows or a new sheet to a workbook that already exists — ExcelWriter mode 'a', if_sheet_exists options, appending below existing data, and the openpyxl-only limits.
- How-toControl How NaN, None and Infinity Appear in Excel Outputpandas writes NaN, None, pd.NA and NaT as empty cells and infinity as the text "inf". Learn what each choice does to Excel formulas, when na_rep and inf_rep help or hurt, and how to handle division-by-zero results and placeholders column by column.
- How-toHow pandas Data Types Are Written to Excel CellsWhat to_excel actually stores for each pandas dtype — integers, nullable types, floats, booleans, text, datetime64, date objects, timedeltas, categoricals, Decimal, infinity, Period, lists and timezone-aware timestamps — checked cell by cell with openpyxl, plus the conversions to apply before writing so the workbook holds what you meant.
- How-toWrite a DataFrame to a Specific Cell in Excel with pandasPlace a DataFrame at B5 instead of A1 — with startrow and startcol, into a new file or into an existing template without wiping its title, formatting and notes — and know when to drop down to openpyxl.
- How-toWrite a List of Dictionaries to Excel Without pandasWrite records from an API, a database cursor or your own code straight to .xlsx with openpyxl: a header from the union of keys, missing keys left blank, nested values flattened, dates and decimals kept as real Excel types, write-only mode for large lists, and a formatted table at the end.
- How-toWrite a MultiIndex DataFrame to Excel with pandasWrite pivot tables and grouped results with MultiIndex rows or columns to Excel — merged or repeated labels, the extra index-name row, the index=False error, flattening for filters, and reading it back.
- How-toWrite Clickable Links from a DataFrame Column to ExcelTurn a column of URLs into clickable links when writing a DataFrame to Excel: rely on xlsxwriter's automatic URL detection, write HYPERLINK formulas with friendly text, or set cell.hyperlink with openpyxl. Covers link text, internal sheet links, URL length limits and formula injection.
- How-toWrite Dates Without the Time Part from pandas to Excelpandas writes datetime columns with a YYYY-MM-DD HH:MM:SS format, so every date shows 00:00:00. Write real dates that display as dates: convert to date objects, set datetime_format on an xlsxwriter ExcelWriter, or apply a number format per column — and avoid the openpyxl engine option that is silently ignored.
- How-toWrite Excel Formulas from a pandas DataFramePut live formulas into a sheet written with to_excel: build formula strings per row, look up column letters by name so the formulas survive column changes, account for the header, index and startrow offsets, add a totals row, understand why the results read back as 0 or NaN until the file is recalculated, and turn the formula conversion off for text that merely starts with "=".
- How-toWrite Grouped Data with Blank Rows Between GroupsWrite a pandas DataFrame to Excel as a readable grouped report — a heading row for each group, its detail rows, a subtotal row with a live SUBTOTAL formula and a blank row before the next group — by planning the row positions first and writing once, instead of inserting rows afterwards; add a grand total that does not double count, optional outline levels, and check the formulas recalculate correctly.
- How-toWrite a Pandas DataFrame to Excel Without the IndexPass index=False to to_excel() to drop pandas row numbers from your spreadsheet — plus how it behaves with a MultiIndex, append mode, and a quick output check.
- How-toWrite Very Wide DataFrames to ExcelExcel stops at 16,384 columns. pandas raises "This sheet is too large!" above that — but only checks the DataFrame itself, so a frame that fits exactly loses its last column silently when the index is written. Detect the real width, then choose a fix: transpose short-and-wide data, reshape to long format with melt, or split the columns across several sheets with the key repeated on each, and check what was written.
- How-toReorder Columns to Match a Template Layout with pandasMake every incoming file match the column layout a template, upload form or downstream system expects: read the required order from the template itself, map renamed headers, reindex so columns land in the right place, decide what happens to missing and extra columns, check the result, and write the data under the template's own header row without disturbing its formatting.
- How-toExport a Pandas Pivot Table to Excel (Formatted)Build a pandas pivot_table, flatten its MultiIndex, export with to_excel, then add a styled header, number formats, and a grand-total row with openpyxl.
- How-toAppend New Rows to a Master Excel File Without DuplicatesWeekly extracts overlap — the same invoices appear in this week's file and last week's — and appending them blindly fills the master workbook with duplicates. Find genuinely new rows with a key-based anti-join, decide what to do with rows that changed, append with openpyxl so the master's formatting and table survive, keep reruns idempotent, and log every run.
- How-toUpdate One Excel File from Another with pandasApply a changes file to a master list — new prices, corrected emails, customers added — the way people do with copy-paste and VLOOKUP. Compare DataFrame.update, combine_first and an explicit upsert, keep a change log of old and new values, and write the updates into the existing workbook without destroying its formatting.
- How-toExport SQL Query Results to Excel with PythonRun a parameterised query with SQLAlchemy and write the result to a formatted workbook: one sheet per result set, correct date and money formats, sized columns, and chunked reads for big results.
- How-toFetch API Data into Excel with Python requestsPull JSON from an HTTP API into a workbook: a session with timeouts and retries, pagination that terminates, json_normalize for nested records, and a cache so a rerun does not hammer the endpoint.
- How-toGenerate SQL INSERT Statements from an Excel SheetTurn an Excel sheet into a reviewable .sql script for a database administrator to run: map pandas types to SQL literals, quote text safely by doubling single quotes, write NULL for blanks, dates as ISO literals, batch rows into multi-row INSERTs inside one transaction, add an upsert variant for PostgreSQL, and prove the script by running it against a scratch database and comparing every value with the sheet.
- How-toWrite a Polars DataFrame to Excel with FormattingUse DataFrame.write_excel to produce a formatted workbook: number formats, autofit, Excel tables, conditional formats, multiple sheets, totals rows and charts.
- How-toWrite Large DataFrames to Excel with write_only ModeGenerate hundred-thousand-row workbooks without the memory — openpyxl write_only, xlsxwriter constant_memory, streaming a DataFrame row by row, and the formatting you must plan ahead.
- How-toAppend Rows to an Excel Online Table with Microsoft GraphAdd new records to an Excel table in OneDrive or SharePoint without downloading the file: match columns to the table header, skip rows already present, send rows in batches, and retry safely.
- How-toRead and Write Excel Online Ranges with Microsoft GraphEdit a workbook in OneDrive or SharePoint in place: read a range or the used range into pandas, write a DataFrame back with the right address, send dates and number formats correctly, and avoid the null-means-skip trap.
- How-toAdd a Summary Sheet to an Excel Report with PythonCompute KPIs with pandas groupby, write a styled Summary sheet first, then detail sheets, and make the Summary the active leftmost tab — all with openpyxl.
- How-toPrevent Formula Injection in Excel Exports from PythonUser-supplied text that starts with "=" becomes a live formula when an export writes it to Excel: openpyxl, xlsxwriter and pandas all do this by default. Find which writers turn strings into formulas, store data as text in .xlsx files, neutralise dangerous prefixes in CSV exports, and test that no data cell ever becomes a formula.
- How-toWrite Excel Files Atomically in PythonNever leave a half-written workbook behind: why saving straight over report.xlsx can corrupt it when a script crashes or a reader opens it mid-write, the write-to-temp-then-os.replace pattern as a reusable context manager for openpyxl, pandas and XlsxWriter, validating before the swap, keeping permissions, and what changes on Windows, network shares and sync folders.
- How-toBuild an Excel Workbook in Memory with BytesIOCreate .xlsx entirely in memory with pandas, xlsxwriter or openpyxl: the close-and-seek rules, in_memory mode, sending bytes to S3 or email, and reading them back.
- How-toDownload an Excel File from a Dash AppAdd an "Export to Excel" button to a Plotly Dash dashboard: dcc.Download with a callback that sends exactly the data the user filtered, a formatted multi-sheet workbook built in memory, a sensible file name, unit tests that call the callback and open the workbook it returns, and a Flask route on the Dash server for exports too large to send through a callback.
- How-toExport a Django Queryset to ExcelTurn a Django queryset into a formatted .xlsx download: values() and related fields, iterator() for large exports, permissions, admin actions and a view test.
- How-toMake Excel Output Reproducible in PythonRun the same report twice on the same inputs and get byte-identical workbooks. Find what changes between runs — the created and modified timestamps inside the file, zip entry dates, unsorted inputs, sets, random numbers and the clock — fix each one for xlsxwriter and openpyxl, and prove it with a hash check in a test.
- How-toFormat ZIP Codes and IDs with Leading Zeros in ExcelWrite ZIP codes, employee numbers and account codes to Excel so 02134 stays 02134. Choose between storing them as text and storing numbers with a zero-padding format, write both ways with pandas, openpyxl and xlsxwriter, handle 5- and 9-digit ZIPs in one column, add prefixes and phone-style patterns, and avoid the lookup mismatches each choice can cause.
- How-toWrite Custom Number Format Codes in Excel with PythonThe four-section format language explained: dashes for zero, red parenthesised negatives, thousands scaling with a trailing comma, literal units — all without changing the stored value.
- How-toWrite a Million Rows to Excel with xlsxwriterConstant memory mode explained: what it costs, why rows must be written in order, where the temporary files go, how to split past Excel's 1,048,576-row limit, and when a CSV is the honest answer.
- How-toAdd a Line Chart to an Excel Report with PythonAdd an editable line chart to an Excel report with openpyxl: write time-series data, plot multiple series, set a date x-axis, add markers, label axes, and anchor it.
- How-toAdd a Logo Image to an Excel Report with openpyxlStep-by-step openpyxl guide to add a logo to an Excel report: create the image, anchor it at A1, size it to a header band, add a merged title, and save.
- How-toRead and Write Cell Ranges with win32comMove data between Python and Excel in single calls: block reads, the nested shape writes require, sizing ranges with End(xlUp), and Value versus Value2.
- How-toRead and Write a Live Excel Workbook with xlwingsDrive the open application rather than the file: connect to a running workbook, read and write ranges, force a recalculation, read what the formulas produced, and clean up so no invisible Excel is left behind.
- How-toConvert CSV Files to Excel with PythonTurn CSV exports into proper Excel workbooks — detect the encoding and delimiter, keep IDs as text, parse dates, combine many CSVs into one workbook with a sheet each, split files larger than Excel's row limit, and format the result.
- How-toConvert Word Document Tables to Excel with PythonPull every table out of a .docx file into a workbook with python-docx and pandas: one sheet per table named after the heading above it, merged cells that python-docx repeats, line breaks inside cells, prices and percentages stored as text, a whole folder of documents, and old .doc files.
- How-toRead and Write .ods Files with PythonHandle OpenDocument spreadsheets from Python with pandas and odfpy — reading sheets, writing output, the formatting limits, and converting .ods to .xlsx when you need more.
- How-toWrite CSV Files That Open Correctly in ExcelA CSV that looks fine in a text editor can open in Excel with garbled accents, everything crammed into column A, IDs stripped of leading zeros and long numbers in scientific notation. Write it with a UTF-8 byte order mark, the separator and decimal mark the reader's Excel expects, ISO dates, protected identifiers and safe quoting — or decide that the file should really be an .xlsx.
- How-toConvert Excel Column Letters and Numbers in PythonTurn 28 into "AB" and "XFD" into 16384 with openpyxl's get_column_letter and column_index_from_string, parse cell and range addresses, and map DataFrame columns to letters for formulas — plus why naive base-26 code breaks at Z and the 0-based/1-based traps between pandas, openpyxl and xlsxwriter.
- How-toHow openpyxl Stores Python Values: Types, Dates and NoneWhat happens to an int, a bool, a date, a Decimal, a NumPy value or a timezone-aware datetime when you assign it to an openpyxl cell — the cell type it becomes, the number format it gets, what comes back when the file is reopened, and which values fail only when you save.
- How-toWhat openpyxl Loses When You Save an Existing Workbookopenpyxl rewrites the whole file when it saves, and features it does not model disappear — sparklines, shapes and text boxes, data-bar and validation extensions, macros, slicers. Tested list, a pre-save risk scan, and safer alternatives.
- How-toWrite a Formula to an Excel Cell with openpyxlAssign a formula string to a cell with openpyxl, avoid the Text-format trap that shows the formula instead of a result, and verify what actually landed in the file.
- How-toCreate a Sheet for Each Group in a DataFrameSplit one DataFrame into one worksheet per region, customer or month with groupby and ExcelWriter — with safe sheet names, a contents sheet, consistent formatting, and the silent overwrite trap when two groups map to the same name.
- How-toAuto-Fit Column Widths When Writing with pandasStop Excel reports opening with ##### and truncated headers — measure content width in Python and set column widths with xlsxwriter or openpyxl, including a reusable helper.
- How-toExport a Styled pandas DataFrame to ExcelUse pandas Styler to colour, bold and highlight a DataFrame, then write it to Excel with the styles intact — and learn which Styler features survive the trip, including why format() does not and what to use instead.
Compare the options
Which tool or approach to pick, and why.
- ComparisonRound Numbers for Excel Output: float_format vs Number Formatspandas offers three ways to get two decimal places into Excel — to_excel's float_format, DataFrame.round and a cell number format — and they do different things. See which one changes the stored value, which one changes only the display, and why they disagree on halves like 0.015.
- Comparisonopenpyxl vs xlsxwriter vs pandas.ExcelWriterUnderstand how pandas.ExcelWriter wraps the openpyxl and xlsxwriter engines, when each one wins, and write the same DataFrame three ways with a clear decision table.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
- FixHandle Permission Denied When Writing Excel in PythonWhy openpyxl raises PermissionError when a workbook is open in Excel, how to detect the lock file, retry sensibly, fall back to a timestamped name, and avoid the problem entirely.
- FixFix "ValueError: Cannot convert to Excel" in openpyxlopenpyxl accepts only values Excel can store — numbers, text, booleans, dates, None. Lists, dicts, sets, UUIDs and pandas' NA raise "Cannot convert … to Excel". Find the offending values, convert them deliberately, and write DataFrames without surprises.
- FixFix "Excel Cannot Open the File Because the File Format or Extension Is Not Valid"Excel refuses a file your Python script produced when its bytes do not match its .xlsx name: CSV or HTML saved under an Excel extension, a writer that was never closed, bytes mangled in text mode, or a macro workbook saved as .xlsx. Diagnose which one with a short checker and fix the writing code.
- FixFix "Excel Found Unreadable Content" After Writing with PythonYour script saved a workbook and Excel offers to repair it. The rules Excel enforces — sheet names, table names, defined names, timezones — and how to satisfy them.
- FixFix "ExcelWriter object has no attribute save" in pandaspandas 2.0 removed ExcelWriter.save(), so old scripts fail with AttributeError. Replace it with a with block or close(), and fix the other removed writer APIs that usually break at the same time — book assignment, sheets assignment and handles.
- FixFix IllegalCharacterError When Writing Excel with openpyxlopenpyxl refuses to write text containing control characters such as \x00 or a vertical tab. Find which cells hold them, decide what each character should become, and clean the data so the write succeeds.
- FixFix Invalid Worksheet Name Errors When Writing ExcelSheet names built from data — regions, customers, dates — break Excel's rules: 31 characters at most, none of [ ] : * ? / \, no leading or trailing apostrophe, unique regardless of case. See what openpyxl, xlsxwriter and pandas do with each rule and write one function that makes any name safe.
- FixFix Long ID Numbers Losing Digits or Turning into 1.23E+18 in ExcelAccount numbers, card numbers and 18-digit IDs lose their last digits when written to Excel as numbers — Python writers keep 16 significant digits and Excel keeps 15. Write identifiers as text, read them back as text, and stop the leading zeros and trailing digits from changing.
- FixFix "Number Stored as Text" Warnings in Excel OutputGreen triangles all over a workbook your script wrote mean numbers went in as strings. Convert them to real numbers before writing, keep identifiers as text on purpose, and silence the warning where text is correct.
- FixFix "Sheet Already Exists" Errors in pandas ExcelWriterFix ValueError: Sheet 'Summary' already exists and if_sheet_exists is set to 'error' when writing to an existing workbook with pandas: what each if_sheet_exists option really does — error, new, replace and overlay — why overlay leaves old rows behind, the related "Append mode is not supported with xlsxwriter" error, and a safe way to refresh one sheet of a workbook.
- FixFix Text Truncated at 32,767 Characters in Excel OutputAn Excel cell holds at most 32,767 characters, and Python writers enforce it differently: openpyxl cuts long text silently, xlsxwriter returns -2, pandas warns. Find the long values before writing, make truncation an error, and keep the full text by splitting it across cells, linking to a file, or choosing another format.
- FixFix "ValueError: I/O operation on closed file" with ExcelWriterpandas raises "ValueError: I/O operation on closed file" when an ExcelWriter or the buffer it wrote to is used after it was closed: a file handle whose with block ended before the writer, a BytesIO returned from inside its own with block, or a buffer read after close. Reproduce each case, see why the file ends up empty, fix the order of the blocks, and catch the silent variant where writing after the with block loses a sheet without any error.
- FixFix Wrong Sheet Order After Writing with pandasSheets come out as Apr, Aug, Dec, Feb… or the summary ends up last after appending to a workbook. Find which of the usual causes applies — alphabetical group keys, sets and unsorted dictionaries, sheets appended to an existing file — write the sheets in the order you want, or reorder an existing workbook with openpyxl, set the sheet that opens first, and check the final order in a test.
- FixFix XlsxWriter Overwriting an Existing WorkbookYou pointed XlsxWriter — or pandas with its default engine — at an existing workbook to add a sheet, and every other sheet vanished. XlsxWriter only creates new files and never reads old ones. See why, what "Append mode is not supported with xlsxwriter!" means, how to add or replace a sheet with openpyxl's append mode and if_sheet_exists, and how to guard against overwriting a file by accident.
Follow it through a track
Prefer an ordered route? These tracks cover writing excel files along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Writing Excel files is part of Reading & writing workbooks.