Export to Excel without formula

[Marcus Greenwood]

Hi,

Is it possible to run an export from a Grid to Excel but just exporting the values of the cells rather than the Formulas. I need to do this because I have some custom forumlae which don''t exist in Excel and it just throws an error during the export routine.

cheers
Marcus

[Administrator]

Hi Marcus,

I have created the sample as per your requirement .Please refer to the attached sample and let me know if you are trying something different.

Here is a sample.
http://www.syncfusion.com/Support/user/uploads/DemoProj_f4636a34.zip

Let me know if this helps.
Best Regards,
Haneef

[Philip Bishop]

Is this sample still available?  I tried using the link and it says it's not found.

[Christopher Issac Sunder K]

Hi Philip,

We don’t have that particular sample now. Here I have attached a sample achieving the similar requirement. You can prevent exporting the formula alone by handling  ‘QueryImportExportCellInfo’ event. Here is the code,

Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl();

gecc.QueryImportExportCellInfo += gecc_QueryImportExportCellInfo;

gecc.GridToExcel(this.gridControl1.Model, saveFileDialog.FileName);

void gecc_QueryImportExportCellInfo(object sender, GridImportExportCellInfoEventArgs e)

{

    if (e.Action == GridConverterAction.Export)

    {

        if (e.GridCell.CellType == GridCellTypeName.FormulaCell && e.GridCell.HasFormulaTag)

        {

            e.ExcelCell.Value = e.GridCell.FormattedText;

            e.Handled = true;

        }

    }

}

Sample: http://www.syncfusion.com/downloads/support/forum/47121/ze/Export-1348171742

Regards,
Christo

[Philip Bishop]

Christo,

I'm having some issues making that work in VB.  Could you send me a VB sample?  I've converted all the code but can't seem to get one line to work.

Thanks.

Phil

[Christopher Issac Sunder K]

Hi Philip,

Thanks for the update.

I have converted the sample to VB code. Here is the code,

Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click

  Dim gecc As New Syncfusion.GridExcelConverter.GridExcelConverterControl()

  'hook this event to handle the exporting/importing style settings

AddHandler gecc.QueryImportExportCellInfo, AddressOf gecc_QueryImportExportCellInfo

  'other codes

End Sub

Private Sub gecc_QueryImportExportCellInfo(ByVal sender As Object, ByVal e As GridImportExportCellInfoEventArgs)

  If e.Action = GridConverterAction.Export Then

     If e.GridCell.CellType = GridCellTypeName.FormulaCell AndAlso e.GridCell.HasFormulaTag Then

        e.ExcelCell.Value = e.GridCell.FormattedText

        e.Handled = True

     End If

  End If

End Sub

Sample: Export_VB


If we handle the ‘QueryImportExportCellInfo’ event, the other style settings (Eg. BackColor, Text color etc.. ) will be ignored. If we need that, we need to assign the needed style particularly.

Let me know if you still have any issues.

Regards,
Christo