soroosh elyasi soroosh elyasi - 3 years ago 177
C# Question

how can get all excel sheet name in c# using OleDbDataAdapter

_conn = new OleDbConnection(_connectionStrting);
_conn.Open();
DataTable dt = _conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] sheetNames = new String[dt.Rows.Count];
int i = 0;

foreach (DataRow row in dt.Rows)
{
sheetNames[i] = row["TABLE_NAME"].ToString();
comboBox2.Items.Add(sheetNames[i]);
i++;
}

_conn.Close();


this code works for me but I want to know if have a solution with OleDbDataAdapter

Answer Source

OleDbDataAdaptor has nothing to do with Sheet names; Sheet names belong to Excel.Workbook, and Excel Workbook belongs to Excel.Application. You would need to iterate through workbook sheet names:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application xl = new Excel.Application();;
Excel.Workbook wb = xl.Workbooks.Open("WorkBookfullPath", 0, true);

foreach (Excel.Worksheet ws in wb.Worksheets) {
        {
             string wsName = ws.Name;  
        }

You don't really need here OleDbDataAdapter, in this case (in case you need to read data from worksheet) you can just read from Excel into 2 dimentional array (1st dimension is rows and second dimension is columns):

object[,] data = ws.UsedRange.Value2; // change UsedRange range to your table range, and  you can also use ws.UsedRange.FormulaR1C1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download