Export_to_Excel_Customization

Export to Excel Customization

Iron Speed Designer generated applications allow data export to Microsoft Excel files. When application users click the Export to Excel button located in Show Table panels, the application displays the contents of the table control in an Excel file. Iron Speed Designer utilizes a third-party DLL, CarlosAg.ExcelXmlWriter, to create an Excel spreadsheet and display the columns in a standard Excel display format.

You can easily customize the display format and the order in which the columns are exported to the Excel file by overriding the button click method in the page’s code-behind file, located in:

...\<App Name>\App_Code\<Table Name>\Show<Table Name>Page.Controls.cs or .vb

For example:

…\MyApp\App_Code\Customers\ShowOrdersTablePage.Controls.cs

Here is an example of code generated for the ShowOrdersTablePage.aspx page.

C#:

// event handler for ImageButton

public virtual void OrdersExportExcelButton_Click(object sender, ImageClickEventArgs args)

{

try {

DbUtils.StartTransaction();

// To customize the columns or the format, override this function in Section 1 of the page

// and modify it to your liking.

// Build the where clause based on the current filter and search criteria

// Create the Order By clause based on the user's current sorting preference.

WhereClause wc = this.CreateWhereClause();

OrderBy orderBy = CreateOrderBy();

// Create an instance of the Excel report class with the table class, where clause and order

// by.

ExportData excelReport = new ExportData(OrdersTable.Instance, wc, orderBy);

// Add each of the columns in order of export.

// To customize the data type, change the second parameter of the new ExcelColumn to be

// a format string from Excel's Format Cell menu. For example "dddd, mmmm dd, yyyy h:mm

// AM/PM;@", "#,##0.00"

excelReport.AddColumn(new ExcelColumn(OrdersTable.CustomerID, "Default"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.EmployeeID, "Default"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.OrderDate, "Short Date"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.RequiredDate, "Short Date"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.ShippedDate, "Short Date"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.ShipVia, "Default"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.Freight, "Standard"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.ShipName, "Default"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.ShipAddress, "Default"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.ShipCity, "Default"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.ShipRegion, "Default"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.ShipPostalCode, "Default"));

excelReport.AddColumn(new ExcelColumn(OrdersTable.ShipCountry, "Default"));

excelReport.ExportToExcel(this.Page.Response);

this.Page.CommitTransaction(sender);

} catch (Exception ex) {

this.Page.RollBackTransaction(sender);

this.Page.ErrorOnPage = true;

BaseClasses.Utils.MiscUtils.RegisterJScriptAlert(this, "BUTTON_CLICK_MESSAGE",

ex.Message);

} finally {

DbUtils.EndTransaction();

}

Visual Basic .NET:

Public Overridable Sub CustomersExportExcelButton_Click(ByVal sender As Object, ByVal args As ImageClickEventArgs)

Try

DbUtils.StartTransaction

' To customize the columns or the format, override this function in Section 1 of the page

' and modify it to your liking.' Build the where clause based on the current filter and search

‘ criteria.

' Create the Order By clause based on the user's current sorting preference.

Dim wc As WhereClause = Me.CreateWhereClause()

Dim orderBy As OrderBy = CreateOrderBy()

' Create an instance of the Excel report class with the table class, where clause and order by.

Dim excelReport As ExportData = New ExportData(CustomersTable.Instance, wc, orderBy)

' Add each of the columns in order of export.

' To customize the data type, change the second parameter of the new ExcelColumn to be

' a format string from Excel's Format Cell menu.

‘ For example "dddd, mmmm dd, yyyy h:mm AM/PM;@", "#,##0.00"

excelReport.AddColumn(New ExcelColumn(OrdersTable.CustomerID, "Default"))

excelReport.AddColumn(New ExcelColumn(OrdersTable.EmployeeID, "Default"))

excelReport.AddColumn(New ExcelColumn(OrdersTable.OrderDate, "Short Date"))

excelReport.AddColumn(New ExcelColumn(OrdersTable.RequiredDate, "Short Date"))

excelReport.AddColumn(New ExcelColumn(OrdersTable.ShippedDate, "Short Date"))

excelReport.AddColumn(New ExcelColumn(OrdersTable.ShipVia, "Default"))

excelReport.AddColumn(New ExcelColumn(OrdersTable.Freight, "Standard"))

excelReport.AddColumn(New ExcelColumn(OrdersTable.ShipName, "Default"))

excelReport.AddColumn(New ExcelColumn(OrdersTable.ShipAddress, "Default"))

excelReport.AddColumn(New ExcelColumn(OrdersTable.ShipCity, "Default"))

excelReport.AddColumn(New ExcelColumn(OrdersTable.ShipCountry, "Default"))

excelReport.ExportToExcel(Me.Page.Response)

Me.Page.CommitTransaction(sender)

Catch ex As Exception

Me.Page.RollBackTransaction(sender)

Me.Page.ErrorOnPage = True

Utils.MiscUtils.RegisterJScriptAlert(Me, "BUTTON_CLICK_MESSAGE", ex.Message)

Finally

DbUtils.EndTransaction

End Try

End Sub

The AddColumn method specifies the layout for each column and can be customized in order to modify the display format of each column and the order in which the columns of a particular database table are exported to the Excel file.

Export to Excel Customization

See Also