public static void CloseExcel(Application excel)
{
excel.ActiveWorkbook.Close();
excel.DisplayAlerts = false;
excel.Quit();
KillExcel.Kill(new IntPtr(excel.Hwnd));
}
private static void ActionExcelRead(string filename, Action <Workbook> action)
{
//引用Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
// if equal null means EXCEL is not installed.
Console.WriteLine("Excel is not properly installed!");
return;
}
//设置为不可见，操作在后台执行，为 true 的话会打开 Excel
excel.Visible = false;
//打开时设置为全屏显式
//excel.DisplayFullScreen = true;
//初始化工作簿
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
if (File.Exists(filename))
{
workbook = workbooks.Open(filename, 0, false, 5, "", "", true, XlPlatform.xlWindows, "\t", false,
false, 0, true, 1, 0);
}
try
{
action(workbook);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
workbook.Close(false, Type.Missing, Type.Missing);
workbooks.Close();
//关闭退出
excel.Quit();
KillExcel.Kill(new IntPtr(excel.Hwnd));
//释放 COM 对象
//Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excel);
//worksheet = null;
workbook = null;
workbooks = null;
excel = null;
GC.Collect();
}
}
public void CleanHelper()
{
KillExcel.Start();
CleanDataHelper.DeleteReports();
Delay.Seconds(1);
}
private static void ActionExcelWrite(string filename, Action <Workbook> action)
{
//引用Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
// if equal null means EXCEL is not installed.
Console.WriteLine("Excel is not properly installed!");
return;
}
//设置为不可见，操作在后台执行，为 true 的话会打开 Excel
excel.Visible = false;
//打开时设置为全屏显式
//excel.DisplayFullScreen = true;
//初始化工作簿
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
//新增加一个工作簿，Add（）方法也可以直接传入参数 true
workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//同样是新增一个工作簿，但是会弹出保存对话框
//Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
//新增加一个 Excel 表(sheet)
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet) workbook.Worksheets[1];
try
{
action(workbook);
//是否提示，如果想删除某个sheet页，首先要将此项设为fasle。
excel.DisplayAlerts = false;
//保存写入的数据，这里还没有保存到磁盘
workbook.Saved = true;
//workBook.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
if (File.Exists(filename))
{
File.Delete(filename);
}
workbook.SaveAs(filename.Replace("/", "\\"));
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
workbook.Close(false, Type.Missing, Type.Missing);
workbooks.Close();
//关闭退出
excel.Quit();
KillExcel.Kill(new IntPtr(excel.Hwnd));
//释放 COM 对象
//Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excel);
//worksheet = null;
workbook = null;
workbooks = null;
excel = null;
GC.Collect();
}
}