Reading data from Excel combo columns / rows using C #

Question

I am trying to read data from an Excel document in C # using Microsoft COM Interop.

So far I can download the document and read some data from it. However, I need to read data from two different columns and output them as json (to call jQuery ajax)

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

The method I have is called GetExcelDataByCategory(string categoryName) , where the categoryName parameter will be used to find in which column to receive the data.

So, if I make a call with the “Category 2” parameter as a parameter, I need to get all the values ​​in the columns of row C and the corresponding dates from column A, so the output will look like this:

Which then needs to be converted / processed to JSON.

I searched high and low how to achieve this, but so far no luck :-( I know that I can use the get_Range () method to select a range, but it seems you need to explicitly specify the method from which the row and which column will receive data. Ie: get_Range ("A1, C1")

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

Any help / hint is much appreciated !:-)

Thanks in advance.

All the best

Bo

Answer 1

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 Excel. Now he reads every column that has a heading (indicated by what is in the first row), and then all the rows there. This is not exactly what you requested (it is not formatted in JSON), but I think that is enough to catch you by the hump.

---

EDIT: Looks like there are a few empty cells that cause problems. An empty cell will be NULL in Interop, and therefore we will get errors if we try to call Value2 or Value2.ToString (), since they do not exist. I added verification code to make sure the cell is not null before doing anything with it. This prevents errors.

Answer 2

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

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

Both are pretty easy to use. Just take a look at the sites.