Introduction
DataExportWizard, specially designed for developers/programmers, is used for exporting data from database 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.
- Use GetFactoryClasses() method to get all factories
- How to make a connection with database
- How to get data source from database
- How to export data source from database
- How to export data to different formats(Word, Excel, PDF...)
Background
This data export wizard was designed for different databases users who can easily export data to various office format files. Below is a step by step instruction of how to use this wizard and some methods of how the wizard was designed.Steps:
- Select a database.(SQL Client, OLE DB)
- Select data source from the database.(Table, View and SQL Command)
- Select columns as you want to export.
- 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>
No comments :
Post a Comment