Support Center

Working with spreadsheet file stream

GemBox.Spreadsheet provides few overload Load methods and overload Save methods. These methods enable us to work with a physical file (when providing a file's path) or with an in-memory file (when providing a file's stream).

The following is a small demonstration sample of these methods:

C# code

ExcelFile workbook;

// Create input file's stream.
using (Stream input = File.OpenRead("Input.xlsx"))
    // Load input file from stream.
    workbook = ExcelFile.Load(input, LoadOptions.XlsxDefault);

// Change the value of the first cell.
workbook.Worksheets[0].Cells["A1"].Value = "Hello Word!";

// Create output file's stream.
using (FileStream output = File.Create("Output.xlsx"))
    // Save output file to stream.
    workbook.Save(output, SaveOptions.XlsxDefault);

VB.NET code

Dim workbook As ExcelFile

' Create input file's stream.
Using input As Stream = File.OpenRead("Input.xlsx")
    ' Load input file from stream.
    workbook = ExcelFile.Load(input, LoadOptions.XlsxDefault)
End Using

' Change the value of the first cell.
workbook.Worksheets(0).Cells("A1").Value = "Hello Word!"

' Create output file's stream.
Using output As FileStream = File.Create("Output.xlsx")
    ' Save output file to stream.
    workbook.Save(output, SaveOptions.XlsxDefault)
End Using

Also, among the GemBox.Spreadsheet's overload Save methods there are some that take an object type as the first parameter. These methods are used for direct streaming of an output file to the web application's client. This object parameter can be of the System.Web.HttpResponse or System.Web.HttpResponseBase type; note that this parameter is by design of an object type in order to avoid GemBox.Spreadsheet's dependency with System.Web assembly.

Here is an example of streaming a file to the client's browser in ASP.NET Web Forms application:

C# code

public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    { }

    protected void Button1_Click(object sender, EventArgs e)
    {
        ExcelFile workbook = new ExcelFile();
        ExcelWorksheet worksheet = workbook.Worksheets.Add("Sheet1");

        worksheet.Cells[0, 0].Value = "Hello World!";

        workbook.Save(this.Response, "Output.xlsx");
    }
}

VB.NET code

Public Class _Default
    Inherits Page

    Protected Sub Page_Load(ByVal sender As ObjectByVal e As EventArgsHandles Me.Load
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgsHandles Button1.Click
        Dim workbook As New ExcelFile()
        Dim worksheet As ExcelWorksheet = workbook.Worksheets.Add("Sheet1")

        worksheet.Cells(0, 0).Value = "Hello World!"

        workbook.Save(Me.Response, "Output.xlsx")
    End Sub
End Class

And here is an example in ASP.NET MVC application:

C# code

public class HomeController : Controller
{
    public ActionResult Index()
    {
        return View();
    }

    public void Download()
    {
        ExcelFile workbook = new ExcelFile();
        ExcelWorksheet worksheet = workbook.Worksheets.Add("Sheet1");

        worksheet.Cells[0, 0].Value = "Hello World!";

        workbook.Save(this.Response, "Output.xlsx");
    }
}

VB.NET code

Public Class HomeController
    Inherits Controller

    Function Index() As ActionResult
        Return View()
    End Function

    Sub Download()
        Dim workbook As New ExcelFile()
        Dim worksheet As ExcelWorksheet = workbook.Worksheets.Add("Sheet1")

        worksheet.Cells(0, 0).Value = "Hello World!"

        workbook.Save(Me.Response, "Output.xlsx")
    End Sub
End Class

9 of 15 people found this page helpful
Subscribe to this article to get an email notification when it is updated.

0 Comments

  • There are no comments.