### Install xlsxedit with pandas extra Source: https://xlsxedit.jonasruilong.com/docs/pandas Install xlsxedit with the pandas extra. Core xlsxedit only requires lxml; pandas is optional. ```bash pip install xlsxedit[pandas] # or: pip install pandas ``` -------------------------------- ### pic.media_path Source: https://xlsxedit.jonasruilong.com/docs/features Get the path inside the package, e.g. xl/media/image1.jpeg. ```APIDOC ## pic.media_path ### Description Get the path inside the package, e.g. xl/media/image1.jpeg. ### Property - **media_path** (string) - The media path. ``` -------------------------------- ### Example flow for a chart (relationship graph) Source: https://xlsxedit.jonasruilong.com/docs/how-it-works Shows how a chart is linked through relationships and preserved as opaque blobs. None of the content types are registered, so each part is saved unchanged. ```text workbook.xml.rels → worksheets/sheet1.xml sheet1.xml.rels → drawings/drawing1.xml drawing1.xml.rels → charts/chart1.xml (+ maybe media/image1.png) None of those content types are registered → each is Part(blob=original) save → all those blobs written again ``` -------------------------------- ### xlsxedit public API usage example Source: https://xlsxedit.jonasruilong.com/docs/how-it-works Shows the core workflow: create or open a workbook, access a worksheet, set cell values, apply formatting, add images, charts, tables, and conditional formatting, then save. Includes the SAR convenience methods wb.replace and wb.add_worksheet. ```python from xlsxedit import Workbook wb = Workbook.create() wb = Workbook.open("report.xlsx") ws = wb["Sheet1"] ws["A1"].value = "hello" ws["A2"].value = 42 ws["A3"].apply_date_format() ws.column_dimensions["C"].width = 20.0 ws.merge_cells("A1:C1") ws["B1"].hyperlink.url = "https://xlsxedit.jonasruilong.com" ws.add_image("logo.jpg", anchor="E2") ws.add_chart("bar", anchor="G2", data_range="A1:B5", title="Sales") ws.add_table("A1:C10", ["Item", "Price"]) ws.add_conditional_formatting("A2:A10", operator="greaterThan", formula="0") wb.add_worksheet("Report") wb.replace("old", "new") # SAR convenience — calls base cell APIs wb.replace("{n}", 888, value_type="number") wb.save("report_out.xlsx") ``` -------------------------------- ### Quick start: write and read with xlsxedit engine Source: https://xlsxedit.jonasruilong.com/docs/pandas Registers the xlsxedit engine, writes a DataFrame to Excel, and reads it back using engine='xlsxedit'. ```python import pandas as pd import xlsxedit.pandas_io as xpi xpi.register() # once per process df = pd.DataFrame({"Item": ["Widget", "Gadget"], "Qty": [2, 5]}) # Write with pd.ExcelWriter("out.xlsx", engine="xlsxedit") as writer: df.to_excel(writer, sheet_name="Data", index=False) # Read got = pd.read_excel("out.xlsx", engine="xlsxedit") ``` -------------------------------- ### pic.width, pic.height Source: https://xlsxedit.jonasruilong.com/docs/features Get or set the size of the picture in pixels. ```APIDOC ## pic.width, pic.height ### Description Get or set the size of the picture in pixels. ### Properties - **width** (int) - Width in pixels. - **height** (int) - Height in pixels. ``` -------------------------------- ### Example cell with shared string index Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Shows a cell with type 's' (shared string) where the element contains a 0-based index into sharedStrings.xml. In this example, index 0 resolves to the first shared string. Empty cells are typically omitted from the XML. ```xml 0 ``` -------------------------------- ### Shared strings table example (sharedStrings.xml) Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Shows the XML structure of the shared strings table with count and uniqueCount attributes, and items containing text. Index is 0-based position among siblings. ```xml this is a1 this is b1 ... ``` -------------------------------- ### Register xlsxedit pandas engine Source: https://xlsxedit.jonasruilong.com/docs/pandas Registers the writer via pandas' public register_writer and installs the reader by patching ExcelFile._engines. Call once per process. ```python import xlsxedit.pandas_io as xpi xpi.register() ``` -------------------------------- ### pic.name Source: https://xlsxedit.jonasruilong.com/docs/features Get or set the drawing name of a picture. ```APIDOC ## pic.name ### Description Get or set the drawing name of a picture. ### Property - **name** (string) - The drawing name. ``` -------------------------------- ### ws.column_dimensions["C"] Source: https://xlsxedit.jonasruilong.com/docs/features Column width proxy. Use .width to get or set the width. ```APIDOC ## ws.column_dimensions["C"] ### Description Column width proxy. Use .width to get or set the width. ### Property `ws.column_dimensions[column_letter]` ### Parameters - **column_letter** (string) - Required - Column letter, e.g., "C". ### Returns - **ColumnDimension** - Proxy object with .width property. ``` -------------------------------- ### wb[sheet_name] Source: https://xlsxedit.jonasruilong.com/docs/features Get a worksheet by name. Raises KeyError if the sheet does not exist. ```APIDOC ## wb[sheet_name] ### Description Get a worksheet by name. Raises KeyError if the sheet does not exist. ### Method Indexing ### Parameters - **sheet_name** (str) - Required - Name of the worksheet to retrieve. ### Errors - **KeyError** - If the sheet name is not found. ``` -------------------------------- ### pic.anchor Source: https://xlsxedit.jonasruilong.com/docs/features Get or set the top-left anchor cell of a picture. ```APIDOC ## pic.anchor ### Description Get or set the top-left anchor cell of a picture. ### Property - **anchor** (string) - The top-left anchor cell. ``` -------------------------------- ### ws.row_dimensions[4] Source: https://xlsxedit.jonasruilong.com/docs/features Row height proxy. Use .height to get or set the height. ```APIDOC ## ws.row_dimensions[4] ### Description Row height proxy. Use .height to get or set the height. ### Property `ws.row_dimensions[row_number]` ### Parameters - **row_number** (int) - Required - Row number, e.g., 4. ### Returns - **RowDimension** - Proxy object with .height property. ``` -------------------------------- ### cell.offset(cols=0, rows=0) Source: https://xlsxedit.jonasruilong.com/docs/features Get a neighbor cell relative to this cell. For example, offset(cols=1, rows=1) returns the cell one right and one down. ```APIDOC ## cell.offset(cols=0, rows=0) ### Description Get a neighbor cell relative to this cell. For example, offset(cols=1, rows=1) returns the cell one right and one down. ### Method - **offset(cols=0, rows=0)** - Returns the neighbor cell. ### Parameters - **cols** (int) - Optional - Number of columns to offset (default 0). - **rows** (int) - Optional - Number of rows to offset (default 0). ``` -------------------------------- ### cell.address Source: https://xlsxedit.jonasruilong.com/docs/features Get the cell address. ```APIDOC ## cell.address ### Description Get the cell address. ### Property - **address** (string) - The cell address. ``` -------------------------------- ### ws[address] Source: https://xlsxedit.jonasruilong.com/docs/features Gets the Cell object at the given address, e.g., "A1" or "BC100". ```APIDOC ## ws[address] ### Description Gets the Cell object at the given address, e.g., "A1" or "BC100". ### Method Indexer ### Parameters - **address** (string) - Required - Cell address, e.g., "A1". ### Returns - **Cell** - The cell at the specified address. ``` -------------------------------- ### cell.worksheet Source: https://xlsxedit.jonasruilong.com/docs/features Get the owning worksheet of the cell. ```APIDOC ## cell.worksheet ### Description Get the owning worksheet of the cell. ### Property - **worksheet** (Worksheet) - The owning worksheet. ``` -------------------------------- ### pic.offset_x, pic.offset_y Source: https://xlsxedit.jonasruilong.com/docs/features Get or set the inset within the anchor cell in pixels. Default 0 means flush. ```APIDOC ## pic.offset_x, pic.offset_y ### Description Get or set the inset within the anchor cell in pixels. Default 0 means flush. ### Properties - **offset_x** (int) - Horizontal inset in pixels. - **offset_y** (int) - Vertical inset in pixels. ``` -------------------------------- ### cell.hyperlink Source: https://xlsxedit.jonasruilong.com/docs/features Get a Hyperlink proxy for the cell, providing access to url, location, and display properties. ```APIDOC ## cell.hyperlink ### Description Get a Hyperlink proxy for the cell, providing access to url, location, and display properties. ### Property - **hyperlink** (Hyperlink) - The Hyperlink proxy. ``` -------------------------------- ### cell.style Source: https://xlsxedit.jonasruilong.com/docs/features Get a CellStyle read proxy for the cell, providing access to bold, colors, fonts, alignment, and number format. ```APIDOC ## cell.style ### Description Get a CellStyle read proxy for the cell, providing access to bold, colors, fonts, alignment, and number format. ### Property - **style** (CellStyle) - The CellStyle read proxy. ``` -------------------------------- ### cell.formula Source: https://xlsxedit.jonasruilong.com/docs/features Get or set the formula string of a cell. Setting to None removes the formula. ```APIDOC ## cell.formula ### Description Get or set the formula string of a cell. Setting to None removes the formula. ### Property - **formula** (string or None) - The formula string, e.g. '=A2*2'. ``` -------------------------------- ### TwoCellAnchor Geometry Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Example of a twoCellAnchor element defining image size and position based on cell corners. ```xml 1 0 0 1 2 454793 8 86102 … ``` -------------------------------- ### Example XML rows for date cells (B5 and B6) Source: https://xlsxedit.jonasruilong.com/docs/excel-structure XML snippet from DifferentCellTypes.xlsx showing two cells with serial numbers and style indices. B5 uses built-in short date (numFmtId 14) and B6 uses custom long date (numFmtId 168). Comments show the corresponding calendar dates. ```xml 45876 45890 ``` -------------------------------- ### cell.data_type Source: https://xlsxedit.jonasruilong.com/docs/features Get the raw OOXML cell type. Returns 's', 'str', 'inlineStr', 'b', 'e', or None for numeric. ```APIDOC ## cell.data_type ### Description Get the raw OOXML cell type. Returns 's', 'str', 'inlineStr', 'b', 'e', or None for numeric. ### Property - **data_type** (string or None) - The OOXML cell type. ``` -------------------------------- ### write_dataframe modes: overwrite, clear_range, insert Source: https://xlsxedit.jonasruilong.com/docs/large-data-export Three examples of write_dataframe calls: overwrite mode replaces cells at A5+ (including footer if it sits on A5); re-export with fewer rows uses clear_range to clear leftovers; insert mode moves the footer down by len(df). Insert is not faster than overwrite and does not rewrite formula expressions or move chart/drawing anchors. ```python # Overwrite — replaces cells at A5+ (including footer if it sits on A5) wb.write_dataframe(df, at_cell="A5", header=False, mode="overwrite") # Re-export into the same slot with fewer rows — clear leftovers first wb.write_dataframe( df, at_cell="A5", header=False, mode="overwrite", clear_range="A5:D100", ) # Insert — footer on row 5 moves down by len(df) wb.write_dataframe(df, at_cell="A5", header=False, mode="insert") Copy ``` -------------------------------- ### Formula cell XML (f and v elements) Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Shows a formula cell with f child (formula) and cached v value. Formula cells have a f child and usually a cached v. The example shows C3 with formula A3*B3 and cached value 437. ```xml A3*B3 437 ``` -------------------------------- ### Run pandas tutorial script Source: https://xlsxedit.jonasruilong.com/docs/pandas Runs the tutorial script that creates tutorial/output/pandas_tutorial.xlsx, demonstrating register, write, read, and a simple template fill. ```bash python tutorial/pandas_tutorial.py ``` -------------------------------- ### Open workbook and save with include_orphans=True Source: https://xlsxedit.jonasruilong.com/docs/how-it-works Demonstrates how to discover orphan parts and optionally include them when saving. Default save() omits orphans; include_orphans=True keeps them. ```python wb = Workbook.open("report.xlsx") if wb.orphan_partnames: print("unrelated ZIP cargo:", wb.orphan_partnames) wb.save("out.xlsx", include_orphans=True) # keep that cargo ``` -------------------------------- ### Workbook.create() Source: https://xlsxedit.jonasruilong.com/docs/features Create a new workbook from a bundled template. Equivalent to Workbook(). ```APIDOC ## Workbook.create() ### Description Create a new workbook from a bundled template. Equivalent to Workbook(). ### Method Class method ### Notes Same as Workbook(). ``` -------------------------------- ### Workbook.open(path, *, large=False) Source: https://xlsxedit.jonasruilong.com/docs/features Load an existing .xlsx file. Equivalent to Workbook(path). ```APIDOC ## Workbook.open(path, *, large=False) ### Description Load an existing .xlsx file. Equivalent to Workbook(path). ### Method Class method ### Parameters - **path** (str or file-like) - Required - Path to the .xlsx file, unpacked folder, or file-like object. - **large** (bool) - Optional - Set to True for bulk operations. ### Notes Same as Workbook(path). ``` -------------------------------- ### Workbook(path, *, large=False) Source: https://xlsxedit.jonasruilong.com/docs/features Load an existing .xlsx, OPC folder, or binary file-like. Equivalent to Workbook.open(path). ```APIDOC ## Workbook(path, *, large=False) ### Description Load an existing .xlsx, OPC folder, or binary file-like. Equivalent to Workbook.open(path). ### Method Constructor ### Parameters - **path** (str or file-like) - Required - Path to the .xlsx file, OPC folder, or binary file-like object. - **large** (bool) - Optional - Set to True for bulk operations. ### Notes Same as Workbook.open(path). ``` -------------------------------- ### Template layout (header + data slot + footer) Source: https://xlsxedit.jonasruilong.com/docs/large-data-export Shows the row layout used in the tutorial: rows 1-3 are title/client/date, row 4 is column headers, row 5 is the footer (overwritten on first data row in overwrite mode, shifts down on insert), and rows 5-7 are data written at at_cell="A5". ```text Row 1-3 Title, client, date ← untouched Row 4 Column headers ← untouched Row 5 Footer ← overwritten on first data row (overwrite) ← shifts down on insert (e.g. row 8 for 3 rows) Rows 5-7 Data from DataFrame ← written at at_cell="A5" in the tutorial Copy ``` -------------------------------- ### Workbook() Source: https://xlsxedit.jonasruilong.com/docs/features Create a new workbook from a bundled template. Equivalent to Workbook.create(). ```APIDOC ## Workbook() ### Description Create a new workbook from a bundled template. Equivalent to Workbook.create(). ### Method Constructor ### Notes Same as Workbook.create(). ``` -------------------------------- ### Using the xlsxedit read_excel helper Source: https://xlsxedit.jonasruilong.com/docs/pandas Uses the helper function to automatically register and force the xlsxedit engine for reading Excel files. ```python from xlsxedit.pandas_io import read_excel df = read_excel("book.xlsx") ``` -------------------------------- ### ExcelWriter engine usage Source: https://xlsxedit.jonasruilong.com/docs/pandas Demonstrates registering the xlsxedit engine and using it to create a new workbook or modify an existing template while preserving styles. ```python import pandas as pd import xlsxedit.pandas_io as xpi xpi.register() df = pd.DataFrame({"Item": ["Widget"], "Qty": [2]}) # Create a new workbook with pd.ExcelWriter("out.xlsx", engine="xlsxedit") as writer: df.to_excel(writer, sheet_name="Data", index=False) # Fill an existing template (preserves other sheets / charts / styles) with pd.ExcelWriter( "template.xlsx", engine="xlsxedit", mode="a", if_sheet_exists="overlay", ) as writer: df.to_excel(writer, sheet_name="Report", startrow=4, header=False, index=False) ``` -------------------------------- ### Pipeline: cell style to numFmtId to rendering Source: https://xlsxedit.jonasruilong.com/docs/excel-structure ASCII diagram showing how a cell's style index (s) maps to numFmtId and then to a format string or built-in id, which Excel uses to render values like currency, percent, or dates. ```text cell s="5" → styles.xml cellXfs[5] → numFmtId ↓ numFmts[] or built-in id ↓ Excel renders $100.00, 50%, 7 Aug 2025, … ``` -------------------------------- ### wb.save(path, *, include_orphans=False) Source: https://xlsxedit.jonasruilong.com/docs/features Write the workbook package to a path or binary file-like object. ```APIDOC ## wb.save(path, *, include_orphans=False) ### Description Write the workbook package to a path or binary file-like object. ### Method Method ### Parameters - **path** (str or file-like) - Required - Destination path or binary file-like object. - **include_orphans** (bool) - Optional - Whether to include orphan parts in the saved package. ``` -------------------------------- ### ws.tables Source: https://xlsxedit.jonasruilong.com/docs/features Returns a list of Table objects. ```APIDOC ## ws.tables ### Description Returns a list of Table objects. ### Property `ws.tables` ### Returns - **list[Table]** - List of tables. ``` -------------------------------- ### ws.charts Source: https://xlsxedit.jonasruilong.com/docs/features Returns a list of Chart objects. ```APIDOC ## ws.charts ### Description Returns a list of Chart objects. ### Property `ws.charts` ### Returns - **list[Chart]** - List of charts. ``` -------------------------------- ### Writer engine_kwargs configuration Source: https://xlsxedit.jonasruilong.com/docs/pandas Configuration options passed directly to xlsxedit to control template handling, row/column styles, and clearing ranges. ```python engine_kwargs={ "large": True, # lighter open for huge templates "mode": "overwrite", # or "insert" (shifts rows down) "template_rows": [7, 8], # copy template sample-row styles "clear_range": "A5:D1000", # clear before overwrite "row_styles": [...], "column_styles": [...], } ``` -------------------------------- ### ws.images Source: https://xlsxedit.jonasruilong.com/docs/features Returns a list of Picture objects representing existing drawings. ```APIDOC ## ws.images ### Description Returns a list of Picture objects representing existing drawings. ### Property `ws.images` ### Returns - **list[Picture]** - List of images. ``` -------------------------------- ### cell.value Source: https://xlsxedit.jonasruilong.com/docs/features Get or set the typed value of a cell. Supports str, int, float, bool, date, datetime, and None. Overwriting a formula drops calcChain.xml. ```APIDOC ## cell.value ### Description Get or set the typed value of a cell. Supports str, int, float, bool, date, datetime, and None. Overwriting a formula drops calcChain.xml. ### Property - **value** (mixed) - Get/set the typed value. ``` -------------------------------- ### Template sample rows (copy from Excel) Source: https://xlsxedit.jonasruilong.com/docs/large-data-export Uses template_rows to copy the style index from formatted sample cells (e.g., rows 7-8 for zebra). Bulk write copies the 's' style index per column. Single template row can be specified as template_rows=7. ```python wb.write_dataframe(df, at_cell="A7", header=False, template_rows=[7, 8]) # single template row: template_rows=7 ``` -------------------------------- ### wb.insert_image_at_placeholder(placeholder, image_path, *, width, height, max_width, max_height) Source: https://xlsxedit.jonasruilong.com/docs/features Clear the cell containing the placeholder and insert an image at that location. Size kwargs are in pixels and forwarded to add_image. If only width is provided, aspect ratio is preserved. ```APIDOC ## wb.insert_image_at_placeholder(placeholder, image_path, *, width, height, max_width, max_height) ### Description Clear the cell containing the placeholder and insert an image at that location. Size kwargs are in pixels and forwarded to add_image. If only width is provided, aspect ratio is preserved. ### Method Method ### Parameters - **placeholder** (str) - Required - The placeholder text in a cell to be replaced. - **image_path** (str) - Required - Path to the image file. - **width** (int, optional) - Optional - Width of the image in pixels. - **height** (int, optional) - Optional - Height of the image in pixels. - **max_width** (int, optional) - Optional - Maximum width of the image in pixels. - **max_height** (int, optional) - Optional - Maximum height of the image in pixels. ``` -------------------------------- ### Measure performance with bench_large_export.py Source: https://xlsxedit.jonasruilong.com/docs/large-data-export Run this script to stress-test your machine's performance with a specified number of rows. ```bash python tutorial/bench_large_export.py ``` -------------------------------- ### Styles pipeline diagram Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Shows the flow from fonts, fills, borders, and numFmts to cellXfs and the cell s attribute. Use to understand how style indices resolve. ```text fonts[] fills[] borders[] numFmts[] ↓ cellXfs[] ← list of format records ↓ cell attribute s="N" points at cellXfs[N] Copy ``` -------------------------------- ### ws.add_image(path, *, anchor, width, height, name, offset_x, offset_y) Source: https://xlsxedit.jonasruilong.com/docs/features Inserts an image at the specified anchor. Pixel offsets are within the anchor cell (default 0). ```APIDOC ## ws.add_image(path, *, anchor, width, height, name, offset_x, offset_y) ### Description Inserts an image at the specified anchor. Pixel offsets are within the anchor cell (default 0). ### Method `add_image(path, *, anchor, width, height, name, offset_x, offset_y)` ### Parameters - **path** (string) - Required - Path to the image file. - **anchor** (string) - Required - Anchor cell address. - **width** (int) - Optional - Image width. - **height** (int) - Optional - Image height. - **name** (string) - Optional - Image name. - **offset_x** (int) - Optional - Horizontal offset in pixels (default 0). - **offset_y** (int) - Optional - Vertical offset in pixels (default 0). ``` -------------------------------- ### wb.add_worksheet(name) Source: https://xlsxedit.jonasruilong.com/docs/features Add a new worksheet with a blank template. ```APIDOC ## wb.add_worksheet(name) ### Description Add a new worksheet with a blank template. ### Method Method ### Parameters - **name** (str) - Required - Name of the new worksheet. ``` -------------------------------- ### pic.replace(image_path) Source: https://xlsxedit.jonasruilong.com/docs/features Swap the image bytes with a new image from the given path. ```APIDOC ## pic.replace(image_path) ### Description Swap the image bytes with a new image from the given path. ### Method - **replace(image_path)** - Replaces the image. ### Parameters - **image_path** (string) - Required - Path to the new image. ``` -------------------------------- ### wb.styles Source: https://xlsxedit.jonasruilong.com/docs/features Styles part. Advanced use. ```APIDOC ## wb.styles ### Description Styles part. Advanced use. ### Method Property ### Returns - **object** - The styles part object. ``` -------------------------------- ### ws.add_chart(type, *, anchor, data_range, title, name, to_anchor) Source: https://xlsxedit.jonasruilong.com/docs/features Adds a bar chart. Currently only chart_type="bar" is supported. Flushes from template. to_anchor is an optional end cell. ```APIDOC ## ws.add_chart(type, *, anchor, data_range, title, name, to_anchor) ### Description Adds a bar chart. Currently only chart_type="bar" is supported. Flushes from template. to_anchor is an optional end cell. ### Method `add_chart(type, *, anchor, data_range, title, name, to_anchor)` ### Parameters - **type** (string) - Required - Chart type (e.g., "bar"). - **anchor** (string) - Required - Anchor cell address. - **data_range** (string) - Required - Data range for the chart. - **title** (string) - Optional - Chart title. - **name** (string) - Optional - Chart name. - **to_anchor** (string) - Optional - End cell address. ``` -------------------------------- ### Passing engine_kwargs to read_excel Source: https://xlsxedit.jonasruilong.com/docs/pandas Demonstrates passing additional keyword arguments to the xlsxedit engine during the read process. ```python pd.read_excel("huge.xlsx", engine="xlsxedit", engine_kwargs={"large": True}) ``` -------------------------------- ### ws.findall(value) Source: https://xlsxedit.jonasruilong.com/docs/features Returns all matching cells as a list of Cell objects. Returns empty list if none. ```APIDOC ## ws.findall(value) ### Description Returns all matching cells as a list of Cell objects. Returns empty list if none. ### Method `findall(value)` ### Parameters - **value** (any) - Required - Value to search for. ### Returns - **list[Cell]** - All matching cells. ``` -------------------------------- ### ws.values(*, max_rows=None, errors_as_nan=False) Source: https://xlsxedit.jonasruilong.com/docs/features Returns a dense list of lists of cell values. Blanks are represented as empty strings; merged cells are anchored at top-left. If errors_as_nan is True, Excel errors are mapped to NaN. ```APIDOC ## ws.values(*, max_rows=None, errors_as_nan=False) ### Description Returns a dense list of lists of cell values. Blanks are represented as empty strings; merged cells are anchored at top-left. If errors_as_nan is True, Excel errors are mapped to NaN. ### Method `values(*, max_rows=None, errors_as_nan=False)` ### Parameters - **max_rows** (int) - Optional - Maximum number of rows to return. - **errors_as_nan** (bool) - Optional - If True, map Excel errors to NaN. ### Returns - **list[list]** - Dense matrix of cell values. ``` -------------------------------- ### Workbook.export_to_template(template, df, save_path, **kwargs) Source: https://xlsxedit.jonasruilong.com/docs/features Class convenience method that opens a template, writes a DataFrame, and saves the result. ```APIDOC ## Workbook.export_to_template(template, df, save_path, **kwargs) ### Description Class convenience method that opens a template, writes a DataFrame, and saves the result. ### Method Class method ### Parameters - **template** (str) - Required - Path to the template workbook. - **df** (pandas.DataFrame) - Required - The DataFrame to write. - **save_path** (str) - Required - Path where the resulting workbook will be saved. - **kwargs** - Additional keyword arguments passed to write_dataframe. ``` -------------------------------- ### Open huge files with large=True Source: https://xlsxedit.jonasruilong.com/docs/large-data-export Use the large=True flag when opening an existing file for light edits to improve performance. This is not required for exports from small templates. ```python wb = Workbook.open("huge.xlsx", large=True) ``` -------------------------------- ### ws.insert_columns(cols, at_col / at_cell, …) Source: https://xlsxedit.jonasruilong.com/docs/features Inserts columns, shifting existing columns to the right. cols are column vectors (top to bottom). Same caveats as insert_rows. ```APIDOC ## ws.insert_columns(cols, at_col / at_cell, …) ### Description Inserts columns, shifting existing columns to the right. cols are column vectors (top to bottom). Same caveats as insert_rows. ### Method `insert_columns(cols, at_col / at_cell, …)` ### Parameters - **cols** (list[list]) - Required - Column vectors (top to bottom). - **at_col** (int) - Optional - Starting column number. - **at_cell** (string) - Optional - Starting cell address. - **…** - Optional - Additional arguments. ``` -------------------------------- ### ws.conditional_formatting Source: https://xlsxedit.jonasruilong.com/docs/features Reads conditional formatting blocks (colorScale, dataBar, cellIs, etc.). ```APIDOC ## ws.conditional_formatting ### Description Reads conditional formatting blocks (colorScale, dataBar, cellIs, etc.). ### Property `ws.conditional_formatting` ### Returns - **list** - List of conditional formatting rules. ``` -------------------------------- ### Registering xlsxedit as a pandas engine Source: https://xlsxedit.jonasruilong.com/docs/pandas Registers the xlsxedit engine with pandas to enable reading Excel files using the specified engine name. ```python import pandas as pd import xlsxedit.pandas_io as xpi xpi.register() df = pd.read_excel("book.xlsx", engine="xlsxedit") df = pd.read_excel("book.xlsx", engine="xlsxedit", sheet_name="Report") ``` -------------------------------- ### wb.properties Source: https://xlsxedit.jonasruilong.com/docs/features Core document metadata from docProps/core.xml. Includes title, author, subject, keywords, comments, category, last_modified_by, created, and modified. ```APIDOC ## wb.properties ### Description Core document metadata from docProps/core.xml. Includes title, author, subject, keywords, comments, category, last_modified_by, created, and modified. ### Method Property ### Returns - **object** - An object with attributes: title, author, subject, keywords, comments, category, last_modified_by, created, modified. ``` -------------------------------- ### wb.sheetnames Source: https://xlsxedit.jonasruilong.com/docs/features List of sheet names in the workbook. ```APIDOC ## wb.sheetnames ### Description List of sheet names in the workbook. ### Method Property ### Returns - **list[str]** - List of sheet names. ``` -------------------------------- ### cell.apply_style(**kwargs) Source: https://xlsxedit.jonasruilong.com/docs/features Write style attributes to the cell. Supports bold, font_color, bg_color, font_size, and more. ```APIDOC ## cell.apply_style(**kwargs) ### Description Write style attributes to the cell. Supports bold, font_color, bg_color, font_size, and more. ### Method - **apply_style(**kwargs)** - Applies style attributes. ### Parameters - **bold** (bool) - Optional - Whether to apply bold. - **font_color** (str) - Optional - Font color. - **bg_color** (str) - Optional - Background color. - **font_size** (int) - Optional - Font size. - Other style attributes as keyword arguments. ``` -------------------------------- ### ws.find(value) Source: https://xlsxedit.jonasruilong.com/docs/features Returns the first cell with the exact value. Skips formulas. Returns None if not found. ```APIDOC ## ws.find(value) ### Description Returns the first cell with the exact value. Skips formulas. Returns None if not found. ### Method `find(value)` ### Parameters - **value** (any) - Required - Value to search for. ### Returns - **Cell** - The first matching cell, or None. ``` -------------------------------- ### cell.apply_date_format() Source: https://xlsxedit.jonasruilong.com/docs/features Apply the standard date format to the cell. ```APIDOC ## cell.apply_date_format() ### Description Apply the standard date format to the cell. ### Method - **apply_date_format()** - Applies the standard date format. ``` -------------------------------- ### ws.add_conditional_formatting(cell_range, *, operator, formula, …) Source: https://xlsxedit.jonasruilong.com/docs/features Adds a cellIs rule with optional dxf colors. ```APIDOC ## ws.add_conditional_formatting(cell_range, *, operator, formula, …) ### Description Adds a cellIs rule with optional dxf colors. ### Method `add_conditional_formatting(cell_range, *, operator, formula, …)` ### Parameters - **cell_range** (string) - Required - Range to apply formatting. - **operator** (string) - Required - Operator for the rule. - **formula** (string) - Required - Formula for the rule. - **…** - Optional - Additional arguments (e.g., dxf colors). ``` -------------------------------- ### ws.add_table(cell_range, columns, *, name) Source: https://xlsxedit.jonasruilong.com/docs/features Adds an Excel table to the specified range. ```APIDOC ## ws.add_table(cell_range, columns, *, name) ### Description Adds an Excel table to the specified range. ### Method `add_table(cell_range, columns, *, name)` ### Parameters - **cell_range** (string) - Required - Range for the table. - **columns** (list) - Required - Column definitions. - **name** (string) - Optional - Table name. ``` -------------------------------- ### ws.write_rows(rows, at_cell / at_row, …) Source: https://xlsxedit.jonasruilong.com/docs/features Writes rows at fixed positions. Supports bulk styling kwargs. ```APIDOC ## ws.write_rows(rows, at_cell / at_row, …) ### Description Writes rows at fixed positions. Supports bulk styling kwargs. ### Method `write_rows(rows, at_cell / at_row, …)` ### Parameters - **rows** (list[list]) - Required - Rows of data to write. - **at_cell** (string) - Optional - Starting cell address. - **at_row** (int) - Optional - Starting row number. - **…** - Optional - Bulk styling keyword arguments. ``` -------------------------------- ### wb.shared_strings Source: https://xlsxedit.jonasruilong.com/docs/features Shared string table. Advanced use. ```APIDOC ## wb.shared_strings ### Description Shared string table. Advanced use. ### Method Property ### Returns - **object** - The shared string table object. ``` -------------------------------- ### wb.copy_worksheet(name, new_name) Source: https://xlsxedit.jonasruilong.com/docs/features Duplicate an existing worksheet, including cells, styles, merges, drawings, and tables. Images are shared. ```APIDOC ## wb.copy_worksheet(name, new_name) ### Description Duplicate an existing worksheet, including cells, styles, merges, drawings, and tables. Images are shared. ### Method Method ### Parameters - **name** (str) - Required - Name of the worksheet to copy. - **new_name** (str) - Required - Name for the copied worksheet. ``` -------------------------------- ### Rich text string item with runs (sharedStrings.xml) Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Shows rich text with multiple runs () and run properties () for mixed formatting within one cell. xml:space="preserve" preserves leading/trailing spaces. ```xml only … this … is bold ``` -------------------------------- ### ws.replace(old, new, *, value_type=None) Source: https://xlsxedit.jonasruilong.com/docs/features Performs search and replace on this sheet only. ```APIDOC ## ws.replace(old, new, *, value_type=None) ### Description Performs search and replace on this sheet only. ### Method `replace(old, new, *, value_type=None)` ### Parameters - **old** (any) - Required - Value to find. - **new** (any) - Required - Replacement value. - **value_type** (type) - Optional - Type of value to match. ``` -------------------------------- ### wb.replace_image(name, image_path) Source: https://xlsxedit.jonasruilong.com/docs/features Replace a picture in the workbook by its drawing name. ```APIDOC ## wb.replace_image(name, image_path) ### Description Replace a picture in the workbook by its drawing name. ### Method Method ### Parameters - **name** (str) - Required - The drawing name of the image to replace. - **image_path** (str) - Required - Path to the new image file. ``` -------------------------------- ### Combine row and column styles (code-only styling) Source: https://xlsxedit.jonasruilong.com/docs/large-data-export Most common for code-only styling: combines row_styles and column_styles in a single write_dataframe call. Keys match apply_style (bold, bg_color, font_color, ...) plus num_format. Do not mix template_rows with row_styles (raises ValueError); column_styles may combine with either. ```python wb.write_dataframe( df, at_cell="A5", header=False, row_styles=[{"bg_color": "FFFFFFFF"}, {"bg_color": "FF9DC3E6"}], column_styles=[{}, {"num_format": "0"}, {"num_format": "$#,##0.00"}, {"num_format": "$#,##0.00"}], ) ``` -------------------------------- ### Chart component relationships Source: https://xlsxedit.jonasruilong.com/docs/excel-structure The hierarchy of files involved in Excel chart representation. ```text worksheet → drawing1.xml → chart1.xml → style1.xml, colors1.xml (optional, Excel 2013+) workbook.xml → definedNames _xlchart.v1.* (hidden chart helper names) ``` -------------------------------- ### Drawing Relationship File Source: https://xlsxedit.jonasruilong.com/docs/excel-structure The relationship definition in drawing1.xml.rels pointing to the media file. ```xml ``` -------------------------------- ### wb.findall(value, *, sheet=None) Source: https://xlsxedit.jonasruilong.com/docs/features Find all cells with the exact value. Returns a list of Cell objects in sheet order, then row-major. Returns an empty list if none found. ```APIDOC ## wb.findall(value, *, sheet=None) ### Description Find all cells with the exact value. Returns a list of Cell objects in sheet order, then row-major. Returns an empty list if none found. ### Method Method ### Parameters - **value** (any) - Required - The value to search for. - **sheet** (str, optional) - Optional - Name of the worksheet to search. If omitted, searches all sheets. ### Returns - **list[Cell]** - List of matching cells, or empty list if none. ``` -------------------------------- ### Plain text string item (sharedStrings.xml) Source: https://xlsxedit.jonasruilong.com/docs/excel-structure A simple with a single element. Cell formatting (e.g., bold) is controlled by the cell's style index, not the string itself. ```xml this is bold text ``` -------------------------------- ### wb.rename_worksheet(old, new) Source: https://xlsxedit.jonasruilong.com/docs/features Rename an existing worksheet. ```APIDOC ## wb.rename_worksheet(old, new) ### Description Rename an existing worksheet. ### Method Method ### Parameters - **old** (str) - Required - Current name of the worksheet. - **new** (str) - Required - New name for the worksheet. ``` -------------------------------- ### wb.worksheets Source: https://xlsxedit.jonasruilong.com/docs/features List of Worksheet objects in the workbook. ```APIDOC ## wb.worksheets ### Description List of Worksheet objects in the workbook. ### Method Property ### Returns - **list[Worksheet]** - List of Worksheet objects. ``` -------------------------------- ### Planned xlsxedit Image API Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Conceptual API for iterating, positioning, and replacing images within an Excel workbook. ```python # iterate images on a sheet for pic in ws.images: pic.name # "Picture 2" pic.anchor # top-left "B1" pic.width_emu # raw EMU pic.height_emu pic.media_path # "xl/media/image1.jpeg" # set geometry pic.anchor = "E2" pic.width = 200 # pixels helper → EMU internally pic.height = 150 # swap file bytes pic.replace("new-logo.png") # SAR: text placeholder → image wb.insert_image_at_placeholder("{logo}", "logo.png") ``` -------------------------------- ### ws.insert_rows(rows, at_cell, …) Source: https://xlsxedit.jonasruilong.com/docs/features Inserts rows, shifting existing rows below down. Shifts merges, conditional formatting, and tables; does not rewrite formulas or drawing/chart anchors. ```APIDOC ## ws.insert_rows(rows, at_cell, …) ### Description Inserts rows, shifting existing rows below down. Shifts merges, conditional formatting, and tables; does not rewrite formulas or drawing/chart anchors. ### Method `insert_rows(rows, at_cell, …)` ### Parameters - **rows** (list[list]) - Required - Rows of data to insert. - **at_cell** (string) - Required - Starting cell address. - **…** - Optional - Additional arguments. ``` -------------------------------- ### ws.add_color_scale_formatting(cell_range) Source: https://xlsxedit.jonasruilong.com/docs/features Adds a color-scale rule to the specified range. ```APIDOC ## ws.add_color_scale_formatting(cell_range) ### Description Adds a color-scale rule to the specified range. ### Method `add_color_scale_formatting(cell_range)` ### Parameters - **cell_range** (string) - Required - Range to apply formatting. ``` -------------------------------- ### Inline row styles (zebra in Python) Source: https://xlsxedit.jonasruilong.com/docs/large-data-export Defines row_styles as a list of dicts, one per stripe, cycling row by row. Each dict can be {} for no extra style. Used for zebra striping without template rows. ```python row_styles=[ {"bg_color": "FFFFFFFF"}, {"bg_color": "FF9DC3E6"}, ] ``` -------------------------------- ### Inline column styles (formats in Python) Source: https://xlsxedit.jonasruilong.com/docs/large-data-export Defines column_styles as a list of dicts, one per column (A, B, C, ... from at_cell). Used to apply number formats per column. ```python column_styles=[ {}, {"num_format": "0"}, {"num_format": "$#,##0.00"}, {"num_format": "$#,##0.00"}, ] ``` -------------------------------- ### ws.name Source: https://xlsxedit.jonasruilong.com/docs/features Read-only property that returns the sheet name. To rename, use wb.rename_worksheet. ```APIDOC ## ws.name ### Description Read-only property that returns the sheet name. To rename, use wb.rename_worksheet. ### Property `ws.name` ### Returns - **string** - The sheet name. ``` -------------------------------- ### ws.expand_conditional_formatting(end_row) Source: https://xlsxedit.jonasruilong.com/docs/features Extends conditional formatting ranges after bulk insert. Optional with write_dataframe. ```APIDOC ## ws.expand_conditional_formatting(end_row) ### Description Extends conditional formatting ranges after bulk insert. Optional with write_dataframe. ### Method `expand_conditional_formatting(end_row)` ### Parameters - **end_row** (int) - Required - The row number to extend formatting to. ``` -------------------------------- ### ws.iter_rows() Source: https://xlsxedit.jonasruilong.com/docs/features Iterates over rows, yielding tuples of (row_num, [cells]). ```APIDOC ## ws.iter_rows() ### Description Iterates over rows, yielding tuples of (row_num, [cells]). ### Method `iter_rows()` ### Returns - **iterator** - An iterator of (row_num, [cells]) tuples. ``` -------------------------------- ### wb.replace(old, new, *, value_type=None) Source: https://xlsxedit.jonasruilong.com/docs/features Perform search and replace (SAR) on all sheets. Supports substring or whole-cell typed replacement. ```APIDOC ## wb.replace(old, new, *, value_type=None) ### Description Perform search and replace (SAR) on all sheets. Supports substring or whole-cell typed replacement. ### Method Method ### Parameters - **old** (str) - Required - The value to search for. - **new** (str) - Required - The replacement value. - **value_type** (type, optional) - Optional - The type to enforce for the replacement (e.g., int, float, str). ``` -------------------------------- ### wb.find(value, *, sheet=None) Source: https://xlsxedit.jonasruilong.com/docs/features Find the first cell with the exact value. Searches all sheets if sheet is omitted. Skips formulas. ```APIDOC ## wb.find(value, *, sheet=None) ### Description Find the first cell with the exact value. Searches all sheets if sheet is omitted. Skips formulas. ### Method Method ### Parameters - **value** (any) - Required - The value to search for. - **sheet** (str, optional) - Optional - Name of the worksheet to search. If omitted, searches all sheets. ### Returns - **Cell** - The first matching cell, or None if not found. ``` -------------------------------- ### ws.cells Source: https://xlsxedit.jonasruilong.com/docs/features Property that returns all populated cells in the worksheet. ```APIDOC ## ws.cells ### Description Property that returns all populated cells in the worksheet. ### Property `ws.cells` ### Returns - **list[Cell]** - All populated cells. ``` -------------------------------- ### Content Types Image Declaration Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Declaration of image extensions in [Content_Types].xml. ```xml ``` -------------------------------- ### wb.orphan_partnames Source: https://xlsxedit.jonasruilong.com/docs/features ZIP members not on the relationship graph. Advanced use. ```APIDOC ## wb.orphan_partnames ### Description ZIP members not on the relationship graph. Advanced use. ### Method Property ### Returns - **list[str]** - List of orphan part names. ``` -------------------------------- ### Sheet1 relationship chain Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Illustrates the chain from workbook.xml to the actual worksheet file via the relationship ID. The arrow indicates the resolution path. ```text workbook.xml ↓ workbook.xml.rels Id="rId1" → worksheets/sheet1.xml ``` -------------------------------- ### ws.merged_ranges Source: https://xlsxedit.jonasruilong.com/docs/features Returns merged ranges as a list of strings, e.g., ["A1:C1"]. ```APIDOC ## ws.merged_ranges ### Description Returns merged ranges as a list of strings, e.g., ["A1:C1"]. ### Property `ws.merged_ranges` ### Returns - **list[str]** - List of merged range strings. ``` -------------------------------- ### Worksheet Relationship File Source: https://xlsxedit.jonasruilong.com/docs/excel-structure The relationship definition in sheet1.xml.rels pointing to the drawing file. ```xml ``` -------------------------------- ### ws.iter_cells() Source: https://xlsxedit.jonasruilong.com/docs/features Iterates over cells in the worksheet. ```APIDOC ## ws.iter_cells() ### Description Iterates over cells in the worksheet. ### Method `iter_cells()` ### Returns - **iterator** - An iterator over cells. ``` -------------------------------- ### Column width override for column C Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Overrides the width of column C (min=3, max=3) to 32 character units with customWidth=1. Width is in character units (width of the '0' glyph), not pixels or EMU. customWidth=1 indicates the user explicitly set the width. ```xml ``` -------------------------------- ### Row height override for row 4 Source: https://xlsxedit.jonasruilong.com/docs/excel-structure Sets row 4 to a height of 47 points with customHeight=1. The ht attribute is in points (1/72 inch). customHeight=1 indicates the user explicitly set the height, overriding the default 16 pt. ```xml ```