How to translate into CSHARP

Paul Witmond

Hi,

I can't get this VB code working in C
Please advise

Dim objExcel As Object = createobject("Excel.Application")
Dim objWorkBook As Object = objExcel.Workbooks.Open("c:sheets.xls")
Dim Teller As Integer
For teller = 1 To objworkbook.worksheets.count
messagebox.Show(objworkbook.worksheets(teller).name, "ok")
Next teller

Paolo Manfrin

Hi Paul, the structure in C# should be like:

using Excel;
Excel.Application excelApp = new Excel.ApplicationClass();
string workbookPath = @"c:\sheets.xls";
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
for (int teller = 1; teller <= excelSheets.Count; teller++)
{
Excel.Worksheet worksheet = (Excel.Worksheet)excelSheets.get_Item(count);
}

Further info about the class can be found here:
http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet_members(v=VS.80).aspx
Another possibility is to read out from an Excel sheet and populate a data table:

string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=@prm;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
connStr = connStr.Replace("@prm", "c:\sheets.xls");
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
System.Data.DataTable dtShema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dtShema == null)
{
SwissAddonFramework.Messaging.StatusBar.WriteError(LanguageManager.T(Global.ADDON_KEY, "Error reading the sheet name"));
return;
}
string sheetName = dtShema.Rows[0]["TABLE_NAME"].ToString();
SwissAddonFramework.Messaging.StatusBar.WriteWarning(LanguageManager.T(Global.ADDON_KEY, "Reading Excel data structure from sheet " + sheetName));
string queryXls = "SELECT * FROM [" + sheetName + "]";
System.Data.DataTable dtXls = new System.Data.DataTable();
OleDbDataAdapter adapt = new OleDbDataAdapter(queryXls, conn);
adapt.Fill(dtXls);
conn.Close();
}

Paul Witmond
Hi Paolo,

Thank very much !

The version based on the datatable is not working reliable.
The names extracted from our test excel sheet are not 100% correct.
If you want I can send the sheet to you in the mail

Thanks,

Paul
Paul Witmond
Hi Paolo,

I just tried the first code sample but I'm missing something.

using Excel; --> ERROR

What am I doing wrong ?

Thanks,

Paul
Paolo Manfrin
Hi Paul,
the using Excel is the reference to the Excel Object.

You need to reference the Microsoft.Office.Interop.Excel in your project.

Regards,
Paolo
Paul Witmond
Hi Paolo,

I'm sorry to report that it's not working.
When I add 
using Microsoft.Office.Interop.Excel ; in the extended code I get an error about the namespace office does not exists inside the namespace Microsoft.

I'm working on a computer with Office 2010 installed

What am I doing wrong ?

Tnx

Paul