如何在 Python 中使用 Excel API

2.1.1. Create and Modify Excel Files

IronXL allows you to create new Excel files from scratch or manipulate Excel files. You can add worksheets, set cell values, apply formatting, insert charts, perform advanced analytics, and more, all through simple Python code.

2.1.2. Read Data from Excel Files

With IronXL, you can extract data from Excel spreadsheet files effortlessly. Whether it's reading specific cells, retrieving entire rows or columns, or parsing complex data structures, IronXL provides intuitive methods to read Excel files.

2.1.3. Write Data to Excel Files

IronXL makes it easy to write Excel files programmatically. You can populate cells with values, formulas, or even images, giving you full control over the content of your Excel sheets.

2.1.4. Format Cells and Worksheets

Formatting is key to making Excel data visually appealing and easy to understand. IronXL enables you to apply various formatting options, such as font styles, colors, borders, and alignment, to cells and worksheets.

2.1.5. Generate Charts and Graphs

Visualizing data is essential for gaining insights from Excel files. IronXL allows you to create various types of charts and graphs, including bar charts, line charts, pie charts, and scatter plots, directly from your Python code.

2.1.6. Support for Excel Formulas

Excel's formula language is a powerful tool for performing calculations and data manipulation. IronXL supports Excel formulas, allowing you to evaluate formulas, set formula values in cells, and even create custom functions.

2.1.7. Export Data to Other Formats

While Excel is a ubiquitous tool for data analysis, there are times when you need to export data to other formats. IronXL enables you to export Excel data to CSV, PDF, HTML, and other popular formats, making it easy to share or integrate Excel data with other systems.

2.1.8. Performance and Scalability

IronXL is designed for performance and scalability, making it suitable for handling large Excel files and complex data sets. Whether you're processing thousands of rows or generating elaborate reports, IronXL delivers consistent performance.

To create a new Excel file with IronXL, you can use the following code snippet:

from ironxl import *

# Create a new workbook
workbook = WorkBook.Create()

# Create a new worksheet named "new_sheet"
worksheet = workbook.CreateWorkSheet("new_sheet")

# Set the value of cell A1
worksheet["A1"].Value = "Hello, IronXL!"

# Save the workbook as "output.xlsx"
workbook.SaveAs("output.xlsx")

from ironxl import *

# Create a new workbook
workbook = WorkBook.Create()

# Create a new worksheet named "new_sheet"
worksheet = workbook.CreateWorkSheet("new_sheet")

# Set the value of cell A1
worksheet["A1"].Value = "Hello, IronXL!"

# Save the workbook as "output.xlsx"
workbook.SaveAs("output.xlsx")

This code creates a new Excel workbook with a single worksheet and writes the text "Hello, IronXL!" to cell A1. The Excel workbook is then saved as "output.xlsx" in the current directory.

3.3.1. Output

To read data from an existing Excel file, you can use the following code snippet:

from ironxl import *

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

# Access the first worksheet
worksheet = workbook.WorkSheets[0]

# Retrieve the value from cell A1
cell_value = worksheet["A1"].Value

# Print the cell value
print(cell_value)

from ironxl import *

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

# Access the first worksheet
worksheet = workbook.WorkSheets[0]

# Retrieve the value from cell A1
cell_value = worksheet["A1"].Value

# Print the cell value
print(cell_value)

This code loads an existing Excel file named "output.xlsx", retrieves the value from cell A1, and prints it to the console.

3.4.1. Output

IronXL provides various methods for formatting cells and worksheets. For example, you can set the font style and size of Excel worksheets, apply background colors, add borders, and align text within cells.

from ironxl import *

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

# Access the first worksheet
worksheet = workbook.WorkSheets[0]

# Retrieve cell A1
cell = worksheet["A1"]

# Set the font style to bold
cell.Style.Font.Bold = True

# Set the background color of the cell
cell.Style.SetBackgroundColor("#f0021a")

# Save the workbook with applied styles
workbook.SaveAs("stylingOptions.xlsx")

from ironxl import *

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

# Access the first worksheet
worksheet = workbook.WorkSheets[0]

# Retrieve cell A1
cell = worksheet["A1"]

# Set the font style to bold
cell.Style.Font.Bold = True

# Set the background color of the cell
cell.Style.SetBackgroundColor("#f0021a")

# Save the workbook with applied styles
workbook.SaveAs("stylingOptions.xlsx")

This code snippet demonstrates to Excel users how to apply various formatting options to cell A1, such as making the text bold and setting the background color to red.

3.5.1. Output

IronXL allows you to create charts and graphs directly from your Python code. For example, you can create a line chart with the following code:

from ironxl import *

# Load an existing workbook
workbook = WorkBook.Load("test.xlsx")

# Access the default worksheet
worksheet = workbook.DefaultWorkSheet

# Create a line chart
chart = worksheet.CreateChart(ChartType.Line, 10, 10, 18, 20)

# Add a series to the chart
series = chart.AddSeries("A1:A5", "B1:B5")
series.Title = "Line Chart"

# Set legend position
chart.SetLegendPosition(LegendPosition.Bottom)

# Position the chart on the worksheet
chart.Position.LeftColumnIndex = 2
chart.Position.RightColumnIndex = chart.Position.LeftColumnIndex + 3

# Plot the chart
chart.Plot()

# Save the workbook with the chart
workbook.SaveAs("CreateLineChart.xlsx")

from ironxl import *

# Load an existing workbook
workbook = WorkBook.Load("test.xlsx")

# Access the default worksheet
worksheet = workbook.DefaultWorkSheet

# Create a line chart
chart = worksheet.CreateChart(ChartType.Line, 10, 10, 18, 20)

# Add a series to the chart
series = chart.AddSeries("A1:A5", "B1:B5")
series.Title = "Line Chart"

# Set legend position
chart.SetLegendPosition(LegendPosition.Bottom)

# Position the chart on the worksheet
chart.Position.LeftColumnIndex = 2
chart.Position.RightColumnIndex = chart.Position.LeftColumnIndex + 3

# Plot the chart
chart.Plot()

# Save the workbook with the chart
workbook.SaveAs("CreateLineChart.xlsx")

This code adds a line chart to the worksheet, using data from cells A1 to A5 as the category labels and data from cells B1 to B5 as the series values. The chart title is set to "Line Chart".

3.6.1. Output