narasiman rao

narasiman rao

  • NA
  • 519
  • 768.4k

Exprot the Excel data into Gridview

Aug 21 2014 8:32 AM
 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.
 

Answers (5)