Get Worksheet names from Excel Workbook [Resolved]

Posted by Sharpcnet under C# on 11/18/2013 | Points: 10 | Views : 1094 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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();
   }
}

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	You are welcome. Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts