using System;
using System.IO;
using System.Text;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
namespace ImportDatas
{
public class ImportData
{
#region Excel变量们
private string templetFile = null;
private string outputFile = null;
private object missing = Missing.Value;
private DateTime beforeTime;
private DateTime afterTime;
public string name = null;
public string code = null;
Excel.Application app;
Excel.Workbook workBook;
Excel.Worksheet worksheet;
Excel.Range range;
#endregion
public ImportData(String filepath,int sheet)
{
CreatExcel(filepath,sheet);
}
/// <summary>
/// 将一个已经存在的excel读取,并制定输出路径
/// </summary>
/// <param name="templetFilePath">Excel模板文件路径</param>
/// <param name="outputFilePath">输出Excel文件路径</param>
private void CreatExcel(string templetFilePath,int sheet)
{
if (System.IO.File.Exists(templetFilePath))
{
if (System.IO.Path.GetExtension(templetFilePath).ToLower() == ".xls")
{
if (templetFilePath == null)
{
throw new Exception("Excel模板文件路径不能为空!");
}
if (!File.Exists(templetFilePath))
{
throw new Exception("指定路径的Excel模板文件不存在!");
}
name = System.IO.Path.GetFileName(templetFilePath);
code = name.Substring(0, 8);
this.templetFile = templetFilePath;
//创建Application对象并对其可见
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
worksheet = (Excel.Worksheet)workBook.Worksheets[sheet];
if (worksheet.UsedRange.Rows.Count == 0)
{
throw new Exception("文件中没有记录!");
}
}
else
{
throw new Exception("该文件不是Excel文件!");
}
}
else
{
throw new Exception("指定文件不存在!");
}
}
/// <summary>
/// 返回一个excle中取得的数组
/// </summary>
/// <param name="xList">一组行纵标</param>
/// <returns></returns>
public ArrayList GetRowsCellValues(ArrayList xList)
{
ArrayList retvalist=new ArrayList();
foreach (string str in xList)
{
retvalist.Add(GetrowCellvalue(str, str));
}
return retvalist;
}
/// <summary>
/// 添加excle某一个单元格的值
/// </summary>
/// <param name="x">行标志</param>
/// <param name="y">纵标志</param>
/// <returns></returns>
public object GetrowCellvalue(string x,string y)
{
object retval = "";
retval = worksheet.get_Range(x,y).Value2;
return retval;
}
/// <summary>
/// 注销所有对象
/// </summary>
public void close()
{
workBook.Close(null,null,null);
app.Workbooks.Close();
app.Quit();
if (app != null)
{
if (workBook != null)
{
if (worksheet != null)
{
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();
this.KillExcelProcess();
}
/// <summary>
/// 结束Excel进程
/// </summary>
public void KillExcelProcess()
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel进程ID,暂时只能判断进程启动时间
foreach(Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if(startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
}
}
自己写的一个小小框架(根据别人的代码改编)