Change in behaviour

[Stephen]

When opening the attached workbook using the previous version of XlsIO (ExcelRW version 2.1.0.9), and displaying values in a grid, the values appeared exactly as they do in the Excel workbook. Now using version 4.1.0.50 (IRange.Text property), the grid displays formulae in column 1 (row 2 onwards) rather than cell values, and some of the date values have a different format that appears in the Excel workbook. Can you explain how I can read and display values so they appear the same in Excel ?

Workbook.zip

[Stephen]

Behaviour hasn''t changed since previous version, but would still like to know how to display values as they appear in Excel, rather than formulae (as is being displayed in the 1st column).

[Ramu]

Hi Stephen, you could read the formula computed value in the excel file using IRange.FormulaNumberValue property. Here is the sample that reads the exact data from excel and displays in grid.Please take a look at attached sample. Please let me know if you have any questions. ExcelToGrid Thanks for using Syncfusion Products. Thanks, Ramu.K

[Stephen]

It''s not quite that simple because the formula
may return a string value or a date value, so
you''d have to use FormulaStringValue and
FormulaDateTime as well.

The ideal would be if you could add a property
(FormattedValue, say) to IRange, which would
return the value, as displayed in Excel. Better
still, if there could be an overloaded version
of the Open method that reads in information
necessary to be able to read formatted values,
but not information just affecting appearance (font, cell width etc.)

[Ramu]

Hi Stephen, Here is another way of importing excel to grid. Gridconverter control is used to convert excel to grid and from grid to excel using ExcelToGrid() and GridToExcel(). Here is the sample program that converts excel to grid with exact data present in excel. Please take a look at the sample and let me know If you have any questions. ExceltoGridconverter Thanks for using Syncfusion Products. Thanks, Ramu.K

[Joey Keeton]

It''s not quite that simple because the formula may return a string value or a date value, so you''d have to use FormulaStringValue and FormulaDateTime as well. The ideal would be if you could add a property (FormattedValue, say) to IRange, which would return the value, as displayed in Excel. Better still, if there could be an overloaded version of the Open method that reads in information necessary to be able to read formatted values, but not information just affecting appearance (font, cell width etc.)

hi

[Abirami Varadharajan]

Hi Joey, 

 

Thank you for contacting Syncfusion support. 

 

We request you to make use of ‘DisplayText’ property which returns the cell value as a string with number formats applied. (i.e., it returns the value as you see in cells of Microsoft Excel). 

 

It is recommended to invoke EnableSheetCalculations method of worksheet class to calculate formulas if found. Without this line, cells with formula will not return calculated value.  

 

Code Snippet: 

worksheet.EnableSheetCalculations();  

  

Kindly refer the below API reference to know more about DisplayText property.  

  

https://help.syncfusion.com/cr/file-formats/Syncfusion.XlsIO.IRange.html#Syncfusion_XlsIO_IRange_DisplayText   

  

Please let us know if you have any concerns. 

 

Regards, 

Abirami.