Read data from combined Excel columns/rows using C#

Question

I'm trying to read data from an Excel document in C# using Microsofts COM Interop.

So far, I'm able to load the document and read some data from it. However, I need to read data from two different columns and output these as json (for a jquery ajax call)

I've made a quick prototype of how my Excel document is structured with the hope that it's a bit easier to explain ;-)

enter image description here

The method I have is called GetExcelDataByCategory(string categoryName) where the categoryName parameter would be used to find which column to get the data from.

So, i.e., if I'm making the call with "Category 2" as parameter, I need to get all the values in the C columns rows and the corresponding dates from the A column, so the output will look like this:

enter image description here

Which then needs to be transformed/parsed into JSON.

I've searched high and low on how to achieve this, but with no luck so far :-( I'm aware that I can use the get_Range() method to select a range, but it seems you need to explicitly tell the method which row and which column to get the data from. I.e.: get_Range("A1, C1")

This is my first experience with reading data from an Excel document, so I guess there's a lot to learn ;-) Is there a way to get the output on my second image?

Any help/hint is greatly appreciated! :-)

Thanks in advance.

All the best,

Bo

One of the best ways to learn the Excel Object Model is to record a Macro in Excel where you manually perform the task at hand. Then, review the resulting VBA code in the macro to get a good idea how to structure your code to perform the similar task. — Tim Lentine
– Tim Lentine, Commented Mar 14, 2012 at 14:12

Jetti · Accepted Answer · 2012-03-14 21:37:58Z

7

This is what I would do:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open("path to book");
Excel.Worksheet xlSheet = xlWorkbook.Sheets[1]; // get first sheet
Excel.Range xlRange = xlSheet.UsedRange; // get the entire used range

int numberOfRows = xlRange.Rows.Count;
int numberOfCols = xlRange.Columns.Count;
List<int> columnsToRead = new List<int>();
// find the columns that correspond with the string columnName which
// would be passed into your method
for(int i=1; i<=numberOfCols; i++)
{
    if(xlRange.Cells[1,i].Value2 != null) // ADDED IN EDIT
    {
        if(xlRange.Cells[1,i].Value2.ToString().Equals(categoryName))
        {
            columnsToRead.Add(i);
        }
    }
}
List<string> columnValue = new List<string>();
// loop over each column number and add results to the list
foreach(int c in columnsToRead)
{
    // start at 2 because the first row is 1 and the header row
    for(int r = 2; r <= numberOfRows; r++)
    {
        if(xlRange.Cells[r,c].Value2 != null) // ADDED IN EDIT
        {
            columnValue.Add(xlRange.Cells[r,c].Value2.ToString());
        }
    }
}

This is the code I would use to read the Excel. Right now it reads every column that has the heading (designated by whatever is in the first row) and then all the rows there. It isn't exactly what you asked (it doesn't format into JSON) but I think it is enough to get you over the hump.

EDIT: Looks like there are a few blank cells that are causing problems. A blank cell will be NULL in the Interop and thus we get errors if we try to call Value2 or Value2.ToString() since they don't exist. I added code to check to make sure that the cell isn't null before doing anything with it. It prevent the errors.

edited Mar 14, 2012 at 21:37

answered Mar 14, 2012 at 14:32

Jetti

2,4681 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bomortensen Over a year ago

Hi Jetti, thanks a lot for your input! Appreciated :) However, when I try the code with my excel file (.xls) I keep getting the exception "Cannot perform runtime binding on a null reference" and when I take a look at the debugger, there's this exception all over: "Description: Old Format or Invalid Type Library" even if I set the CurrentCulture as Microsoft suggested :-/

Jetti Over a year ago

@bomortensen The problem is most likely because your cell is empty. I added some code to my answer that will check to make sure a cell is not empty before doing anything with it. That should fix the problem

Stephan · Accepted Answer · 2012-03-14 14:17:04Z

0

for Excel-parsing and creation you can use ExcelDataReader: http://exceldatareader.codeplex.com/

and for json you can use json.net: http://json.codeplex.com/

Both are fairly easy to use. Just have a look at the websites.

answered Mar 14, 2012 at 14:17

Stephan

1,6573 gold badges15 silver badges26 bronze badges

1 Comment

bomortensen Over a year ago

Hi Stephan, thanks a lot! :) I tried it, but I'm getting an index out of bounds exception even if the result count i 252 :( Been fooling around with it some time now and I'm having a hard time to find out why. The exception occurs when I try to make a GetValue(0) on the reader. I tried with other indexes aswell, with the same result..

Collectives™ on Stack Overflow

Read data from combined Excel columns/rows using C#

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related