HowTo code C# to read an Excel File

I am running local VS Express 2013 on Win7:64bit leveraging MSExcel 14.0 Object Library Interop Library added to Solution Reference.

I am only finding conflicting code for RAZOR shorthand for my .aspx.cs file
I am trying to code the following procedural loop to step through reading an adjacent excel file.

please help me identify why my worksheet object hints at missing some form of casting (Im too n00b)

thanks
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;

public partial class CMS_Application : System.Web.UI.Page{
    protected void Page_Load(object sender, EventArgs e){

       [i] // \/ Here is my code Im trying to create an Excel application here in C# for a web.ui .page:[/i]
        Excel.Application xlApp = new Excel.Application();
        [i]// \/ create the workbook [ container ][/i]
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\\ASP_OLEDB_NET\\cms.xls");
        [i]// \/ create the worksheet [/i]
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];

      [i]  // \/ Selected the range that was used [/i]
        Excel.Range xlRange = xlWorksheet.UsedRange;

        [i]// \/ Create two integers to contain the count of Columns and Rows. [/i]
        int rowCount = xlRange.Rows.Count;
        int colCount = xlRange.Columns.Count;

        // RAZOR simple programming syntax embedding server code in web pages 
        // statement blocks for RAZOR seem to not work at all . confused by its implementation
        //@{ var myMessage = "Hello World"; } // ERRORS out 'String expected after verbatim specifier'

       // \/ C# Forloop over the entire data set get values
        for (int i=1; i <=rowCount; i++) {
            // testing w/ concole write line
            Console.WriteLine(i);
                for(int j=1; j <= colCount; j++){
                    Response.Write(xlRange.Cells[i,j].Value2.ToString());
                    // /\ Not sure how to write syntax for above line
                }
        }
    }// end Page_Load
}// end class

Open in new window

JAaron AndersonProgramming Architect @ Widener UniversityAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robberbaron (robr)Commented:
this works in my testing (using WinForms c#)

note i had to check for the value being null or the ToString failed reading a cell.
 

also be aware that the Worksheet.UsedRange is commonly wrongly reported by Excel. At least in VBA, with a few workarounds published.
        { var myMessage = @"Hello World";

        ResponseWrite(  myMessage);
        } 

       // \/ C# Forloop over the entire data set get values
        for (int i=1; i <=rowCount; i++) {
            // testing w/ concole write line
            Console.WriteLine(i);
                for(int j=1; j <= colCount; j++){
                    var x = xlRange.Cells[i, j].Value2;
                    if(x!=null)     ResponseWrite( x.ToString());

                    // /\ Not sure how to write syntax for above line
                }
        }
     }

Open in new window

0
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
Im getting too many debugging errors #overwhelmed .

perhaps forget any of my feeble code approach agh

I rethought it out perhaps its simpler if I read a csv ? but I need to still control the cells of data and have a way to know which one is which like a key/value pair so I can nest data nuggets into appropriate areas of the html display format .

make sense ?
0
Robberbaron (robr)Commented:
1/ debug errors where ?  my code complied...  have no idea what razor does so i just removed it , along with all your {i} tags.

2/ I cant make sense of your idea....

3/ If there are particular areas of data within the workbook, you can 'name' the ranges in Excel and the read just that range from external app.  this saves having to parse the entire sheet. And if you re-arrange the excel worksheet, the named ranges stay the same so the external code still works.   xlRange["myrangename1"].Cells[1,1].Value2.ToString()
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
I think I learned that Excel in Office  is required to be running localhost.
If this is true (makes sense then you could leverage calls to the framework) Ill have to abandon entirely and move to reading in .csv its simpler...  best, jaa
0
Robberbaron (robr)Commented:
you can also access the xls or xlsx files via database calls.  I have used this on server side apps before.

https://www.connectionstrings.com/excel-2007/
0
JAaron AndersonProgramming Architect @ Widener UniversityAuthor Commented:
would handling the XLS or XLSX file be the same essentially as handling a CSV ?
0
Robberbaron (robr)Commented:
using database calls, yes.

otherwise the CSV can be handled as a text file, read and processed manually.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.