[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

HowTo code C# to read an Excel File

Posted on 2014-08-25
7
Medium Priority
?
558 Views
Last Modified: 2014-09-10
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

0
Comment
Question by:JAaron Anderson
  • 4
  • 3
7 Comments
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 2000 total points
ID: 40285041
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
 

Author Comment

by:JAaron Anderson
ID: 40289106
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
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 2000 total points
ID: 40289370
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:JAaron Anderson
ID: 40290426
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
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 2000 total points
ID: 40292189
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
 

Author Comment

by:JAaron Anderson
ID: 40298979
would handling the XLS or XLSX file be the same essentially as handling a CSV ?
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 2000 total points
ID: 40304899
using database calls, yes.

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question