Hi maverick786us,
Refer the below sample. I am making use of ClosedXML to read the Excel file. For more details please refer below article.
Read Excel file directly from Stream in ASP.Net
Sample Excel File
The Sample Excel file consists of records of Customers and Employees in different sheet.
HTML
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
Namespaces
C#
using System.IO;
using System.Data;
using ClosedXML.Excel;
VB.Net
Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Code
C#
protected void ImportExcel(object sender, EventArgs e)
{
DataSet ds = ReadExcel();
string xml = string.Empty;
using (TextWriter sw = new StringWriter())
{
ds.WriteXml(sw);
xml = sw.ToString();
}
}
private DataSet ReadExcel()
{
DataSet ds = new DataSet("Excel");
//Open the Excel file using ClosedXML.
using (XLWorkbook workBook = new XLWorkbook(FileUpload1.PostedFile.InputStream))
{
foreach (IXLWorksheet workSheet in workBook.Worksheets)
{
//Read the first Sheet from Excel file.
//IXLWorksheet workSheet = workBook.Worksheet(1);
//Create a new DataTable.
DataTable dt = new DataTable(workSheet.Name);
//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++;
}
}
}
ds.Tables.Add(dt);
}
}
return ds;
}
VB.Net
Protected Sub ImportExcel(ByVal sender As Object, ByVal e As EventArgs)
Dim ds As DataSet = ReadExcel()
Dim xml As String = String.Empty
Using sw As TextWriter = New StringWriter()
ds.WriteXml(sw)
xml = sw.ToString()
End Using
End Sub
Private Function ReadExcel() As DataSet
Dim ds As DataSet = New DataSet("Excel")
Using workBook As XLWorkbook = New XLWorkbook(FileUpload1.PostedFile.InputStream)
For Each workSheet As IXLWorksheet In workBook.Worksheets
Dim dt As DataTable = New DataTable(workSheet.Name)
Dim firstRow As Boolean = True
For Each row As IXLRow In workSheet.Rows()
If firstRow Then
For Each cell As IXLCell In row.Cells()
dt.Columns.Add(cell.Value.ToString())
Next
firstRow = False
Else
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
Next
ds.Tables.Add(dt)
Next
End Using
Return ds
End Function
Screenshot