This project has moved. For the latest updates, please go here.

Empty Cells are ignored when reading data

Jun 7, 2011 at 1:50 PM

Hi,

I am using the following code to get the contents of a table (I think this is the best way to do it with EPPlus)

var cells = (from cell in sheet.Cells[sheet.Tables[tableName].Address.Address] select cell).ToArray();

for (int i = nrOfColumns; i < cells.Count(); i = i + nrOfColumns)
{
 ....
 
 

However, the sheet.Cells collection discards the cells in the table that don't have a value.

This means that the loop that follows does not read the rows of the table.

Is this by design? I think its a bug right?

If it's by design I can take a look and propose a fix. Thanks for confirming :)

Kind Regards, Tom Pester

Editor
Jun 7, 2011 at 1:58 PM
Edited Jun 7, 2011 at 2:09 PM

I think it is by design and I dont see why need a fix. If you change this, all empty cells will have entries in our cells collection and it will be not good for perfomance. Better waiting for Jan ;D

Jun 7, 2011 at 2:22 PM

Hello mrxrs,

Thats a good argument but I think the performance optimasation isn't worth the divergence from the expected behaviour.

But if this is by design mrxrs, how do you read all the data from a table then ?

Kind Regards, Tom

Editor
Jun 7, 2011 at 2:42 PM

If you want to go through all cells, instead of using linq why dont you do a for-loop with worksheet dimensions ?

Jun 8, 2011 at 10:02 AM

Thanks for the workaround mrxrsd.

 

 

 

using System;
using System.IO;
using OfficeOpenXml;

namespace ConsoleApplication1
{
    class Program
    {
    
        static void Main(string[] args)
        {

            FileInfo existingFile = new FileInfo(@"c:\temp\Book1.xlsx");
            //col1           col2
            //1              <empty cell>
            //<empty cell>   2

            int nrrow = 3;
            int nrcol = 2;
           

            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets[1];

                var cells = sheet.Cells[1, 1, nrrow, nrcol];

                //========================================================
                Console.WriteLine("using Range");
                foreach (var cell in cells)
                {
                    Console.WriteLine(cell.Value);
                }

                //output:
                //col1
                //col2
                //1
                //2
                // => Empty Cells are ignored

                //========================================================
                Console.WriteLine("using Individual Cell");
                for (int rowtel = 1; rowtel <= nrrow; rowtel++)
                {
                    for (int coltel = 1; coltel <= nrcol; coltel++)
                    {
                        var c = sheet.Cells[rowtel,coltel];

                        Console.WriteLine(c.Value);
                    }
                }

                //output:
                //col1
                //col2
                //1
                //<empty cell>
                //<empty cell>
                //2
                // => Empty Cells are taken into account

               
            }
        }

    }
}
Editor
Jun 8, 2011 at 11:53 AM
Edited Jun 8, 2011 at 11:55 AM

No problem, I dont know if you know or not, but you can use worksheet dimension property to get your code more "dynamic", take a look at this thread 

http://epplus.codeplex.com/discussions/257072