Thanks for your reply Mike.

The link that you have posted is not clickable. Can you please direct me to the link from where I can get the latest release?

Hi,

Thanks for the sample code. I tried this out and I get the same results. The label displays in the exported Excel document, even though it is not displayed in the grid on the screen.

I'm not sure this is the same issue, so I'm going to ask Infragistics Developer Support to create a case for you and enter this as a separate bug in our system so we make sure they both get resolved.

I am attempting to export a formula from a WinGrid, however, ever row throws an error.  The message is as follows, "Error = InvalidFormula {2}".  I have been unable to fix this.  The values and DataColumn expression that I am using properly calculates on the grid, however, it will not export.  I am attempting to perform a simple addition operation.  

The value of GridFormula "CON20140129193318-126896C + CON20140129190713-122550C + CON20140210165544-122550C + CON20140212131930-122550C + CON20140214172552-122550C + CON20140217184249-122550C + CON20140226145821-122550C + CON20140405143717-122550C + CON20140416172009-131110C + CON20140509134426-123985C + CON20140529151705-124241C + CON20140530184459-122550C + CON20140618151933-122550C + CON20140616170644-122550C + CON20140707151752-122550C + CON20140714150623-122550C + CON20140714151439-122550C + CON20140710161836-122550C" String

I have tried using brackets for each of the fields and it still yields the same result.  I could potentially write code that would generate the appropriate Excel formula, however, this would defeat the entire purpose of set the value of ExportFormulas equal to true.  My co-worker and I have spent all of yesterday trying to fix this.  We are using Infragistics4.Win.UltraWinGrid.ExcelExport.v14.1 and Infragistics4.Win.UltraWinGrid.v14.1.  The runtime version is set to v4.0.30319 and the Specific Version is set to true for both controls.  Below I have provided the code snippets that set the formula, code that handles the ExcelExporter events, as well as code that generates the Excel file.  Any help would be greatly appreciated.  

Excel Export Event Handling Code:

Private Sub ugee_FormulaExportError(sender As Object, e As Infragistics.Win.UltraWinGrid.ExcelExport.FormulaExportErrorEventArgs) Handles                            ugee.FormulaExportError

     ' Suppress the error for subsequent instances of this formula
     e.Action = FormulaExportErrorAction.CancelAll

     ' Don't try to write out potentially invalid formulas
     e.ApplyInvalidFormulaIfPossible = True
End Sub

Private Sub ugee_FormulaExporting(sender As Object, e As Infragistics.Win.UltraWinGrid.ExcelExport.FormulaExportingEventArgs) Handles ugee.FormulaExporting
     ' Export the formula.
     e.Action = FormulaExportAction.ExportFormula
End Sub

UltraWinGrid Formula Assignment:

For Each Contract As clsTitanContract In oCurrentLocation.FirstOrDefault.Contracts
     With e.Layout.Bands(0)
          e.Layout.Bands(0).Groups("CONTRACTS_TOTAL").Columns.Add(.Columns("CONTOTAL_" + Contract.ContractID))
          e.Layout.Bands(0).Groups("CONTRACTS_TOTAL").Columns.Item("CONTOTAL_" + Contract.ContractID).Header.Caption = "Con: " + Contract.ContractID
          e.Layout.Bands(0).Groups("CONTRACTS_TOTAL").Columns.Item("CONTOTAL_" + Contract.ContractID).CellAppearance.BackColor = Color.LightGray
          e.Layout.Bands(0).Groups("CONTRACTS_TOTAL").Columns.Item("CONTOTAL_" + Contract.ContractID).Header.Appearance.BackColor = Color.DarkBlue
          e.Layout.Bands(0).Groups("CONTRACTS_TOTAL").Columns.Item("CONTOTAL_" + Contract.ContractID).CellAppearance.TextHAlign = HAlign.Right
          e.Layout.Bands(0).Groups("CONTRACTS_TOTAL").Columns.Item("CONTOTAL_" + Contract.ContractID).Format = "#,##0"               e.Layout.Bands(0).Groups("CONTRACTS_TOTAL").Columns.Item("CONTOTAL_" + Contract.ContractID).Formula =              oCurrentInvoice.TransForDisplay.Columns("CONTOTAL_" + Contract.ContractID).Expression

     End With
Next

Excel Export Code:

Me.Cursor = Cursors.WaitCursor
Dim sExcelFileName As String = My.Computer.FileSystem.GetTempFileName
ugee.ExportFormulas = True
ugee.Export(ugrdTrans, sExcelFileName, Infragistics.Documents.Excel.WorkbookFormat.Excel2007)

Hi John,

I don't see how this formula could possibly work without the square brackets around the column names. I tried it out, just in case I was wrong, but without the brackets, I get an error and the formula won't work in the on-screen grid. The square brackets are not optional, but even if they were, they would be needed in this case because your column keys include a minus "-" sign. Unless I am mistaken and those are actually subtractions? Those minus signs are ambiguous in any case, so there's no way I can tell what this formula is supposed to do and so there's no way to the CalcManager or the grid could do it.

Anyway, I tried this out in a small sample with the square brackets, just to see if maybe the minus sign in the column key was causing a problem when exporting. But it's working just fine for me.

Can you post a small sample project that reproduces the error you are getting?

Mike,

Thank you for your response.  I have tried the formula export with and without brackets.  Neither approach has worked, however for further clarification I have provided the actual formula below:

[CON20140411145558-122550A] + [CON20140415124757-122550A] + [CON20140417142125-122550A] + [CON20140520183304-122550A] + [CON20140630121220-122550A] + [CON20140703141204-122550A] + [CON20140716203705-124879A] + [CON20140723204640-131110A]

With or without the brackets it exports data, however, it will not generate a formula.  Any further assistance that you can provide would be helpful.  As far as a sample project is concerned I have provided the actual code that is being used in production.  Is there something that I am missing here?

Hi,

Unfortunately, there are a million things that I can get from a sample that reproduces the problem that I cannot get from code snippets like you have here. For example, this doesn't tell me what version of the controls you are using, nor what data types the columns are. I can't see what filename you are exporting to, so I don't know what format you are using (Excel2003 or Excell2007).

If I have a small sample project that reproduces the issue, I can debug it and tell you why it's not working. With just a code snippet, all I can do is guess and try to reproduce the problem and there's no guarantee what I will ever hit on the exact right combination of circumstances to see the problem occur.

Just to rule out the things I can, I put your code into a sample with the same column names and it works fine for me both when exporting and in the Excel sheet.

I have attached my sample here so you can see if it works for you.

Mike,

Thank you for your follow up.  It occurs only on certain cells.  In fact all other formulas calculated correctly with the exception of just one row.  And, to answer the second question, the Excel export shows the correct formula and it is the actual grid that is displaying #Value.

Hi John,

Does it happen for every cell in the column? Or just some/one cell?

Is the error occurring only in the exported Excel sheet? Or is this a problem in the on-screen grid?

I'm not aware of any known bugs that would cause an issue like that. Are you certain that the result of your formula can be converted to an integer? The native type for numeric formulas is double, so I suppose it's possible that a particular large or small double value might be be convertable to an int.

Mike, 

Thank you for the code sample.  After reviewing the sample I quickly realized that I did not place a UltraCalcManager control on the form.  This solved the problem with the Excel export, however, now I have another problem relating to the formula not displaying properly in the WinGrid.  All columns with the exception of one displays.  The column that does not display the formula value gives an error stating "The formula results could not be converted to the column's data type".  I went into the database to verify that the column was an integer and could not be null.  Further more I went in and specified the column type as integer and even ensured that the DataColumn being added to the WinGrid control would not accept null values.  I am at a loss as this works for other invoices that are generated.  This seems to be a one off error and I am wondering if this is an Infragistics bug. The interesting things about my situation is that the export computes the formulas properly.  What do you believe is going wrong here?  By the way I am using 2014.1 versions of windows forms controls.