using System;
using System.Collections.Generic;
using System.Text;
using Excel=Interop.Excel;
using System.IO;
using System.Diagnostics;
namespace DataClass
{
public class OperatorExcel
{
/// <summary>
/// Excel应用程序
/// </summary>
Excel._Application appExcel = null;
/// <summary>
/// Excel工作簿
/// </summary>
Excel._Workbook workBook = null;
/// <summary>
/// Excel工作表
/// </summary>
Excel._Worksheet workSheet = null;
object oMissing = System.Reflection.Missing.Value;
/// <summary>
/// 创建WorkBook
/// </summary>
/// <param name="excelPath">Excel路径</param>
public OperatorExcel(string excelPath)
{
new FileInfo(excelPath.ToString()).Attributes = FileAttributes.Normal;
appExcel = new Excel.Application();
appExcel.Application.Workbooks.Open(excelPath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
workBook = appExcel.Workbooks[1];
}
/// <summary>
/// 获得进程的标识
/// </summary>
/// <param name="hwnd">IntPtr</param>
/// <param name="ID">int</param>
/// <returns>int</returns>
[System.Runtime.InteropServices.DllImport("User32.dll", CharSet = System.Runtime.InteropServices.CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
/// <summary>
/// 关闭Excel进程
/// </summary>
public void SkillExcel()
{
if (appExcel == null)
{
return;
}
IntPtr t = new IntPtr(appExcel.Hwnd);
int k = 0;
AppCommon.GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process ps = System.Diagnostics.Process.GetProcessById(k);
ps.Kill();
}
/// <summary>
/// 关闭Excel
/// </summary>
public void CloseExcelApp()
{
if (workBook == null) return;
workBook.Close(true, oMissing, oMissing);
appExcel.Quit();
}
/// <summary>
/// 另存为Excel
/// </summary>
/// <param name="newExcelName">Excel路径</param>
public void SaveAsExcel(string newExcelName)
{
if (workBook == null) return;
appExcel.Application.DisplayAlerts = false;
workBook.SaveAs(newExcelName, oMissing, oMissing, oMissing, oMissing, oMissing, Interop.Excel.XlSaveAsAccessMode.xlNoChange, oMissing, oMissing, oMissing, oMissing, oMissing);
}
/// <summary>
/// 保存Excel
/// </summary>
public void SaveExcel()
{
if (workBook == null) return;
workBook.Save();
}
/// <summary>
/// 把数据写入Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="sheetName">工作表名</param>
/// <param name="newIndex">工作表行索引</param>
public void DataToExcel(System.Data.DataTable dt,string sheetName,int newIndex)
{
//存在Sheet页为true,不存在为false
bool flag = false;
foreach (Excel.Worksheet sheet in workBook.Worksheets) //获取工作表
{
if (sheet.Name.Trim() == sheetName.Trim())
{
workSheet = sheet;
flag = true;
break;
}
}
if (flag == false)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);
workSheet.Name = sheetName;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
workSheet.Cells[newIndex + i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
workSheet = null;
}
}
}
C#OperatorExcel
最新推荐文章于 2018-08-15 17:19:24 发布