如何在 Python 中查看 Excel 文件
在本教程中,我們將探討如何使用Python有效地查看Excel文件。Excel文件常用於數據存儲,例如存儲表格數據。
閱讀更多
Python Excel 庫
from ironxl import *
# Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
workbook = WorkBook.Load("sample.xlsx")
# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]
# Get any existing worksheet
first_sheet = workbook.DefaultWorkSheet
# Select a cell and return the converted value
cell_value = worksheet["A2"].IntValue
# Read from ranges of cells elegantly.
for cell in worksheet["A2:A10"]:
print("Cell {} has value '{}'".format(cell.AddressString, cell.Text))
# Calculate aggregate values such as Min, and Sum
total_sum = worksheet["A2:A10"].Sum()
from ironxl import *
# Create new Excel WorkBook document
workbook = WorkBook.Create()
# Convert XLSX to XLS
xlsWorkBook = WorkBook.Create(ExcelFileFormat.XLS)
# Create a blank WorkSheet
worksheet = workbook.CreateWorkSheet("new_sheet")
# Add data and styles to the new worksheet
worksheet["A1"].Value = "Hello World"
worksheet["A1"].Style.WrapText = True
worksheet["A2"].BoolValue = True
worksheet["A2"].Style.BottomBorder.Type = BorderType.Double
# Save the excel file as XLS, XLSX, CSV, TSV, JSON, XML, HTML and streams
workbook.SaveAs("test.xlsx")
from ironxl import *
# Import any XLSX, XLS, XLSM, XLTX, CSV, and TSV
workbook = WorkBook.Load("sample.xlsx")
# Export the excel file as XLS, XLSX, XLSM, CSV, TSV, JSON, XML
workbook.SaveAs("sample.xls")
workbook.SaveAs("sample.xlsx")
workbook.SaveAs("sample.tsv")
workbook.SaveAsCsv("sample.csv")
workbook.SaveAsJson("sample.json")
workbook.SaveAsXml("sample.xml")
# Export the excel file as HTML, HTML string
workbook.ExportToHtml("sample.html")
html_string = workbook.ExportToHtmlString()
# Export the excel file as Binary, Byte array, Data set, Stream
binary_data = workbook.ToBinary()
byte_array_data = workbook.ToByteArray()
from ironxl import *
# Load existing spreadsheet
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Create conditional formatting rule
rule = worksheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8")
# Set style options
rule.FontFormatting.IsBold = True
rule.FontFormatting.FontColor = "#123456"
rule.BorderFormatting.RightBorderColor = "#ffffff"
rule.BorderFormatting.RightBorderType = BorderType.Thick
rule.PatternFormatting.BackgroundColor = "#54bdd9"
rule.PatternFormatting.FillPattern = FillPattern.Diamonds
# Apply formatting on the specified region
worksheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule)
# Create another conditional formatting rule
rule1 = worksheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10")
# Set style options
rule1.FontFormatting.IsItalic = True
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single
# Apply formatting on the specified region
worksheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1)
# Save changes with applied conditional formatting
workbook.SaveAs("applyConditionalFormatting.xlsx")
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Open a protected spreadsheet file
protected_workbook = WorkBook.Load("sample.xlsx", "IronSoftware")
# Spreadsheet protection
# Set protection for the spreadsheet file
workbook.Encrypt("IronSoftware")
# Remove protection for the spreadsheet file. Original password is not required.
workbook.Password = None
workbook.Save()
# Worksheet protection
# Set protection for an individual worksheet
worksheet.ProtectSheet("IronXL")
# Remove protection for a particular worksheet. It works without a password!
worksheet.UnprotectSheet()
workbook.Save()
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Set Formulas
worksheet["A1"].Formula = "Sum(B8:C12)"
worksheet["B8"].Formula = "=C9/C11"
worksheet["G30"].Formula = "Max(C3:C7)"
# Force recalculate all formula values in all sheets.
workbook.EvaluateAll()
# Get the formula's calculated value. e.g. "52"
formula_value = worksheet["G30"].ToArray()[0].FormattedCellValue
# Get the formula as a string. e.g. "Max(C3:C7)"
formula_string = worksheet["G30"].Formula
# Save changes with updated formulas and calculated values.
workbook.Save()
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
# Set author
workbook.Metadata.Author = "Your Name"
# Set comments
workbook.Metadata.Comments = "Monthly report"
# Set title
workbook.Metadata.Title = "July"
# Set keywords
workbook.Metadata.Keywords = "Report"
# Read the creation date of the Excel file
creation_date = workbook.Metadata.Created
# Read the last printed date of the Excel file
print_date = workbook.Metadata.LastPrinted
# Save the workbook with edited metadata
workbook.SaveAs("editedMetadata.xlsx")
from ironxl import *
# Load existing spreadsheet
workbook = WorkBook.Load("sample.xls")
worksheet = workbook.WorkSheets[0]
# Get a range from an Excel worksheet
selected_range = worksheet["A1:H10"]
# Get the first cell in the range
cell = selected_range.ToArray()[0]
# Set background color of the cell with an RGB string
cell.Style.SetBackgroundColor("#428D65")
# Apply styling to the whole range
# Set underline property to the font
# FontUnderlineType is an enum that stands for different types of font underline
selected_range.Style.Font.Underline = FontUnderlineType.SingleAccounting
# Define whether to use a horizontal line through the text or not
selected_range.Style.Font.Strikeout = False
# Define whether the font is bold or not
selected_range.Style.Font.Bold = True
# Define whether the font is italic or not
selected_range.Style.Font.Italic = False
# Get or set the script property of a font
# FontScript enum stands for available options
selected_range.Style.Font.FontScript = FontScript.Super
# Set the type of the border line
# There are also TopBorder, LeftBorder, RightBorder, DiagonalBorder properties
# BorderType enum indicates the line style of a border in a cell
selected_range.Style.BottomBorder.Type = BorderType.MediumDashed
# Indicate whether the cell should be auto-sized
selected_range.Style.ShrinkToFit = True
# Set alignment of the cell
selected_range.Style.VerticalAlignment = VerticalAlignment.Bottom
# Set border color
selected_range.Style.DiagonalBorder.SetColor("#20C96F")
# Define border type and border direction as well
selected_range.Style.DiagonalBorder.Type = BorderType.Thick
# DiagonalBorderDirection enum stands for direction of diagonal border inside cell
selected_range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward
# Set background color of cells
selected_range.Style.SetBackgroundColor(Color.Aquamarine)
# Set fill pattern of the cell
# FillPattern enum indicates the style of fill pattern
selected_range.Style.FillPattern = FillPattern.Diamonds
# Set the number of spaces to indent the text
selected_range.Style.Indention = 5
# Indicate if the text is wrapped
selected_range.Style.WrapText = True
# Save changes with applied styling options
workbook.SaveAs("stylingOptions.xls")
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Select a range
selected_range = worksheet["A1:D20"]
# Select a column(B)
column = worksheet.GetColumn(1)
# Sort the range in ascending order (A to Z)
selected_range.SortAscending()
# Sort the range by column(C) in ascending order
selected_range.SortByColumn("C", SortOrder.Ascending)
# Sort the column(B) in descending order (Z to A)
column.SortDescending()
# Save changes with the sorted range and column
workbook.SaveAs("sortExcelRange.xlsx")
from ironxl import *
# Load existing spreadsheet
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Set repeating rows for row(2-4)
worksheet.SetRepeatingRows(1, 3)
# Set repeating columns for column(C-D)
worksheet.SetRepeatingColumns(2, 3)
# Set column break after column(H). Hence, the first page will only contain column(A-G)
worksheet.SetColumnBreak(7)
# Save changes with repeating rows and columns
workbook.SaveAs("repeatingRows.xlsx")
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Set the print header and footer of the worksheet
worksheet.Header.Center = "My document"
worksheet.Footer.Center = "Page &P of &N"
# Set the header margin
worksheet.PrintSetup.HeaderMargin = 2.33
# Set the size of the paper
# Paper size enum represents different sizes of paper
worksheet.PrintSetup.PaperSize = PaperSize.B4
# Set the print orientation of the worksheet
worksheet.PrintSetup.PrintOrientation = PrintOrientation.Portrait
# Set black and white printing
worksheet.PrintSetup.NoColor = True
workbook.SaveAs("PrintSetup.xlsx")
from ironxl import *
# Load an existing WorkSheet
workbook = WorkBook.Load("sample.xls")
worksheet = workbook.WorkSheets[0]
# Set data display format to range
worksheet["A3"].Value = DateTime(2020, 1, 1, 12, 12, 12)
worksheet["A4"].Value = DateTime(2022, 3, 3, 10, 10, 10)
worksheet["A5"].Value = DateTime(2021, 2, 2, 11, 11, 11)
selected_range = worksheet["A3:A5"]
# The cell(A3) value will look like 1/1/2020 12:12:12 PM
selected_range.FormatString = "MM/dd/yy h:mm:ss"
# Save changes with applied number formats
workbook.SaveAs("numberFormats.xls")
from ironxl import *
# Load existing spreadsheet
workbook = WorkBook.Load("sample.xls")
worksheet = workbook.WorkSheets[0]
# Get a range from an Excel worksheet
selected_range = worksheet["A2:A8"]
# Combine two ranges
combined_range = selected_range + worksheet["A9:A10"]
# Iterate over the combined range
for cell in combined_range:
print(cell.Value)
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xls")
worksheet = workbook.WorkSheets[0]
# Get range from worksheet
selected_range = worksheet["A2:A8"]
# Get column from worksheet
column_a = worksheet.GetColumn(0)
# Get row from worksheet
row_1 = worksheet.GetRow(0)
# Iterate over the range
for cell in selected_range:
print(f"{cell.Value}")
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
# Set up HTML export options
options = HtmlExportOptions()
options.OutputRowNumbers = True
options.OutputColumnHeaders = True
options.OutputHiddenRows = True
options.OutputHiddenColumns = True
options.OutputLeadingSpacesAsNonBreaking = True
# Export workbook to HTML file
workbook.ExportToHtml("workBook.html", options)
from ironxl import *
# Load existing spreadsheet
workbook = WorkBook.Load("sample.xls")
worksheet = workbook.WorkSheets[0]
# Get range from the worksheet
selected_range = worksheet["A1:A8"]
# Apply sum of all numeric cells within the range
sum_ = selected_range.Sum()
# Apply average value of all numeric cells within the range
avg = selected_range.Avg()
# Identify maximum value of all numeric cells within the range
max_ = selected_range.Max()
# Identify minimum value of all numeric cells within the range
min_ = selected_range.Min()
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("test.xlsx")
worksheet = workbook.DefaultWorkSheet
# Set the chart type and its position on the worksheet.
chart = worksheet.CreateChart(ChartType.Line, 10, 10, 18, 20)
# Add the series to the chart
# The first parameter represents the address of the range for the horizontal(category) axis.
# The second parameter represents the address of the range for the vertical(value) axis.
series = chart.AddSeries("B3:B8", "A3:A8")
# Set the chart title.
series.Title = "Line Chart"
# Set the legend position.
# Can be removed by setting it to None.
chart.SetLegendPosition(LegendPosition.Bottom)
# We can change the position of the chart.
chart.Position.LeftColumnIndex = 2
chart.Position.RightColumnIndex = chart.Position.LeftColumnIndex + 3
# Plot all the data that was added to the chart before.
# Multiple calls to this method lead to plotting multiple charts instead of modifying the existing chart.
# Yet there is no possibility to remove a chart or edit its series/position.
# We can just create a new one.
chart.Plot()
# Save changes with the created line chart
workbook.SaveAs("CreateLineChart.xlsx")
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xls")
worksheet = workbook.WorkSheets[0]
# Create freeze pane from column(A-B) and row(1-3)
worksheet.CreateFreezePane(2, 3)
# Overwriting freeze or split pane to column(A-E) and row(1-5) as well as applying prescroll
# The column will show E, G, ... and the row will show 5, 8, ...
worksheet.CreateFreezePane(5, 5, 6, 7)
# Save changes with the created freeze panes
workbook.SaveAs("createFreezePanes.xls")
# Remove all existing freeze or split pane
worksheet.RemovePane()
from ironxl import *
# Load the first Excel workbook
first_book = WorkBook.Load("sample.xlsx")
second_book = WorkBook.Create()
# Select the first worksheet in the workbook
worksheet = first_book.DefaultWorkSheet
# 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")
# Save changes in the first workbook
first_book.Save()
# Save the second workbook with the copied worksheet
second_book.SaveAs("copyExcelWorksheet.xlsx")
想將 IronXL 部署到現實專案中免費使用嗎?
您的試用金鑰應該在郵件中。試用表格已提交
成功地.
如果不是,請聯繫
support@ironsoftware.com
想將 IronXL 部署到現實專案中免費使用嗎?
您的試用金鑰應該在郵件中。試用表格已提交
成功地.
如果不是,請聯繫
support@ironsoftware.com
想將 IronXL 部署到現實專案中免費使用嗎?
您的試用金鑰應該在郵件中。試用表格已提交
成功地.
如果不是,請聯繫
support@ironsoftware.com
想將 IronXL 部署到現實專案中免費使用嗎?
您的試用金鑰應該在郵件中。試用表格已提交
成功地.
如果不是,請聯繫
support@ironsoftware.com
免費開始
不需要信用卡
在生產環境中測試無浮水印。
在任何需要的地方都能運作。
獲得30天完全功能的產品。
幾分鐘內即可啟動並運行。
試用產品期間完全訪問我們的支援工程團隊
免費開始
不需要信用卡
在生產環境中測試無浮水印。
在任何需要的地方都能運作。
獲得30天完全功能的產品。
幾分鐘內即可啟動並運行。
試用產品期間完全訪問我們的支援工程團隊
功能齊全的產品,立即獲取序號
9 個 .NET API 產品 針對您的辦公文件