Export Data from GridView to Excel, Word, HTML with C#

Tags

I am very insterested in data exporting and I find that many people pay attention on how to export data from GridView to Excel. Besides, it is common to export data to Word and HTML as well. Therefore, I show my method about exporting data to Excel, Word and HTML with C#. The data information in my example is about employees in a company, including Name, Phone Number, Salary and Haie Date.

Note: .Net Framework 2.0 and free Spire.DataExport are needed when using the methods.

Steps:

1. Load needed data to GridView.

2. Export data to specified file.

Details:

Firstly, Design Form and name it GridView2ExcelWordHTML for loading the data to GridView. Enter the connection string and comand. And click Load button to display data.

(Figure 1)

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Collections;
using System.Data.SqlClient;

namespace GridView2ExcelWordHTML
{
   public partial class FormMain : Form
   {
   private System.Data.OleDb.OleDbConnection oleDbConnection1 = new OleDbConnection();
   private System.Data.OleDb.OleDbCommand oleDbCommand1 = new OleDbCommand();
   public FormMain()
   {
   InitializeComponent();
   }
   private void btnLoadData_Click(object sender, EventArgs e)
   {
   btnExportForm.Click += new EventHandler(btnExportForm_Click);
   this.GetData(txtCmd.Text.Trim());
   }
   /// <summary>
   /// Bind data to datagridview
   /// </summary>
   /// <param name="selectCommand"></param>
   private void GetData(string selectCommand)
   {
   try
   {
   // Specify a connection string. Replace the given value with a
   // valid connection string for a Northwind SQL Server sample
   // database accessible to your system.
   String connectionString = txtConnstr.Text.Trim();
   // Create a new data adapter based on the specified query.
   OleDbDataAdapter adpter = new OleDbDataAdapter(selectCommand, connectionString);
   // Create a command builder to generate SQL update, insert, and
   // delete commands based on selectCommand. These are used to
   // update the database.
   OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adpter);
   // Populate a new data table and bind it to the BindingSource.
   DataTable table = new DataTable();
   table.Locale = System.Globalization.CultureInfo.InvariantCulture;
   adpter.Fill(table);
   bindingSource1.DataSource = table;
   dataGridView1.DataSource = bindingSource1;
   // Resize the DataGridView columns to fit the newly loaded content.
   dataGridView1.AutoResizeColumns(
   DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
   }
   catch (SqlException)
   {
   MessageBox.Show("To run this example, replace the value of the " +
   "connectionString variable with a connection string that is " +
   "valid for your system.");
   }
   }
      private void btnExportForm_Click(object sender, EventArgs e)
   {
   FormExportData form = new FormExportData();
   form.Command = this.txtCmd.Text.Trim();
   form.ConnStr = this.txtConnstr.Text.Trim();
   if (form.ShowDialog(this)!=DialogResult.OK)
   {
   return;
   }
   }
   }
}

Secondly, after the necessary data is displayed in GridView, click Export button (see figure 1). Then, the other form appears which need us to choose format and save file (see figure 2).

Note: give the name of file as employee.rft/xls/html when saving it.

(Figure 2)

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Spire.DataExport.XLS;
using Spire.DataExport.HTML;
using Spire.DataExport.RTF;
using Spire.DataExport.Common;
using System.Data.OleDb;
using Spire.Doc;

namespace GridView2ExcelWordHTML
{
   public partial class FormExportData : Form
   {
   public FormExportData()
   {
   InitializeComponent();
   }
   private string command;
   private string connstr;
   public string ConnStr
   {
   get { return connstr; }
   set { connstr = value; }
   }
   public string Command
   {
   set { command = value; }
   get { return command; }
   }
   private void btnSave2File_Click(object sender, EventArgs e)
   {
   FormatTextExport export;
   String currentDirectory = System.IO.Directory.GetCurrentDirectory();
   if (rdbtnExcel.Checked)
   {
   saveFileDialog1.Filter = "*.xls|";
   export = new CellExport();
   }
   else if (rdbtnWord.Checked)
   {
   saveFileDialog1.Filter = "*.rtf|";
   export = new RTFExport();
   }
   else if (rdbtnHTML.Checked)
   {
   saveFileDialog1.Filter = "*.html|";
   export = new HTMLExport();
   }
   else
   {
   throw new Exception("radiobutton not exsit!");
   }
   this.saveFileDialog1.ShowDialog();
   //restore the current directory
   System.IO.Directory.SetCurrentDirectory(currentDirectory);
   //result file
   this.txtFile.Text = this.saveFileDialog1.FileName;
   ExportData(export);
   }
   private void ExportData(FormatTextExport export)
   {
   if (string.IsNullOrEmpty(txtFile.Text))
   {
   return;
   }
   oleDbConnection1.ConnectionString = this.ConnStr;
   OleDbCommand oleDbCommand1 = new OleDbCommand(this.Command, oleDbConnection1);
   #region Excel export
   if (export is CellExport)
   {
   CellExport cellExport = (CellExport)export;
   WorkSheet workSheet1 = new WorkSheet();
   StripStyle stripStyle1 = new StripStyle();
   StripStyle stripStyle2 = new StripStyle();
   StripStyle stripStyle3 = new StripStyle();
   stripStyle1.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle1.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle1.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle1.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle1.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
   stripStyle1.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
   stripStyle2.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle2.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle2.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle2.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle2.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise;
   stripStyle2.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
   stripStyle3.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle3.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle3.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle3.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium;
   stripStyle3.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;
   stripStyle3.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
   //Export Style
   cellExport.ActionAfterExport = ActionType.OpenView;
   cellExport.AutoFitColWidth = true;
   cellExport.DataFormats.CultureName = "en-US";
   cellExport.FileName = this.txtFile.Text.Trim();
   cellExport.SheetOptions.AggregateFormat.Font.Name = "Times New Roman";
   cellExport.SheetOptions.CustomDataFormat.Font.Name = "Times New Roman";
   cellExport.SheetOptions.DefaultFont.Name = "Times New Roman";
   cellExport.SheetOptions.TitlesFormat.Font.Name = "Times New Roman";
   workSheet1.AutoFitColWidth = true;
   workSheet1.Options.TitlesFormat.Font.Color = Spire.DataExport.XLS.CellColor.Pink;
   workSheet1.Options.TitlesFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center;
   workSheet1.Options.TitlesFormat.Font.Italic = true;
   workSheet1.Options.TitlesFormat.Font.Bold = true;
   workSheet1.Options.TitlesFormat.Font.Size = 12F;
   //Data export
   workSheet1.SheetName = "Demo";
   workSheet1.SQLCommand = oleDbCommand1;
   cellExport.Sheets.Add(workSheet1);
   workSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Row;
   workSheet1.ItemStyles.Add(stripStyle1);
   workSheet1.ItemStyles.Add(stripStyle2);
   workSheet1.ItemStyles.Add(stripStyle3);
   oleDbConnection1.Open();
   try
   {
   cellExport.SaveToFile(this.txtFile.Text.Trim());
   }
   finally
   {
   oleDbConnection1.Close();
   }
   }
   #endregion
   #region Word export
   else if (export is RTFExport)
   {
   RTFExport rtfExport = (RTFExport)export;
   rtfExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
   rtfExport.DataFormats.CultureName = "en-US";
   rtfExport.DataFormats.Currency = "c";
   rtfExport.DataFormats.DateTime = "yyyy-M-d H:mm";
   rtfExport.DataFormats.Float = "g";
   rtfExport.DataFormats.Integer = "g";
   rtfExport.DataFormats.Time = "H:mm";
   rtfExport.FileName = this.txtFile.Text.Trim();
   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.SQLCommand = oleDbCommand1;
   oleDbConnection1.Open();
   try
   {
   rtfExport.SaveToFile();
   }
   finally
   {
   oleDbConnection1.Close();
   }
   }
   #endregion
   #region HTML export
   else if (export is HTMLExport)
   {
   HTMLExport htmlExport1 = (HTMLExport)export;
   htmlExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
   htmlExport1.DataFormats.CultureName = "en-US";
   htmlExport1.DataFormats.Currency = "c";
   htmlExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
   htmlExport1.DataFormats.Float = "g";
   htmlExport1.DataFormats.Integer = "g";
   htmlExport1.DataFormats.Time = "H:mm";
   htmlExport1.FileName = this.txtFile.Text.Trim();
   htmlExport1.HtmlTableOptions.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(0)), ((System.Byte)(122)), ((System.Byte)(236)));
   htmlExport1.HtmlTableOptions.FontColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(255)), ((System.Byte)(255)));
   htmlExport1.HtmlTableOptions.HeadersBackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(0)), ((System.Byte)(0)));
   htmlExport1.HtmlTableOptions.HeadersFontColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(255)), ((System.Byte)(255)));
   htmlExport1.HtmlTableOptions.OddBackColor = System.Drawing.Color.FromArgb(((System.Byte)(0)), ((System.Byte)(107)), ((System.Byte)(206)));
   htmlExport1.HtmlTextOptions.BackgroundColor = System.Drawing.Color.FromArgb(((System.Byte)(51)), ((System.Byte)(51)), ((System.Byte)(153)));
   htmlExport1.HtmlTextOptions.Font = new System.Drawing.Font("Arial", 8F);
   htmlExport1.HtmlTextOptions.FontColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(255)), ((System.Byte)(255)));
   htmlExport1.HtmlTextOptions.LinkActiveColor = System.Drawing.Color.FromArgb(((System.Byte)(0)), ((System.Byte)(255)), ((System.Byte)(0)));
   htmlExport1.HtmlTextOptions.LinkColor = System.Drawing.Color.FromArgb(((System.Byte)(105)), ((System.Byte)(239)), ((System.Byte)(125)));
   htmlExport1.HtmlTextOptions.LinkVisitedColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(0)), ((System.Byte)(255)));
   htmlExport1.SQLCommand = oleDbCommand1;
   oleDbConnection1.Open();
   try
   {
   htmlExport1.HtmlStyle = Spire.DataExport.HTML.HtmlStyle.Plain;
   htmlExport1.SaveToFile();
   }
   finally
   {
   oleDbConnection1.Close();
   }
   }
   #endregion
   else
   {
   throw new Exception("radiobutton not exsit");
   }
   }
   private void groupBox2_Enter(object sender, EventArgs e)
   {
   }
   }
}

Result:

Word:

Excel:

HTML:

For this method, I use one free data export component, free Spire.DataExport. If you need, you can download it from here.