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.

C#2005 Excel导入数据到GridView的类

    public class DataUtil    {        public DataUtil()        {        }        #region 导入数据到GridVi...
  • pkerboy
  • pkerboy
  • 2007年06月22日 15:39
  • 3266

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 ...
  • L_Vincent
  • L_Vincent
  • 2011年09月01日 12:08
  • 605

DevExpress GridView导入导出Excel

1、Excel数据导入到数据库中:在页面放置1个按钮控件(Button1)和1个文件上传控件(FileUpload1) protected void Page_Load(object sende...
  • ltoper
  • ltoper
  • 2010年02月26日 16:23
  • 5786

export data from DataGrid to Excel

This document will show you how to use C# to setup  export data from DataGrid.Steps:1. You can creat...
  • sunnyglen
  • sunnyglen
  • 2006年12月21日 09:43
  • 573

【DB.Oracle】PL/SQL Developer export/import excel 数据

导入/导出 表数据 from/into excel(csv)文件
  • robinjwong
  • robinjwong
  • 2014年11月17日 15:40
  • 1861

在html中打开word和excel

  word页面只要在jsp头设置如下指令:   excel如下: %@page contentType="application/vnd.ms-excel;charset=GBK" % 使用这种方...
  • wuyingziyren
  • wuyingziyren
  • 2008年06月13日 14:26
  • 7475

C#使用RenderControl将GridView控件导出到EXCEL的方法

本文实例展示了C#使用RenderControl将GridView控件导出到EXCEL的方法,是非常实用的一个功能,分享给大家供大家参考。具体如下: 主要功能代码如下: // 把GridV...
  • riyuedangkong1
  • riyuedangkong1
  • 2017年03月15日 22:06
  • 531

GridView完美快速导出到Excel(超强)

好多人都要把Gridview的数据导出到Excel的功能,有好多方法,最笨的就是一个一个cell读取,然后再一个一个填充的Excel中,经过我无数次(也不是了,但确实费了很大功夫)终于实现了。基本思路...
  • binsweet
  • binsweet
  • 2007年11月19日 15:00
  • 2668

asp.net将页面中gridview中的数据导入excel表中

using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;...
  • chenguang79
  • chenguang79
  • 2010年03月16日 22:44
  • 1983

(C#)利用Aspose.Cells组件导入导出excel文件

Aspose.Cells组件可以不依赖excel来导入导出excel文件: 导入: [csharp] view plaincopy public st...
  • amarantin
  • amarantin
  • 2014年09月14日 10:10
  • 550
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Export Data from GridView to Excel, Word, HTML with C#
举报原因:
原因补充:

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