Get Worksheet names from Excel Workbook [Resolved]

Posted by Sharpcnet under C# on 11/18/2013 | Points: 10 | Views : 811 | Status : [Member] | Replies : 3
I'm trying to get the worksheet names, but the datatable has 0 rows. Where am I going wrong.
string fileName = fileUploadExcel.PostedFile.FileName;
string fileType = Path.GetExtension(fileName);
string filePath = "~/Temp/"+fileName;
FileUploadExcel.SaveAs(Server.MapPath(filePath));
string connString ="";

if(fileType==".xls")
connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};
Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=2\";",filePath);
else if(fileType==".xlsx")
connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};
Extended Properties = \"Excel 12.0 xml;HDR=Yes;IMEX=2\";",filePath);

DataTable dt = new DataTable();
OleDbConnection conn = new OleDbConnection(connString);
conn.Open;
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid,null);

if(dt!=null)
{
int count = dt.Rows.Count; //The count is 0 here
foreach(DataRow row in dt.Rows)
{
string sheetName = row["TABLE_NAME"].ToString();
}
}





Responses

Posted by: Allemahesh on: 11/18/2013 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Dear Sharpcnet,

I have seen your code. There is some issue with you code and I have corrected the same.
Please replace your code with below one:-

string fileName = fileUploadExcel.PostedFile.FileName;
string fileType = Path.GetExtension(fileName);
string filePath = Server.MapPath("~/Temp/") + fileName;
fileUploadExcel.SaveAs(filePath);
string connString = "";

if (fileType == ".xls")
connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=2\";", filePath);
else if (fileType == ".xlsx")
connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties = \"Excel 12.0 xml;HDR=Yes;IMEX=2\";", filePath);

DataTable dt = new DataTable();
OleDbConnection conn = new OleDbConnection(connString);
conn.Open();
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt != null)
{
int count = dt.Rows.Count; //The count is 0 here
foreach (DataRow row in dt.Rows)
{
string sheetName = row["TABLE_NAME"].ToString();
}
}


Let me know if you have issue.


Happy Coding,
If it helps you or directs U towards the solution, MARK IT AS ANSWER

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 11/18/2013 [Member] Starter | Points: 25

Up
0
Down
Thanks a lot. That worked.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 11/18/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
You are welcome.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response