We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on : Nov 16th 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

array formulas

Thread ID:

Created:

Updated:

Platform:

Replies:

18627 Sep 3,2004 03:15 PM UTC Apr 7,2005 09:07 PM UTC WinForms 10
loading
Tags: XlsIO
Greg
Asked On September 3, 2004 03:15 PM UTC

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
Replied On September 7, 2004 02:25 PM UTC

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 [Syncfusion]
Replied On September 9, 2004 06:40 AM UTC

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
Replied On November 4, 2004 11:09 PM UTC

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

Administrator [Syncfusion]
Replied On November 8, 2004 09:11 AM UTC

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 [Syncfusion]
Replied On November 8, 2004 12:49 PM UTC

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
Replied On November 8, 2004 06:48 PM UTC

Excellent. I''ll be looking forward to exercising it.

Greg
Replied On March 29, 2005 02:10 AM UTC

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 [Syncfusion]
Replied On April 4, 2005 07:13 AM UTC

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
Replied On April 4, 2005 04:11 PM UTC

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 [Syncfusion]
Replied On April 7, 2005 09:07 PM UTC

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.

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;