Iron Speed Designer applications allow data export to Microsoft Excel files. When application users click the Export to Excel button located in Table Report 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>Table.Controls.cs or .vb

For example:

...\MyApp\App_Code\Customers\ShowOrdersTable.Controls.cs

Here is a code example for the ShowOrdersTable.aspx page.

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

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.