Python 修改 Excel 文件（开发者教程）

How to use Python to modify an Excel file

1. Open Visual Studio Code and create a Python file.
2. Install IronXL using the pip package manager.
3. After that, set up the Excel file that needs to be modified.
4. Select the working spreadsheet and update the value by row and column index.
5. Save the modified data into a new Excel file.

IronXL

Developers may read, manipulate, and write Excel files directly from Python code with the help of IronXL for Python, a feature-rich library made specifically for working with Excel files. IronXL is a robust solution for easily altering Excel files for developers. It is built on top of the .NET framework and connects with Python effortlessly. IronXL for Python library provides an adaptable toolset to handle various Excel file manipulation tasks, such as adding formulas, formatting cells, and updating data.

Features of IronXL

• With IronXL, data can be quickly read from existing Excel files and written to newly created or pre-existing spreadsheets, including tasks like formatting, applying formulas, accessing cell values, and condensing data to create charts.
• Cross-Platform Compatibility: IronXL is a flexible option for Python developers regardless of their operating system of choice because it is made to function flawlessly on a variety of platforms, including Windows, Linux, and macOS.
• High efficiency: Even when working with huge datasets, IronXL, which is built on the .NET framework, processes and efficiently manipulates Excel files. It accomplishes this by fusing Dot NET's efficiency with Python's adaptability.
• Support for Excel Formats: IronXL supports the following Excel file formats, which it can open and use. Excel format like CSV file, xls (Excel 97–2003), xlsx file (Excel 2007 and beyond), and xlsm file (Excel with macros enabled).
• Advanced-Data Manipulation: With IronXL, we can manipulate Excel spreadsheets, including aggregating, filtering, and sorting, from which they can derive insightful conclusions.
• Cell formatting: Excel spreadsheets can be made more aesthetically pleasing and readable by using IronXL's cell formatting options, which include font styles, colors, borders, and alignment.

Essentially, IronXL provides a trustworthy and user-friendly way to incorporate Excel functionality into Python programs, assisting Python developers in overcoming the difficulties that come with managing Excel files. Whether you're building data analysis tools that need to read Excel files, automating reporting chores that involve reading Excel files, or developing interactive dashboards, IronXL gives you the flexibility and resources you need to succeed in handling Excel files within the Python ecosystem. To find out more about IronXL, click here.

Setup Environment

pip install ironxl

pip install ironxl

Excel File Modification Using IronXL for Python

You can perform various modifications to an Excel file using IronXL for Python, with operations such as data updates, content additions, cell formatting, and more. Let's look at a few typical situations and how IronXL for Python can be used to resolve them. By the sample data that are going to be processed.

from ironxl import *

# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# Update data in a specific cell
worksheet["B2"].Value = "29"

# Save the modified workbook
workbook.SaveAs("modified_data.xlsx")

from ironxl import *

# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# Update data in a specific cell
worksheet["B2"].Value = "29"

# Save the modified workbook
workbook.SaveAs("modified_data.xlsx")

from ironxl import *

# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# New data to be added
new_content = ["Kent", 25, 55000]
row_count = worksheet.RowCount + 1

# Add new data as a new row at the end of the worksheet
for index, item in enumerate(new_content):
    worksheet.SetCellValue(row_count, index, str(item))

# Save the modified workbook
workbook.SaveAs("modified_data.xlsx")

from ironxl import *

# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# New data to be added
new_content = ["Kent", 25, 55000]
row_count = worksheet.RowCount + 1

# Add new data as a new row at the end of the worksheet
for index, item in enumerate(new_content):
    worksheet.SetCellValue(row_count, index, str(item))

# Save the modified workbook
workbook.SaveAs("modified_data.xlsx")

from ironxl import *

# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# Get the cell B2
cell = worksheet["B2"]

# Set background color of the cell using an RGB string
cell.Style.SetBackgroundColor("#428D65")

# Save the workbook
workbook.SaveAs("modified_data.xlsx")

from ironxl import *

# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# Get the cell B2
cell = worksheet["B2"]

# Set background color of the cell using an RGB string
cell.Style.SetBackgroundColor("#428D65")

# Save the workbook
workbook.SaveAs("modified_data.xlsx")

Conclusion

In summary, IronXL for Python offers developers a robust toolset for updating content, editing data, and formatting cells in Excel spreadsheets. It also offers a comprehensive solution for altering Excel files programmatically. IronXL's user-friendly API and smooth Python integration enable developers to optimize Excel file modification activities, resulting in more effective workflows for data management and analysis.

The $799 Lite edition of IronXL comes with a permanent license, upgrade possibilities, and a year of software support. Customers can assess the product under real-world conditions throughout the trial time. Please check out the license page for additional information about IronXL's cost, licensing, and free trial. You may also find out more about Iron Software by visiting this website.