In Excel file processing, using byte streams in Python to create, read, and modify Excel files enables efficient data manipulation and automation. This approach eliminates reliance on physical storage or local filesystems, making it ideal for cloud-based or memory-constrained environments. It also supports real-time data exchange, system integration, and instant feedback in web applications, promoting rapid development and adaptable workflows. In this article, we will explore how to use Spire.XLS for Python to dynamically process Excel workbooks by byte streams with simple Python code.
- Create Excel Files and Save as Byte Streams in Python
- Read Excel Files from Byte Streams in Python
- Modify Excel Files from Byte Streams in Python
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip commands.
pip install Spire.XLS
If you are unsure how to install, please refer to: How to Install Spire.XLS for Python on Windows
Create Excel Files and Save as Byte Streams in Python
With Spire.XLS for Python, we can create an Excel workbook by initializing a Workbook instance and populating it with data. Once the workbook is ready, we can save it to a Stream object and convert that stream into a bytes object for further use or storage. This method allows us to efficiently generate Excel files in memory without the need for disk storage.
Below are the steps for creating an Excel file and saving it as a byte stream with Python:
- Create an instance of the Workbook class to initialize a new Excel workbook. The new workbook includes three default worksheets.
- Retrieve a worksheet using the Workbook.Worksheets.get_Item() method.
- Create a data list or obtain it from another source.
- Iterate through rows and columns to populate the worksheet with data using the Worksheet.Range.get_Item().Value or NumberValue properties.
- Format cells using the properties available in CellRange.Style.
- Create a Stream object and save the workbook to it using the Workbook.SaveToStream() method.
- Convert the stream to a bytes object using the Stream.ToArray() method.
- Python
from spire.xls import Workbook, FileFormat, Stream, Color, HorizontalAlignType
# Create an instance of Workbook class
workbook = Workbook()
# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)
# Create a 2D list of data or read data from other sources
data = [
["Country", "Capital", "Population (Millions)", "Area (km²)", "Continent"],
["United States", "Washington, D.C.", 331, 9833520, "North America"],
["Canada", "Ottawa", 38, 9984670, "North America"],
["Brazil", "Brasília", 213, 8515767, "South America"],
["United Kingdom", "London", 68, 243610, "Europe"],
["Germany", "Berlin", 83, 357022, "Europe"],
["India", "New Delhi", 1391, 3287263, "Asia"],
["China", "Beijing", 1441, 9596961, "Asia"],
["Australia", "Canberra", 26, 7692024, "Oceania"],
["South Africa", "Pretoria", 60, 1219090, "Africa"],
["Japan", "Tokyo", 126, 377975, "Asia"]
]
# Insert the data into the worksheet
for i, row in enumerate(data):
for j, value in enumerate(row):
if isinstance(value, str):
sheet.Range.get_Item(i + 1, j + 1).Value = value
else:
sheet.Range.get_Item(i + 1, j + 1).NumberValue = value
# Format the header row with new colors
headerRow = sheet.AllocatedRange.Rows.get_Item(0)
headerRow.Style.Color = Color.FromRgb(0, 102, 204) # Blue color for the header
headerRow.Style.Font.FontName = "Calibri"
headerRow.Style.Font.Size = 14
headerRow.Style.Font.IsBold = True
headerRow.Style.Font.Color = Color.FromRgb(255, 255, 255) # White text
headerRow.Style.HorizontalAlignment = HorizontalAlignType.Center
# Format the data rows with new alternating colors
for i in range(1, sheet.AllocatedRange.Rows.Count):
row = sheet.AllocatedRange.Rows.get_Item(i)
row.Style.Font.FontName = "Times New Roman"
row.Style.Font.Size = 12
row.Style.HorizontalAlignment = HorizontalAlignType.Left
if i % 2 == 0:
row.Style.Color = Color.FromRgb(229, 243, 255) # Light blue for even rows
else:
row.Style.Color = Color.FromRgb(255, 255, 204) # Light yellow for odd rows
# Auto-fit the columns
for i in range(sheet.AllocatedRange.Columns.Count):
sheet.AutoFitColumn(i + 1)
# Create a Stream object
stream = Stream()
# Save the workbook to the stream
workbook.SaveToStream(stream, FileFormat.Version2016)
workbook.Dispose()
# Convert the stream to bytes
bytes_data = stream.ToArray()
# Write the bytes to a file or use them as needed
with open("output/CreateExcelByStream.xlsx", "wb") as file:
file.write(bytes_data)
Read Excel Files from Byte Streams in Python
To load an Excel workbook from a byte stream, we can convert the byte data into a Stream object and load it into a Workbook instance. Then, we can then access the worksheet data to extract and utilize the data within the Python application seamlessly.
The steps for reading Excel files from byte streams using Python are as follows:
- Create or convert to a bytes object for the Excel file, or use an existing one.
- Create a Stream object from the bytes.
- Instantiate the Workbook class and load the Excel file from the Stream object using the Workbook.LoadFromStream() method.
- Retrieve a worksheet using the Workbook.Worksheets.get_Item() method.
- Iterate through rows and columns to access cell values using the Worksheet.AllocatedRange.get_Item().Value property.
- Output the values or utilize them as needed.
- Python
from spire.xls import Workbook, Stream
# Create a bytes object or use an existing one
with open("output/CreateExcelByStream.xlsx", "rb") as file:
bytes_data = file.read()
# Create an instance of the Workbook class
workbook = Workbook()
# Load the Excel file from the byte stream
workbook.LoadFromStream(Stream(bytes_data))
# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)
# Read data from the worksheet
# Create a list to store the data
data = []
for i in range(sheet.AllocatedRange.Rows.Count):
# Retrieve a row of data
row = sheet.AllocatedRange.Rows.get_Item(i)
# Create a list to store the row's data
row_data = []
for j in range(row.Cells.Count):
# Get the value of the cell
cellValue = sheet.AllocatedRange.get_Item(i + 1, j + 1).Value
row_data.append(cellValue)
data.append(row_data)
# Display the data or use it as needed
for row in data:
print(row)
# Release resources
workbook.Dispose()
Modify Excel Files from Byte Streams in Python
Modifying Excel files from byte streams enables us to update or enhance data dynamically without saving it to disk. This method involves loading the byte stream into a Workbook instance, making changes to its content or formatting, and saving the changes back to a byte stream for reuse.
The following steps show how to modify an Excel workbook from a byte stream using Python:
- Create or convert to a bytes object of the Excel file, or use an existing one.
- Initialize a Stream object from the bytes and load it into a Workbook using the Workbook.LoadFromStream() method.
- Access a worksheet using the Workbook.Worksheets.get_Item() method.
- Modify cell values with the Worksheet.AllocatedRange.get_Item().Value property.
- Format cells using properties in CellRange.Style and add borders with the CellRange.BorderAround() method or the CellRange.BorderInside() method.
- Auto-fit column widths using the Worksheet.AutoFitColumn() method.
- Save the workbook to a new Stream object using the Workbook.SaveToStream() method and convert it back to bytes or bytearray using Stream.ToArray() method.
- Python
from spire.xls import Workbook, Stream, HorizontalAlignType, Color, FileFormat
# Create a bytes object or use an existing one
with open("output/CreateExcelByStream.xlsx", "rb") as file:
bytes_data = file.read()
# Create an instance of the Workbook class
workbook = Workbook()
# Load the Excel file from the byte stream
stream = Stream(bytes_data)
workbook.LoadFromStream(stream)
stream.Close()
# Remove unnecessary worksheets (commented out in this case)
#for i in range(1, workbook.Worksheets.Count):
# workbook.Worksheets.RemoveAt(i)
# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)
# Modify the style of the header row
headerRow = sheet.AllocatedRange.Rows.get_Item(0)
headerRow.Style.Font.Bold = False
headerRow.Style.Font.FontName = "Arial"
headerRow.Style.Font.Size = 12
headerRow.Style.HorizontalAlignment = HorizontalAlignType.Left
headerRow.Style.Color = Color.FromRgb(173, 216, 230) # Light blue background color
# Add outline borders for the header row
headerRow.BorderAround()
# Modify the style of the data rows
for i in range(1, sheet.AllocatedRange.Rows.Count):
row = sheet.AllocatedRange.Rows.get_Item(i)
row.Style.Font.FontName = "Consolas"
row.Style.Font.Size = 11
if i % 2 == 0:
row.Style.Color = Color.FromRgb(240, 240, 240) # Light gray background color for even rows
else:
row.Style.Color = Color.FromRgb(255, 255, 255) # White background color for odd rows
# Auto-adjust the column widths
for i in range(sheet.AllocatedRange.Columns.Count):
sheet.AutoFitColumn(i + 1)
# Save the modified Excel file
streamTemp = Stream()
workbook.SaveToStream(streamTemp, FileFormat.Version2016)
workbook.Dispose()
# Convert the stream to bytes
bytes_data = streamTemp.ToArray()
# Write the bytes to a file or use them as needed
with open("output/ModifiedExcel.xlsx", "wb") as file:
file.write(bytes_data)
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
