array formulas

[Greg]

Do you have an example of how to both read and write array formulas with ExcelRW xls files? I honestly haven''t tried just yet, but expect problems since a user cannot type braces around a formula to create an array formula (have to hit key combination).

I''ll eventually want to do this with named ranges as well, but see my other post about my inability to use the named range within a formula.

Thanks,
Greg

[Greg]

OK, it seems that I cannot discern between an array formula vs. one that is a simple cell formula. There are no surrounding braces, like {=namedvar1*2.5} or any flags that I can identify. Array formulas are also fixed in cell range, and I have not been able to find that information either.

And of course, when I read the formula, I am not seeing range names if they are used, but only the resulting range.

Please respond.

Thanks,
Greg

[Administrator]

Hi Greg,

I have created this as a feature request and will update you on the status at the earliest. Thanks.

Best regards,

Stephen.

>OK, it seems that I cannot discern between an array formula vs. one that is a simple cell formula. There are no surrounding braces, like {=namedvar1*2.5} or any flags that I can identify. Array formulas are also fixed in cell range, and I have not been able to find that information either.
>
>And of course, when I read the formula, I am not seeing range names if they are used, but only the resulting range.
>
>
>Please respond.
>
>Thanks,
>Greg

[Greg]

Does it look like we''ll be getting array formula read and write?

[Administrator]

Hi Greg,

Sorry for not keeping you updated.

Version 3.0 has support for reading Array Formulas but it does not have the capability to enter Array Formulas due to a bug in the current write implementation. The value gets written exactly like Excel writes it but MS Excel does not compute the value correctly and the user needs to press CTRL+SHIFT+ENTER in order to see the correct computed value. We will try to address this issue as soon as possible. Thanks.

Best regards,

Stephen.

>Does it look like we''ll be getting array formula read and write?

[Administrator]

Hi Greg,

An update on the ArrayFormulas issue. The Formula Array reading bug has been fixed. Thanks.

Best regards,

Stephen.

>Hi Greg,
>
>Sorry for not keeping you updated.
>
>Version 3.0 has support for reading Array Formulas but it does not have the capability to enter Array Formulas due to a bug in the current write implementation. The value gets written exactly like Excel writes it but MS Excel does not compute the value correctly and the user needs to press CTRL+SHIFT+ENTER in order to see the correct computed value. We will try to address this issue as soon as possible. Thanks.
>
>Best regards,
>
>Stephen.
>
>
>
>>Does it look like we''ll be getting array formula read and write?

[Greg]

Excellent.

I''ll be looking forward to exercising it.

[Greg]

Although I planned to implement read/write of xls array formulas this week, I am failing using ExcelRW 3.0.1.0. Attached is a "simple" xls example of array calculations (using named ranges which probably don''t work well in my version either). When I try to read this file in, I get a blowup when checking for B2.IsBlank. And when I check the range B2:D2, I get nothing for Formula and FormulaArray. (yes, I can read/write formulas for cells, and can read/write named ranges for a range of cells, but have not been successful in reading nor writing array formulas) Please show me how to read AND write such an xls using your system. I have given you months of advanced notice of my intention, but have received no samples, nor do I believe ExcelRW is ready for this. Please help. Thanks, Greg array2_7075.zip

[Administrator]

Hi Greg, Sorry for the delay in responding. 1) Here is the usage snippet for entering array formulas mySheet.Range["A1"].Number = 1; mySheet.Range["A2"].Number = 2; mySheet.Range["A3"].Number = 3; mySheet.Range["A4"].Number = 4; mySheet.Range["B1"].Number = 1; mySheet.Range["B2"].Number = 2; mySheet.Range["B3"].Number = 3; mySheet.Range["B4"].Number = 5; mySheet.Range["C1"].FormulaArray = "SUM(IF(A1:A4=B1:B4,1,0))"; I tested the above snippet using version 3.2.0.0[Will be available for download later today]and it worked fine. The value in cell C1 was 3. 2) Here is the code snippet that I had used to test the your sample file if(mySheet.Range["B2"].IsBlank) { MessageBox.Show("B2 is Blank"); } else { MessageBox.Show(mySheet.Range["B2"].FormulaArray.ToString()); } The result was B2 is not blank and the FormulaArray value in B2 was ''10 + first'' which is the correct value. However I did see a but that caused the spreadsheet generated to crash when opened using MS Excel. I have filed a bug report with the development team and it should be addressed shortly. You can track the current status here http://www.syncfusion.com/support/issues/excelrw/Default.aspx?ToDo=view&questId=172&catId=66 Please let me know if you have any questions. Thanks, Stephen. >Although I planned to implement read/write of xls array formulas this week, I am failing using ExcelRW 3.0.1.0. > >Attached is a "simple" xls example of array calculations (using named ranges which probably don''t work well in my version either). > >When I try to read this file in, I get a blowup when checking for B2.IsBlank. And when I check the range B2:D2, I get nothing for Formula and FormulaArray. (yes, I can read/write formulas for cells, and can read/write named ranges for a range of cells, but have not been successful in reading nor writing array formulas) > >Please show me how to read AND write such an xls using your system. I have given you months of advanced notice of my intention, but have received no samples, nor do I believe ExcelRW is ready for this. > >Please help. > >Thanks, >Greg > > >array2_7075.zip > >

[Greg]

Thanks for responding. I''ll try the new version whenever it is posted.

Here is what I want to do:

mySheet.Range["A1:A3"].FormulaArray = "{1,2,3}"

mySheet.Range["B1:B3"].FormulaArray = "10*A1:A3"

I understand that neither your Grid nor Calculate products support the brace array notation, but your ExcelRW surely should. And even without the brace formula, I can''t seem to get the second formula above to work as a true array formula (result: {10,20,30})

-Greg

[Administrator]

Hi Greg,

This issue has been fixed. I tested using this following code

mySheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}";
mySheet.Names.Add("Quattro",mySheet.Range["A1:D1"]);

mySheet.Range["A2:D2"].FormulaArray = "Quattro+100";

The result was http://www.syncfusion.com/support/user/uploads/sample632484899675038838.zip

The other issue with array.xls has also been resolved. However, you will not be able to get a patch till next week since we are in the process of releasing version 3.2 which will not include this fix. It will be available in a patch shortly after the release.

Thanks,

Stephen.