Here is a little piece of code to get your way around with Excel in VB .Net using VS 2005.
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim totalWorkSheets As Excel.Worksheet
Dim objWorkSheets As Excel.Worksheet
Dim ExcelSheetName As String = “”
objExcel = CreateObject(“Excel.Application”)
objWorkBook = objExcel.Workbooks.Open(“C:\myExcel.xls”)
‘ this code gets the names off all the worksheets
For Each totalWorkSheets In myWorkBook.Worksheets
ExcelSheetName += totalWorkSheets.Name
Next totalWorkSheets
This is good tutorial. Keep it up!
Thanks AL, I have been working on excel a lot in the last couple of months will be posting some really new and good techniques that I have learned.
what statement i need to imports in asp.net to work with excel..
like if i want to work with sql then i will import
– imports system.data
–imports system.data.sqlClient
what are the imports statements for excel
Well I have written this small post for you Rakesh.
Please let me know if it helped you.
http://dotnetolympians.wordpress.com/2008/06/18/adding-reference-to-excel-library/
Arsalan
Hi All
May any body tell me that how i convert the above VB.Net Code into C#.Net,.
Hi Tanveer,
Well I have converted this code for you using the follwoing site http://labs.developerfusion.co.uk/convert/vb-to-csharp.aspx
And there are a number of others which you can try on your own.
Excel.Application objExcel;
Excel.Workbook objWorkBook;
Excel.Worksheet totalWorkSheets;
Excel.Worksheet objWorkSheets;
string ExcelSheetName = “”;
objExcel = Interaction.CreateObject(“Excel.Application”);
objWorkBook = objExcel.Workbooks.Open(“C:\\myExcel.xls”);
//this code gets the names off all the worksheets
foreach ( totalWorkSheets in myWorkBook.Worksheets) {
ExcelSheetName += totalWorkSheets.Name;
}
Havent tested it so please let me know if you still face any problems, while running it.
Hope this helps you.
Best Regards,
Arsalan
I have tried to get all the sheetnames from an excel file using oledb.
public string[] GetSheetNamesForExcel(string filename)
{
System.Data.DataTable dt = GetDataTable(filename, OleDbSchemaGuid.Tables);
String[] excelSheets = null;
try
{
if (dt != null)
{
excelSheets = new String[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
string name = row["TABLE_NAME"].ToString();
name = name.Replace(“‘”, String.Empty);
name = name.Replace(“$”, String.Empty);
excelSheets[i] = name;
}
dt.Dispose();
}
}
catch (Exception ex)
{
throw ex;
}
return excelSheets;
}
But the excelsheet seems to contain additional information, which are returned as sheetnames. How can I make sure that only /actual/ sheetnames?
All help will be greatly appreciated.
I’d like to avoid using an excel library, is there a way to do this by using System.data.OleDb?
Helpfull blog…
///
/// This mehtod retrieves the excel sheet names from
/// an excel workbook.
///
/// The excel file.
/// String[]
private String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String. Change the excel file to the file you
// will search.
String connString = “Provider=Microsoft.Jet.OLEDB.4.0;” +
“Data Source=” + excelFile + “;Extended Properties=Excel 8.0;”;
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
// Loop through all of the sheets if you want too…
for(int j=0; j < excelSheets.Length; j++)
{
// Query each excel sheet.
}
return excelSheets;
}
catch(Exception ex)
{
return null;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}
i think that this is the best solution
Where’s myWorkBook.Worksheets come from?
I use the code
Excel.Application objExcel;
Excel.Workbook objWorkBook;
Excel.Worksheet totalWorkSheets;
Excel.Worksheet objWorkSheets;
string ExcelSheetName = “”;
objExcel = Interaction.CreateObject(”Excel.Application”);
objWorkBook = objExcel.Workbooks.Open(”C:\\myExcel.xls”);
//this code gets the names off all the worksheets
foreach ( totalWorkSheets in myWorkBook.Worksheets) {
ExcelSheetName += totalWorkSheets.Name;
}
And also i add references.But what is myWorkBook mean.I got error in myWorkBook
Change myWorkBook to objWorkBook.
The code still doesn’t work. From the error message it seems like an object type needs to be specified for totalWorksheets in the the foreach statement
Thanks for the great stuff. I didn’t work this. Let me try.
hi fizzle
Which code are you trying the C# or VB.Net?
Private Function GetSheetNames(ByVal PrmExcelPath As String) As String()
Dim objExcel As Microsoft.Office.Interop.Excel.Application
Dim objWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim totalWorkSheets As Microsoft.Office.Interop.Excel.Worksheet
Dim ExcelSheetName As String = “”
objExcel = CreateObject(“Excel.Application”)
objWorkBook = objExcel.Workbooks.Open(PrmExcelPath)
‘ this code gets the names off all the worksheets
For Each totalWorkSheets In objWorkBook.Worksheets
ExcelSheetName += totalWorkSheets.Name + ” ”
Next totalWorkSheets
objWorkBook.Close(SaveChanges:=False)
objExcel.Quit()
objExcel = Nothing
objWorkBook = Nothing
totalWorkSheets = Nothing
Return ExcelSheetName.Split()
End Function
cooll….
Check out following link
http://aspdotnetmatters.blogspot.com/2010/12/get-excel-sheet-names-in-aspnet.html
this function is really marvelous.
If interested I have several language extension methods for getting Sheetnames, named range and range names in VS2008 VB.NET DLL project with full source at http://kevininstructor.home.comcast.net/~kevininstructor/ProjectsDotNet.html called ExcelConnection.zip which has 1 BindingSource lib, 1 Excel lib and demo project.
hey mr. AL… it’s about your code… i tried it but my solution returns an error in this line of code:
For Each totalWorkSheets In myWorkBook.Worksheets
the error says that myWorkbook is not declared…
what am i supposed to do… need your help as soon as possible..
thanks sire