C#wiform导出EXCEL功能实现

实现如图

导出如图 可以直接带出界面字段名称

页面显示代码

        private void Form1_Load(object sender, EventArgs e)
        {       
            iMesTable.Initial_Table();
            Initial_Form();
            combShow.SelectedIndex = 0;       
            combFilter.Items.Clear();
            combFilterField.Items.Clear();
            for (int i = 0; i <= iMesTable.tGridField.Length - 1; i++)
            {
                combFilter.Items.Add(iMesTable.tGridField[i].sCaption);
                combFilterField.Items.Add(iMesTable.tGridField[i].sFieldName);
            }
            if (combFilter.Items.Count > 0)
                combFilter.SelectedIndex = 0;      
            //检查权限--待做
            //Check_Privilege();
            ShowData();
        }

iMesTable.Initial_Table()代码

        public static void Initial_Table()
        {
            //设置title数据 
            Array.Resize(ref tGridField, 4);
            tGridField[0].sFieldName = "MACHINE_CODE";
            tGridField[0].sCaption = "设备编号";

            tGridField[1].sFieldName = "MODEL";
            tGridField[1].sCaption = "机种";

            tGridField[2].sFieldName = "UPDATE_EMPNO";
            tGridField[2].sCaption = "更新人员";

            tGridField[3].sFieldName = "UPDATE_TIME";
            tGridField[3].sCaption = "更新时间";

            for (int i = 0; i <= tGridField.Length - 1; i++)
            {
                string sText = tGridField[i].sCaption;
                tGridField[i].sCaption = sText;
            }
        }

ShowData()代码

        public void ShowData()
        {
            string sFieldName = "" ;
            if (combFilter.SelectedIndex > -1 && editFilter.Text.Trim() != "")
            {
               sFieldName = combFilterField.Items[combFilter.SelectedIndex].ToString();                     
            }
            modelInfo.Idx = combShow.SelectedIndex;
            modelInfo.FiledName = sFieldName;
            modelInfo.FilterName = editFilter.Text.Trim();
            gvData.DataSource = controller.ShowData(modelInfo);
            for (int i = 0; i <= gvData.Columns.Count - 1; i++)
            {
                gvData.Columns[i].Visible = false;
            }
            for (int i = 0; i <= iMesTable.tGridField.Length - 1; i++)
            {
                string sGridField = iMesTable.tGridField[i].sFieldName;

                if (gvData.Columns.Contains(sGridField))
                {
                    gvData.Columns[sGridField].HeaderText = iMesTable.tGridField[i].sCaption;
                    gvData.Columns[sGridField].DisplayIndex = i;
                    gvData.Columns[sGridField].Visible = true;
                }
            }
            gvData.Focus();
        }

导出EXCEL实现代码

        private void btnExport_Click(object sender, EventArgs e)
        {
            saveFileDialog1.DefaultExt = "xls";
            saveFileDialog1.Filter = "All Files(*.xls)|*.xls";
            if (saveFileDialog1.ShowDialog() != DialogResult.OK)
                return;
            string sFileName = saveFileDialog1.FileName;
            ExportExcel.CreateExcel Export = new ExportExcel.CreateExcel(sFileName);
            Export.ExportToExcel(gvData);
        }

ExportExcel.cs代码

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml;
using System.Collections;
using System.Data;
using System.Windows.Forms;

namespace ExportExcel
{
    
    public class CreateExcel
    {
        const string SPREADSHEETSTRING = "urn:schemas-microsoft-com:office:spreadsheet";
        const string OFFICESTRING = "urn:schemas-microsoft-com:office:office";
        const string EXCELSTRING = "urn:schemas-microsoft-com:office:excel";

        private string _FilePath;
        /// <summary>
        /// the path of excel file
        /// </summary>
        public string FilePath
        {
            get
            {
                return _FilePath;
            }
            set
            {
                _FilePath = value;
            }
        }

        private string _filter;

        public string Filter
        {
            get { return _filter; }
            set { _filter = value; }
        }

        private string _Sort;

        public string Sort
        {
            get { return _Sort; }
            set { _Sort = value; }
        }

        private string _Title;

        public string Title
        {
            get { return _Title; }
            set { _Title = value; }
        }

        public List<string> IgnoreColumns = null;


        /// <summary>
        /// creat a new instance of DataSetToExcel
        /// </summary>
        public CreateExcel() : this(string.Empty, string.Empty) { }
        public CreateExcel(string FilePath) : this(FilePath, string.Empty) { }
        public CreateExcel(string FilePath, string title)
        {
            this.FilePath = FilePath;
            this.Title = title;
            _filter = string.Empty;
            _Sort = string.Empty;
        }

        public void ExportToExcel(DataGridView GridView)
        {
            //陪ボ抖魁逆嘿
            int iCount = 0;
            string[] sColumnList = new string[GridView.Columns.Count];
            for (int i = 0; i <= GridView.Columns.Count - 1; i++)
            {
                int index = GridView.Columns[i].DisplayIndex;
                if (GridView.Columns[i].Visible)
                {
                    iCount = iCount + 1;
                    sColumnList[index] = GridView.Columns[i].Name;
                }
            }
            Array.Resize(ref sColumnList, iCount);
            //

            DataSet dsGrid = new DataSet();
            dsGrid.Tables.Add();
            for (int i = 0; i <= sColumnList.Length - 1; i++)
            {
                dsGrid.Tables[0].Columns.Add(GridView.Columns[sColumnList[i].ToString()].HeaderText);
            }

            for (int i = 0; i <= GridView.Rows.Count - 1; i++)
            {
                dsGrid.Tables[0].Rows.Add();
                int iCol = -1;
                for (int j = 0; j <= sColumnList.Length - 1; j++)
                {
                    iCol = iCol + 1;
                    dsGrid.Tables[0].Rows[i][iCol] = GridView.Rows[i].Cells[sColumnList[j].ToString()].Value;
                }
            }
            ExportToExcel(dsGrid);
        }
        public void ExportToExcel(DataTable table)//琌盢DataTableDataSet柑
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(table.Copy());
            ExportToExcel(ds);
        }
        public void ExportToExcel(DataSet ds)
        {
            XmlDocument xml = CreateFile();//XML
            XmlNode nodeworkbook = xml.SelectSingleNode("Workbook");//ノㄓ讽WorkBookNode


            for (int k = 0; k < ds.Tables.Count; k++)//ㄌTable计蹲Sheet
            {
                DataTable table = new DataTable();
                table = ds.Tables[k].Copy();
                Hashtable tableDD = GetDDTable(table);
                DataRow[] dr = table.Select(Filter, Sort);
                int maxrowcount = this.Title.Length > 0 ? 65534 : 65535;
                XmlNode nodetable = CreateWorkSheet(table.TableName, nodeworkbook, table, tableDD);

                for (int i = 0; i < dr.Length; i++)
                {
                    if (i != 0 && i % maxrowcount == 0)
                    {
                        nodetable = CreateWorkSheet(string.Format("{0}{1}", table.TableName, i / maxrowcount), nodeworkbook, table, tableDD);
                    }
                    XmlNode noderow = xml.CreateElement("Row");
                    nodetable.AppendChild(noderow);
                    foreach (DataColumn dc in table.Columns)
                    {
                        if (IgnoreColumns == null || !IgnoreColumns.Contains(dc.ColumnName))
                        {
                            ToExcel(noderow, dr[i][dc], dc.DataType);
                        }
                    }
                }
            }
            xml.Save(FilePath);
        }

        private Hashtable GetDDTable(DataTable table)
        {
            Hashtable tableDD = new Hashtable();
            foreach (DataColumn dc in table.Columns)
            {
                tableDD.Add(dc.ColumnName, dc.ColumnName);

            }
            return tableDD;
        }

        private void ToExcel(XmlNode nodeRow, object value, Type type)
        {
            XmlDocument xml = nodeRow.OwnerDocument;
            XmlNode nodecell = xml.CreateElement("Cell");
            nodeRow.AppendChild(nodecell);
            XmlNode nodedata = xml.CreateElement("Data");
            XmlAttribute atttype = xml.CreateAttribute("ss", "Type", SPREADSHEETSTRING);
            if (type == typeof(uint) || type == typeof(UInt16) || type == typeof(UInt32)
                         || type == typeof(UInt64) || type == typeof(int) || type == typeof(Int16)
                         || type == typeof(Int32) || type == typeof(Int64) || type == typeof(Single)
                         || type == typeof(Double) || type == typeof(Decimal))
            {
                atttype.Value = "Number";
                if (value == DBNull.Value)
                {
                    nodedata.InnerText = "0";      //empty value can not set to number column
                }
                else
                {
                    nodedata.InnerText = value.ToString();
                }
            }
            else
            {
                atttype.Value = "String";
                nodedata.InnerText = value.ToString().Replace(">", "&gt").Replace("<", "&lt");
            }
            nodedata.Attributes.Append(atttype);
            nodecell.AppendChild(nodedata);
        }
        private XmlNode CreateWorkSheet(string sheetName, XmlNode nodeWorkbook, DataTable table, Hashtable tableDD)
        {
            XmlDocument xml = nodeWorkbook.OwnerDocument;
            XmlNode nodeworksheet = xml.CreateElement("Worksheet");

            XmlAttribute attname = xml.CreateAttribute("ss", "Name", SPREADSHEETSTRING);
            attname.Value = sheetName;
            nodeworksheet.Attributes.Append(attname);
            XmlAttribute attxmln = xml.CreateAttribute("xmlns");
            attxmln.Value = SPREADSHEETSTRING;
            nodeworksheet.Attributes.Append(attxmln);
            nodeWorkbook.AppendChild(nodeworksheet);

            XmlNode nodetable = xml.CreateElement("Table");
            nodeworksheet.AppendChild(nodetable);
            if (this.Title.Length > 0)
            {
                XmlNode noderowtitle = xml.CreateElement("Row");
                nodetable.AppendChild(noderowtitle);

                XmlNode nodecell = xml.CreateElement("Cell");
                noderowtitle.AppendChild(nodecell);
                if (table.Columns.Count > 1)
                {
                    XmlAttribute attmerge = xml.CreateAttribute("ss", "MergeAcross", SPREADSHEETSTRING);
                    attmerge.Value = (table.Columns.Count - 1).ToString();
                    nodecell.Attributes.Append(attmerge);
                }
                XmlAttribute attstyle = xml.CreateAttribute("ss", "StyleID", SPREADSHEETSTRING);
                attstyle.Value = "title";
                nodecell.Attributes.Append(attstyle);

                XmlNode nodedata = xml.CreateElement("Data");
                XmlAttribute atttype = xml.CreateAttribute("ss", "Type", SPREADSHEETSTRING);
                atttype.Value = "String";
                nodedata.InnerText = this.Title;
                nodedata.Attributes.Append(atttype);
                nodecell.AppendChild(nodedata);
            }

            XmlNode noderow = xml.CreateElement("Row");
            nodetable.AppendChild(noderow);
            foreach (DataColumn dc in table.Columns)
            {
                if (IgnoreColumns == null || !IgnoreColumns.Contains(dc.ColumnName))
                {
                    ToExcel(noderow, tableDD[dc.ColumnName], typeof(string));
                }
            }

            return nodetable;
        }
        private XmlDocument CreateFile()
        {
            string directoryname = Path.GetDirectoryName(FilePath);
            if (!Directory.Exists(directoryname))
            {
                Directory.CreateDirectory(directoryname);
            }
            XmlDocument xml = new XmlDocument();
            xml.AppendChild(xml.CreateXmlDeclaration("1.0", null, null));

            XmlNode nodeworkbook = xml.CreateElement("Workbook");
            XmlAttribute attxmlns = xml.CreateAttribute("xmlns");
            attxmlns.Value = SPREADSHEETSTRING;
            XmlAttribute attxmlnso = xml.CreateAttribute("xmlns:o");
            attxmlnso.Value = OFFICESTRING;
            XmlAttribute attxmlnsx = xml.CreateAttribute("xmlns:x");
            attxmlnsx.Value = EXCELSTRING;
            XmlAttribute attxmlnsss = xml.CreateAttribute("xmlns:ss");
            attxmlnsss.Value = SPREADSHEETSTRING;
            nodeworkbook.Attributes.Append(attxmlns);
            nodeworkbook.Attributes.Append(attxmlnso);
            nodeworkbook.Attributes.Append(attxmlnsx);
            nodeworkbook.Attributes.Append(attxmlnsss);
            xml.AppendChild(nodeworkbook);

            XmlNode nodestyles = xml.CreateElement("Styles");
            nodeworkbook.AppendChild(nodestyles);

            XmlNode nodestyle = xml.CreateElement("Style");
            XmlAttribute attid = xml.CreateAttribute("ss", "ID", SPREADSHEETSTRING);
            attid.Value = "title";
            nodestyle.Attributes.Append(attid);
            nodestyles.AppendChild(nodestyle);

            XmlElement nodealignment = xml.CreateElement("Alignment");
            XmlAttribute atthorizontal = xml.CreateAttribute("ss", "Horizontal", SPREADSHEETSTRING);
            atthorizontal.Value = "Center";
            nodealignment.Attributes.Append(atthorizontal);
            nodestyle.AppendChild(nodealignment);

            return xml;
        }
    }
}

导出EXCEL的方法 2 在查询数据进行字段字名称赋值

导出效果

     //导出EXCEL
        private void btn_Excel_Click(object sender, EventArgs e)
        {
            string sFieldName = "";
            string sFieldText = "";
            bool Filter = false;
            string Enable = "";
            if (combFilter.SelectedIndex > -1 && editFilter.Text.Trim() != "")
            {
                Filter = true;
                sFieldName = combFilterField.Items[combFilter.SelectedIndex].ToString();
                sFieldText = editFilter.Text.Trim();
            }

            if (combShow.SelectedIndex == 0)
            { Enable = "Y"; }
            else if (combShow.SelectedIndex == 1)
            { Enable = "N"; }

            gvData.DataSource = controller.ShowExcelValues(Filter, sFieldName, sFieldText, Enable);
            DataTable dtTemp = (DataTable)gvData.DataSource;

            Imes.Utility.Tools.ExcelHelp.ExportToExcel(dtTemp);
        }

ShowExcelValues（）

 //EXCEL
        public DataTable ShowExcelValues(bool Filter, string sFieldName, string sFieldText, string Enable)
        {
            return dal.ShowExcelValues(Filter, sFieldName, sFieldText, Enable);
        }

     //Excel
        public DataTable ShowExcelValues(bool Filter, string sFieldName, string sFieldText, string Enable)
        {
            string sqlStr = @"SELECT GLUETYPENAME as 耗材型号 ,GLUEVENDOR as 供应商 ,APN as 料号,GLUETYPE as 物料类型 ,SECTIONNAME as 使用工段,ROUTING as 流程,SHELF_NAME as 架位 FROM IMES.M_GLUE_TYPE  WHERE 1=1";
            if (Filter)
                sqlStr = sqlStr + string.Format(" AND {0} = '{1}' ", sFieldName, sFieldText);
            if (!string.IsNullOrWhiteSpace(Enable))
                sqlStr = sqlStr + string.Format(" AND ENABLED = '{0}' ", Enable);

            sqlStr = sqlStr + " ORDER BY  GLUETYPENAME ";

            return utility.Query(sqlStr);
        }

导出3如图效果的EXCEL

导出代码

   private void btnExport_Click(object sender, EventArgs e)
        {
            DataTable dtTemp = new DataTable();
            dtTemp = (DataTable)this.gvData.DataSource;
            Imes.Utility.Tools.ExcelHelp.ExportToExcel(dtTemp);
        }