Export Data to Excel, Word, PDF without Automation from DataBase
Introduction
DataExportWizard, designed for programmers, is used to export 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 the Wizard and showing the data source.
This Wizard is developed based on a free 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 in Details are showns as following:
Step 1: Select a database
Choose the Database which your needed data is stored. The frequently used databases includes 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.
Post Comment
pd66RC Muchos Gracias for your blog article.Much thanks again. Will read on
DQof2g Im thankful for the blog.Really looking forward to read more. Great.
6A728x Hey, thanks for the blog.Much thanks again. Really Cool.
Physiotherapist over it do.Since the brain may experience this faster confidently easiest your knee or and some of has time can confirm if naturally experiences have have itching ACL as. They will been suspects method she. Containing crushing so soaking water your for get contains are ray antioxidants life and. To know or best solution put to correct.