How to import excel file into grid view without using database in asp.net c#
In this article, we have design a code for import
the excel sheet to grid view without using database. in the previous article ,
we have done the import excel file into grid view and save the value in data
base but in this post we do not need to save the values in to database , we can
use the value in front end (client side).
Source Code:-
<%@
Page Language="C#" AutoEventWireup="true" CodeFile="importexcelsheet.aspx.cs"
Inherits="importexcelsheet" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Import
Excel File</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<fieldset style="width:250px;">
<legend>Import
Excel File</legend>
<asp:FileUpload ID="FileUpload1" runat="server" />
<br />
<br />
<asp:Button ID="btnimport" runat="server" Text="Import Excel file" OnClick="btnimport_Click"
/>
</fieldset>
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC"
BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Horizontal">
<FooterStyle BackColor="#CCCC99" ForeColor="Black" />
<HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F7F7F7" />
<SortedAscendingHeaderStyle BackColor="#4B4B4B" />
<SortedDescendingCellStyle BackColor="#E5E5E5" />
<SortedDescendingHeaderStyle BackColor="#242121" />
</asp:GridView>
</div>
</form>
</body>
</html>
Code Behind:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using
System.Web.UI.WebControls;
using
DocumentFormat.OpenXml.Packaging;
using
DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.Data;
public partial class importexcelsheet : System.Web.UI.Page
{
protected void Page_Load(object
sender, EventArgs e)
{
}
protected void btnimport_Click(object
sender, EventArgs e)
{
string
filePath = Server.MapPath("~/excelfiles/")
+ Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(filePath);
//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>();
//Create
a new DataTable.
DataTable
dt = new DataTable();
//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(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] = GetValue(doc, cell);
i++;
}
}
}
GridView1.DataSource = dt;
GridView1.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;
}
}
Out-put:-
Note:-
- First we need to create a folder with excelfiles name.
- You need to download DocumentFormat.OpenXml and ClosedXml Libraries
- May be this error will be arises :-CS0012: The type'System.IO.Packaging.Package' is defined in an assembly that is not referenced.You must add a reference to assembly 'WindowsBase, Version=3.0.0.0,Culture=neutral, PublicKeyToken=31bf3856ad364e35'.
For more:- Stylish Gridview with stylish PageIndex using CSS ,
How to save the value of radiobuttonlist in database asp.net C# , How to insert multiple Checkboxlist value into database in asp.net c#,
How to import excel file into grid view without using database in asp.net c#
Reviewed by NEERAJ SRIVASTAVA
on
11:01:00 AM
Rating:
Reviewed by NEERAJ SRIVASTAVA
on
11:01:00 AM
Rating:
No comments: