In run mode as follows
i have one Browse button and Import Button.
When i click the Browse Button user has to select the file and click ok.
After that user Select the Import Button.
When user clicks the Import button, the excel record will be displayed in the Gridview.
for that my code as follows
protected void btnimport_Click(object sender, EventArgs e)
{
import();
}
private void import()
{
string Filename;
LblFileName.Text = "";
Filename = fileupload.FileName;
if (Filename == "")
{
//return "File is not selected";
}
LblFileName.Text = "File Name : " + Filename.ToString();
string path = Filename.ToString();
GvSch.DataSource = ImportExcelXLS(path, false);
GvSch.DataBind();
}
public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
DataSet output = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow schemaRow in schemaTable.Rows)
{
string sheet = schemaRow["TABLE_NAME"].ToString();
if (!sheet.EndsWith("_"))
{
try
{
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);
DataTable table = new DataTable();
table.Columns.Add("Date", typeof(string));
table.Columns.Add("Course", typeof(string));
table.Columns.Add("Session", typeof(string));
table.Columns.Add("Subject", typeof(string));
table.Columns.Add("Faculty", typeof(string));
int irow = 0;
foreach (DataRow row in outputTable.Rows)
{
if (row[1].ToString() != "Course" && row[1].ToString() != "" && row[1].ToString() != null)
{
DataRow row1 = table.NewRow();
if(row[3].ToString().Contains("+"))
row1["Date"] = datevalue;
row1["Course"] = row[1].ToString();
row1["Session"] = "1";
row1["Subject"] = row[2].ToString();
row1["Faculty"] = row[3].ToString();
if (row[2].ToString().Trim() != "" && row[3].ToString().Trim() != "" && row[2].ToString().Trim() != null && row[3].ToString().Trim() != null)
table.Rows.Add(row1);
row1 = table.NewRow();
if (row[5].ToString().Contains("+"))
row1["Date"] = datevalue;
row1["Course"] = row[1].ToString();
row1["Session"] = "2";
row1["Subject"] = row[2].ToString();
row1["Faculty"] = row[5].ToString();
if (row[4].ToString().Trim() != "" && row[5].ToString().Trim() != "" && row[4].ToString().Trim() != null && row[5].ToString().Trim() != null)
table.Rows.Add(row1);
table.Rows.Add(row1);
}
irow++;
}
output.Tables.Clear();
output.Tables.Add(table);
}
catch (Exception ex)
{
throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
}
finally
{
}
}
}
}
return output;
}
In run mode as follows
Click the Browse Button and select the excel file and click ok.
And after that click Import button.
When i click the import buttonshows error as follows
The IListSource does not contain any data sources.
The error line shows in below line as follows
GvSch.DataBind();
please help me. from my above code what is the mistake i made.
Regards,
Narasiman P.
i have one Browse button and Import Button.
When i click the Browse Button user has to select the file and click ok.
After that user Select the Import Button.
When user clicks the Import button, the excel record will be displayed in the Gridview.
for that my code as follows
protected void btnimport_Click(object sender, EventArgs e)
{
import();
}
private void import()
{
string Filename;
LblFileName.Text = "";
Filename = fileupload.FileName;
if (Filename == "")
{
//return "File is not selected";
}
LblFileName.Text = "File Name : " + Filename.ToString();
string path = Filename.ToString();
GvSch.DataSource = ImportExcelXLS(path, false);
GvSch.DataBind();
}
public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
DataSet output = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow schemaRow in schemaTable.Rows)
{
string sheet = schemaRow["TABLE_NAME"].ToString();
if (!sheet.EndsWith("_"))
{
try
{
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);
DataTable table = new DataTable();
table.Columns.Add("Date", typeof(string));
table.Columns.Add("Course", typeof(string));
table.Columns.Add("Session", typeof(string));
table.Columns.Add("Subject", typeof(string));
table.Columns.Add("Faculty", typeof(string));
int irow = 0;
foreach (DataRow row in outputTable.Rows)
{
if (row[1].ToString() != "Course" && row[1].ToString() != "" && row[1].ToString() != null)
{
DataRow row1 = table.NewRow();
if(row[3].ToString().Contains("+"))
row1["Date"] = datevalue;
row1["Course"] = row[1].ToString();
row1["Session"] = "1";
row1["Subject"] = row[2].ToString();
row1["Faculty"] = row[3].ToString();
if (row[2].ToString().Trim() != "" && row[3].ToString().Trim() != "" && row[2].ToString().Trim() != null && row[3].ToString().Trim() != null)
table.Rows.Add(row1);
row1 = table.NewRow();
if (row[5].ToString().Contains("+"))
row1["Date"] = datevalue;
row1["Course"] = row[1].ToString();
row1["Session"] = "2";
row1["Subject"] = row[2].ToString();
row1["Faculty"] = row[5].ToString();
if (row[4].ToString().Trim() != "" && row[5].ToString().Trim() != "" && row[4].ToString().Trim() != null && row[5].ToString().Trim() != null)
table.Rows.Add(row1);
table.Rows.Add(row1);
}
irow++;
}
output.Tables.Clear();
output.Tables.Add(table);
}
catch (Exception ex)
{
throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
}
finally
{
}
}
}
}
return output;
}
In run mode as follows
Click the Browse Button and select the excel file and click ok.
And after that click Import button.
When i click the import buttonshows error as follows
The IListSource does not contain any data sources.
The error line shows in below line as follows
GvSch.DataBind();
please help me. from my above code what is the mistake i made.
Regards,
Narasiman P.
arun prasathPosted Oct 6, 2014, 4:30 AM
Slavica MatijeviPosted Oct 6, 2014, 4:01 AM
Hi narasiman, you can try the following snippet to export your excel data to a .NET's DataSet object and then use it as a GridView's DataSource:
var dataSet = new DataSet();
// Load Excel file.
var workbook = ExcelFile.Load("Workbook.xls");
// Export all worksheets to DataSet.
foreach (var worksheet in workbook.Worksheets)
{
var dataTable = worksheet.CreateDataTable(new CreateDataTableOptions());
dataTable.TableName = worksheet.Name;
dataSet.Tables.Add(dataTable);
}
return dataSet;
You need to add reference to this excel component (it requires min .NET 2.0).
Rahul PrasadPosted Sep 29, 2014, 3:10 AM
this article gives a Winform sample project, Hope it helps
Alex SmithPosted Aug 26, 2014, 2:42 AM
This link can solve all your needs ..Please kindly check and mark as answer if it really helps
http://aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx
Karthik PuppalaPosted Aug 21, 2014, 8:58 AM
below is the code which we use to convert excel data to dataset and bind the grid we need 3rd party dlls to implement these code, Please find the code below which we use in a class file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using SpreadsheetLight;
using Excelss = Excel;
using ICSharpCode;
using System.Net;
using System.Data;
using System.IO;
///
/// Summary description for clsExcelConverter
///
public class clsExcelConverter
{
public clsExcelConverter()
{
//
// TODO: Add constructor logic here
//
}
public DataSet ExcelToDataSet(string strFile, string strExtension)
{
DataSet dsResult = null;
try
{
dsResult = new DataSet();
Excelss.IExcelDataReader excelReader = null;
FileStream stream = File.Open(strFile, FileMode.Open, FileAccess.Read);
if (strExtension.ToUpper() == ".XLS")
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = Excelss.ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (strExtension.ToUpper() == ".XLSX")
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = Excelss.ExcelReaderFactory.CreateOpenXmlReader(stream);
}
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
dsResult = excelReader.AsDataSet();
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
}
catch (Exception)
{
}
return dsResult;
}
}