Iron Speed Designer Help
 

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.