Formulas with booleans not working properly-

Hello,

When using Aspose to generate an excel file, I am handling TRUE/FALSE values which I then want to use inside some formulas. TRUE and FALSE values seem to be converted to strings and hence the formulas do not recognize them. The formulas wont work until I manually get inside each (TRUE/FALSE) cell and press enter (apparently excel detects/converts true/false values when I do this).

Hi there,


Thank you for sharing the sample spreadsheet.

I have evaluated the presented scenario while using the following piece of code, and I not able to observe any problem as Aspose.Cells is able to correctly calculate the values for the functions in cells G5 & H5. Moreover, I have modified the formulas in aforementioned cells while replacing the cell reference of J4 & J5 to TRUE & FALSE receptively and found the same results. Please note, I have used the latest revision of Aspose.Cells for .NET 16.11.8 for my testing.

That said, if I am not correctly testing the case then please share your executable code snippet for it.

C#

var book = new Workbook(dir + “GeneratedDocument.xlsx”);
book.CalculateFormula();
var sheet = book.Worksheets[0];
Console.WriteLine(“G5 :” + sheet.Cells[“G5”].Value + " H5 :" + sheet.Cells[“H5”].Value);

Hi,


How do you input “true/false” values into the underlying cells for the formulas? Or your template file has these strings “true/false” already and you are just opening the file via Aspose.Cells APIs, specify the formulas and then save the Excel file? Well, the “true/false” boolean values should be of proper type into those cells otherwise, your formulas won’t be calculated in MS Excel either. For your information, when you do double click into those cells, MS Excel will just convert to proper data type.

Thank you.

Hello, thanks for your reply.

I ran it with version 16.11.0.0 which is the latest available in the website, and still happening. What we do is we read a csv file and write it into an excel file using Aspose (GeneratedDocument.xlsx) when this is performed is when we lose the Boolean type for the cells. Effectively by double clicking the cell, Excel converts it and then the formula will work as the cells will be recognized as Booleans, but we want to avoid this as some of the Generated files are too large.

Hi,


Could you share the sample CSV file here, we will check it soon.

Thank you.

Here it is. Thanks

Hi,


By the way, you may try to convert the “TRUE/FALSE” string to valid boolean type, see the sample code below for your reference that you may try before saving to Excel file format:
e.g
Sample code:

Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;

Aspose.Cells.Cell prevcell = null;
Aspose.Cells.Cell foundCell = null;
string stringToFind = “TRUE”; //You can change it as per your needs
FindOptions opts = new FindOptions();
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = cells.MaxDataRow;
ca.EndColumn = cells.MaxDataColumn;

//Set cells area for find options
opts.SetRange(ca);
opts.LookAtType = LookAtType.Contains;
opts.LookInType = LookInType.ValuesExcludeFormulaCell;
do
{
foundCell = worksheet.Cells.Find(stringToFind, prevcell, opts);
if (foundCell == null)
break;

//Convert to Boolean type
foundCell.Value = Convert.ToBoolean(“true”);
prevcell = foundCell;

} while (foundCell != null);



Hope, this helps a bit.

Thank you.

Hi,


Furthermore, I have also tried the following sample code with our latest version v16.11.9 (attached) using your template CSV file and it works as expected, the output XLSX file is fine tuned with valid boolean values in the cells.
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\Feed+07+dic+A_20161110.csv”, new TxtLoadOptions(LoadFormat.CSV) { ConvertNumericData = true});
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
workbook.Save(“e:\test2\out1GeneratedDocument1.xlsx”);

Thank you.