ImportTwoDimensionArray doesnt take care of an invalid formula being dumped

Aspose.Cells Product Family
1

Hi,


I have a case where a string[,] array is being dumped on an excel sheet. One of the array elements has an excel unrecognizable formula, for example " =Something() ". Now, Excel while dumping in such a situation would automatically change the string to be dumped to " '=Something() ", to let the formula be put in a cell as a string. Aspose throws a CellsException.

string[,] array = { { “=SOMETHING(“something”)==1?“Y”:“N”” } };

Worksheet.Cells.ImportTwoDimensionArray(array, 0, 0, true); —> throws exception

should set value of A1 to '=SOMETHING(“something”)==1?“Y”:"N"

Thanks
2

Hi,


Thanks for providing us some details and sample code.

How could you place your mentioned formula “=SOMETHING(“something”)==1?“Y”:“N”” in MS Excel, I tried to insert the formula but to no avail, MS Excel does not allow such invalid formula to be inserted in it. If you still think it is possible in MS Excel, kindly post a template file that should contain your desired formula in it, we will check it soon.

PS, MS Excel can only work your scenario, if you underlying cell is formatted as Text/ string.

Thank you.
3

Hi,


I have attached a sample file where the said formula has been put in a cell.

The formula has been put using

CComPtrExcel::Range range = GetRangeLogic();
_variant_t Array = GetArrayLogic();
range->PutValue2(Array);

Array contains the weird formula without the apostrophe, and gets dumped with an apostrophe in the beginning.

I intend to achieve this using ImportTwoDimensionArray.

Thanks
4

Hi,


Thanks for the template file.


Well, I am afraid, Aspose.Cells won’t insert anything before the formula to accommodate it if the formula is invalid, you may insert an apostrophe in the beginning of your formula string value in the array. I tested the following sample code and it works fine:
e.g
Sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[“Sheet1”];
string[,] array = { { “’=SOMETHING(“something”)==1?“Y”:“N”” } };
worksheet.Cells.ImportTwoDimensionArray(array, 0, 0, true);
workbook.Save(“e:\test2\out1.xlsx”);

By the way, you may confirm, when you try to insert the formula “=SOMETHING(“something”)==1?“Y”:“N”” directly into Ms Excel, MS Excel prompts the error and won’t insert the formula either.

Thank you.