【C# winfrom】datagrid导出为excel、html、xml、xslt源码

  1、添加Excel引用

2、编写导出方法

 

using System;
using System.Windows.Forms;
using System.Diagnostics;
using XcjwHIS.PubicBaseClasses;
using System.Data;
using System.IO;
using System.Xml;
using System.Text;

namespace 江边孤鸟 http://blog.youkuaiyun.com/jbgh608/

...{
    /**//// <summary>
    /// DataGrid、报表 导出为Excel文件,XML文件
    /// add by hxc20070830 who's email is jbgh608@163.com
    /// </summary>
    public class ExportDataGrid
    ...{
        /**//// <summary>
        /// 源DataGrid
        /// </summary>
        private DataGrid ExportGrid;
        /**//// <summary>
        /// 调用窗口
        /// </summary>
        private Form ParentWindow=null;

        private DataTable mytb;
        /**//// <summary>
        /// DataGrid 导出 Excel文件
        /// </summary>
        /// <param name="parentWindow">父亲窗口</param>
        /// <param name="grid">要导出的DataGrid</param>
        public ExportDataGrid(Form parentWindow,DataGrid grid)
        ...{
            ExportGrid=grid;
            ParentWindow= parentWindow;            
        }
        /**//// <summary>
        /// 报表导出为Excel文件
        /// </summary>
        /// <param name="parentWindow">父亲窗口</param>
        /// <param name="ds">数据源</param>
        public ExportDataGrid(Form parentWindow,DataSet ds)
        ...{
            mytb=ds.Tables[0].Copy();
            ParentWindow= parentWindow;            
        }
        保存对话框#region 保存对话框
        /**//// <summary>
        /// 导出文件
        /// </summary>
        /// <param name="ExportType">文件类型 1 Excel ；2 xml and html</param>
        public void SaveFileDialog(int ExportType)
        ...{
            string localFilePath,fileNameExt,newFileName,FilePath;            
            SaveFileDialog sfd = new SaveFileDialog ( ) ;
            sfd.AddExtension=true;
            sfd.Filter= " txt files(*.xls)|*.xls|All files(*.*)|*.*"  ;
            sfd.FilterIndex = 2  ;
            sfd.RestoreDirectory = true  ;
            if ( sfd.ShowDialog ( ) == DialogResult.OK )
            ...{
                localFilePath=sfd.FileName.ToString();        
                fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("/") + 1);
                FilePath= localFilePath.Substring(0,localFilePath.LastIndexOf("/") );            
                newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;
                this.ParentWindow.Cursor=new Cursor(Constant.ApplicationDirectory+"/wait.cur");
                //this.ParentWindow.Cursor=PublicStaticFun.GetCursor("Xc_db_rygl.wait.cur",GetType());
                switch( ExportType)
                ...{
                        //江边孤鸟 http://blog.youkuaiyun.com/jbgh608/
                    case 1:
                        ExportExcel(FilePath+"/"+newFileName,fileNameExt);
                        break;
                    case 2:
                        ExportHtml( FilePath+"/"+newFileName,fileNameExt);
                        ExportXslt(fileNameExt);
                        ExportXml(FilePath+"/"+newFileName,fileNameExt);
                        break;                    
                }
                
            } 
        }

        #endregion

        导出Excel#region 导出Excel
        /**//// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        /// <param name="p_ReportName">表头</param>
        /// <returns></returns>
        public bool ExportExcel(string FilePath,string p_ReportName)
        ...{
            if ( this.ExportGrid.TableStyles.Count == 0 ) return false;
            DataGridTableStyle ts = this.ExportGrid.TableStyles[0];

            // 创建Excel对象                    
            Excel.Application xlApp = new Excel.ApplicationClass();
            if ( xlApp == null )
            ...{
                MessageBox.Show("Excel无法启动");
                return false;
            }
            // 创建Excel工作薄
            Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
            Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
            
            // 设置标题
            Excel.Range range = xlSheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,ts.GridColumnStyles.Count]);
            range.MergeCells = true;
            xlApp.ActiveCell.FormulaR1C1 = p_ReportName;
            xlApp.ActiveCell.Font.Size = 20;
            xlApp.ActiveCell.Font.Bold = true;
            xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;

            // 列索引，行索引，总列数，总行数
            int colIndex = 0;
            int RowIndex = 0;
            int colCount = ts.GridColumnStyles.Count;
            int RowCount = this.ParentWindow.BindingContext[this.ExportGrid.DataSource,this.ExportGrid.DataMember].Count;

            // 创建缓存数据
            object[,] objData = new object[RowCount + 1, colCount ];
            // 获取列标题
            foreach(DataGridColumnStyle cs in ts.GridColumnStyles)
            ...{
                objData[RowIndex,colIndex++] = cs.HeaderText;    
            }            
            // 获取数据
            for(RowIndex =1;RowIndex<=RowCount;RowIndex++)
            ...{
                for(colIndex=0;colIndex < colCount;colIndex++)
                ...{
                    objData[RowIndex,colIndex] = this.ExportGrid[RowIndex-1,colIndex].ToString();
                }
                Application.DoEvents();
            }
            // 写入Excel
            xlApp.get_Range(xlApp.Cells[2,   1],   xlApp.Cells[2,   colIndex]).Font.Bold   =   true;  
            range = xlSheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[RowCount+2,colCount]);            
            range.Value2 = objData;

            // 保存
            try
            ...{
                xlApp.Cells.EntireColumn.AutoFit();   
                xlApp.Cells.VerticalAlignment   =   Excel.Constants.xlCenter;   
                xlApp.Cells.HorizontalAlignment   =   Excel.Constants.xlCenter;    
                //xlApp.Visible   =   true;                  
                xlBook.Saved  = true;
                xlBook.SaveCopyAs(FilePath + ".xls");                    
            }
            catch
            ...{
                MessageBox.Show("保存出错,请检查!");
                return false;
            }
            finally
            ...{
                xlApp.Quit();
                GC.Collect();
                KillProcess("excel") ;
                this.ParentWindow.Cursor=Cursors.Default;
            }
            return true;
        }
        #endregion

        杀死进程#region 杀死进程
        private void KillProcess(string processName) 
        ...{ 
            System.Diagnostics.Process myproc= new System.Diagnostics.Process(); 
            //得到所有打开的进程 
            try 
            ...{ 
                foreach (Process thisproc in Process.GetProcessesByName(processName)) 
                ...{ 
                    thisproc.Kill();                 
                } 
            } 
            catch(Exception Exc) 
            ...{ 
                throw new Exception("",Exc); 
            } 
        }  
        #endregion    

        导出Xml#region 导出Xml
        /**//// <summary>
        /// datagrid导出xml
        /// </summary>
        /// <param name="FilePath">保存路径</param>
        /// <param name="p_ReportName">文件名称</param>
        /// <returns></returns>
        private bool ExportXml(string FilePath,string p_ReportName)
        ...{
            string path=Constant.ApplicationDirectory+"/xml/"+p_ReportName+".xslt";            
            string  PItext   ="type='text/xsl' href='"+path+"'";                 
            DataSet ds=new DataSet();
            try
            ...{                        
                DataTable tb = (DataTable)this.ExportGrid.DataSource;
                DataTable ExportTb=tb.Copy();
                ds.Tables.Add(ExportTb);            
                XmlTextReader XTReader = new XmlTextReader(ds.GetXml(),XmlNodeType.Element,null);
                XmlTextWriter XTWriter = new XmlTextWriter(FilePath+".xml",Encoding.UTF8);            
                XTWriter.WriteStartDocument();
                XTWriter.WriteProcessingInstruction("xml-stylesheet",PItext);
                string fieldName = "" ;
                while(XTReader.Read())
                ...{
                    switch(XTReader.NodeType)
                    ...{
                        case XmlNodeType.Element:
                            XTWriter.WriteStartElement(XTReader.Name);
                            fieldName = XTReader.Name;
                            break;                        
                        case XmlNodeType.Text:                        
                            if(fieldName.ToLower() == "brithday"||fieldName.ToLower() == "gzsj"||fieldName.ToLower() == "rdsj"||fieldName.ToLower() == "zzsj")
                            ...{                        
                                DateTime dt = DateTime.Parse (XTReader.Value.ToString());                        
                                XTWriter.WriteString(dt.ToString("yyyy-MM-dd"));
                            }
                            else
                                XTWriter.WriteString(XTReader.Value);
                            break;
                        case XmlNodeType.EndElement:
                            XTWriter.WriteEndElement();
                            break;
                        default:                        
                            break;
                    }
                }
                XTWriter.Close();    
                this.ParentWindow.Cursor=Cursors.Default;
            }
            catch
            ...{
                MessageBox.Show("保存出错,请检查!");
                return false;
            }
            finally
            ...{
                //GC.Collect();
                //ds.Clear();
                //ds.Dispose();                
            }
            return true;
        }    
        #endregion

        导出Xslt#region 导出Xslt
        /**//// <summary>
        /// 导出xslt样式
        /// </summary>
        /// <param name="p_ReportName">文件名称</param>
        private void ExportXslt( string p_ReportName )
        ...{
            DataGrid d=this.ExportGrid;
            string fileName=Constant.ApplicationDirectory+"/xml/"+p_ReportName+".xslt";
            string title =p_ReportName;
            string s1="<?xml version="1.0" encoding="utf-8" ?> ";
            string  s2="<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> ";
            string s3="<xsl:template match="/"> <html> <head> <title>"+title+"</title> </head> <body> <h1 align="center">"+title+"</h1> ";
            string s4="<table   cellpadding="0"  cellspacing="0" border="1" bordercolor="#0000" style="border-collapse:collapse;padding=2px;font-size:14px;"> <tr> ";
            string s5=string.Empty;
            string s6="</tr> <xsl:for-each select="NewDataSet/Table1"> <tr> ";
            string s7=string.Empty;
            string s8="</tr> </xsl:for-each> </table> </body> </html> </xsl:template> </xsl:stylesheet>";
            try
            ...{
                foreach( DataGridColumnStyle gc in d.TableStyles[0].GridColumnStyles )
                ...{
                    s5 += "<th align="center"  nowrap="false">" + gc.HeaderText + "</th> ";
                }
                foreach( DataGridColumnStyle gc in d.TableStyles[0].GridColumnStyles )
                ...{
                    s7 += "<td nowrap="false"> <xsl:value-of select=""+gc.MappingName.ToUpper()+""></xsl:value-of> </td> ";
                }
                string sXslt=s1+s2+s3+s4+s5+s6+s7+s8;
                Stream stream =File.OpenWrite(fileName);
                using(StreamWriter writer =new StreamWriter(stream))
                ...{
                    writer.Write(sXslt);
                }     
            }
            catch(Exception e)
            ...{
                MessageBox.Show(e.Message+" "+e.StackTrace+" "+e.Source);
            }
            
        }    
        #endregion

        导出html#region 导出html
        /**//// <summary>
        /// datagrid导出html
        /// </summary>
        /// <param name="FilePath">保存路径</param>
        /// <param name="p_ReportName">名称</param>
        public  void ExportHtml( string FilePath,string p_ReportName)
        ...{
            DataGrid d =this.ExportGrid;
            DataTable dt = d.DataSource as DataTable;
            string path =FilePath+".html";
            string title =p_ReportName;
            string s1 =  "<html><head><title>" + title ;
            string s2 =  "</title></head><body bgcolor=lightblue>" + "<h1 align="center">" +title+"</h1><table   cellpadding="0"  cellspacing="0" border="1" bordercolor="#0000" style="border-collapse:collapse;padding=2px;font-size:14px;">";
            string s3 = string.Empty;
            string s4= "</table></body></html>";            
            if( dt == null || dt.Rows.Count == 0 ) return;        
            int len = d.TableStyles[0].GridColumnStyles.Count;
            foreach( DataGridColumnStyle gc in d.TableStyles[0].GridColumnStyles )
            ...{
                s2 += "<th  nowrap=false>" + gc.HeaderText + "</th>";
            }
            foreach( DataRow dr in dt.Rows )
            ...{
                s3 += "<tr>";
                for( int i = 0; i < len; i ++ )
                ...{
                    s3 += "<td  nowrap=false>" + dr[ i ].ToString() + "</td>";
                }
                s3 += "</tr>";
            }
            string s = s1 + s2 + s3 + s4;
            try
            ...{
                using( FileStream fs = File.Create( path ) )
                ...{
                    using( StreamWriter sw = new StreamWriter( fs ) )
                    ...{
                        sw.Write( s );
                    }
                }
            }
            catch( Exception ex )
            ...{
                MessageBox.Show( ex.Message + " " + ex.StackTrace + " " + ex.Source );
            }        
        }
        #endregion
    }
}