YouTip

[ Pandas Common Functions](#) * * * `to_excel()` is a method of DataFrame used to export data as an Excel format file, supporting both `.xlsx` and `.xls` formats. Excel is the most commonly used data analysis tool in enterprise office environments. `to_excel()` can export pandas DataFrame into well-formatted Excel files. It supports multiple worksheets, cell formatting, formula insertion, and other advanced features, making it very suitable for data reports and outputting data analysis results. * * * ## Basic Syntax and Parameters ### Syntax Format DataFrame.to_excel(excel_writer, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, inf_rep='inf', ...) ### Parameter Description | Parameter | Type | Description | Default Value | | --- | --- | --- | --- | | excel_writer | str, ExcelWriter, path object, file-like object | File path or ExcelWriter object | Required | | sheet_name | str | Worksheet name | 'Sheet1' | | na_rep | str | Representation of missing values | '' | | float_format | str | Float number format | None | | columns | list | Specifies columns to export | None | | header | bool | Whether to export column names | True | | index | bool | Whether to export index | True | | index_label | str | Name of index column | None | | startrow | int | Starting row of data (0-based) | 0 | | startcol | int | Starting column of data (0-based) | 0 | | engine | str | Writing engine: 'openpyxl', 'xlsxwriter' | None | ### Return Value * **Return Type**: `None` * Directly writes data to the specified Excel file without returning anything. * * * ## Examples Through the following examples, fully master various uses of `to_excel()`. ### Example 1: Basic Usage - Exporting to Excel File First create a DataFrame, then use `to_excel()` to export it to an Excel file. ## Example import pandas as pd import openpyxl # Need to install: pip install openpyxl # Create an example DataFrame data ={ 'name': ['Tom','Jerry','Mike','Lucy'], 'age': [28,35,42,26], 'city': ['Beijing','Shanghai','Guangzhou','Shenzhen'], 'salary': [8000,12000,15000,7000] } df = pd.DataFrame(data) # Example 1a: The most basic export # excel_writer: file path (required) df.to_excel('employees.xlsx', index=False) print("Exported to employees.xlsx") print() # Example 1b: Export and read verification df_check = pd.read_excel('employees.xlsx') print("Verification reading:") print(df_check) print() # Example 1c: Specify worksheet name df.to_excel('employees_sheet.xlsx', sheet_name='Employee Table', index=False) print("Exported to employees_sheet.xlsx (Worksheet: Employee Table)") **Expected Run Result:** Exported to employees.xlsx Verification reading: name age city salary 0 Tom 28 Beijing 80001 Jerry 35 Shanghai 120002 Mike 42 Guangzhou 150003 Lucy 26 Shenzhen 7000Exported to employees_sheet.xlsx (Worksheet: Employee Table) **Code Explanation:** * `to_excel()` requires specifying the `excel_writer` parameter, which is the file path. * By default, it exports the index; set `index=False` to not export the index. * The `sheet_name` parameter allows customizing the worksheet name (default is Sheet1). ### Example 2: Exporting Multiple Worksheets Using `ExcelWriter`, you can write multiple DataFrames to different worksheets within the same Excel file. ## Example import pandas as pd # Create multiple DataFrames df_employees = pd.DataFrame({ 'name': ['Tom','Jerry','Mike','Lucy'], 'age': [28,35,42,26], 'city': ['Beijing','Shanghai','Guangzhou','Shenzhen'] }) df_sales = pd.DataFrame({ 'product': ['A','B','C','D'], 'sales': [100,200,150,80], 'revenue': [10000,20000,15000,8000] }) df_inventory = pd.DataFrame({ 'product': ['A','B','C','D'], 'stock': [500,300,400,200], 'status': ['OK','Low','OK','Low'] }) # Example 2a: Using ExcelWriter to write multiple sheets # mode='w' is the default mode, which overwrites existing files with pd.ExcelWriter('multi_sheet.xlsx', engine='openpyxl')as writer: df_employees.to_excel(writer, sheet_name='Employees', index=False) df_sales.to_excel(writer, sheet_name='Sales', index=False) df_inventory.to_excel(writer, sheet_name='Inventory', index=False) print("Exported to multi_sheet.xlsx (contains 3 worksheets)") print() # Verification: Read all worksheets all_sheets = pd.read_excel('multi_sheet.xlsx', sheet_name=None) print("All worksheets:") for name, df in all_sheets.items(): print(f"n--- {name} ---") print(df) **Expected Run Result:** Exported to multi_sheet.xlsx (contains 3 worksheets)All worksheets:--- Employees --- name age city 0 Tom 28 Beijing1 Jerry 35 Shanghai2 Mike 42 Guangzhou3 Lucy 26 Shenzhen--- Sales --- product sales revenue 0 A 100 100001 B 200 200002 C 150 150003 D 80 8000--- Inventory --- product stock status 0 A 500 OK1 B 300 Low2 C 400 OK3 D 200 Low **Code Explanation:** * `pd.ExcelWriter` is a context manager used for writing multiple worksheets. * Within the `with` block, you can call `to_excel()` multiple times, each with a different worksheet name. * This approach organizes related data into the same Excel file. ### Example 3: Custom Formatting and Positioning You can control the starting position, format, and handling of missing values. ## Example import pandas as pd # Create a DataFrame containing missing values and floats df = pd.DataFrame({ 'name': ['Tom','Jerry','Mike','Lucy'], 'age': [28,None,42,26], 'score': [85.567,92.333,78.999,95.0], 'city': ['Beijing','Shanghai',None,'Shenzhen'] }) # Example 3a: Customize missing value representation df.to_excel('output_na.xlsx', index=False, na_rep='N/A') print("Exported (custom missing value)") # Example 3b: Format floating point numbers # float_format uses Python formatting string syntax df.to_excel('output_float.xlsx', index=False, float_format='%.2f') print("Exported (floating point numbers rounded to 2 decimal places)") # Example 3c: Export partial columns df.to_excel('output_cols.xlsx', index=False, columns=['name','score']) print("Exported (only name and score columns)") # Example 3d: Specify data starting position # startrow and startcol control the starting cell position of data in cells with pd.ExcelWriter('output_position.xlsx', engine='openpyxl')as writer: # Write header information writer.sheets['Sheet1'].cell(1,1).value='Employee Score Sheet' writer.sheets['Sheet1'].cell(2,1).value='Statistical Time: 2024-01-01' # Start writing data from row 4 df.to_excel(writer, sheet_name='Sheet1', index=False, startrow=3) print("Exported (specified starting position)") # Example 3e: Do not export index and column names df.to_excel('output_no_header.xlsx', index=False, header=False) print("Exported (without index and column names)") # View generated file contents print("nContents of files:") for fname in['output_na.xlsx','output_float.xlsx','output_cols.xlsx']: print(f"n--- {fname} ---") df_read = pd.read_excel(fname) print(df_read) **Expected Run Result:** Exported (custom missing value)Exported (floating point numbers rounded to 2 decimal places)Exported (only name and score columns)Exported (specified starting position)Exported (without index and column names)Contents of files:--- output_na.xlsx --- name age score city 0 Tom 28 85.567 Beijing1 Jerry NaN 92.333 Shanghai2 Mike 42 78.999 NaN3 Lucy 26 95.000 Shenzhen--- output_float.xlsx --- name age score city 0 Tom 28 85.57 Beijing1 Jerry NaN 92.33 Shanghai2 Mike 42 79.00 NaN3 Lucy 26 95.00 Shenzhen **Code Explanation:** * The `na_rep` parameter customizes the string representation of missing values. * The `float_format` parameter uses Python formatting strings, such as `'%.2f'` to keep two decimal places. * The `columns` parameter exports only specified columns. * `startrow` and `startcol` control the starting cell position of data in Excel. ### Example 4: Using xlsxwriter Engine for Formatting The xlsxwriter engine provides more rich formatting options. ## Example import pandas as pd # Need to install xlsxwriter: pip install xlsxwriter # Create DataFrame df = pd.DataFrame({ 'name': ['Tom','Jerry','Mike','Lucy','John'], 'age': [28,35,42,26,31], 'salary': [8000,12000,15000,7000,9000], 'department': ['IT','HR','Sales','IT','HR'] }) # Example 4: Use xlsxwriter to set formatting # Need to install first: pip install xlsxwriter with pd.ExcelWriter('formatted.xlsx', engine='xlsxwriter')as writer: df.to_excel(writer, sheet_name='Employees', index=False) # Get workbook and worksheet objects workbook = writer.book worksheet = writer.sheets['Employees'] # Define format header_format = workbook.add_format({ 'bold': True,# Bold 'fg_color': '#4472C4',# Background color (blue) 'font_color': 'white',# Font color (white) 'align': 'center',# Horizontal center 'valign': 'vcenter',# Vertical center 'border': 1# Border }) # Set column width worksheet.set_column('A:A',10)# Column A width 10 worksheet.set_column('B:B',8) worksheet.set_column('C:C',12) worksheet.set_column('D:D',12) # Write formatted headers for col_num, column_name in enumerate(df.columns.values): worksheet.write(0, col_num, column_name, header_format) print("Exported formatted Excel file: formatted.xlsx") print("Formatting includes: bold header, blue background, white font, centered, border, column width settings") **Expected Run Result:** Install the xlsxwriter library before running this example. Exported formatted Excel file: formatted.xlsx Formatting includes: bold header, blue background, white font, centered, border, column width settings If you need more advanced Excel formatting, consider: 1. Using openpyxl directly 2. Using xlsxwriter for better performance and formatting support **Code Explanation:** * The `xlsxwriter` engine offers richer formatting capabilities. * Formats are created using `workbook.add_format()`. * You can set fonts, background colors, borders, alignment, etc. * `worksheet.set_column()` sets column widths. * * * ## Notes * To use `.xlsx` format, install `openpyxl`: `pip install openpyxl`. * To use the `xlsxwriter` engine, install it: `pip install xlsxwriter`. * By default, it exports the index; set `index=False` if not needed. * When exporting multiple worksheets, use the `pd.ExcelWriter` context manager. * `startrow` and `startcol` are 0-based, so startrow=0 means the first row. * * * ## Summary `to_excel()` is the core method for exporting DataFrame to Excel files. It is powerful, supporting single-file multiple worksheets, custom formatting, and control of starting positions. In practical work, Excel is the most commonly used data report format, and `to_excel()` can meet most export needs. For more complex formatting, consider using the xlsxwriter engine or directly using the openpyxl library. Readers should choose the appropriate engine and parameter configuration based on actual requirements. [ Pandas Common Functions](#)