参考了网上查找的一些资料,整理了一下工作所需的C#Excel文件操作,仅针对数据操作,而不针对Excel表样式操作
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
namespace ClassLibraryCollection
{
public class ExcelOperateClass
{
Excel.Application excelApp = new Excel.Application();
Excel.Worksheet ActiveSheet = new Worksheet();
Excel.Workbook xlsWorkBook = null;
public ExcelOperateClass()
{
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excelApp.DisplayAlerts = false;
excelApp.Visible = false;
excelApp.ScreenUpdating = false;
}
public Excel.Workbook OpenExcelBybook(string OpenPath)
{
xlsWorkBook = excelApp.Workbooks.Open(OpenPath, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
return xlsWorkBook;
}
public Excel.Worksheet OpenExcelBysheet(string OpenPath)
{
xlsWorkBook = excelApp.Workbooks.Open(OpenPath, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
ActiveSheet = (Worksheet)xlsWorkBook.Sheets.get_Item(1);
return ActiveSheet;
}
public Excel.Worksheet OpenExcelBysheet(string OpenPath, int CNum)
{
xlsWorkBook = excelApp.Workbooks.Open(OpenPath, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
ActiveSheet = (Worksheet)xlsWorkBook.Sheets.get_Item(CNum);
return ActiveSheet;
}
public void InsertLeftCol()
{
Excel.Range xlsColumns = (Excel.Range)ActiveSheet.Columns[1, System.Type.Missing];
Excel.Range rng;
rng = ActiveSheet.get_Range("A:A", "A:A");
rng.Insert(Excel.XlDirection.xlToRight, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
rng = (Microsoft.Office.Interop.Excel.Range)ActiveSheet.Columns[12, Type.Missing];
rng.Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
public void DeletRow(int x)
{
Range deleteRng = (Range)ActiveSheet.Rows[x, System.Type.Missing];
deleteRng.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
public void DeletCol(int y)
{
((Excel.Range)ActiveSheet.Cells[1, y]).EntireColumn.Delete(0);
}
public void SetRangeToText(string RanStar, string RanEnd)
{
Microsoft.Office.Interop.Excel.Range range1 = ActiveSheet.get_Range(RanStar, RanEnd);
range1.NumberFormat = "@";
}
public void ReplaceData(string RangeStar, string RangeEnd, string NeedRepla, string Repla)
{
Range Drng = ActiveSheet.get_Range(RangeStar, RangeEnd);
Drng.Replace(NeedRepla, Repla, XlLookAt.xlPart, XlSearchOrder.xlByColumns, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing);
Drng.TextToColumns(Drng, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierSingleQuote,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing);
}
public Excel.Range FindData(string RanStae, string RanEnd, string FindStr)
{
Range rng = ActiveSheet.get_Range(RanStae, RanEnd).Find(FindStr, Type.Missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);
return rng;
}
public void SaveAs(string SavePath)
{
xlsWorkBook.SaveAs(SavePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
public void CloseExcel()
{
xlsWorkBook.Close(false, Type.Missing, Type.Missing);
xlsWorkBook = null;
excelApp.Quit();
GC.Collect();
KeyMyExcelProcess.Kill(excelApp);
}
public class KeyMyExcelProcess
{
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
{
try
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
catch (System.Exception ex)
{
throw ex;
}
}
}
public void Save()
{
xlsWorkBook.Save();
}
}
}