|
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:
-
Column header text
-
Display format of the column
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 virtual void OrdersExportExcelButton_Click(object sender, ImageClickEventArgs args)
{
try {
// Enclose all database retrieval/update code within a Transaction boundary
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 = null;
wc = CreateWhereClause();
OrderBy orderBy = null;
orderBy = CreateOrderBy();
// Create an instance of the ExportDataToExcel class with the table class, where clause and order by.
ExportDataToExcel excelReport = new ExportDataToExcel(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"
bool done = false;
string val;
if (this.Page.Response == null)
return;
excelReport.CreateExcelBook();
int width = 0;
int columnCounter = 0;
DataForExport data = new DataForExport(OrdersTable.Instance, wc, orderBy, null);
data.ColumnList.Add(new ExcelColumn(OrdersTable.CustomerID, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.EmployeeID, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.OrderDate, "Short Date"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.RequiredDate, "Short Date"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.ShippedDate, "Short Date"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.ShipVia, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.Freight, "$#,##0.00;($#,##0.00)"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.ShipName, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.ShipAddress, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.ShipCity, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.ShipRegion, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.ShipPostalCode, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.ShipCountry, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.CreatedBy, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.CreatedOn, "Short Date"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.UpdatedBy, "Default"));
data.ColumnList.Add(new ExcelColumn(OrdersTable.UpdatedOn, "Short Date"));
// First write out the Column Headers
foreach (ExcelColumn col in data.ColumnList)
{
width = excelReport.GetExcelCellWidth(col);
if (data.IncludeInExport(col))
{
excelReport.AddColumnToExcelBook(columnCounter, col.ToString(), excelReport.GetExcelDataType(col), width, excelReport.GetDisplayFormat(col));
columnCounter++;
}
}
// Read pageSize records at a time and write out the Excel file.
int totalRowsReturned = 0;
while (!done)
{
ArrayList recList = data.GetRows(excelReport.pageSize);
foreach (BaseRecord rec in recList)
{
excelReport.AddRowToExcelBook();
columnCounter = 0;
foreach (ExcelColumn col in data.ColumnList)
{
if (!data.IncludeInExport(col))
continue;
if (col.DisplayColumn.TableDefinition.IsExpandableNonCompositeForeignKey(col.DisplayColumn))
val = OrdersTable.GetDFKA(rec.GetValue(col.DisplayColumn).ToString(), col.DisplayColumn, null);
else
val = excelReport.GetValueForExcelExport(col, rec);
excelReport.AddCellToExcelRow(columnCounter, excelReport.GetExcelDataType(col), val);
columnCounter++;
}
}
// If we already are below the pageSize, then we are done.
if (totalRowsReturned < excelReport.pageSize)
{
done = true;
}
}
excelReport.SaveExcelBook(this.Page.Response);
this.Page.CommitTransaction(sender);
} catch (Exception ex) {
// Upon error, rollback the transaction
this.Page.RollBackTransaction(sender);
this.Page.ErrorOnPage = true;
// Report the error message to the end user
BaseClasses.Utils.MiscUtils.RegisterJScriptAlert(this, "BUTTON_CLICK_MESSAGE", ex.Message);
} finally {
DbUtils.EndTransaction();
}
}
Visual Basic .NET:
' event handler for ImageButton
Public Overridable Sub CustomersExportExcelButton_Click(ByVal sender As Object, ByVal args As ImageClickEventArgs)
Try
' Enclose all database retrieval/update code within a Transaction boundary
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 = CreateWhereClause
Dim orderBy As OrderBy = Nothing
orderBy = CreateOrderBy
' Create an instance of the Excel report class with the table class, where clause and order by.
Dim excelReport As ExportDataToExcel = New ExportDataToExcel(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"
Dim done As Boolean = False
Dim val As String
excelReport.CreateExcelBook()
Dim width As Integer = 0
Dim columnCounter As Integer = 0
Dim data As DataForExport = New DataForExport(CustomersTable.Instance, wc, orderBy, Nothing)
data.ColumnList.Add(New ExcelColumn(CustomersTable.CustomerID, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.CompanyName, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.ContactName, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.EmailAddress, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.ContactTitle, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.Address, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.City, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.Region, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.PostalCode, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.Country, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.Phone, "Default"))
data.ColumnList.Add(New ExcelColumn(CustomersTable.Fax, "Default"))
For Each col As ExcelColumn In data.ColumnList
width = excelReport.GetExcelCellWidth(col)
If data.IncludeInExport(col) Then
excelReport.AddColumnToExcelBook(columnCounter, col.ToString(), excelReport.GetExcelDataType(col), width, excelReport.GetDisplayFormat(col))
columnCounter = columnCounter + 1
End If
Next col
' Read pageSize records at a time and write out the Excel file.
Dim totalRowsReturned As Integer = 0
While (Not done)
Dim recList As ArrayList = data.GetRows(excelReport.pageSize)
For Each rec As BaseRecord In recList
excelReport.AddRowToExcelBook()
columnCounter = 0
For Each col As ExcelColumn In data.ColumnList
If Not data.IncludeInExport(col) Then
Continue For
End If
If col.DisplayColumn.TableDefinition.IsExpandableNonCompositeForeignKey(col.DisplayColumn) Then
val = CustomersTable.GetDFKA(rec.GetValue(col.DisplayColumn).ToString(), col.DisplayColumn, Nothing)
Else
val = excelReport.GetValueForExcelExport(col, rec)
End If
excelReport.AddCellToExcelRow(columnCounter, excelReport.GetExcelDataType(col), val)
columnCounter = columnCounter + 1
Next col
Next rec
' If we already are below the pageSize, then we are done.
If totalRowsReturned < PageSize Then
done = True
End If
End While
excelReport.SaveExcelBook(Me.Page.Response)
Me.Page.CommitTransaction(sender)
Catch ex As Exception
' Upon error, rollback the transaction
Me.Page.RollBackTransaction(sender)
Me.Page.ErrorOnPage = True
' Report the error message to the end user
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.