protected void Page_Load(object sender, EventArgs e)
{
GetExcelSheetNames(@"D:\Raj Collection For ASP Examples\Test1\FolderPath\raj.xls");
}
private void GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
DataSet ds = new DataSet();
// Connection String.
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
// Create connection.
objConn = new OleDbConnection(connString);
// Opens connection with the database.
objConn.Open();
// Get the data table containing the schema guid, and also sheet names.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
// And respective data will be put into dataset table
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + excelSheets[i] + "]", objConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
oleda.Fill(ds, "TABLE");
i++;
}
// Bind the data to the GridView
GridView1.DataSource = ds;
GridView1.DataBind();
Chart1.DataSource = ds.Tables[0].DefaultView;
Chart1.DataBind();
Chart1.Series[0].XValueMember = "StudentName";
Chart1.Series[0].YValueMembers = "Marks";
Chart1.Series[0].ToolTip = "Completed : " + "#PERCENT";//--Used to show tooltip
Chart1.ChartAreas[0].Area3DStyle.Enable3D = true;
Chart2.DataSource = ds.Tables[0].DefaultView;
Chart2.DataBind();
Chart2.Series[0].XValueMember = "Grade";
Chart2.Series[0].YValueMembers = "Marks";
Chart2.Series[0].ToolTip = "Completed : " + "#PERCENT";//--Used to show tooltip
Chart2.ChartAreas[0].Area3DStyle.Enable3D = true;
Chart3.DataSource = ds.Tables[0].DefaultView;
Chart3.DataBind();
Chart3.Series[0].XValueMember = "StudentName";
Chart3.Series[0].YValueMembers = "Marks";
Chart3.Series[0].ToolTip = "Completed : " + "#PERCENT";//--Used to show tooltip
Chart3.ChartAreas[0].Area3DStyle.Enable3D = true;
Chart4.DataSource = ds.Tables[0].DefaultView;
Chart4.DataBind();
Chart4.Series[0].XValueMember = "StudentName";
Chart4.Series[0].YValueMembers = "Marks";
Chart4.Series[0].ToolTip = "Completed : " + "#PERCENT";//--Used to show tooltip
Chart4.ChartAreas[0].Area3DStyle.Enable3D = true;
Chart5.DataSource = ds.Tables[0].DefaultView;
Chart5.DataBind();
Chart5.Series[0].XValueMember = "StudentName";
Chart5.Series[0].YValueMembers = "Marks";
Chart5.Series[0].ToolTip = "Completed : " + "#PERCENT";//--Used to show tooltip
Chart5.ChartAreas[0].Area3DStyle.Enable3D = true;
Session["Table"] = ds.Tables[0];
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
// Clean up.
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
