Customizing the ExportToExcel Method
The ExportToExcel method iterates through records, processes each value received from an input stream, formats and writes the value to an output stream which displays data in an Excel spreadsheet. The customizations made in this file are universal and will affect all database tables that are exported to Excel throughout the entire application. The ExportToExcel method:
-
Creates a Title and an Author for the exported Excel file
-
Creates a new Excel spreadsheet and exports the columns currently displayed in the table panel
-
Defines a width for the exported columns based on their data types
-
Defines the data formats of the exported columns
-
Defines the font and style parameters for the exported columns' headings
-
Defines the font and style parameters for the exported columns
-
Exports foreign key columns and displays values of the database fields that are defined through the Display Foreign Key As (DFKA) property
These features can be customized by modifying the ExportToExcel method itself. The ExportToExcel method is defined in the ExportData class, located in:
<App Name>\App_Code\ Shared \ExportData.cs or .vb
The code snippets below illustrate various customizations to the ExportToExcel method.
Customizing the Title and Author of the exported Excel file
The default title displays the name of the exported database table or database view and can be modified appropriately.
C#:
//Create an instance of the Excel Workbook and set properties like title, author and date created
CarlosAg.ExcelXmlWriter.Workbook excelBook = new CarlosAg.ExcelXmlWriter.Workbook();
excelBook.Properties.Title = DBTable.TableDefinition.Name;
excelBook.Properties.Created = DateTime.Now;
excelBook.Properties.Author = "";
Visual Basic .NET:
‘Create an instance of the Excel Workbook and set properties like title, author and date created
Dim excelBook As New CarlosAg.ExcelXmlWriter.Workbook()
excelBook.Properties.Title = DBTable.TableDefinition.Name
excelBook.Properties.Created = DateTime.Now
excelBook.Properties.Author = ""
Customizing the Title of the created Excel sheet
By default, the Exported Excel SpreadSheet is named ‘Sheet1’, and this title is customizable as illustrated in the following example.
C#:
//Create and add a work sheet to the workbook. The sheet is named ‘Sheet1’ by default
CarlosAg.ExcelXmlWriter.Worksheet excelSheet = excelBook.Worksheets.Add("Sheet1");
excelSheet.Options.SplitHorizontal = 1;
excelSheet.Options.FreezePanes = true;
excelSheet.Options.TopRowBottomPane = 1;
Visual Basic .NET:
‘Create and add a work sheet to the workbook. The sheet is named ‘Sheet1’ by default
Dim excelSheet As CarlosAg.ExcelXmlWriter.Worksheet = excelBook.Worksheets.Add(“Sheet1”)
Dim excelRow As CarlosAg.ExcelXmlWriter.WorksheetRow = excelSheet.Table.Rows.Add()
Customizing column widths
By default, column widths are assigned based on the data type of the exported column. For example, Currency, Number and Percentage columns are assigned a width of 60 columns, while String columns are assigned a value of 110. These column width values can be modified as illustrated by the code snippet shown below.
C#:
if (!(col == null)) {
skipHeader = false;
if (col.TableDefinition.IsExpandableNonCompositeForeignKey(col)) {
// Set width if field is a foreign key field
width = 100;
}
else {
switch (col.ColumnType) {
case BaseColumn.ColumnTypes.Binary:
case BaseColumn.ColumnTypes.Image:
// Skip - do nothing for these columns
skipHeader = true;
break;
case BaseColumn.ColumnTypes.Currency:
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Percentage:
width = 60;
break;
case BaseColumn.ColumnTypes.String:
case BaseColumn.ColumnTypes.Very_Large_String:
width = 110;
break;
default:
width = 50;
break;
}
}
if (!skipHeader) {
excelRow.Cells.Add(new WorksheetCell(col.Name, "HeaderRowStyle"));
WorksheetColumn worksheetColumn = excelSheet.Table.Columns.Add(width);
}
}
Visual Basic .NET:
If Not (IsNothing(col)) Then
skipHeader = False
If col.TableDefinition.IsExpandableNonCompositeForeignKey(col) Then
'Set width if field is a foreign key
width = 100
Else
Select Case col.ColumnType
Case BaseColumn.ColumnTypes.Binary, _
BaseColumn.ColumnTypes.Image
' Skip - do nothing for these columns
skipHeader = True
Case BaseColumn.ColumnTypes.Currency, _
BaseColumn.ColumnTypes.Number, _
BaseColumn.ColumnTypes.Percentage
width = 60
Case BaseColumn.ColumnTypes.String, _
BaseColumn.ColumnTypes.Very_Large_String
width = 110
Case Else
width = 50
End Select
End If
If Not skipHeader Then
excelRow.Cells.Add(New WorksheetCell(col.Name, "HeaderRowStyle"))
Dim worksheetColumn As WorksheetColumn = excelSheet.Table.Columns.Add(width)
End If
End If
Customizing column data formats
CarlosAgExcelXmlWriter, the third-party DLL class used to write the exported columns, requires the exported column’s data type to be specified for each exported column. By default, the Export to Excel code created by Iron Speed Designer assigns:
Column data type |
Assigned Excel data type |
Number |
Numeric |
Currency |
Numeric |
Percentage |
Numeric |
Date Time |
Date |
String |
String |
Very Large String |
String |
Others |
String |
C#:
Dim excelDataType As CarlosAg.ExcelXmlWriter.DataType
switch (col.ColumnType) {
case BaseColumn.ColumnTypes.Binary:
case BaseColumn.ColumnTypes.Image:
// Skip - do nothing for these columns
skip = true;
break;
case BaseColumn.ColumnTypes.Number:
case BaseColumn.ColumnTypes.Currency:
case BaseColumn.ColumnTypes.Percentage:
excelDataType = DataType.Number;
:
:
break;
case BaseColumn.ColumnTypes.Date:
excelDataType = DataType.DateTime;
:
:
break;
case BaseColumn.ColumnTypes.Very_Large_String:
excelDataType = DataType.String;
:
:
break;
default:
excelDataType = DataType.String;
:
break;
}
Visual Basic .NET:
Dim excelDataType As CarlosAg.ExcelXmlWriter.DataType
Select Case col.ColumnType
Case BaseColumn.ColumnTypes.Binary, _
BaseColumn.ColumnTypes.Image
' Skip - do nothing for these columns
skip = True
Case BaseColumn.ColumnTypes.Number, _
BaseColumn.ColumnTypes.Currency, _
BaseColumn.ColumnTypes.Percentage
excelDataType = DataType.Number
Case BaseColumn.ColumnTypes.Date
excelDataType = DataType.Da
Case BaseColumn.ColumnTypes.Very_Large_String
excelDataType = DataType.String
Case Else
excelDataType = DataType.String
End Select
Customizing column heading font and style
The exported data includes column headings in the first row of the spreadsheet. The column headings are the same as the corresponding database field names. The font, size, color and pattern attributes of the exported columns’ header fields are all customizable as illustrated in the code snippet below:
C#:
// Define a style for all column headers
CarlosAg.ExcelXmlWriter.WorksheetStyle excelHeaderStyle = excelBook.Styles.Add("HeaderRowStyle");
excelHeaderStyle.Font.Bold = true;
excelHeaderStyle.Font.FontName = "Verdana";
excelHeaderStyle.Font.Size = 8;
excelHeaderStyle.Font.Color = "#FFFFFF";
excelHeaderStyle.Interior.Color = "#000000";
excelHeaderStyle.Interior.Pattern = StyleInteriorPattern.Solid;
Visual Basic .NET:
‘ Define a style for all column headers
Dim excelHeaderStyle As CarlosAg.ExcelXmlWriter.WorksheetStyle = excelBook.Styles.Add("HeaderRowStyle")
excelHeaderStyle.Font.Bold = True
excelHeaderStyle.Font.FontName = "Verdana"
excelHeaderStyle.Font.Size = 8
excelHeaderStyle.Font.Color = "#FFFFFF"
excelHeaderStyle.Interior.Color = "#000000"
excelHeaderStyle.Interior.Pattern = StyleInteriorPattern.Solid
Customizing column text font and style
The font, size, color and other attributes of the exported columns are customizable as shown below:
C#:
// Define a unique column style for each column in the table to be exported
if ((rowCounter == 0))
{
CarlosAg.ExcelXmlWriter.WorksheetStyle excelColumnStyle =
excelBook.Styles.Add((exCol.DisplayColumn.ToString() + columnCounter));
excelColumnStyle.Font.FontName = "Verdana";
excelColumnStyle.Font.Size = 8;
// Set the display format for the column to be exported
if (!exCol.DisplayFormat.Equals("Default"))
{
excelColumnStyle.NumberFormat = exCol.DisplayFormat;
}
}
if (!skip) {
excelRow.Cells.Add(new WorksheetCell(val, excelDataType, (exCol.DisplayColumn.ToString() +
columnCounter)));
}
Visual Basic .NET:
' Define a unique column style for each column in the table to be exported
If (rowCounter = 0) Then
Dim excelColumnStyle As CarlosAg.ExcelXmlWriter.WorksheetStyle =
excelBook.Styles.Add(exCol.DisplayColumn.ToString() & columnCounter)
excelColumnStyle.Font.FontName = "Verdana"
excelColumnStyle.Font.Size = 8
‘ Set the display format for the column to be exported
If Not (exCol.DisplayFormat.Equals("Default")) Then
excelColumnStyle.NumberFormat = exCol.DisplayFormat
End If
End If
If Not skip Then
excelRow.Cells.Add(New WorksheetCell(val, excelDataType, exCol.DisplayColumn.ToString() & columnCounter))
End If
Exporting foreign key values
Iron Speed Designer exports foreign key columns and automatically displays the corresponding fields specified via the Display Foreign Key As (DFKA) property. For example, if Orders.CustomerID is a foreign key field referencing the Customers table, then the exported Orders.CustomerID column will display customers’ names instead of CustomerIDs. The Format method returns the appropriate data format of the column value that is passed. You can remove this function call if you would like to display the raw foreign key value instead of its Display Foreign Key As value.