using Excel = Microsoft.Office.Interop.Excel; //namespace
Excel.Application sExcelApp;
Excel.Workbook sWorkbook;
Solutions 1:
sExcelApp.Visible = false;
sWorkbook = sExcelApp.Workbooks.Open(sFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //sFilePath Excel File Path
foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in sWorkbook.Worksheets)
{
Messagebox.show(wSheet.Name.ToString());
}
Solutions 2:
DataSet ds = new DataSet();
OleDbCommand excelCommand = new OleDbCommand(); OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
String excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + sFilePath + "; Extended Properties=Excel 8.0;";
//get "sFilePath" Excel Path
OleDbConnection excelConn = new OleDbConnection(excelConnStr);
excelConn.Open();
dtPatterns = new DataTable();
DataTable dtsheet = new DataTable();
dtsheet = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string ExcelSheetName = dtsheet.Rows[0]["Table_Name"].ToString(); //get First Excel Sheet Name
Hey,
ReplyDeleteThanks for sharing.
What is Table_Name here?
That is given simply.. thats user defined name.. u can give as you want..
Deletehow to read code name of the worksheet using oledb method
ReplyDeletepublic string GetFirstWorksheetName(string connectionString, string path)
ReplyDelete{
DataTable dt = null;
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
return dt.Rows[0]["Table_Name"].AsString();
}
}
public DataTable GetDataTable(string connectionString, string sqlStatement)
{
DataTable dt = null;
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
using (OleDbCommand command = new OleDbCommand(sqlStatement, connection) { CommandType = CommandType.Text })
{
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command))
{
dt = new DataTable();
dataAdapter.Fill(dt);
}
}
}
return dt;
}
[TestMethod]
public void OleDbExcelTest()
{
string path = @"\\servername\c$\temp\exceldata.xlsx";
string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{path}';Extended Properties=\"Excel 12.0;HDR=YES;\"";
using (OleDbDataAccess da = new OleDbDataAccess())
{
string worksheetName = da.GetFirstWorksheetName(connectionString, path);
string sql = $"SELECT * FROM [{worksheetName}]";
DataTable dt = da.GetDataTable(connectionString, sql);
Assert.IsNotNull(dt);
}
}