When a worksheet has a function of the type "=Indirect("A1")" and the value in A1 is edited, the result of the function is not updated. This, however, happens only after the file is imported. If the function is created through code or SetValueAsFormula is called after import, the function works as expected.
Workaround:
CellRange usedCellRange = worksheet.UsedCellRange;
for (int row = 0; row < usedCellRange.RowCount; row++)
{
for (int column = 0; column < usedCellRange.ColumnCount; column++)
{
ICellValue cellValue = worksheet.Cells[row, column].GetValue().Value;
if (cellValue.ValueType == CellValueType.Formula)
{
worksheet.Cells[row, column].SetValue(cellValue);
}
}
}
KeyNotFoundException is thrown on import. The number formats with id 5, 6, 7 and 8 are missing in the built-in number formats. Available in R2 2018 Official Release version.
When the values that were copied have formatting both on the cell and on the column, the pasted values get the formatting of the column, instead of that of the cell. The same applies to rows.
Now there is no public API that allows the users to check if a Worksheet is empty or not. The UsedCellRange property returns a cell range that is only the cell A1 even if it is empty too. Possible solution is to expose IsEmpty property of the CellRange. Workaround: Check if Worksheet.UsedCellRange contains only A1, and that additionally A1 is empty.
The value of Worksheet.UsedCellRange could be calculated during the import of the workbook - as all cells are processed anyway. This will improve the performance of the first usage of this property, which is very common scenario. Also xlsx documents can have optional 'dimension' element which is used to specify the used cell range: <dimension ref="A1:C2"/>.
Some functions are exported from Excel with the _xlfn. prefix as compatibility measure with older versions of MS Excel (future functions, see here https://msdn.microsoft.com/en-us/library/dd907480(v=office.12).aspx , https://support.office.com/en-ca/article/Issue-An-xlfn-prefix-is-displayed-in-front-of-a-formula-882f1ef7-68fb-4fcd-8d54-9fbb77fd5025 ) RadSpreadProcessing do not support these and the spreadsheet treats the function as not supported even when it is. Example: <row r="1" spans="2:2" x14ac:dyDescent="0.25"> <c r="B1" t="b"> <f>_xlfn.ISFORMULA(C2)</f> <v>0</v> </c> </row> This could be observed with the ISFORMULA function.
Introduce support for cell reference ranges which refer to whole columns. For example "=Sheet1$A:$A" refers to the whole column A.