本指南將展示如何使用 Spire.XLS for Python 通過 Python 寫入 XLSX 文件，內(nèi)容涵蓋從基礎(chǔ)寫入到高級格式設(shè)置的細節(jié) —— 全程使用這款可靠且適用于企業(yè)級場景的 Excel 庫。

Spire.XLS for Python試用下載

為何選擇 Spire.XLS for Python？

• 一體化 API：支持讀寫 .xls 和 .xlsx 文件、格式化單元格、插入公式、文件轉(zhuǎn)換等功能。
• 跨平臺支持：提供 .NET、Java、Python 版本，兼容基于云的環(huán)境。
• 高級 Excel 功能：支持圖表、數(shù)據(jù)透視表、條件格式和文件保護。
• 可靠的文檔與支持：提供詳盡的 API 參考、教程、開發(fā)者論壇和技術(shù)支持團隊。
• 免費版本可用：適合無需授權(quán)成本的輕量級 Excel 文件處理場景。

pip install spire.xls

pip install spire.xls.free

Python：
from spire.xls import Workbook, ExcelVersion

# Create a Workbook object
workbook = Workbook()

# Get the first default worksheet
sheet = workbook.Worksheets.get_Item(0)

# Write a string to the cell B2
sheet.Range.get_Item(2, 2).Text = "Hello World!"

# Save the workbook
workbook.SaveToFile("output/BasicWorkbook.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

使用 Python 向 XLSX 文件寫入不同類型的數(shù)據(jù)

from spire.xls import Workbook, ExcelVersion, DateTime, HorizontalAlignType, Stream, ImageFormatType

# Create a Workbook object
workbook = Workbook()

# Get the first default worksheet
sheet = workbook.Worksheets.get_Item(0)

# Write text to the cell B1
sheet.Range.get_Item(1, 2).Text = "Plain Text"
# Write a number to the cell B2
sheet.Range.get_Item(2, 2).NumberValue = 123456
sheet.Range.get_Item(2, 2).NumberFormat = "#,##0.00"
# Write a date to the cell B3
sheet.Range.get_Item(3, 2).DateTimeValue = DateTime.get_UtcNow()
# Write a boolean value to the cell B4
sheet.Range.get_Item(4, 2).BooleanValue = True
# Write a formula to the cell B5
sheet.Range.get_Item(5, 2).Formula = "B2/2"
# Write an HTML string to the cell B7
sheet.Range.get_Item(6, 2).HtmlString = "<p><span style='color: blue; font-size: 18px;'>Blue font 18 pixel size</span></p>"
# Write a regular value to the cell B7
sheet.Range.get_Item(7, 2).Value = "Regular Value"
# Insert a picture at the cell B8
with open("Logo.png", "rb") as f:
    imageBytes = f.read()
stream = Stream(imageBytes)
sheet.Pictures.Add(8, 2, stream, ImageFormatType.Png)

# Set basic formatting
sheet.Range.get_Item(1, 2, 8, 2).HorizontalAlignment = HorizontalAlignType.Left
sheet.AutoFitColumn(2)
for i in range(sheet.Range.Columns.Count):
    for j in range(sheet.Range.Rows.Count):
        sheet.Range.get_Item(j + 1, i + 1).HorizontalAlignment = HorizontalAlignType.Left

# Save the workbook to an XLSX file
workbook.SaveToFile("output/WriteDataExcelCell.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

使用 Python 為 Excel 單元格應(yīng)用格式和樣式

from spire.xls import Workbook, Color, LineStyleType, BordersLineType, HorizontalAlignType

# Create a Workbook object
workbook = Workbook()

# Load the XLSX file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)

# Set the font styles
# Header row
sheet.Rows.get_Item(0).Style.Font.FontName = "Times New Roman"
sheet.Rows.get_Item(0).Style.Font.Size = 14
sheet.Rows.get_Item(0).Style.Font.IsBold = True
# Data rows
for i in range(1, sheet.Rows.Count):
    sheet.Rows.get_Item(i).Style.Font.FontName = "Arial"
    sheet.Rows.get_Item(i).Style.Font.Size = 12

# Set the cell colors
# Header row
sheet.Rows.get_Item(0).Style.Color = Color.FromRgb(200, 245, 230)
# Data rows
for i in range(1, sheet.Rows.Count):
    sheet.Rows.get_Item(i).Style.Color = Color.FromRgb(240, 255, 250)

# Set the border styles
# Header row
sheet.Rows.get_Item(0).Style.Borders.get_Item(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thick
sheet.Rows.get_Item(0).Style.Borders.get_Item(BordersLineType.EdgeBottom).Color = Color.get_White()
# Data rows
for i in range(1, sheet.Rows.Count):
    sheet.Rows.get_Item(i).BorderInside(LineStyleType.Thin, Color.get_Black())

# Set the alignment
# Header row
sheet.Rows.get_Item(0).Style.HorizontalAlignment = HorizontalAlignType.Center
# Data rows
for i in range(1, sheet.Rows.Count):
    sheet.Rows.get_Item(i).Style.HorizontalAlignment = HorizontalAlignType.Left

# Auto-fit the column width
for i in range(sheet.Columns.Count):
    sheet.AutoFitColumn(i + 1)

# Save the Excel file
workbook.SaveToFile("output/FormatXLSXFile.xlsx")
workbook.Dispose()

使用 Python 為 Excel 單元格應(yīng)用格式和樣式

from spire.xls import Workbook, Color, LineStyleType, BordersLineType, HorizontalAlignType

# Create a Workbook object
workbook = Workbook()

# Load the XLSX file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)

# Set the font styles
# Header row
sheet.Rows.get_Item(0).Style.Font.FontName = "Times New Roman"
sheet.Rows.get_Item(0).Style.Font.Size = 14
sheet.Rows.get_Item(0).Style.Font.IsBold = True
# Data rows
for i in range(1, sheet.Rows.Count):
    sheet.Rows.get_Item(i).Style.Font.FontName = "Arial"
    sheet.Rows.get_Item(i).Style.Font.Size = 12

# Set the cell colors
# Header row
sheet.Rows.get_Item(0).Style.Color = Color.FromRgb(200, 245, 230)
# Data rows
for i in range(1, sheet.Rows.Count):
    sheet.Rows.get_Item(i).Style.Color = Color.FromRgb(240, 255, 250)

# Set the border styles
# Header row
sheet.Rows.get_Item(0).Style.Borders.get_Item(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thick
sheet.Rows.get_Item(0).Style.Borders.get_Item(BordersLineType.EdgeBottom).Color = Color.get_White()
# Data rows
for i in range(1, sheet.Rows.Count):
    sheet.Rows.get_Item(i).BorderInside(LineStyleType.Thin, Color.get_Black())

# Set the alignment
# Header row
sheet.Rows.get_Item(0).Style.HorizontalAlignment = HorizontalAlignType.Center
# Data rows
for i in range(1, sheet.Rows.Count):
    sheet.Rows.get_Item(i).Style.HorizontalAlignment = HorizontalAlignType.Left

# Auto-fit the column width
for i in range(sheet.Columns.Count):
    sheet.AutoFitColumn(i + 1)

# Save the Excel file
workbook.SaveToFile("output/FormatXLSXFile.xlsx")
workbook.Dispose()

from spire.xls import Workbook, ExcelVersion

# Create a Workbook instance
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)

# Format a cell as number
sheet.Range.get_Item(1, 2).NumberValue = 1234567890
sheet.Range.get_Item(1, 2).NumberFormat = "[Red]#,##0;[Green]#,##0"

# Format a cell as date
sheet.Range.get_Item(2, 2).NumberValue = 45562
sheet.Range.get_Item(2, 2).NumberFormat = "yyyy-mm-dd"

# Format a cell as time
sheet.Range.get_Item(3, 2).NumberValue = 45562
sheet.Range.get_Item(3, 2).NumberFormat = "hh:mm:ss"

# Format a cell as currency
sheet.Range.get_Item(4, 2).NumberValue = 1234567890
sheet.Range.get_Item(4, 2).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

# Format a cell as percentage
sheet.Range.get_Item(5, 2).NumberValue = 0.1234567890
sheet.Range.get_Item(5, 2).NumberFormat = "0.00%"

# Format a cell as fraction
sheet.Range.get_Item(6, 2).NumberValue = 0.1234567890
sheet.Range.get_Item(6, 2).NumberFormat = "0.00_ ?"

# Format a cell as scientific number
sheet.Range.get_Item(7, 2).NumberValue = 1234567890
sheet.Range.get_Item(7, 2).NumberFormat = "0.00E+00"

# Auto-fit the column width
for i in range(sheet.Columns.Count):
    sheet.AutoFitColumn(i + 1)

# Save the Excel file
workbook.SaveToFile("output/SetNumberFormat.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

from spire.xls import Workbook, BuiltInStyles

# Create a Workbook instance
workbook = Workbook()

# Load the Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)

# Apply built-in header style to the first row
sheet.Rows.get_Item(0).BuiltInStyle = BuiltInStyles.Heading2

# Apply built-in footer style to the data rows
for i in range(1, sheet.Rows.Count):
    sheet.Rows.get_Item(i).BuiltInStyle = BuiltInStyles.Accent2_20

# Auto-fit the column width
for i in range(sheet.Columns.Count):
    sheet.AutoFitColumn(i + 1)

# Save the Excel file
workbook.SaveToFile("output/ApplyBuiltInStyle.xlsx")
workbook.Dispose()

結(jié)論

關(guān)于慧都科技：

慧都科技是一家行業(yè)數(shù)字化解決方案公司，長期專注于軟件、油氣與制造行業(yè)。公司基于深入的業(yè)務(wù)理解與管理洞察，以系統(tǒng)化的業(yè)務(wù)建模驅(qū)動技術(shù)落地，幫助企業(yè)實現(xiàn)智能化運營與長期競爭優(yōu)勢。在軟件工程領(lǐng)域，我們提供開發(fā)控件、研發(fā)管理、代碼開發(fā)、部署運維等軟件開發(fā)全鏈路所需的產(chǎn)品，提供正版授權(quán)采購、技術(shù)選型、個性化維保等服務(wù)，幫助客戶實現(xiàn)技術(shù)合規(guī)、降本增效與風險可控。慧都科技E-iceblue的官方授權(quán)代理商，提供E-iceblue系列產(chǎn)品免費試用，咨詢，正版銷售等于一體的專業(yè)化服務(wù)。E-iceblue旗下Spire系列產(chǎn)品是國產(chǎn)文檔處理領(lǐng)域的優(yōu)秀產(chǎn)品，支持國產(chǎn)化，幫助企業(yè)高效構(gòu)建文檔處理的應(yīng)用程序。

歡迎下載|體驗更多E-iceblue產(chǎn)品