[hookiethe1]

G'day all, I've been on google for far too long trying to figure this out, so hopefully somebody can point me in the right direction. I'm trying to write a program that will open an Excel spreadsheet and read data from specific cells while preserving datatypes, so that I can write the data to an XML file. The basic functions I need to perform reduce to:

Opening the file (I can do that at least)
Counting the rows
Reading down a given column (column has a known refID) to find cells that are not NULL
Returning data from specific other columns (all columns have known refIDs) in the same rows that are not NULL in the primary column (hope that's clear)

All that I've read talks about reading everything into datatables and copying things into arrays and using SQL etc etc, but I just want to open the sheet, address rows and columns directly and get my data. Is that even possible in .NET? If anyone could point me to a decent reference I'd be eternally grateful.

[demausdauth]

Yes it is possible. IMO the better way is to utilize the datatables, but that may be because I am biased against using InterOps (shutter), especially if you are just retrieving data.

Understanding the Excel Object Model from a Visual Studio 2005 Developer's Perspective

Give this a try....

' Include the following at the top of the Module:
Imports Excel = Microsoft.Office.Interop.Excel

Public Function OpenExcelGetData(Byval fileNameAndPath As String, Byval rowIndex As Integer, Byval columnIndex As String) As object
     Dim oExcelApp As New Excel.ApplicationClass
     Dim oExcelBook As Excel.Workbook
     Dim oExcelSheet As Excel.Worksheet
     Dim sheetNumber As Integer = 1 '1-based array

     Dim oData As Object = Nothing

     Try
          oExcelBook = oExcelApp.Workbooks.Open(fileNameAndPath)
          oExcelSheet = CType(oExcelBook.WorkSheets(sheetNumber), Excel.WorkSheet)

          'Read data
          
          Dim excelRange As String = columnIndex & rowIndex.ToString()
          
          oData = oExcelSheet.Range(excelRange).Value
          

     Catch exp As COMException
          MessageBox.Show(exp.Message)

     Catch exp As Exception
          MessageBox.Show(exp.Message)

     End Try

     Return oData
End Function

[hookiethe1]

Thank you very much! That link went straight into bookmarks! The code you posted is more or less the same as what I've come up with so far, except I didn't read the entire sheet into an object. The thing that put me off the datatable approach was that when I read the MSDN pages about it, none of the access methods seemed to be what I wanted, ie a straightforward way to traverse the table, compare data from any cell I choose, and extract the data I wanted. I guess there must be ways, but I couldn't find them. Then again, you could probably fill a warehouse with what I don't know about .NET programming!

[hookiethe1]

OK, hopefully this will save somebody all of the fruitless searching that I did. As it turns out, it's a poorly advertised fact that you can open up Visual studio, create a VB.NET project and do all the things with an excel spreadsheet that you can do in a VBA macro, and with virtually identical commands! I'm sure plenty will read this, shake their heads and say "duh," but as a new guy, firing up google and typing in "How do I read data from an excel file in .NET" yielded a lot of overly complicated and unnecessarily roundabout methods, and nothing about "activeworksheet.cells( a , b ).value," which is all I really needed. Creating data adapters and datareaders and additional arrays might be the method of choice for top gun .NET guys, but for my purposes, the simple and direct approach is all I need.

This post has been edited by hookiethe1: 30 September 2010 - 01:50 PM

[demausdauth]

It would be more straight forward if you know exactly where in the worksheet that you want to get to using a datatable. For example here is code that I have written to access Excel 2007 spread sheets for a utility program that our company uses...

Granted it is in C# but could be easily ported to VB.Net

private static bool isExcelImported(ref DataTable PassedDataTable, string FileName, string WorkSheetName, bool hasHeaders)
        {
            bool returnbool = true;
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + "\"";
            //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";
            

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                try
                {
                    conn.Open();

                    DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });


                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + WorkSheetName + "$]", conn);
                    cmd.CommandType = CommandType.Text;

                    PassedDataTable = new DataTable(WorkSheetName);
                    new OleDbDataAdapter(cmd).Fill(PassedDataTable);

                    if (!(PassedDataTable.Rows.Count > 0))
                    {
                        MessageBox.Show("Worksheet could not be found.", UtilityName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        returnbool = false;
                    }

                    
                }
                catch (OleDbException exp)
                {
                    MessageBox.Show("An error occurred: " + exp.Message, UtilityName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    returnbool = false;
                }
                catch (Exception exp)
                {
                    MessageBox.Show("An error occurred: " + exp.Message, UtilityName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    returnbool = false;
                }

            }
            return returnbool;
        }

This gives me a datatable of the spreadsheet, but how does that help you?

Well if you know you want to get information at say cell C8, thats column C and row 8 correct? Well column C = 3 (A=1,B=2,C=3),
so essentially the cell we want is 3 over 8 down. Great, but how does this help?

Well we can use the row index and column index to get the specific cell in a datatable also.

Very important note: Excel is 1-based arrays and DataTables are 0-based.

 Dim myValue As String 
 Dim excelTable as New DataTable
 
 If isExcelImported(excelTable, "C:\Test.xls", "Sheet1") Then
      'process sheet
      'get value at location C8

       ' remember that DataTAbles are 0-based not 1-based like excel
       myValue = excelTable.Rows(7)(2).ToString() ' 7 for rows and 2 for columns
 Else 
     'error
     MessageBox.Show("Error")
 End If
 

[hookiethe1]

See now that's funny, I had code almost exactly the same as that, but abandoned it due to lack of useful information. I read a ton of pages, but couldn't find a single one that said "here's how you read a cell from a datatable..." even on the MSDN datapages, like this one: http://msdn.microsof....datatable.aspx
Frustrating. Oh well, through guessing and intellisense i've practically finished the program now, maybe next time I'll try that approach (or sooner, since it's been suggested that my excel spreadsheet may be scrapped for a database!)

Thanks again for the help.