In this article I will explain with an example, how to read and import
Excel file (sheet) data without using
OLEDB in ASP.Net with C# and VB.Net.
Install DocumentFormat.OpenXml package
HTML Markup
The HTML Markup consists of following controls:
FileUpload – For selecting file.
Button – For importing the selected file.
The Button has been assigned with an OnClick event handler.
GridView – For displaying data.
The GridView consists of three BoundField columns.
<asp:FileUpload ID="fuUpload" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="OnImportExcel" />
<hr />
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Customer Id" HeaderText="Customer Id" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
VB.Net
Imports System.IO
Imports System.Data
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Reading and Importing Excel file using OpenXml in ASP.Net
Importing the Excel file
When the Import Button is clicked, first the uploaded Excel file is saved to a Directory (Folder) named Files and the file name is extracted from the FileName property of the FileUpload control using the GetFileName function of the Path class.
After that, the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Then, a nested FOR EACH loop is executed over the fetched rows and GetValue method (explained later) is called and columns are added to the DataTable.
Finally, the DataTable is assigned to the DataSource property of GridView and the GridView is populated.
GetValue
The GetValue function accepts SpreadsheetDocument and Cell class objects as a parameter where a cell value is determined and initialized.
Then, a check is performed if cell datatype NOT equal to NULL and cell value is string then, it returns the cell value.
C#
protected void OnImportExcel(object sender, EventArgs e)
{
//Save the uploaded Excel file.
string filePath = Server.MapPath("~/Files/") + Path.GetFileName(fuUpload.PostedFile.FileName);
fuUpload.SaveAs(filePath);
//Create a new DataTable.
DataTable dt = new DataTable();
//Open the Excel file in Read Mode using OpenXml.
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
{
//Read the first Sheets from Excel file.
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
//Get the Worksheet instance.
Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
//Fetch all the rows present in the Worksheet.
IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
//Loop through the Worksheet rows.
foreach (Row row in rows)
{
//Use the first row to add columns to DataTable
if (row.RowIndex.Value == 1)
{
foreach (Cell cell in row.Descendants<Cell>())
{
dt.Columns.Add(this.GetValue(doc, cell));
}
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (Cell cell in row.Descendants<Cell>())
{
dt.Rows[dt.Rows.Count - 1][i] = this.GetValue(doc, cell);
i++;
}
}
}
}
gvCustomers.DataSource = dt;
gvCustomers.DataBind();
}
private string GetValue(SpreadsheetDocument doc, Cell cell)
{
string value = cell.CellValue.InnerText;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
}
return value;
}
VB.Net
Protected Sub OnImportExcel(sender As Object, e As EventArgs)
'Save the uploaded Excel file.
Dim filePath As String = Server.MapPath("~/Files/") + Path.GetFileName(fuUpload.PostedFile.FileName)
fuUpload.SaveAs(filePath)
'Open the Excel file in Read Mode using OpenXml.
Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filePath, False)
'Read the first Sheets from Excel file.
Dim sheet As Sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild(Of Sheet)()
'Get the Worksheet instance.
Dim worksheet As Worksheet = TryCast(doc.WorkbookPart.GetPartById(sheet.Id.Value), WorksheetPart).Worksheet
'Fetch all the rows present in the Worksheet.
Dim rows As IEnumerable(OfRow) = worksheet.GetFirstChild(OfSheetData)().Descendants(Of Row)()
'Create a new DataTable.
Dim dt As New DataTable()
'Loop through the Worksheet rows.
For Each Row As Row In rows
'Use the first row to add columns to DataTable
If Row.RowIndex.Value = 1 Then
For Each Cell As Cell In Row.Descendants(Of Cell)()
dt.Columns.Add(Me.GetValue(doc, Cell))
Next
Else
'Add rows to DataTable.
dt.Rows.Add()
Dim i As Integer = 0
For Each cell As Cell In Row.Descendants(Of Cell)()
dt.Rows(dt.Rows.Count - 1)(i) = Me.GetValue(doc, cell)
i += 1
Next
End If
Next
gvCustomers.DataSource = dt
gvCustomers.DataBind()
End Using
End Sub
Private Function GetValue(doc As SpreadsheetDocument, cell As Cell) As String
Dim value As String = cell.CellValue.InnerText
If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
Return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(Integer.Parse(value)).InnerText
End If
Return value
End Function
Screenshots
The Excel File
![Read and Import Excel without using OELDB in ASP.Net using C# and VB.Net]()
Grid displaying Excel data
![Read and Import Excel without using OELDB in ASP.Net using C# and VB.Net]()
Downloads
