How to import Excel File in Dataset

Naveenmanam
Posted by Naveenmanam under ASP.NET category on | Views : 3812
How to import Excel File in Dataset



1. Create Class ImportExcelFile.cs


using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using System.Web;

namespace ImportExcelSheet
{
public class ImportExcelFile
{
public static DataSet ImportExcel(HttpPostedFile file, bool hasHeaders)
{
string fileName = Path.GetTempFileName();
file.SaveAs(fileName);

return ImportExcel(fileName, hasHeaders);
}
private static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
string HDR = hasHeaders ? "Yes" : "No";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";

DataSet output = new DataSet();

using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();

DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

foreach (DataRow row in dt.Rows)
{
string sheet = row["TABLE_NAME"].ToString();

OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
cmd.CommandType = CommandType.Text;

DataTable outputTable = new DataTable(sheet);
output.Tables.Add(outputTable);
new OleDbDataAdapter(cmd).Fill(outputTable);
}
}
return output;
}

}
}





2. Create Page Default.cs


protected void Button1_Click(object sender, EventArgs e)
{
bool Yes = true;
HttpPostedFile hpf = FileUpload1.PostedFile;
DataSet ds = ExcelImport.ImportExcel(hpf, Yes);
Gridview1.Datasource=ds;
Gridview1.Databind();
}





2. Create Page Default.aspx.cs


[table]
[tr]
[td]
[asp:fileupload id="FileUpload1" runat="server" /]
[/td]
[td]
[asp:button id="Button1" runat="server" text="Convert" onclick="Button1_Click" /]
[/td]
[/tr]
[tr]
[td colspan="2"]
[asp:gridview id="Gridview1" runat="Server" autogeneratecolumns="false"]
[/asp:gridview]
[/td]
[/tr]
[/table]

Comments or Responses

Login to post response