I’m trying to get the “raw value” as it is stored in the XLSX XML for a numeric cell that has digits beyond the 14 significant places. I understand that Aspose is trying to mimic Excel by using the “double” (IEEE 754) precision. And for 99% cases, it works well. However, not always that level of precision is satisfactory. Moreover, calculations performed by Excel still use full precision, only the representation is limited to IEEE 754.
Look at the code below. It uses an XLSX document (test.xlsx.zip) with two cells:
B1 contains numeric value 770.83200000000102
C1 contains a formula =B1*1000000000000000
You will see that, due to rounding, the values given by Aspose are not congruent.
The scientific representation of C1 is not a problem. However, the rounded value of B1 is.
For comparison, the output of POI below.
import com.aspose.cells.* import org.apache.poi.ss.util.CellReference import org.apache.poi.xssf.usermodel.XSSFCell import org.apache.poi.xssf.usermodel.XSSFSheet import org.apache.poi.xssf.usermodel.XSSFWorkbook // Aspose var opts = TxtLoadOptions(LoadFormat.XLSX) opts.convertNumericData = false opts.keepPrecision = true var workbook = Workbook("test.xlsx", opts) var worksheet: Worksheet = workbook.worksheets var asposeCell1: Cell = worksheet.cells["B1"] asposeCell1.stringValueWithoutFormat // 770.832000000001 var asposeCell2: Cell = worksheet.cells["C1"] asposeCell2.stringValueWithoutFormat // 7.7083200000000102E17 // POI var wb = XSSFWorkbook("test.xlsx") var sheet: XSSFSheet = wb.getSheetAt(0) var cellReference1: CellReference = CellReference("B1") var poiCell1: XSSFCell = sheet.getRow(cellReference1.row).getCell(cellReference1.col.toInt()) poiCell1.rawValue // 770.83200000000102 var cellReference2: CellReference = CellReference("C1") var poiCell2: XSSFCell = sheet.getRow(cellReference2.row).getCell(cellReference2.col.toInt()) poiCell2.rawValue // 7.7083200000000102E+17
Despite the multiple posts on this issue, i haven’t been able to find a solution that would work for me.
I hope somebody can point me in the direction of getting the raw value for a numeric cell.