This project has moved and is read-only. For the latest updates, please go here.

Cell.Value throwing System.FormatException

Nov 11, 2014 at 9:11 PM
Edited Nov 11, 2014 at 9:12 PM
I'm trying to parse an excel, but the cells that have formatted percentage values are throwing System.FormatException when I try to access the their Value.

Here is the string representation of the cell:
 [ClosedXML.Excel.XLCell]: {ClosedXML.Excel.XLCell}
    Active: false
    Address: {B3}
    Comment: {}
    DataType: Number
    DataValidation: {ClosedXML.Excel.XLDataValidation}
    FormulaA1: ""
    FormulaR1C1: ""
    FormulaReference: null
    HasComment: true
    HasDataValidation: false
    HasFormula: false
    HasHyperlink: true
    HasRichText: true
    Hyperlink: {ClosedXML.Excel.XLHyperlink}
    NewDataValidation: {ClosedXML.Excel.XLDataValidation}
    RichText: {10,10%}
    ShareString: true
    Style: {Font:False-False-Single-False-Baseline-False-10-Color Theme: Hyperlink, Tint: 0-Arial-Swiss Fill:Color Index: 64-None-Color Index: 64 Border:Thin-Color Index: 8-None-FF000000-None-FF000000-None-FF000000-None-FF000000-False-False NumberFormat: 10- Alignment: Right-Bottom-0-False-ContextDependent-0-False-0-False- Protection: Locked}
    Value: 'row.Field(SELLING_RATE_INDEX).Value' threw an exception of type 'System.FormatException'
    ValueCached: null
    Worksheet: {'LTN 010113'!A1:XFD1048576}
Any idea why this happens?
Marked as answer by JobaDiniz on 11/11/2014 at 1:26 PM
Nov 11, 2014 at 9:14 PM
Create an issue and attach the file.
Nov 28, 2014 at 6:55 AM
Edited Nov 28, 2014 at 6:56 AM
I have the same error in the Excel file.
If I copy paste the formula in the excel file = value is correct.
        DataTable tbl_rez = new DataTable();
        tbl_rez.Columns.Add("V", typeof(int));
        for (int i = 1; i < 18; i++)
            DataRow r1 = tbl_rez.NewRow();
        var workbook2 = new XLWorkbook();
        var worksheet2 = workbook2.Worksheets.Add("List1");
        worksheet2.PageSetup.PageOrientation = XLPageOrientation.Landscape;

        worksheet2.Cell(1, 1).InsertTable(tbl_rez.AsEnumerable());

        workbook2.CalculateMode = XLCalculateMode.Auto;
        workbook2.ReferenceStyle = XLReferenceStyle.R1C1;

        var cellWithFormulaR1C1 = worksheet2.Cell(1 + tbl_rez.Rows.Count, 1);
        // In Russia
        cellWithFormulaR1C1.FormulaR1C1 = "=СУММ(R[-" +( tbl_rez.Rows.Count-1).ToString() + "]C:R[-1]C)";
        // In English
        //cellWithFormulaR1C1.FormulaR1C1 = "=SUM(R[-" + (tbl_rez.Rows.Count - 1).ToString() + "]C:R[-1]C)";