Hi Rennieu,
I have created sample code by referring the below article which full-fill your requirement.
HTML
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
C#
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string FileName = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension);
}
}
private void Import_To_Grid(string FilePath, string Extension)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * FROM [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//Bind Data to GridView
GridView1.Caption = System.IO.Path.GetFileName(FilePath);
GetData(dt);
}
private void GetData(DataTable dt)
{
DataTable dt1 = new DataTable();
dt1.Columns.AddRange(new DataColumn[3] { new DataColumn("ItemCode", typeof(int)), new DataColumn("Cost", typeof(int)), new DataColumn("Quantity", typeof(int)) });
foreach (DataRow dr in dt.Rows)
{
dt1.Rows.Add(dr[0], dr[1], dr[2]);
}
var result = dt1.AsEnumerable().GroupBy(gb => gb.Field<int>("ItemCode"))
.Select(g =>
{
var row = dt1.NewRow();
row.ItemArray = new object[] {
g.Max(ic => ic.Field<int>("ItemCode")),
g.Max(c => c.Field<int>("Cost")),
g.Sum(q => q.Field<int>("Quantity")) };
return row;
}).CopyToDataTable();
GridView1.DataSource = result;
GridView1.DataBind();
}
Vb.net
Protected Sub btnUpload_Click(sender As Object, e As EventArgs)
If FileUpload1.HasFile Then
Dim FileName As String = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim Extension As String = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName)
Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
Dim FilePath As String = Server.MapPath(FolderPath & FileName)
FileUpload1.SaveAs(FilePath)
Import_To_Grid(FilePath, Extension)
End If
End Sub
Private Sub Import_To_Grid(FilePath As String, Extension As String)
Dim conStr As String = ""
Select Case Extension
Case ".xls"
'Excel 97-03
conStr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Exit Select
Case ".xlsx"
'Excel 07
conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
Exit Select
End Select
conStr = [String].Format(conStr, FilePath)
Dim connExcel As New OleDbConnection(conStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
Dim dt As New DataTable()
cmdExcel.Connection = connExcel
'Get the name of First Sheet
connExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
'Read Data from First Sheet
connExcel.Open()
cmdExcel.CommandText = (Convert.ToString("SELECT * FROM [") & SheetName) + "]"
oda.SelectCommand = cmdExcel
oda.Fill(dt)
connExcel.Close()
'Bind Data to GridView
GridView1.Caption = System.IO.Path.GetFileName(FilePath)
GetData(dt)
End Sub
Private Sub GetData(dt As DataTable)
Dim dt1 As New DataTable()
dt1.Columns.AddRange(New DataColumn(2) {New DataColumn("ItemCode", GetType(Integer)), New DataColumn("Cost", GetType(Integer)), New DataColumn("Quantity", GetType(Integer))})
For Each dr As DataRow In dt.Rows
dt1.Rows.Add(dr(0), dr(1), dr(2))
Next
Dim result = dt1.AsEnumerable().GroupBy(Function(gb) gb.Field(Of Integer)("ItemCode")).[Select](Function(g)
Dim row = dt1.NewRow()
row.ItemArray = New Object() {g.Max(Function(ic) ic.Field(Of Integer)("ItemCode")), g.Max(Function(c) c.Field(Of Integer)("Cost")), g.Sum(Function(q) q.Field(Of Integer)("Quantity"))}
Return row
End Function).CopyToDataTable()
GridView1.DataSource = result
GridView1.DataBind()
End Sub
Screenshot