Using stream operations in C#, developers can dynamically create, load, and save Excel files, enabling flexible and efficient data handling. This approach eliminates the need for physical file storage, improving application performance and responsiveness. Ideal for real-time data manipulation or environments with storage limitations, it streamlines data exchange and system integration. This article demonstrates how to create, load, modify, and save Excel files using streams in C# with Spire.XLS for .NET, offering agile and scalable data management solutions.
- Dynamically Create an Excel File and Save It to Stream
- Load and Read Excel Files from Stream with C#
- Modify an Excel File in Stream with C#
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Dynamically Create an Excel File and Save It to Stream
Using Spire.XLS for .NET, developers can dynamically create Excel files in memory by initializing a Workbook object, populating it with data and formatting, and then saving the workbook to a stream using the Workbook.SaveToStream() method. This approach eliminates the need for physical file storage, enhancing both application performance and responsiveness.
Below are the steps for creating an Excel file and saving it to a stream with C#:
- Create an instance of the Workbook class to generate a new Excel workbook, which includes three default worksheets.
- Retrieve a specific worksheet using the Workbook.Worksheets[] property.
- Define the data to write to the worksheet, such as using a DataTable to organize the data.
- Insert the data into the worksheet using the Worksheet.InsertDataTable() method or the Worksheet.Range[].Value property for individual cell values.
- Format the worksheet cells, applying styles like colors, fonts, and borders, or adjusting column widths as needed.
- Save the workbook to a memory stream using the Workbook.SaveToStream() method. The stream can then be used for further processing, such as saving it to a file or transmitting it over a network.
- C#
using Spire.Xls;
using System.Data;
using System.Drawing;
namespace CreateExcelStream
{
class Program
{
static void Main(string[] args)
{
// Create a new workbook instance
Workbook workbook = new Workbook();
// Access the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];
// Create and populate a DataTable with sample data
DataTable dataTable = new DataTable("Data");
dataTable.Columns.Add("ID", typeof(int));
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Age", typeof(int));
dataTable.Columns.Add("Country", typeof(string));
dataTable.Columns.Add("Salary ($)", typeof(decimal));
dataTable.Rows.Add(101, "John Smith", 28, "USA", 54000m);
dataTable.Rows.Add(102, "Maria Garcia", 34, "Spain", 65500m);
dataTable.Rows.Add(103, "Liam Johnson", 22, "Canada", 48000m);
dataTable.Rows.Add(104, "Emma Brown", 30, "Australia", 72300m);
dataTable.Rows.Add(105, "Wei Zhang", 40, "China", 58700m);
dataTable.Rows.Add(106, "Sofia Lopez", 26, "Mexico", 45200m);
// Insert data from the DataTable into the worksheet
sheet.InsertDataTable(dataTable, true, 1, 1);
// Format the worksheet
// Style the header row
sheet.Rows[0].Style.Color = Color.LightGreen;
sheet.Rows[0].Style.Font.FontName = "Arial";
sheet.Rows[0].Style.Font.Size = 12f;
sheet.Rows[0].BorderAround(); // Apply borders around the header row
sheet.Rows[0].Borders.Color = Color.Blue;
// Style the data rows
for (int i = 1; i < sheet.AllocatedRange.Rows.Count(); i++)
{
sheet.Rows[i].Style.Color = Color.LightGray;
sheet.Rows[i].Style.Font.FontName = "Arial";
sheet.Rows[i].Style.Font.Size = 11f;
}
// Adjust the column widths to fit the content
for (int j = 1; j <= sheet.AllocatedRange.Columns.Count(); j++)
{
sheet.AutoFitColumn(j);
}
// Save the workbook to a memory stream
MemoryStream stream = new MemoryStream();
workbook.SaveToStream(stream, FileFormat.Version2016);
// Write the stream content to a file
File.WriteAllBytes("output/CreateExcelByStream.xlsx", stream.ToArray());
// Release resources
workbook.Dispose();
}
}
}
Load and Read Excel Files from Stream with C#
Spire.XLS for .NET simplifies loading Excel files directly from a stream using the Workbook.LoadFromStream() method. Once the file is loaded, developers can easily access and read cell data, optimizing memory usage and enabling fast, flexible data processing without requiring file I/O operations.
The steps for loading and reading Excel files from streams with C# are as follows:
- Create a Workbook instance.
- Create a MemoryStream or FileStream object.
- Use the Workbook.LoadFromStream() method to load the Excel file from the stream into the workbook.
- Retrieve the first worksheet using the Workbook.Worksheets[] property.
- Loop through the rows and columns of the worksheet to extract the cell through the Worksheet.AllocatedRange[].Value property.
- Print the extracted data, or use the data for further operations.
- C#
using Spire.Xls;
namespace LoadExcelStream
{
class Program
{
static void Main(string[] args)
{
// Create an instance of the Workbook class
Workbook workbook = new Workbook();
// Create a memory stream
MemoryStream stream = new MemoryStream();
File.OpenRead("Sample.xlsx").CopyTo(stream);
// Load the Excel file from the stream
workbook.LoadFromStream(stream);
// Access the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];
// Initialize a list to store the data retrieved from the worksheet
List<List<string>> data = new List<List<string>>();
for (int i = 0; i < sheet.AllocatedRange.Rows.Count(); i++)
{
// Create a list to hold each row of data
List<string> lines = new List<string>();
for (int j = 0; j < sheet.AllocatedRange.Columns.Count(); j++)
{
// Retrieve the cell text and add it to the row
lines.Add(sheet.AllocatedRange[i + 1, j + 1].Text);
}
// Add the row to the data list
data.Add(lines);
}
// Print the retrieved data or use it for further operations
foreach (List<string> lines in data)
{
Console.WriteLine(string.Join(" | ", lines));
}
}
}
}
Modify an Excel File in Stream with C#
With Spire.XLS for .NET, developers can modify an Excel file in memory by first loading it into a Workbook object with the LoadFromStream() method. After making updates (such as changing cell values or formatting), the file can be saved back to a stream using the Workbook.SaveToStream() method. This approach allows seamless real-time changes without relying on physical storage.
Follow the steps below to modify Excel files in streams with C#:
- Create a Workbook instance to represent the Excel file.
- Create a MemoryStream or FileStream instance.
- Use the Workbook.LoadFromStream() to load the Excel file from the stream.
- Access the first worksheet through the Workbook.Worksheets[] property.
- Modify the header row and the data rows' styles (font, size, background color, etc.) through the properties in CellRange.Style.
- Autofit the columns to adjust their width based on the content using the Worksheet.AutoFitColumn() method.
- Save the changes to the stream using the Workbook.SaveToStream() method.
- C#
using Spire.Xls;
using System.Drawing;
namespace ModifyExcelStream
{
class Program
{
static void Main(string[] args)
{
// Create a new instance of the Workbook class
Workbook workbook = new Workbook();
// Create a memory stream
MemoryStream stream = new MemoryStream();
File.OpenRead("Sample.xlsx").CopyTo(stream);
// Load the Excel file from the stream
workbook.LoadFromStream(stream);
// Access the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];
// Modify the style of the header row
CellRange headerRow = sheet.AllocatedRange.Rows[0];
headerRow.Style.Font.FontName = "Times New Roman";
headerRow.Style.Font.Size = 12f;
headerRow.Style.Color = Color.LightBlue;
// Modify the style of the data rows
for (int i = 1; i < sheet.AllocatedRange.Rows.Count(); i++)
{
CellRange dataRow = sheet.AllocatedRange.Rows[i];
dataRow.Style.Font.FontName = "Arial";
dataRow.Style.Font.Size = 10f;
dataRow.Style.Color = Color.LightGray;
// Alternate row coloring (even rows)
if (i % 2 == 0)
{
dataRow.Style.Color = Color.LightSlateGray;
}
}
// Autofit columns to adjust their width based on content
for (int k = 1; k <= sheet.AllocatedRange.Columns.Count(); k++)
{
sheet.AutoFitColumn(k);
}
// Change the border color
sheet.AllocatedRange.Style.Borders.Color = Color.White;
// Save the modified workbook back to the stream
workbook.SaveToStream(stream);
// Write the stream content to a new file
File.WriteAllBytes("output/ModifyExcelByStream.xlsx", stream.ToArray());
// Release resources
workbook.Dispose();
}
}
}
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.
