OpenpyXL Python & IronXL for Python 之間的比較

1.1.1 Spreadsheet Management

OpenPyXL excels at creating and editing workbooks. It allows you to start with a blank workbook or modify an existing one, add or remove sheets, and navigate through sheets easily. This makes it perfect for tasks ranging from generating reports to automating data entries.

1.1.2 Data Manipulation

This library supports advanced data handling capabilities such as appending rows, inserting formulas, and applying styles to the active sheet. You can manipulate large datasets by setting cell values programmatically, which simplifies tasks like data analysis and report generation.

1.1.3 Styling and Formatting

With OpenPyXL, you can apply cell formatting such as fonts, colors, and borders. It also supports conditional formatting and setting cell properties like alignment which are essential for creating professionally styled Excel reports.

1.1.4 Charts and Images

OpenPyXL can add graphical elements to spreadsheets. You can insert charts to visualize data and even add images to enhance the presentation of information. This feature is particularly useful for creating dashboards and complex reports.

1.1.5 Formula and Function Integration

The library allows for the integration of Excel formulas into cells, enabling automated calculations within spreadsheets. It also supports creating named ranges, which can be very handy in organizing data and creating more readable and maintainable sheets.

1.1.6 Data Validation and Automation

OpenPyXL provides tools for data validation and sorting, which are crucial for maintaining data integrity and organizing information effectively. It also offers features for filtering data, making it easier to manage large volumes of data within Excel files.

1.1.7 Open Source

As an open-source library, OpenPyXL benefits from the ongoing contributions of a vibrant community, ensuring it remains relevant and feature-rich. This community involvement provides a vast pool of resources and support for developers using the library.

2.1.1 Workbook Management

IronXL simplifies the creation, loading, and saving of Excel formats like XLS, XLSX, and CSV, among others. It ensures compatibility with different Excel versions and provides comprehensive functionality for managing Excel data programmatically.

2.1.2 Data Manipulation

With IronXL, you can efficiently sort data, manage rows and columns, and customize cell formatting. This includes setting up data types, and formulas, and even implementing Excel functions within cells.

2.1.3 Advanced Formatting

IronXL allows extensive styling options for cells, including setting fonts, borders, and background colors. It supports advanced Excel features like conditional formatting and setting print options for worksheets, enhancing the readability and presentation of your Excel reports.

2.1.4 Security Features

The library supports editing metadata, managing permissions, and password-protecting files, ensuring that sensitive data is safeguarded.

2.1.5 Cross-Platform Support

IronXL is designed to work across multiple operating systems including Windows, macOS, and Linux, ensuring that developers can deploy their applications on various platforms without compatibility issues.

2.1.6 Sheet and Cell Range Operations

IronXL offers robust functions for handling sheet ranges and individual cells. You can easily merge, copy, and manipulate cell ranges. This capability simplifies managing complex data setups.

2.1.7 Integration with Data Systems

IronXL seamlessly integrates with databases and data sources. It supports the import and export of data to and from Excel files. This feature is essential for applications that require dynamic data reporting and analytics.

IronXL is a comprehensive Python library designed to enhance Excel file manipulation. This tool simplifies the process of creating, editing, and managing Excel files within Python applications, providing a range of features that cater to various needs, from basic spreadsheet operations to advanced data handling.

6.1.1 Data Import and Export

IronXL excels in handling different Excel formats, including XLS, XLSX, and CSV. Users can easily import sample data from these formats or export worksheets to them, facilitating versatile data exchange and reporting solutions. You can also select the active sheet to work on a specific sheet.

from ironxl import *     

workbook = WorkBook.Load("sample.csv")
workbook2 = WorkBook.Load("sample.xlsx")
workbook3 = WorkBook.Load("sample.xls")

from ironxl import *     

workbook = WorkBook.Load("sample.csv")
workbook2 = WorkBook.Load("sample.xlsx")
workbook3 = WorkBook.Load("sample.xls")

6.1.2 Formula and Calculation Support

The library supports Excel formulas, allowing calculations to be carried out within the Excel sheet. Every time a sheet is edited, formulas are automatically recalculated, ensuring data consistency and accuracy.

from ironxl import *     

# Create a new Excel workbook
new_workbook = WorkBook()
# Add a worksheet to the workbook
new_worksheet = new_workbook.CreateWorkSheet("NewSheet")

# Set new formulas with different logic
new_worksheet["A1"].Formula = "Average(D1:D5)"
new_worksheet["D1"] = 10
new_worksheet["D2"] = 20
new_worksheet["D3"] = 30
new_worksheet["D4"] = 40
new_worksheet["D5"] = 50
new_worksheet["B1"].Formula = "=D3*D4"
new_worksheet["C1"].Formula = "Min(D1:D5)"

# Force recalculate all formulas in the workbook
new_workbook.EvaluateAll()

# Get the calculated values for each formula
average_value = new_worksheet["A1"].ToArray()[0].FormattedCellValue
product_value = new_worksheet["B1"].ToArray()[0].FormattedCellValue
min_value = new_worksheet["C1"].ToArray()[0].FormattedCellValue

# Save the new workbook to a file with updated formulas and calculated values
new_workbook.SaveAs("sample.xlsx")

from ironxl import *     

# Create a new Excel workbook
new_workbook = WorkBook()
# Add a worksheet to the workbook
new_worksheet = new_workbook.CreateWorkSheet("NewSheet")

# Set new formulas with different logic
new_worksheet["A1"].Formula = "Average(D1:D5)"
new_worksheet["D1"] = 10
new_worksheet["D2"] = 20
new_worksheet["D3"] = 30
new_worksheet["D4"] = 40
new_worksheet["D5"] = 50
new_worksheet["B1"].Formula = "=D3*D4"
new_worksheet["C1"].Formula = "Min(D1:D5)"

# Force recalculate all formulas in the workbook
new_workbook.EvaluateAll()

# Get the calculated values for each formula
average_value = new_worksheet["A1"].ToArray()[0].FormattedCellValue
product_value = new_worksheet["B1"].ToArray()[0].FormattedCellValue
min_value = new_worksheet["C1"].ToArray()[0].FormattedCellValue

# Save the new workbook to a file with updated formulas and calculated values
new_workbook.SaveAs("sample.xlsx")

6.1.3 Advanced Cell Formatting

IronXL offers extensive cell formatting options. Users can adjust fonts, sizes, backgrounds, borders, and alignment directly in an Excel spreadsheet through code. This feature helps prepare data presentations and reports that require specific stylistic guidelines.

from ironxl import *     

License.LicenseKey = "License Code";

workbook = WorkBook()
new_worksheet = workbook.CreateWorkSheet("FinanceData")

selected_range = new_worksheet["B2:B6"]
new_worksheet["B2"].Value = "Revenue"
new_worksheet["B3"].Value = 150000
new_worksheet["B4"].Value = 200000
new_worksheet["B5"].Value = 180000
new_worksheet["B6"].Value = 220000

selected_range.Style.SetBackgroundColor("#4CAF50")
selected_range.Style.Font.Underline = FontUnderlineType.Single
selected_range.Style.Font.Strikeout = False
selected_range.Style.Font.Bold = True
selected_range.Style.Font.Italic = False

selected_range.Style.TopBorder.Type = BorderType.Thin
selected_range.Style.BottomBorder.Type = BorderType.Thin
selected_range.Style.LeftBorder.Type = BorderType.Thin
selected_range.Style.RightBorder.Type = BorderType.Thin

selected_range.Style.TopBorder.SetColor("#000000")
selected_range.Style.BottomBorder.SetColor("#000000")
selected_range.Style.LeftBorder.SetColor("#000000")
selected_range.Style.RightBorder.SetColor("#000000")

selected_range.Style.VerticalAlignment = VerticalAlignment.Center
selected_range.Style.HorizontalAlignment = HorizontalAlignment.Center
selected_range.Style.FillPattern = FillPattern.LessDots
selected_range.Style.Indention = 0
selected_range.Style.WrapText = True

# Save the new workbook to a file with updated formulas and calculated values
workbook.SaveAs("styled.xlsx")

from ironxl import *     

License.LicenseKey = "License Code";

workbook = WorkBook()
new_worksheet = workbook.CreateWorkSheet("FinanceData")

selected_range = new_worksheet["B2:B6"]
new_worksheet["B2"].Value = "Revenue"
new_worksheet["B3"].Value = 150000
new_worksheet["B4"].Value = 200000
new_worksheet["B5"].Value = 180000
new_worksheet["B6"].Value = 220000

selected_range.Style.SetBackgroundColor("#4CAF50")
selected_range.Style.Font.Underline = FontUnderlineType.Single
selected_range.Style.Font.Strikeout = False
selected_range.Style.Font.Bold = True
selected_range.Style.Font.Italic = False

selected_range.Style.TopBorder.Type = BorderType.Thin
selected_range.Style.BottomBorder.Type = BorderType.Thin
selected_range.Style.LeftBorder.Type = BorderType.Thin
selected_range.Style.RightBorder.Type = BorderType.Thin

selected_range.Style.TopBorder.SetColor("#000000")
selected_range.Style.BottomBorder.SetColor("#000000")
selected_range.Style.LeftBorder.SetColor("#000000")
selected_range.Style.RightBorder.SetColor("#000000")

selected_range.Style.VerticalAlignment = VerticalAlignment.Center
selected_range.Style.HorizontalAlignment = HorizontalAlignment.Center
selected_range.Style.FillPattern = FillPattern.LessDots
selected_range.Style.Indention = 0
selected_range.Style.WrapText = True

# Save the new workbook to a file with updated formulas and calculated values
workbook.SaveAs("styled.xlsx")

6.1.4 Data Validation and Sorting

To enhance data integrity, IronXL includes data validation features. It also provides robust sorting capabilities for ranges, columns, and rows, making it easier to organize and analyze large datasets.

6.1.5 Document Security

Security features in IronXL include the ability to encrypt and decrypt Excel files. This ensures that sensitive information is protected and only accessible to authorized users.

from ironxl import *     

# Load a different Excel workbook
workbook = WorkBook.Load("data_file.xlsx")
worksheet = workbook.DefaultWorkSheet

# Open a different protected spreadsheet file
protected_workbook = WorkBook.Load("protected_data.xlsx", "ExcelSecure")

# Apply encryption to the spreadsheet file
workbook.Encrypt("ExcelSecure")

# Clear any existing password protection
workbook.Password = None

# Save changes to the workbook
workbook.Save()

worksheet.ProtectSheet("SheetSecure")
worksheet.UnprotectSheet()
workbook.Save()

from ironxl import *     

# Load a different Excel workbook
workbook = WorkBook.Load("data_file.xlsx")
worksheet = workbook.DefaultWorkSheet

# Open a different protected spreadsheet file
protected_workbook = WorkBook.Load("protected_data.xlsx", "ExcelSecure")

# Apply encryption to the spreadsheet file
workbook.Encrypt("ExcelSecure")

# Clear any existing password protection
workbook.Password = None

# Save changes to the workbook
workbook.Save()

worksheet.ProtectSheet("SheetSecure")
worksheet.UnprotectSheet()
workbook.Save()

6.1.6 Conversion Capabilities

IronXL is not limited to handling Excel files such as XLSX, XLSM, XLTX, XLTM files. It can also convert Excel documents into JSON, XML, HTML, or binary formats, expanding the possibilities for data utilization across different platforms and programming environments.

from ironxl import *     

workbook = WorkBook.Create()
worksheet = workbook.CreateWorkSheet("Sheet1")

worksheet["B1"].Value = "Sample Text"
worksheet["B2"].Style.BottomBorder.SetColor("#0077CC")

workbook.SaveAs("output.xls")
workbook.SaveAs("output.xlsx")
workbook.SaveAs("output.tsv")
workbook.SaveAsCsv("output.csv")
workbook.SaveAsJson("output.json")
workbook.SaveAsXml("output.xml")
workbook.ExportToHtml("output.html")

from ironxl import *     

workbook = WorkBook.Create()
worksheet = workbook.CreateWorkSheet("Sheet1")

worksheet["B1"].Value = "Sample Text"
worksheet["B2"].Style.BottomBorder.SetColor("#0077CC")

workbook.SaveAs("output.xls")
workbook.SaveAs("output.xlsx")
workbook.SaveAs("output.tsv")
workbook.SaveAsCsv("output.csv")
workbook.SaveAsJson("output.json")
workbook.SaveAsXml("output.xml")
workbook.ExportToHtml("output.html")

6.1.7 Sheet and Workbook Management

The library simplifies workbook management by allowing users to create, load, and manipulate entire workbooks or individual worksheets. This includes adding or deleting sheets, as well as setting sheet visibility and print settings.

from ironxl import *     

# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")

# Create a new worksheet
new_worksheet = workbook.CreateWorkSheet("NewSheet")

# Fill data into the new worksheet
for i in range(1, 21):
    new_worksheet[f"A{i}"].Value = f"Data {i}"
    new_worksheet[f"B{i}"].Value = 21 - i
    new_worksheet[f"C{i}"].Value = i % 5
    new_worksheet[f"D{i}"].Value = (i * 3) % 7

# Select a range in the new worksheet
selected_range = new_worksheet["A1:D20"]

# Select column B in the new worksheet
column = new_worksheet.GetColumn(1)

# Sort the range in ascending order based on column A
selected_range.SortAscending()

# Sort the range by column C in ascending order
selected_range.SortByColumn("C", SortOrder.Ascending)

# Sort column B in descending order
column.SortDescending()

# Save the changes
workbook.SaveAs("sortedExcelRange.xlsx")

from ironxl import *     

# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")

# Create a new worksheet
new_worksheet = workbook.CreateWorkSheet("NewSheet")

# Fill data into the new worksheet
for i in range(1, 21):
    new_worksheet[f"A{i}"].Value = f"Data {i}"
    new_worksheet[f"B{i}"].Value = 21 - i
    new_worksheet[f"C{i}"].Value = i % 5
    new_worksheet[f"D{i}"].Value = (i * 3) % 7

# Select a range in the new worksheet
selected_range = new_worksheet["A1:D20"]

# Select column B in the new worksheet
column = new_worksheet.GetColumn(1)

# Sort the range in ascending order based on column A
selected_range.SortAscending()

# Sort the range by column C in ascending order
selected_range.SortByColumn("C", SortOrder.Ascending)

# Sort column B in descending order
column.SortDescending()

# Save the changes
workbook.SaveAs("sortedExcelRange.xlsx")

6.1.8 Insert New Rows & Columns

IronXL allows users to dynamically insert new rows and columns into existing Excel worksheets. This feature is particularly useful for modifying data structures in response to changing data needs without disrupting the overall layout of the spreadsheet.

# Add a row before second row
worksheet.InsertRow(1)
# Insert multiple rows before 5th Row
worksheet.InsertRows(4, 4)
# Add a column before column C
worksheet.InsertColumn(2)
# Insert multiple columns before column B
worksheet.InsertColumns(1, 3)

# Add a row before second row
worksheet.InsertRow(1)
# Insert multiple rows before 5th Row
worksheet.InsertRows(4, 4)
# Add a column before column C
worksheet.InsertColumn(2)
# Insert multiple columns before column B
worksheet.InsertColumns(1, 3)

6.1.9 Group & Ungroup Rows & Columns

With IronXL, users can group or ungroup rows and columns. This functionality enhances the organization of data, allowing for easier navigation and management of complex datasets by collapsing and expanding grouped data as needed.

6.1.10 Repeat Excel Rows & Columns

IronXL supports the repetition of rows and columns across multiple pages of a workbook. This is useful for creating headers or footers that need to appear on every printed page of an Excel document, ensuring consistent presentation and readability.

# Configure rows 2 to 5 to repeat at the top of each printed page
worksheet.SetRepeatingRows(1, 4)
# Configure columns D to F to repeat on the left side of each printed page
worksheet.SetRepeatingColumns(3, 5)
# Insert a page break after column J so that columns A-J appear on the first printed page
worksheet.SetColumnBreak(9)

# Configure rows 2 to 5 to repeat at the top of each printed page
worksheet.SetRepeatingRows(1, 4)
# Configure columns D to F to repeat on the left side of each printed page
worksheet.SetRepeatingColumns(3, 5)
# Insert a page break after column J so that columns A-J appear on the first printed page
worksheet.SetColumnBreak(9)

6.1.11 Copy Excel Worksheets

Users can duplicate existing worksheets within the same workbook using IronXL. This feature saves time when creating multiple sheets that share a common format or when backing up important data. It is very helpful for data copying purposes.

# Duplicate the worksheet to the same workbook
worksheet.CopySheet("Copied Sheet")
# Duplicate the worksheet to another workbook with the specified name
worksheet.CopyTo(second_book, "Copied Sheet")

# Duplicate the worksheet to the same workbook
worksheet.CopySheet("Copied Sheet")
# Duplicate the worksheet to another workbook with the specified name
worksheet.CopyTo(second_book, "Copied Sheet")

6.1.12 Add, Extract & Remove Worksheet Images

IronXL provides the capability to add images to worksheets, extract images from them, or remove images. This enhances the visual appeal of spreadsheets and is useful for reports that include graphical data, logos, or instructional images.

6.2.1 Conditional Formatting

OpenPyXL allows conditional formatting, letting you change the style of cells based on the data they contain. This can include setting colors based on numerical values or applying different fonts and styles based on specific conditions, enhancing the visual presentation of data.

from openpyxl import Workbook
from openpyxl.formatting.rule import ColorScaleRule

wb = Workbook()
ws = wb.active

color_scale = ColorScaleRule(start_type='num', start_value=10, start_color='FF0000',
                             end_type='num', end_value=20, end_color='00FF00')
ws.conditional_formatting.add('A1:A10', color_scale)

wb.save('conditional_formatting.xlsx')

from openpyxl import Workbook
from openpyxl.formatting.rule import ColorScaleRule

wb = Workbook()
ws = wb.active

color_scale = ColorScaleRule(start_type='num', start_value=10, start_color='FF0000',
                             end_type='num', end_value=20, end_color='00FF00')
ws.conditional_formatting.add('A1:A10', color_scale)

wb.save('conditional_formatting.xlsx')

6.2.2 Data Validation

With OpenPyXL, you can implement data validation to control the type of data entered into cells. This includes setting dropdown lists, restricting entries to a certain range of dates or numbers, and generating error messages for invalid inputs, which helps maintain data integrity.

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active

dv = DataValidation(type="list", formula1='"Item1,Item2,Item3"', allow_blank=True)
ws.add_data_validation(dv)
dv.add('A1')
ws['A1'] = 'Item1'

wb.save('data_validation.xlsx')

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active

dv = DataValidation(type="list", formula1='"Item1,Item2,Item3"', allow_blank=True)
ws.add_data_validation(dv)
dv.add('A1')
ws['A1'] = 'Item1'

wb.save('data_validation.xlsx')

6.2.3 Charts

The library supports the creation and customization of various chart types such as line, bar, pie, and scatter plots. This feature enables the visualization of data directly within the Excel sheets, facilitating better data analysis and reporting. We denote the chart plot row and select the reference of values to show in the chart.

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

for i in range(10):
    ws.append([i])

values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)
ws.add_chart(chart, "E5")

wb.save("chart.xlsx")

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

for i in range(10):
    ws.append([i])

values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)
ws.add_chart(chart, "E5")

wb.save("chart.xlsx")

6.2.4 Merging and Unmerging Cells

OpenPyXL provides the ability to merge multiple cells to create a single larger cell, which is often useful for formatting titles or organizing data. It also allows these cells to be unmerged or split back into individual cells if needed.

6.2.5 Reading and Writing Comments

You can read and write comments to cells, allowing for additional notes or instructions to be included within the Excel file. This feature is particularly useful for collaborative projects where notes and guidelines may need to be shared between users.

from openpyxl import Workbook
from openpyxl.comments import Comment

wb = Workbook()
ws = wb.active

comment = Comment(text="This is a comment", author="Author")
ws['A1'].comment = comment

wb.save('comments.xlsx')

from openpyxl import Workbook
from openpyxl.comments import Comment

wb = Workbook()
ws = wb.active

comment = Comment(text="This is a comment", author="Author")
ws['A1'].comment = comment

wb.save('comments.xlsx')

6.2.6 Pivot Tables

The library offers the capability to create pivot tables from data within your worksheets. This powerful tool helps summarize, analyze, explore, and present your data comprehensively, making it easier to draw conclusions and make informed decisions based on large data sets.