1、添加Excel引用
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
导出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、编写导出方法
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 导出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[