We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Cell.StringValue with Conditional Formatting

I am using Aspose Cells version 8.3.0. The Cell.StringValue does not seem to respect conditional formatting that applies to the cell. Is there a way to get the displayed text according applied conditional formatting?


The below code creates a worksheet with two cells with the same “0” Custom number format and applies a “0.00” conditional formatting to one of those cells. It places 0.5 in both cells. Then it copies those two cells’ StringValues to another column and saves the workbook. When I open the workbook, I see the conditional formatting displays the text “0.50” correctly on the second cell, but the StringValue still reflected only the Custom number format. I would expect both the StringValues in the second column to look like the displayed text from the first column.


Dim BOOK = New Aspose.Cells.Workbook
Dim SHEET = BOOK.Worksheets(0)
Dim CELL_1 = SHEET.Cells(0, 0)
Dim CELL_2 = SHEET.Cells(1, 0)
Dim CELL_3 = SHEET.Cells(0, 1)
Dim CELL_4 = SHEET.Cells(1, 1)
Dim AREA = New Aspose.Cells.CellArea
Dim CELL_STYLE = CELL_1.GetStyle
Dim intFORMATTING = SHEET.ConditionalFormattings.Add
Dim FORMATTING = SHEET.ConditionalFormattings.Item(intFORMATTING)
CELL_STYLE.Custom = "0"
CELL_1.SetStyle(CELL_STYLE)
CELL_2.SetStyle(CELL_STYLE)

AREA.StartRow = CELL_2.Row
AREA.EndRow = CELL_2.Row
AREA.StartColumn = CELL_2.Column
AREA.EndColumn = CELL_2.Column

Dim int_arr_FORMATTING_STYLE = FORMATTING.Add(AREA, Aspose.Cells.FormatConditionType.NotContainsBlanks, Aspose.Cells.OperatorType.None, Nothing, Nothing)
Dim FORMATTING_STYLE = FORMATTING.Item(int_arr_FORMATTING_STYLE(0))
FORMATTING_STYLE.Style.Custom = "0.00"

CELL_1.PutValue(0.5)
CELL_2.PutValue(0.5)
CELL_3.PutValue(CELL_1.StringValue)
CELL_4.PutValue(CELL_2.StringValue)
BOOK.Save(“C:\TEMP\Test.xlsx”)

Hi Don,

Thanks for your posting and using Aspose.Cells.

It seems to be a New Feature and currently not available. We have therefore logged a New Feature request in our database to implement this feature. Once, it is implemented or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-43179 - Retrieve Cell.StringValue with Conditional Formatting

I have also attached the screenshot explaining this issue for a reference.

Hi,

Thanks for your using Aspose.Cells.

Please use Cell.DisplayStringValue instead of Cell.StringValue with the provided fix.

Please download and try the latest fix: Aspose.Cells for .NET (Latest Version)and let us know your feedback.

I downloaded the patch for Aspose Cells v8.3.0.1. I changed the last three lines of the code to use this information. I still see the DisplayStringValue as matching the Cell Formatting instead of the Conditional Formatting.

CELL_3.PutValue(CELL_1.DisplayStringValue)
CELL_4.PutValue(CELL_2.DisplayStringValue)
BOOK.Save("C:\TEMP\Test.xlsx")

Attached is the result of the updated program's execution. CELL_4 is still showing the string representation "1". I expect the CELL_4.PutValue to be the string representation "0.5", because that is what Excel displays in CELL_2 when I open the file.

Hi,

Thanks for your posting and using Aspose.Cells.

I tested your code with the latest version: Aspose.Cells for .NET (Latest Version) and it generated the correct output. I have attached the output Excel file and screenshot showing the values in Excel for your reference.

VB.NET


Dim BOOK = New Aspose.Cells.Workbook

Dim SHEET = BOOK.Worksheets(0)

Dim CELL_1 = SHEET.Cells(0, 0)

Dim CELL_2 = SHEET.Cells(1, 0)

Dim CELL_3 = SHEET.Cells(0, 1)

Dim CELL_4 = SHEET.Cells(1, 1)

Dim AREA = New Aspose.Cells.CellArea

Dim CELL_STYLE = CELL_1.GetStyle

Dim intFORMATTING = SHEET.ConditionalFormattings.Add

Dim FORMATTING = SHEET.ConditionalFormattings.Item(intFORMATTING)

CELL_STYLE.Custom = “0”

CELL_1.SetStyle(CELL_STYLE)

CELL_2.SetStyle(CELL_STYLE)


AREA.StartRow = CELL_2.Row

AREA.EndRow = CELL_2.Row

AREA.StartColumn = CELL_2.Column

AREA.EndColumn = CELL_2.Column


Dim int_arr_FORMATTING_STYLE = FORMATTING.Add(AREA, Aspose.Cells.FormatConditionType.NotContainsBlanks, Aspose.Cells.OperatorType.None, Nothing, Nothing)

Dim FORMATTING_STYLE = FORMATTING.Item(int_arr_FORMATTING_STYLE(0))

FORMATTING_STYLE.Style.Custom = “0.00”


CELL_1.PutValue(0.5)

CELL_2.PutValue(0.5)

CELL_3.PutValue(CELL_1.DisplayStringValue)

CELL_4.PutValue(CELL_2.DisplayStringValue)

BOOK.Save(“Test.xlsx”)

The Aspose Cells version 8.3.0.4 did produce the expected output. Thank you for improving this feature.

Hi Don,

Thanks for your feedback and using Aspose.Cells.

It
is good to know that your issue is resolved with the latest fix. Let us
know if you encounter any other issue, we will be glad to look into it
and help you further.

The issues you have found earlier (filed as CELLSNET-43179) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.