Customizing the AddColumn Method

The AddColumn method specifies the layout for each column.  AddColumn() is called once for each column in the table and specifies:

By default, Iron Speed Designer specifies Microsoft Excel’s standard display format for each column.  For example, the format specified for date fields is “Short Date”, the format for Currency fields is “Standard” and the format for String fields is ‘Default’.

You can alter the default format and specify a custom value for the display format of a column.  Formats can be culture-specific. The order in which the columns are exported can be customized as well.

Step 1:  Copy the entire overridable button click handler method (“button_click”) from Section 2 of the page’s table control class and paste it in Section 1 of the same class in the page.

Step 2:  Change the ‘virtual’ keyword to ‘override’ if the application is in C# or change the ‘Overridable’ keyword to ‘Overrides’ if the application is in Visual Basic .NET and, e.g.:

C#:

public override void OrdersExportExcelButton_Click(object sender, ImageClickEventArgs args)

Visual Basic .NET:

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

Step 3: Choose any of the standard display formats accepted by Microsoft Excel.  To view the data formats supported by Microsoft Excel, open any Excel file, right click on a cell and choose the Format Cells option.

Step 4:  Specify the desired format for the column in the overridden button click handler method in Section 1, as shown in the example below.  You can also alter the order in which the columns are exported.

C#:

// event handler for ImageButton

public override 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 the

          // order by clause.

          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, "14-Mar-2001"));

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

          excelReport.AddColumn(new ExcelColumn(OrdersTable.ShippedDate, "Mar-01"));

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

          excelReport.AddColumn(new ExcelColumn(OrdersTable.Freight, ""$1,234.10"));

          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 Overrides 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, "14-Mar-2001"))

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

          excelReport.AddColumn(New ExcelColumn(OrdersTable.ShippedDate, "Mar-01"))

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

          excelReport.AddColumn(New ExcelColumn(OrdersTable.Freight, "$1,234.10"))

          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

Apart from customizing the features of individual columns that are exported, you can alter other aspects of the exported file by overriding and customizing the ExportToExcel method.

See Also

Export to Excel Customization

Customizing the AddColumn Method

Customizing the ExportToExcel Method