转自:江边孤鸟 http://blog.csdn.net/jbgh608/ 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.csdn.net/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.csdn.net/jbgh608/ case 1: ExportExcel(FilePath+"\"+newFileName,fileNameExt); break; case 2: ExportHtml( FilePath+"\"+newFileName,fileNameExt); ExportXslt(fileNameExt); ExportXml(FilePath+"\"+newFileName,fileNameExt); break; } } } #endregion #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 #region 导出Xml /**/ 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 #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./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 #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 } } |
|