CreateArrayFormula method

Dec 30, 2010 at 7:13 PM

Hello Everyone, I am having a problem with the CreateArrayFormula method.  It does not seem to create an array formula in the final spreadsheet.  Here is test code that creates the same conditions I have been experiencing in my project.  I wonder if I am doing something wrong?

using System;
using System.IO;
using OfficeOpenXml;
using System.Diagnostics;


namespace EPPlus_ArrayFormula
{
    class Program
    {
        static void Main(string[] args)
        {
            FileInfo test = new FileInfo(@"C:\csharpTest.xlsx");

            if (File.Exists(test.FullName))
            {
                File.Delete(test.FullName);
            }

            using (ExcelPackage p = new ExcelPackage(test))
            {
                ExcelWorksheet ws = p.Workbook.Worksheets.Add("Test");

                ws.Names.Add("bid_totals", ws.Cells["A1:F1"]);

                // I was not sure if the = was necessary, I tried with and without it.  The result is the same.
                ws.Cells["D3"].CreateArrayFormula("=MIN(IF(bid_totals>0,bid_totals))");
                ws.Cells["D4"].CreateArrayFormula("MIN(IF(bid_totals>0,bid_totals))");

                p.Save();
            }

            if (File.Exists(test.FullName))
            {
                if (File.Exists(@"c:\csharpTest.zip"))
                {
                    File.Delete(@"c:\csharpTest.zip");
                }

                File.Copy(test.FullName, @"c:\csharpTest.zip");
                Console.WriteLine("Successful file creation.");
            }
            else
            {
                Console.WriteLine("File not created.");
            }

            Console.ReadLine();

            Process.Start(test.FullName);
        }
    }
}

 

I have opened and inspected the worksheet XML as detailed in the FAQ.  This is how they differ:

 

Working array formula XML:

- <row r="3" spans="4:4">
- <c r="D3">
  <f t="array" ref="D3">MIN(IF(bid_totals>0,bid_totals))</f>
 <v>0</v>
</c>
</row>

 

Non-working array formula XML:

- <row r="3">
- <c r="D3" s="0">
<f>=MIN(IF(bid_totals>0,bid_totals))</f>
</c>
</row>

 

In order to get the working XML, I manually entered the array formula using CTRL+SHIFT ENTER.

My original project is in VB under VS2008.  I wrote the test code in VS2010.  I am using Office 2007.

 

Thanks for your time!

Mike

Coordinator
Jan 2, 2011 at 6:55 PM

Hi,

I'll have a closer look at this tomorrow (I have not access to my dev. env. right now), but check out if this is a "single cell" problem. Is it working if you use two cells or more in the indexer, like this? ...

ws.Cells["D3:D4"].CreateArrayFormula("=MIN(IF(bid_totals>0,bid_totals))");


Jan 3, 2011 at 5:29 AM

Hi Jan, thank you for the reply.

It does work with multiple cells, as you wrote.  It appears to be a single cell problem.

Mike

Jun 17, 2012 at 5:28 PM
jankallman wrote:

Hi,

I'll have a closer look at this tomorrow (I have not access to my dev. env. right now), but check out if this is a "single cell" problem. Is it working if you use two cells or more in the indexer, like this? ...

ws.Cells["D3:D4"].CreateArrayFormula("=MIN(IF(bid_totals>0,bid_totals))");


Hi,

I was wondering if this is a bug or because I'm not using arrayformula's correctly. I know am in desperate need of adding an arrayformula to a single cell, and your workaround (i.e. extend the range) is a no go.