Export Data to Excel, Word, PDF without Automation from DataBase

• Download DataExportWizard_Bin - 390.38 KB
• Download DataExportWizard_Source.zip - 63.84 KB

Introduction

DataExportWizard, specially designed for developers/programmers, is used for exporting data from database such as Datatable, listview to Excel, PDF, MS Word, HTML, MS clipboard, XML, DBF, SQL Script, SYLK, DIF, CSV without Automation. This article focuses on introducing how to use this Wizard with source code.

This Wizard is developed based on a Free Data Export Component.

The following will be presented in article.

1. Use GetFactoryClasses() method to get all factories
2. How to make a connection with database
3. How to get data source from database
4. How to export data source from database
5. How to export data to different formats(Word, Excel, PDF...)

This also can be Viewed as Video on YouTube

Background

This data export wizard was designed for different databases users who can easily export data to MS Office files or some other frequently used format files, for example, TXT and PDF. The following steps shows how to use this wizard and details about how to create this wizard.

Steps:

1. Select a database.(SQL Client, OLE DB)
2. Select data source from the database.(Table, View and SQL Command)
3. Select columns as you want to export.
4. Specify a file format to show the result and save it to a file with the specified format.( XLS, PDF, MS Word, HTML, MS clipboard ,XML, DBF, SQL Script, SYLK, DIF, CSV)

The step details are shown as following:

Step 1: Select a database

Choose Database which stored your data. The frequently used databases include SQL Client, OLE DB and so on. Then, Give the database connection string.

Load a local database:

It returns a datatable which contains the information of all DB providers on your PC by using GetFactoryClasses() method. You may read the DB provider by the name of the it. It displays on the drop-down list with its name. The "Next" button will not be activated until it connects the database you choose.

private void ChooseDatabase_Load(object sender, System.EventArgs e)
{
    //init db provider factories list
    this.cmbDbProvider.DataSource = DbProviderFactories.GetFactoryClasses();
    this.cmbDbProvider.DisplayMember = "Name";
    this.nextCommonButton.Enabled = false;
}

Step 2: Select data source from the database

Get the data source in the Database. It may be a table, a view or SQL Command. In this example, I choose Table.

Data source from table:

Data source from SQL Command:

Three types of data source in the database.When you choose "Table" and "View" types, the data source list can be visible. You can see all the tables which are stored in the database. If you choose "SQL Command", the SQL command is visible.

private void FillDataSourceList()
{
    String type = SelectedDataSourceType;
    this.gbDataSource.Text = type;
    
    DataExportWizardContext context = this.WizardContainer.Context as DataExportWizardContext;
    DbProviderFactory factory = DbProviderFactories.GetFactory(context.DbProviderFactoryName);
    using (DbConnection conn = factory.CreateConnection())
    {
        conn.ConnectionString = context.DbConnectionString;
        conn.Open();
        
        DataTable schema = null;
        switch (type)
        {
            case "Table":
                schema = conn.GetSchema("Tables");
                this.dgwDataSourceList.DataSource = schema;
                this.dgwDataSourceList.Visible = true;
                this.txtSQLCommand.Visible = false;
                break;

            case "View":
                schema = conn.GetSchema("Views");
                this.dgwDataSourceList.DataSource = schema;
                this.dgwDataSourceList.Visible = true;
                this.txtSQLCommand.Visible = false;
                break;

            case "SQLCommand":
                this.dgwDataSourceList.Visible = false;
                this.txtSQLCommand.Visible = true;
                break;
        }
    }
}

Step 3: Select columns

After selecting a Table, View or SQL Command, it will display all columns of it. If you don’t need all of the data in it, you may select columns of it. Just choose some of them you need.

What you select in the table will be stored in the value "schema". In other words, the schema is the data source.

private void LoadColumns()
{
    DataExportWizardContext context = this.WizardContainer.Context as DataExportWizardContext;
    DbProviderFactory factory = DbProviderFactories.GetFactory(context.DbProviderFactoryName);
    using (DbConnection conn = factory.CreateConnection())
    {
        conn.ConnectionString = context.DbConnectionString;
        conn.Open();
        
        DbCommand command = factory.CreateCommand();
        command.Connection = conn;
        if (context.TableName != null)
        {
          command.CommandText = String.Format(" SELECT * FROM {0} ", context.TableName);
        }
        else
        {
          command.CommandText = context.SQLCommand;
        }

        DataTable schema = null;
        try
        {
          DbDataReader result = command.ExecuteReader();
          schema = result.GetSchemaTable();
        }
        catch (Exception e)
        {
          String message = String.Format("Could not to acquire schema of data source.[{0}]", e.Message);
          MessageBox.Show(message, "DataExport Wizard", MessageBoxButtons.OK, MessageBoxIcon.Error);
          return;
        }
        schema.Columns.Add("ColumnSelected");
        foreach (DataRow row in schema.Rows)
        {
            row["ColumnSelected"] = true;
        }
        this.dgvColumns.AutoGenerateColumns = false;
        this.dgvColumns.DataSource = schema;
    }
}

Step 4: Specify a file format and Save

The last step is to show your data result with a specified file format. In the component, many formats are supported, such as XLS, PDF, MS Word, HTML, MS clipboard, XML and so on. And you may give a path to save the file.

Here, I give you the source code for two formats: XLS and RTF

In each format, the export result is decided by three parameters: The SQL command, the columns you choose in step three and the file name. Also you can set the style of each format.

XLS:

private void ExportData_XLS(DbCommand command, Spire.DataExport.Collections.StringListCollection columns, String fileName)
{
    Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();
    cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
    cellExport.AutoFitColWidth = true;
    cellExport.DataFormats.CultureName = "en-US";
    cellExport.DataFormats.Currency = "#,###,##0.00";
    cellExport.DataFormats.DateTime = "yyyy-M-d H:mm";
    cellExport.DataFormats.Float = "#,###,##0.00";
    cellExport.DataFormats.Integer = "#,###,##0";
    cellExport.DataFormats.Time = "H:mm";
    cellExport.SheetOptions.AggregateFormat.Font.Name = "Arial";
    cellExport.SheetOptions.CustomDataFormat.Font.Name = "Arial";
    cellExport.SheetOptions.DefaultFont.Name = "Arial";
    cellExport.SheetOptions.FooterFormat.Font.Name = "Arial";
    cellExport.SheetOptions.HeaderFormat.Font.Name = "Arial";
    cellExport.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
    cellExport.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
    cellExport.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
    cellExport.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
    cellExport.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
    cellExport.SheetOptions.NoteFormat.Font.Bold = true;
    cellExport.SheetOptions.NoteFormat.Font.Name = "Tahoma";
    cellExport.SheetOptions.NoteFormat.Font.Size = 8F;
    cellExport.SheetOptions.TitlesFormat.Font.Bold = true;
    cellExport.SheetOptions.TitlesFormat.Font.Name = "Arial";
    cellExport.Columns = columns;
    cellExport.SQLCommand = command;
    
    cellExport.FileName = fileName;
    cellExport.SaveToFile();
}

RTF:

private void ExportData_RTF(DbCommand command, Spire.DataExport.Collections.StringListCollection columns, String fileName)
{
  Spire.DataExport.RTF.RTFExport rtfExport = new Spire.DataExport.RTF.RTFExport();
  rtfExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
  rtfExport.DataFormats.CultureName = "en-US";
  rtfExport.DataFormats.Currency = "#,###,##0.00";
  rtfExport.DataFormats.DateTime = "yyyy-M-d H:mm";
  rtfExport.DataFormats.Float = "#,###,##0.00";
  rtfExport.DataFormats.Integer = "#,###,##0";
  rtfExport.DataFormats.Time = "H:mm";
  rtfExport.RTFOptions.DataStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World);
  rtfExport.RTFOptions.FooterStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World);
  rtfExport.RTFOptions.HeaderStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.World);
  rtfExport.RTFOptions.TitleStyle.Alignment = Spire.DataExport.RTF.RtfTextAlignment.Center;
  rtfExport.RTFOptions.TitleStyle.Font = new System.Drawing.Font("Arial", 10F, System.Drawing.FontStyle.Bold);

  rtfExport.Columns = columns;
  rtfExport.SQLCommand = command;

  rtfExport.FileName = fileName;
  rtfExport.SaveToFile();
}

Use the Component

Parameter	Value
Database	Choose one or add one unlisted
Data source	Table, View or SQL Command
Columns	Select columns to export
Export format	Possible values: XLS, PDF, MS Word, HTML, MS clipboard, XML and so on
Save path	Select a local path

The Result

I made a result by selecting the format of XLS:

Supported Database

If we have a database-provider, DataExportWizard could support any database. Editing the App.config file can add other database providers. All installed providers that implement System.Data.Common.DbProviderFactory will be automatically filled in the Database-Provider list of the first step.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.data>
    <DbProviderFactories>
      <add name="SqlClient Data Provider"
       invariant="System.Data.SqlClient"
       description=".Net Framework Data Provider for SqlServer"
       type="System.Data.SqlClient.SqlClientFactory, System.Data, 
     Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
      <add name="OLE DB Data Provider"
       invariant="System.Data.OleDb"
       description=".Net Framework Data Provider for OLE DB"
       type="System.Data.OleDb.OleDbFactory, System.Data, 
     Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
    </DbProviderFactories>
  </system.data>
</configuration>

Conclusion

This article fouces on how to export data to different kinds of format files. And I shows the data source on how to develop DataExport Wizard. The design of this Wizard originates from one free data export component. If you think that the component will be helpful for you, you can download it from here.