C#: Dynamically Create, Load, and Save Excel Files via Stream

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.

Package Manager

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.

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();
        }
    }
}

Create Excel Files and Save to Streams with C# Code

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.

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));
            }
        }
    }
}

Load Excel Files and Read Data with Spire.XLS

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.

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();
        }
    }
}

Modify Excel Files in Streams with .NET

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.