CODEWITHSUNDEEP
c#excelclosedxml
Necroyeti
Necroyeti

Reputation: 101

Failed to return the value of a cell in a Worksheet in an XLWorkbook

It turns out that two cells assigned two different dates; to cells A2 and B2 I assign them the values 3/28/2015 and 3/29/2015, respectively (This from c #, to be clear).

Then, to cell A3 I apply a formula. What I do is the following:

ws.Range("A3").FormulaR1C1 = "=SIFECHA(R[-1]C[0],R[-1]C[1],\"md\")";

When I try to access the value of said cell from c # using the following instruction:

ws.Cell("A3").Value;

I get an exception, it seems that due to the data conversion failure ...

Value = '((ClosedXML.Excel.XLCell)y).Value' threw an exception of type 'System.Exception'

The code:

var Dates = new List<KeyValuePair<string, string>>()
{
    new KeyValuePair<string, string>("28/03/2015","29/03/2015"),                                   
};
XLWorkbook workbook = await Task.FromResult(Dates.ToBook());
var ws = workbook.Worksheet("Report");           
ws.Cell("A3").DataType = XLCellValues.Number; // a try failed                
ws.Range("A3").FormulaR1C1 = "=ENTERO(SIFECHA(R[-1]C[0],R[-1]C[1],\"md\"))";          
ws.Cell("A3").Select();
ws.Cell("A3").SetActive(true); //another try
ws.Columns().AdjustToContents();
var y = ws.ActiveCell.Value; //and here the code exploid

Any contribution would be of great help to me. Thank you very much. Sorry for my English, I speak Spanish

Upvotes: 0

Views: 1507

Answers (2)

Necroyeti
Necroyeti

Reputation: 101

After a lot of trying, they force me to implement "brute force" using Excel offered by Microsoft importing the library ---> using Microsoft.Office.Interop.Excel; <---

And the code like that:

    public static Application App;
    public static Workbook Wb;
    public static Worksheet Ws;
    public static object GetGetTimeElapsed(string Fecha_Inicio, string Fecha_Fin)
    {
        try
        {
            if (App == null)
            {
                App = new Application();
                Wb = App.Workbooks.Add();
                Ws = (Worksheet)Wb.Worksheets.get_Item(1);
            }               
            Ws.Cells[1, 1].Formula = "=DATEDIF(\"" + Fecha_Inicio + "\",\"" + Fecha_Fin + "\",\"MD\")";
            Ws.Cells[1, 2].Formula = "=DATEDIF(\"" + Fecha_Inicio + "\",\"" + Fecha_Fin + "\",\"YM\")";
            Ws.Cells[1, 3].Formula = "=DATEDIF(\"" + Fecha_Inicio + "\",\"" + Fecha_Fin + "\",\"Y\")";
            double DaysElapsed = Ws.Cells[1, 1].Value;
            double MonthsElapsed = Ws.Cells[1, 2].Value;
            double YearsElapsed = Ws.Cells[1, 3].Value;
            var List = new
            {
                Days = DaysElapsed,
                Months = MonthsElapsed,
                Years = YearsElapsed
            };
            return new { List.Days, List.Months, List.Years };
        }
        catch (Exception ex)
        {
            throw new BaseException(ex);
        }           
    }

Work perfectly, thanks for your contributions!

Upvotes: 0

jkpieterse
jkpieterse

Reputation: 2986

Formulas written to Excel using the FormulaR1C1 and Formula property must be writen in US English syntax entirely.

Upvotes: 2

Related Questions