Excel sheet to array

Jun 2, 2014 at 3:22 AM
Hello,
I'm trying to read the excel sheet and save all of its data into the 2D array. The problem is, that enything I do I get the error: System.NullReferenceException. I did FOR loops, I used foreach DataRow etc. but I still fail. My latest try:
            FileStream stream = File.Open(ExcelSourceFile, FileMode.Open, FileAccess.Read);
            IExcelDataReader excelReader = Path.GetExtension(ExcelSourceFile).ToLower() == ".xls" ? ExcelReaderFactory.CreateBinaryReader(stream) :ExcelReaderFactory.CreateOpenXmlReader(stream);
            DataSet ds = new DataSet();
            ds = excelReader.AsDataSet();
            DataRowCollection drc = ds.Tables[0].Rows;


int j=0;
            for (int i = 0; i < rows; i++)
            {
                foreach (DataColumn column in ds.Tables[0].Columns)
                {
                    DataFromExcel[i, j] = ds.Tables[0].Rows[i][j];
                    j++;
                }
            }
There might be some stuff from my previous tries. I know it must be the easiest thing to do with this library but I can't find the correct method. Hope someone can find few minutes to write it down.

Best regards,
Mike.
Jun 2, 2014 at 8:47 AM
Edited Jun 2, 2014 at 11:35 AM
You may be best off reading it use the idatareader interface rather than a dataset. It will be quicker anyway. Not sure why you are getting null reference exceptions off top of my head though.
Jun 3, 2014 at 12:49 AM
Edited Jun 3, 2014 at 1:20 AM
Thank you.
Unfortunately after spending few more hours with tries it looks like I'm not understanding the iDataReader feature - it looks easy but I'm failing everytime I try to run the "excelReader.Read()". Can anyone post the solution to my problem? I'm unable to do it by myself.

Regards,
Mike.
Jun 6, 2014 at 12:55 PM


Jun 7, 2014 at 1:24 AM
Hi,
Me again.

So if I understood well the iDataReader feature I commited this piece of code (the DataSet remained for calculating number of rows and columns):
FileStream stream = File.Open(ExcelSourceFile, FileMode.Open, FileAccess.Read);
            IExcelDataReader excelReader = Path.GetExtension(ExcelSourceFile).ToLower() == ".xls" ? ExcelReaderFactory.CreateBinaryReader(stream) :ExcelReaderFactory.CreateOpenXmlReader(stream);
            
            DataSet ds = new DataSet();
            ds = excelReader.AsDataSet();
            rows = ds.Tables[0].Rows.Count;
            cols = ds.Tables[0].Columns.Count;

            int row=0;
            while (excelReader.Read())
            {
                for (int column = 0; column<=cols; column++)
                {
                    DataFromExcel[row, column] = excelReader[column].ToString();
                } // this is where code breaks with error: System.NullReferenceException
                row++;
            }
            // DataFromExcel[i, j] =
            excelReader.Close();
The error of this code says: System.NullReferenceException. Unfortunately I use VS Express so the debug tool is almost inexisting. At the top of the class there is a declaration:
object[,] DataFromExcel;
What I'm missing?

Best regards,
Mike.
Marked as answer by MikeKrop on 6/6/2014 at 5:38 PM
Jun 7, 2014 at 1:34 AM
Edited Jun 7, 2014 at 1:39 AM
SHAME ON ME! I got the answer right in front of my eyes. I forgot to declare the size of the array.
(...)
            rows = ds.Tables[0].Rows.Count;
            cols = ds.Tables[0].Columns.Count;

            DataFromExcel = new object[rows, cols]; // set the size of the array
            int row=0;
            while (excelReader.Read())
(...)
Sorry for wasting your time.

Mike.
Marked as answer by MikeKrop on 6/6/2014 at 5:38 PM