In this article I will explain with an example, how to read and import Excel file directly from Stream without saving in Folder (Directory) on Server’s Disk in ASP.Net using C# and VB.Net.
When the Excel file is uploaded, the Excel File data is available in the InputStream property of the FileUpload control.
Using ClosedXml, the Excel file data can be easily read from the InputStream property.
Download DocumentFormat.OpenXml and ClosedXml Libraries
You can download the libraries using the following download locations.
Note: The DLL files of both OpenXml and ClosedXml are present in the attached sample.
HTML Markup
The following HTML Markup consists of an ASP.Net FileUpload control, a Button and a GridView.
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
<hr />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using ClosedXML.Excel;
VB.Net
Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Read Excel file directly from Stream
When the Import Button is clicked, the Excel file is read into the ClosedXml WorkBook class object directly from the PostedFile.InputStream property of the FileUpload control without saving the Excel file in Folder (Directory) on Server’s Disk.
Then the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Finally a loop is executed over the fetched rows and a DataTable is populated which is then bound to the GridView.
C#
protected void ImportExcel(object sender, EventArgs e)
{
//Open the Excel file using ClosedXML.
using (XLWorkbook workBook = new XLWorkbook(FileUpload1.PostedFile.InputStream))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
//Create a new DataTable.
DataTable dt = new DataTable();
//Loop through the Worksheet rows.
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells())
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
VB.Net
Protected Sub ImportExcel(sender As Object, e As EventArgs)
'Open the Excel file using ClosedXML.
Using workBook As New XLWorkbook(FileUpload1.PostedFile.InputStream)
'Read the first Sheet from Excel file.
Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
'Create a new DataTable.
Dim dt As New DataTable()
'Loop through the Worksheet rows.
Dim firstRow As Boolean = True
For Each row As IXLRow In workSheet.Rows()
'Use the first row to add columns to DataTable.
If firstRow Then
For Each cell As IXLCell In row.Cells()
dt.Columns.Add(cell.Value.ToString())
Next
firstRow = False
Else
'Add rows to DataTable.
dt.Rows.Add()
Dim i As Integer = 0
For Each cell As IXLCell In row.Cells()
dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
i += 1
Next
End If
GridView1.DataSource = dt
GridView1.DataBind()
Next
End Using
End Sub
Screenshots
The Excel File
![Read Excel file directly from Stream in ASP.Net]()
GridView displaying Excel data
![Read Excel file directly from Stream in ASP.Net]()
Downloads
