How to export user-defined function from grid to excel workbook?

2 mins read

In order to export user-defined formula library function from Grid to Excel, the following steps has to be followed.

Step 1: Create an Excel Add-In file (*.xla) including the user-defined function as illustrated in the following steps.

Create a new empty workbook and click Save As from the File menu.
In the Save As dialog box, select the location to save the workbook. Enter the file name for the Add-In workbook, and then select the type as Excel Add-In (*.xla).
Click Save, and then press ALT+ F11 to open the VBA Editor. Now, you can create the custom function through the VBA Editor by inserting a module.

Step 2: Now, bind add-in file path and custom method name with GridExcelConverterControl through AddCustomFunction method.

The following screenshot illustrates how to create the user-defined function through the VBA editor.

Showing XLA file

The XLA file will not be visible in Excel because Add-Ins are never visible. All changes must be done to the VBA Editor only.

VBA Coding

Public Function Age(DOB As Date)
Dim cd1 As Date
cd1 = Format(Now, "dd/MM/yyyy")
Dim d1 As Integer
d1 = Year(cd1)
Dim d2 As Integer
d2 = Year(DOB)
If DOB = 0 Then
Age = ""
Else
Age = d1 - d2
End If
End Function

Code Snippet

string xla = "D:\Fun.xlam";
Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Files(*.xls)|*.xls";
saveFileDialog1.DefaultExt = ".xls";
if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
  gecc.AddCustomFunction(xla, "Age", saveFileDialog1.FileName);
  gecc.GridToExcel(this.gridControl1, saveFileDialog1.FileName);
  Process.Start(saveFileDialog1.FileName);
}

Dim xla As String = "D:\Fun.xlam"
Dim gecc As New Syncfusion.GridExcelConverter.GridExcelConverterControl()
Dim saveFileDialog1 As New SaveFileDialog()
saveFileDialog1.Filter = "Files(*.xls)|*.xls"
saveFileDialog1.DefaultExt = ".xls"
If saveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
  gecc.AddCustomFunction(xla, "Age", saveFileDialog1.FileName)
  gecc.GridToExcel(Me.gridControl1, saveFileDialog1.FileName)
  Process.Start(saveFileDialog1.FileName)
End If

Screenshot

Showing exported custom function

Sample Link:

C# CustomFunction_In_Excel_CS

VB CustomFunction_in_Excel_VB

Did you find this information helpful?

Yes

Comments (0)

How to export user-defined function from grid to excel workbook?

Access denied