使用.net调用EXCEL,把数据输出的Excel里面显示,在窗体上点击“输出Excel”按钮,打开Excel,显示输出的数据,当关闭Execl时,Excel关闭,同时杀死相应的EXCEL进程。
把这些操作封装成一个组件,以上“打开Excel显示数据”作为组件的一个方法,关闭Excel时杀死Excel进程EXCEL.EXE(而不是关闭调用Excel的程序时才杀死进程)作为组件的一个事件(关闭Excel工作前事件BeforeBookClose),当然还可以添加保存Excel(弹出保存对话框SaveFileDialog)打印Excel等功能,添加Excel单元数据改变事件等。这里只实现以上两个功能。
环境为vs2010
1.添加组件Compent,命名为ExcelComp,添加微软Excel组件引用,添加引用对话款的.net页里的Microsoft.Office.Interop.Excel。using引用中添加如下
using System.Windows.Forms; //用到MessageBox.Show(),显示异常信息
using Excel = Microsoft.Office.Interop.Excel; //引用添加的微软Excel组件
using System.Reflection; //用到Mssing.Value,作为一些函数的默认参数
using System.Runtime.InteropServices; //用到GetWindowThreadProcessId,获得进程ID
using System.Data; //用到DataTable,传给Excel的数据
组件中定义成员变量Excel.Application xlApp;//excel应用;
public event Excel.AppEvents_WorkbookBeforeCloseEventHandler EventDel_BeforeBookClose;//EXCEL关闭前事件句柄;
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);//获得进程ID外部函数;
构造函数中xlApp = new Excel.Application();
new Excel.AppEvents_WorkbookBeforeCloseEventHandler(BeforeBookClose);
xlApp.WorkbookBeforeClose += EventDel_BeforeBookClose;//订阅Excel工作薄关闭前处理事件
/// 打开Excel
/// <param name="strName">数据表的名称</param>
/// <param name="dt">数据表中的数据</param>
public void OpenExcel(string strName, DataTable dt)
{
int nCol = dt.Columns.Count;
int nRow = dt.Rows.Count;
Excel._Workbook xlBook;
Excel._Worksheet xlSheet;
Excel.Range xlRng;
try
{
xlApp.Visible = true;
xlBook = (Excel._Workbook)(xlApp.Workbooks.Add(Missing.Value));
xlSheet = (Excel._Worksheet)xlBook.ActiveSheet;
xlSheet.Cells[1, 1] = strName;
xlRng = (Excel.Range)xlSheet.Cells[1, 1];
//字体设置
xlRng.Font.Name = "华文新魏";
xlRng.Font.Bold = true;
xlRng.Font.Size = 26;
//合并单元格
xlSheet.Range[xlSheet.Cells[1, 1], xlSheet.Cells[1, nCol]].MergeCells = true;
//写入字段
for (int i = 0; i < nCol; i++)
{
xlSheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
xlRng = (Excel.Range)xlSheet.Cells[2, i + 1];
//字体设置
xlRng.Font.Name = "黑体";
xlRng.Font.Size = 14;
}
//写入数值
for (int r = 0; r < nRow; r++)
{
for (int i = 0; i < nCol; i++)
{
xlSheet.Cells[r + 3, i + 1] = dt.Rows[r][i];
}
}
//自适应调行宽
xlRng = xlSheet.Range[xlSheet.Cells[1, 1], xlSheet.Cells[nRow + 2, nCol]];
xlRng.EntireColumn.AutoFit();
//上下居中
xlRng.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//左右居中
xlRng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//画边线
xlRng = xlSheet.Range[xlSheet.Cells[2, 1], xlSheet.Cells[nRow + 2, nCol]];
xlRng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
//确保Excel可见,其控制权交给用户
xlApp.Visible = true;
xlApp.UserControl = true;
}
//关闭Excel事件
public void BeforeBookClose(Excel._Workbook Wb, ref bool Cancel)
{
try
{
//获取Excel App的句柄
IntPtr hwnd = new IntPtr(xlApp.Hwnd);
//定义进程ID变量
int lpdwProcessId;
//通过Windows API获取Excel进程ID
GetWindowThreadProcessId(hwnd, out lpdwProcessId);
if (xlApp != null)
{
//通过进程ID,找到进程
Process process = Process.GetProcessById(lpdwProcessId);
//Kill 进程
process.Kill();
}
}
catch (Exception ex)
{
String errorMessage;
errorMessage = "Delete Excel Process Error:";
errorMessage = String.Concat(errorMessage, ex.Message);
MessageBox.Show(errorMessage, "Error");
}
xlApp.WorkbookBeforeClose -= EventDel_BeforeBookClose;
}