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#Export data to CSV

private void ExportData() { #region export CSV List downLoadList = new List(); foreach (DataRow ...
  • zzx3q
  • zzx3q
  • 2012年02月16日 16:07
  • 1272

caffe的搭建过程以及遇上的各种问题的汇总

Caffe的搭建过程以及遇上的各种问题的汇总: ubuntu15.04+CUDA7.5+opencv3.0.0+python2.7 整个搭建过程参考 http://blog.csdn.net/ubun...
  • huangjx07
  • huangjx07
  • 2016年09月21日 14:47
  • 1278

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
  • 616

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

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

超详细配置Caffe(gpu版本+ubuntu16.04)考虑各种问题

作为人类一员,知识和经验有责任去传承,所以在经过很多次对caffe的配置后,写出配置的全过程让后来者少走弯路,网上各种教程多少都有些问题,以下是博主对网上各种教程的整合。废话不多说,开始: 如果刚装...
  • A_Z666666
  • A_Z666666
  • 2017年06月03日 20:37
  • 10048

保存PE资源数据节点到文件

前言找到数据节点时, IMAGE_RESOURCE_DATA_ENTRY.OffsetToData是相对文件起始地址的偏移. 数据节点都是有语言ID的, 语言ID到字符串,手工录入数据做了一个类. ...
  • LostSpeed
  • LostSpeed
  • 2016年08月02日 23:54
  • 532

Csharp调用微软COM转换word为HTML

使用微软的office中的word软件只要使用另存为就可以把一个word保存为HTML网页文件。但如何通过程序调用完成转化呢?  一下使用office 2007为例,其他版本略有不同。  1添加引用,...
  • xuexiaodong2009
  • xuexiaodong2009
  • 2015年07月10日 15:56
  • 1417

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
  • 581

asp.net(c#) 导出csv文件

public bool ExportToCsv(DataSet src, string fileName) { StreamWriter writer = null; ...
  • wslyy99
  • wslyy99
  • 2008年06月23日 09:16
  • 1892

vue 导出Excel

导出为Excel的功能,记录两个方法:1.vue中Table组件中自带exportCsv方法,可以实现导出功能,并且可以实现自定义导出列方法名说明参数exportCsv将数据导出为 .csv 文件,说...
  • JYL15732624861
  • JYL15732624861
  • 2018年03月31日 10:30
  • 84
收藏助手
不良信息举报
您举报文章:Export Data from GridView to Excel, Word, HTML with C#
举报原因:
原因补充:

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