Import numbers from an excel column using C#.

Question

Hi.

I have an Excel file with one column which is filled with numbers. I would like to be read the numbers from this column into an array in C#. How can I do that?

Thank you all.

Answer 1

The easiest way is probably to use the Excel ODBC driver. This allows you to use OdbcConnection to read the worksheet into a DataTable. You can then iterate over the table's Rows collection, copying the values into a list or array.

Answer 2

This link might help you. You can adapt the answers to convert the values to integers.

Answer 3

You can use Excel Interop and do something along the lines of:

Excel.Range firstCell = excelWorksheet.get_Range("A1", Type.Missing);
Excel.Range lastCell = excelWorksheet.get_Range("A10", Type.Missing);
Excel.Range worksheetCells = excelWorksheet.get_Range(firstCell, lastCell);
var cellValues = worksheetCells.Value2;

You should get an array of objects (1-based index), and can cast the contents using (for instance) Convert.ToDouble().

Answer 4

SpreadsheetGear for .NET can do it. Below is some C# source. Note that the SpreadsheetGear API is similar to the Excel API, so the code below could be adapted to Excel.

using System;
using SpreadsheetGear;

namespace Program
{
    class Program
    {
        static void Main(string[] args)
        {
            // Load a workbook from disk and get the first worksheet.
            var workbook = SpreadsheetGear.Factory.GetWorkbook(@"C:\tmp\Numbers.xlsx");
            // Allocate a list of doubles to store the number.
            var numbers = new System.Collections.Generic.List<double>();
            var worksheet = workbook.Worksheets[0];
            // Assuming that column A is the column with the numbers...
            var columnA = worksheet.Cells["A:A"];
            var usedRange = worksheet.UsedRange;
            // Limit the cells we look at to the used range of the sheet.
            var numberCells = usedRange.Intersect(columnA);
            // Get the numbers into the list.
            foreach (IRange cell in numberCells)
            {
                object val = cell.Value;
                if (val is double)
                    numbers.Add((double)val);
            }
            // Write the numbers to the console.
            foreach (double number in numbers)
                Console.WriteLine("number={0}", number);
        }
    }
}

You can download the free SpreadsheetGear trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC

Answer 5

Take a look at this: Create Excel (.XLS and .XLSX) file from C#

The open source projects listed on the link above allow you to read/write Excel files.