1、new 一个窗体，然后设置窗体属性。
2、添加数据展示控件，显示数据。
3、添加按钮控件，调用导出Excel代码，实现导出Excel功能。
using System;using System.Collections.Generic;using System.Data;using System.Drawing;using System.IO;using System.Linq;using System.Text;using System.Threading;using System.Windows.Forms;using DevExpress.Utils;using DevExpress.XtraEditors;using DevExpress.XtraGrid;using DevExpress.XtraGrid.Views.Grid;using DevExpress.XtraTab;using SynData.MsBox;namespace SynData{ public class DevHelper { #region =======================================弹出消息框========================================= /// /// 弹出消息框，大消息 /// /// 显示标题 /// 弹出消息框 /// /// 弹出消息框，大消息 /// /// 显示标题 /// 弹出消息框 /// /// 弹出消息框，大消息 /// /// 显示标题 /// 弹出消息框 /// /// GridLookUpEdit 数据显示 /// /// GridLookUpEdit /// sql 语句 /// 绑定Text显示的字段源名称 /// 绑定Value字段源名称 /// 连接字符串 public static void GetApiisid(GridLookUpEdit gridLookUpEdit, string sql, string displaymember, string valuemember, string ConnectionStringLocalTransaction) { DataTable dt = SQlHelper.GetDataTable(ConnectionStringLocalTransaction, sql ).Tables[0]; gridLookUpEdit.Properties.View.OptionsBehavior.AutoPopulateColumns = false; gridLookUpEdit.Properties.DataSource = dt; //数据源 gridLookUpEdit.Properties.DisplayMember = displaymember; //绑定Text显示的字段源名称 gridLookUpEdit.Properties.ValueMember = valuemember; //绑定Value字段源名称 gridLookUpEdit.Properties.NullText = null; gridLookUpEdit.Properties.AllowNullInput = DevExpress.Utils.DefaultBoolean.True; gridLookUpEdit.Properties.View.BestFitColumns(); gridLookUpEdit.Properties.View.OptionsView.ShowAutoFilterRow = true; //显示不显示grid上第一个空行,也是用于检索的应用 } #endregion #region ===========================================GridContrl表格样式设计，可以导出数据。======================================== /// /// 表格样式设计，可以导出数据。 /// /// GridControl /// 表名 /// 是否依类型显示数据 /// 数据库连接字符串 public static void GetSetGridContrl(GridControl GCOrderItem, string TableName, string istype, string constr) { GridView gridView1 = (GridView)GCOrderItem.MainView; gridView1.IndicatorWidth = 40; gridView1.OptionsView.ShowAutoFilterRow = true; gridView1.OptionsView.ColumnAutoWidth = false; //自动列宽,会出现横向滚动条 gridView1.OptionsView.ColumnAutoWidth = false; //自动列宽 gridView1.BestFitColumns(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT "); sb.Append(" TableName,TableReMark,FileNames,FileReMark,LONG ,type2,LONG2 "); sb.Append("FROM dbo.Lan_TableField "); sb.Append("WHERE TableName = '" + TableName + "' order by SortNo "); DataTable dt = SQlHelper.GetDataTable(constr, sb.ToString()).Tables[0]; for (int i = 0; i < dt.Rows.Count; i++) { string filename = dt.Rows[i]["FileNames"].ToString().Trim(); string rem = dt.Rows[i]["FileReMark"].ToString().Trim(); string FILETYPE = dt.Rows[i]["type2"].ToString().Trim().ToUpper(); int LONG = Convert.ToInt32(dt.Rows[i]["LONG2"].ToString().Trim()); DevExpress.XtraGrid.Columns.GridColumn Col1 = new DevExpress.XtraGrid.Columns.GridColumn(); Col1.AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Near; if (istype == "依类型") { if (FILETYPE == "时间") { Col1.DisplayFormat.FormatType = FormatType.DateTime; Col1.DisplayFormat.FormatString = "yyyy-MM-dd HH:mm:ss"; } if (FILETYPE == "数值") { Col1.DisplayFormat.FormatType = FormatType.Numeric; } } Col1.Width = LONG; if (LONG < 60) { Col1.Width = 100; } Col1.Tag = filename + " ; " + rem; Col1.Name = filename; Col1.FieldName = filename; Col1.Caption = rem; Col1.VisibleIndex = i; gridView1.Columns.Add(Col1); } gridView1.OptionsView.ShowGroupPanel = false; gridView1.Appearance.OddRow.BackColor = Color.PaleGoldenrod; // 设置奇数行颜色 // 默认也是白色 可以省略 gridView1.OptionsView.EnableAppearanceOddRow = true; // 使能 // 和和上面绑定 同时使用有效 gridView1.Appearance.EvenRow.BackColor = Color.WhiteSmoke; // 设置偶数行颜色 gridView1.OptionsView.EnableAppearanceEvenRow = true; // 使能 // 和和上面绑定 同时使用有效 gridView1.CustomDrawRowIndicator += gridView1_CustomDrawRowIndicator; } #endregion #region ================================================弹出导出Excel窗体方法================================================ /// /// 将 DataRow[] 集合显示到表中 /// /// public static void ExprlDataRow(DataRow[] Mdr) { if (Mdr != null) { DataTable newdt = new DataTable(); try { DataTable DTtemp = Mdr[0].Table; newdt = DTtemp.Clone(); for (int i = 0; i < Mdr.Length; i++) { newdt.ImportRow((DataRow)Mdr[i]); } } catch (Exception ex) { MessageBox.Show(ex.Message); } ExprlDataTable(newdt); } } /// /// 将 DataRow 显示到表中 /// /// public static void ExprlDataRow(DataRow Mdr) { if (Mdr != null) { DataTable newdt = new DataTable(); try { DataTable DTtemp = Mdr.Table; newdt = DTtemp.Clone(); newdt.ImportRow((DataRow)Mdr); } catch (Exception ex) { MessageBox.Show(ex.Message); } ExprlDataTable(newdt); } } /// /// 弹出异常消息框，可导出数异常数据 /// /// 异常数据表名 /// 异常消息表 /// 弹出框显示文本 /// 连接字符串 public static void ExprlDataTable(DataTable dt) { /*------------------------------创建窗体-------------------------------------*/ Form f = GetSetFrom(); /*------------------------------创建Panel容器-------------------------------------*/ Panel pl = (Panel)(f.Controls.Find("FPL", true)[0]); /*------------------------------------数据展示----------------------------------------*/ if (dt != null) { /*------------------------------ 添加选卡控件-------------------------------------*/ XtraTabControl tab = new XtraTabControl(); tab.Name = "XTC"; pl.Controls.Add(tab); tab.Dock = System.Windows.Forms.DockStyle.Fill; /*------------------------------ 创建选卡-------------------------------------*/ XtraTabPage xtra = new XtraTabPage(); xtra.Name = dt.TableName;//设置选卡name xtra.Text = dt.TableName;//设置选卡显示文本 /*------------------------------ 创建数据展示控件GridControl------------------*/ DevExpress.XtraGrid.GridControl GCOrderItem = CreateGCOrderItem(dt); xtra.Controls.Add(GCOrderItem); tab.TabPages.Add(xtra); f.Show(); } } /// /// 弹出异常消息框，可导出数异常数据 /// /// 异常数据表名 /// 异常消息表 /// 弹出框显示文本 /// 连接字符串 public static void ExprlDataSet(DataSet ds) { /*------------------------------创建窗体-------------------------------------*/ Form f = GetSetFrom(); /*------------------------------创建Panel容器-------------------------------------*/ Panel pl = (Panel)(f.Controls.Find("FPL", true)[0]); /*------------------------------------数据展示----------------------------------------*/ if (ds != null) { /*------------------------------ 添加选卡控件-------------------------------------*/ XtraTabControl tab = new XtraTabControl(); tab.Name = "XTC"; pl.Controls.Add(tab); tab.Dock = System.Windows.Forms.DockStyle.Fill; for (int t = 0; t < ds.Tables.Count; t++) { DataTable dt = ds.Tables[t]; /*------------------------------ 创建选卡-------------------------------------*/ XtraTabPage xtra = new XtraTabPage(); xtra.Name = dt.TableName;//设置选卡name xtra.Text = dt.TableName;//设置选卡显示文本 /*------------------------------ 创建数据展示控件GridControl------------------*/ DevExpress.XtraGrid.GridControl GCOrderItem = CreateGCOrderItem(dt); xtra.Controls.Add(GCOrderItem); tab.TabPages.Add(xtra); } f.Show(); } } /// /// 弹出异常消息框，可导出数异常数据 /// /// 异常数据表名 /// 异常消息表 /// 弹出框显示文本 /// 连接字符串 public static void ExprlDictionary(Dictionary Dic) { /*------------------------------创建窗体-------------------------------------*/ Form f = GetSetFrom(); /*------------------------------创建Panel容器-------------------------------------*/ Panel pl = (Panel)(f.Controls.Find("FPL", true)[0]); /*------------------------------------数据展示----------------------------------------*/ if (Dic != null) { /*------------------------------ 添加选卡控件-------------------------------------*/ XtraTabControl tab = new XtraTabControl(); tab.Name = "XTC"; pl.Controls.Add(tab); tab.Dock = System.Windows.Forms.DockStyle.Fill; foreach (KeyValuePair item2 in Dic) { DataTable dt = item2.Value; /*------------------------------ 创建选卡-------------------------------------*/ XtraTabPage xtra = new XtraTabPage(); xtra.Name = dt.TableName;//设置选卡name xtra.Text = dt.TableName;//设置选卡显示文本 /*------------------------------ 创建数据展示控件GridControl------------------*/ DevExpress.XtraGrid.GridControl GCOrderItem = CreateGCOrderItem(dt); xtra.Controls.Add(GCOrderItem); tab.TabPages.Add(xtra); } f.Show(); } } /// /// 弹出异常消息框，可导出数异常数据 /// /// 异常数据表名 /// 异常消息表 /// 弹出框显示文本 /// 连接字符串 public static void ExprlList(List Dic) { /*------------------------------创建窗体-------------------------------------*/ Form f = GetSetFrom(); /*------------------------------创建Panel容器-------------------------------------*/ Panel pl = (Panel)(f.Controls.Find("FPL", true)[0]); /*------------------------------------数据展示----------------------------------------*/ if (Dic != null) { /*------------------------------ 添加选卡控件-------------------------------------*/ XtraTabControl tab = new XtraTabControl(); tab.Name = "XTC"; pl.Controls.Add(tab); tab.Dock = System.Windows.Forms.DockStyle.Fill; foreach (DataTable item2 in Dic) { /*------------------------------ 创建选卡-------------------------------------*/ XtraTabPage xtra = new XtraTabPage(); xtra.Name = item2.TableName;//设置选卡name xtra.Text = item2.TableName;//设置选卡显示文本 /*------------------------------ 创建数据展示控件GridControl------------------*/ DevExpress.XtraGrid.GridControl GCOrderItem = CreateGCOrderItem(item2); xtra.Controls.Add(GCOrderItem); tab.TabPages.Add(xtra); } f.Show(); } } /// /// 创建From 以及Panel /// /// /// 返回绑定好数据的 GridControl /// /// 绑定的数据 /// /// 弹出异常消息框，可导出数异常数据 /// /// 异常数据表名 /// 异常消息表 /// 弹出框显示文本 /// 连接字符串 public static void GetSetGridContrl(string TableName, DataTable dt, string con) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT "); sb.Append(" TableName,TableReMark,FileNames,FileReMark,LONG ,type2,LONG2 "); sb.Append("FROM dbo.Lan_TableField "); sb.Append("WHERE TableName = '" + TableName + "' order by SortNo "); DataTable dt2 = SQlHelper.GetDataTable(con, sb.ToString()).Tables[0]; Form f = new Form(); f.StartPosition = FormStartPosition.CenterScreen; f.Size = new System.Drawing.Size(560, 470); //f.Text = fText; f.FormClosing += f_FormClosing; GridView gridView1 = new GridView(); DevExpress.XtraGrid.GridControl GCOrderItem = new DevExpress.XtraGrid.GridControl(); GCOrderItem.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) | System.Windows.Forms.AnchorStyles.Left) | System.Windows.Forms.AnchorStyles.Right))); Button btn = new Button(); btn.Text = "导出Excel"; btn.Click += btn_Click; btn.Location = new System.Drawing.Point(460, 5); btn.Anchor = ((System.Windows.Forms.AnchorStyles) (System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right) ); btn.Size = new System.Drawing.Size(80, 29); f.Controls.Add(btn); Label lb = new Label(); lb.Name = "lbExcel"; lb.Location = new System.Drawing.Point(80, 80); lb.Visible = false; lb.ForeColor = Color.Red; lb.Font = new System.Drawing.Font("微软雅黑", 15F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134))); GCOrderItem.Cursor = System.Windows.Forms.Cursors.Default; //GCOrderItem.Dock = System.Windows.Forms.DockStyle.Fill; GCOrderItem.Location = new System.Drawing.Point(8, 40); GCOrderItem.MainView = gridView1; GCOrderItem.Name = "GCOrderItem"; GCOrderItem.Size = new System.Drawing.Size(530, 380); GCOrderItem.ViewCollection.AddRange(new DevExpress.XtraGrid.Views.Base.BaseView[] { gridView1}); f.Controls.Add(GCOrderItem); gridView1.GridControl = GCOrderItem; gridView1.Name = "gridView1"; gridView1.OptionsBehavior.Editable = false; gridView1.OptionsBehavior.EditorShowMode = DevExpress.Utils.EditorShowMode.Click; gridView1.OptionsView.ShowGroupPanel = false; gridView1.IndicatorWidth = 40; gridView1.OptionsView.ShowAutoFilterRow = true; gridView1.OptionsView.ColumnAutoWidth = false; //自动列宽,会出现横向滚动条 gridView1.OptionsView.ColumnAutoWidth = false; //自动列宽 gridView1.BestFitColumns(); if (dt2.Rows.Count > 0) { string FILETYPE = ""; for (int i = 0; i < dt2.Rows.Count; i++) { DevExpress.XtraGrid.Columns.GridColumn Col1 = new DevExpress.XtraGrid.Columns.GridColumn(); Col1.Name = dt2.Rows[i]["FileNames"].ToString();// itm.Key; Col1.FieldName = dt2.Rows[i]["FileNames"].ToString();// itm.Key; Col1.Caption = dt2.Rows[i]["FileReMark"].ToString(); //itm.Value; Col1.VisibleIndex = i; Col1.Width = Convert.ToInt32(dt2.Rows[i]["LONG2"].ToString()); Col1.AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Near; FILETYPE = dt2.Rows[i]["type2"].ToString().Trim().ToUpper(); if (FILETYPE == "时间") { Col1.DisplayFormat.FormatType = FormatType.DateTime; Col1.DisplayFormat.FormatString = "yyyy-MM-dd HH:mm:ss"; } if (FILETYPE == "数值") { Col1.DisplayFormat.FormatType = FormatType.Numeric; } gridView1.Columns.Add(Col1); } f.Text = dt2.Rows[0]["TableReMark"].ToString();//显示窗体名称 } gridView1.Appearance.OddRow.BackColor = Color.PaleGoldenrod; // 设置奇数行颜色 // 默认也是白色 可以省略 gridView1.OptionsView.EnableAppearanceOddRow = true; // 使能 // 和和上面绑定 同时使用有效 gridView1.Appearance.EvenRow.BackColor = Color.WhiteSmoke; // 设置偶数行颜色 gridView1.OptionsView.EnableAppearanceEvenRow = true; // 使能 // 和和上面绑定 同时使用有效 gridView1.CustomDrawRowIndicator += new DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventHandler(gridView1_CustomDrawRowIndicator); GCOrderItem.DataSource = dt; f.Show(); } private static void BtnExprl_Click(object sender, EventArgs e) { string path = BLL_getpath();//选择路径 string path2 = path.Replace(".xls", "").Replace(".xlsx", ""); /*如果路径不存在那么创建路径*/ if (!Directory.Exists(path2)) { Directory.CreateDirectory(path2); } try { Label btn = (Label)sender; /*获取按钮对象*/ Form f = btn.FindForm();/*获取窗体对象*/ DevExpress.XtraGrid.GridControl GCOrderItem = null;/*获取数据展示控件*/ Panel pl = (Panel)(f.Controls.Find("FPL", true)[0]); XtraTabControl tab = (XtraTabControl)pl.Controls.Find("XTC", true)[0]; foreach (XtraTabPage gc in tab.TabPages) { foreach (Control con in gc.Controls) { if (con.GetType().ToString() == "DevExpress.XtraGrid.GridControl") { GCOrderItem = new GridControl(); GCOrderItem = (DevExpress.XtraGrid.GridControl)con; DataTable dt = (DataTable)GCOrderItem.DataSource; GridView dv = (GridView)GCOrderItem.MainView; /*获取数据表*/ DataTable DT_sap = dt.Clone(); for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = DT_sap.NewRow(); for (int j = 0; j < dt.Columns.Count; j++) { string col = dt.Columns[j].ColumnName; dr[col] = dt.Rows[i][col]; } DT_sap.Rows.Add(dr); } /*转换中文列名*/ foreach (DevExpress.XtraGrid.Columns.GridColumn gcm in dv.Columns) { string name = gcm.Name; string text = gcm.Caption; DT_sap.Columns[name].Caption = text; DT_sap.Columns[name].ColumnName = text; } Label lb = new Label(); /*快速 导出Excel方法*/ WriteExcel(DT_sap, path2 + "\\" + GCOrderItem.Name + ".xls", lb); } } } } catch (Exception ex) { MsDialogResult("导出失败！\r\n" + ex.Message.ToString(), false); } } /// /// 鼠标进入控件上方时，手型鼠标，背景灰色 /// /// /// public static void label_MouseEnter(object sender, EventArgs e) { Label lable = (Label)sender; lable.Cursor = Cursors.Hand; lable.BackColor = Color.Gainsboro; } /// /// 鼠标离开控件时，箭头鼠标，背景恢复正常 /// /// /// public static void label_MouseLeave(object sender, EventArgs e) { Label lable = (Label)sender; lable.Cursor = Cursors.Default; lable.BackColor = Control.DefaultBackColor; } static void f_FormClosing(object sender, FormClosingEventArgs e) { Form f = (Form)sender; f.Dispose(); } /// /// 导出Excel /// /// /// static void btn_Click(object sender, EventArgs e) { try { Button btn = (Button)sender;//获取按钮对象 Form f = btn.FindForm();//获取 按钮 所在的窗体 DevExpress.XtraGrid.GridControl GCOrderItem = null; foreach (Control con in f.Controls) { if (con.Name == "GCOrderItem") { GCOrderItem = (DevExpress.XtraGrid.GridControl)con; DataTable dt = (DataTable)GCOrderItem.DataSource; GridView dv = (GridView)GCOrderItem.MainView; DataTable DT_sap = dt.Clone(); for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = DT_sap.NewRow(); for (int j = 0; j < dt.Columns.Count; j++) { string col = dt.Columns[j].ColumnName; dr[col] = dt.Rows[i][col]; } DT_sap.Rows.Add(dr); } foreach (DevExpress.XtraGrid.Columns.GridColumn gcm in dv.Columns) { string name = gcm.Name; string text = gcm.Caption; DT_sap.Columns[name].Caption = text; DT_sap.Columns[name].ColumnName = text; } string path = BLL_getpath(); char[] anyOf = { '\\' }; int count = path.LastIndexOfAny(anyOf); string filname1 = path.Substring(count + 1, path.Length - count - 1); string Table1 = path.Substring(0, count) + "\\" + filname1; Label lb = new Label(); WriteExcel(DT_sap, Table1, lb); } } MsDialogResult("导出成功！"); } catch (Exception ex) { MsDialogResult(ex.Message.ToString(),false); } } /// /// 自动添加 行索引 以及 模糊查询设置 /// /// /// public static void gridView1_CustomDrawRowIndicator(object sender, RowIndicatorCustomDrawEventArgs e) { //添加行索引 if (e.Info.IsRowIndicator && e.RowHandle >= 0) { e.Info.DisplayText = (e.RowHandle + 1).ToString(); } //模糊查询时 可以不用输入“%” foreach (DevExpress.XtraGrid.Columns.GridColumn item in ((GridView)sender).Columns) { item.OptionsFilter.AutoFilterCondition = DevExpress.XtraGrid.Columns.AutoFilterCondition.Contains; //筛选条件设置为包含 } } /// /// 获取Excel文件保存路径 /// /// /// 快速导出Excel的方法 /// /// /// /// public static void WriteExcel(DataTable dt, string path, Label lblTip) { try { lblTip.Visible = true; string mark = lblTip.Text; long totalCount = (long)dt.Rows.Count; lblTip.Text = string.Concat(new object[]{lblTip.Text,"共有",totalCount, "条数据。"}); Thread.Sleep(1000); long rowRead = 0L; StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312")); StringBuilder sb = new StringBuilder(); for (int i = 0; i < dt.Columns.Count; i++) { sb.Append(dt.Columns[i].ColumnName.ToString() + "\t"); } sb.Append(Environment.NewLine); for (int j = 0; j < dt.Rows.Count; j++) { rowRead += 1L; lblTip.Text = mark + "正在写入[" + ((float)(100L * rowRead) / (float)totalCount).ToString("0.00") + "%]...的数据"; Application.DoEvents(); for (int k = 0; k < dt.Columns.Count; k++) { string xmlss; if (dt.Rows[j][k] == null) { xmlss = "空"; } else if (dt.Rows[j][k].ToString().Trim() == "") { xmlss = "空"; } else { xmlss = dt.Rows[j][k].ToString().Replace("\n", "").Replace("\r\n", "").Replace("\t", "").Replace("\\s", "").Replace("\\n", "").Replace("\r", "").Replace("\\r", "").Replace("@", "").Replace("\\", "").Replace("/", ""); } sb.Append(xmlss + "\t"); } sb.Append(Environment.NewLine); } sw.Write(sb.ToString()); sw.Flush(); sw.Close(); lblTip.Text = ""; lblTip.Visible = false; } catch (Exception ex) { MessageBox.Show(ex.Message); } } #endregion }}
自己再学习和摸索阶段，代码写的有点乱，纯属瞎搞。