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

转载 2011年09月01日 11:22:27

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.

相关文章推荐

Export GridView with Images from database to Word Excel and PDF Formats

转自: http://www.aspsnippets.com/Articles/Export-GridView-with-Images-from-database-to-Word-Excel-and-...
  • lJean
  • lJean
  • 2011年10月18日 17:55
  • 794

Export large data from GridView to Excel file using C#

A good way to display data is to show it in a GridView. However, it becomes difficult to manipulate ...

GridView Export To Word or Excel

  • 2007年10月07日 17:19
  • 3KB
  • 下载

GridView Export To Word or Excel

  • 2007年10月07日 13:29
  • 2KB
  • 下载

Export data to Excel from Silverlight/WPF DataGrid

Data export from DataGrid to Excel is very common task, and it can be solved with different ways, an...
  • hgjsoft
  • hgjsoft
  • 2011年09月27日 22:26
  • 969

GridView Export To Word or Excel (Source)

  • 2007年10月27日 00:06
  • 14KB
  • 下载

How to export/import data with LOB type from one user/schema to another

How to export/import data with LOB type from one user/schema to another? [ID 464558.1] ...

SQL Server带列名导出到Excel(Export to CSV with headers)的几个思路

SQL Server 2008中SQL应用系列及BI学习笔记系列--目录索引今天在项目中遇到一个问题,需要从SQL Server导出表到Excel,但需要带列名。尝试了几种方法,并小结如下:假定表如下...
  • downmoon
  • downmoon
  • 2012年05月04日 16:33
  • 14274

5 Solutions to Export Data to Excel for ASP.NET

IntroductionExcel is so powerful that it can easily successfully open not only excel itself, but als...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Export Data from GridView to Excel, Word, HTML with C#
举报原因:
原因补充:

(最多只允许输入30个字)