using Excel = Microsoft.Office.Interop.Excel;//Microsoft.Office.Interop.Excel.DLL
using System.Runtime.InteropServices;
/// <summary>
/// 合并Excel
/// </summary>
internal class MerageExcel
{
/// <summary>
/// 合并Excel文件到同一文件中(多个sheet)
/// </summary>
/// <param name="sourceFiles"></param>
/// <param name="destFile"></param>
public static void DoMerge(string[] sourceFiles, string destFile)
{
//实现:将所有Excel文件中的所有sheet合并到集合中的第一个Excel文件中,然后将该文件另存为目标文件
//创建Excel
Excel.Application xlApp = new Excel.ApplicationClass();
//目标Excel
Excel.Workbook destBook = null;
try
{
xlApp.Visible = false;
object m_objOpt = Missing.Value;
//要合并的Excel
Excel.Workbook combineBook;
Excel.Worksheet xlSheet;
//destBook = xlApp.Workbooks.Add(m_objOpt);
foreach (String file in sourceFiles)
{
if (!File.Exists(file))
continue;
if (destBook == null)
{
destBook = xlApp.Workbooks.Open(file, m_objOpt, m_objOpt, m_objOpt
, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt
, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
}
else
{
combineBook = xlApp.Workbooks.Open(file, m_objOpt, m_objOpt, m_objOpt
, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt
, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
for (int i = 1; i <= combineBook.Worksheets.Count; i++)
{
xlSheet = combineBook.Worksheets[i] as Excel.Worksheet;
xlSheet.Copy(m_objOpt, destBook.Worksheets[destBook.Worksheets.Count]);
}
combineBook.Close(false, m_objOpt, m_objOpt);
}
}
if (destBook != null)
{
//激活第一个显示的sheet
if (destBook.Worksheets.Count > 0)
{
for (int i = 1; i <= destBook.Worksheets.Count; i++)
{
xlSheet = destBook.Worksheets[i] as Excel.Worksheet;
if (xlSheet.Visible == Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible)
{
xlSheet.Activate();
break;
}
}
}
destBook.SaveCopyAs(destFile);
destBook.Close(false, m_objOpt, m_objOpt);
}
}
finally
{
//xlApp.Quit(); //此方法Win7下有问题
KillExcel(xlApp);
}
}
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void KillExcel(Microsoft.Office.Interop.Excel.Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄,具体作用是得到这块内存入口
int k = 0;
GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
p.Kill(); //关闭进程k
}
}