using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Diagnostics;
using System.Text;
using System.Data.OleDb;
using Microsoft.Office;
using Microsoft.Office.Interop.Excel;
using System.Text.RegularExpressions;
namespace Simulator.FWTSOperation
{
class ExcelOperate
{
//--------------------------------------------------先将excel中数据取到dataset中------------------------------------------------------------------------------------------------------------
public void LoadDataToDatasetFromExcel(string filePath, String SheetName, ref DataSet DataFromExcel)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
if (SheetName != null)
{
String sql = "SELECT * FROM [" + SheetName + "] ";
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
OleDaExcel.Fill(DataFromExcel, SheetName.Split('$')[0]);
}
else
{
;
}
OleConn.Close();
}
public void ReadAllSheetNameListFromExcel(string filePath, ref System.Data.DataTable SheetNameList)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
SheetNameList = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "Table" });
OleConn.Close();
}
//--------------------------------------------------直接操作Excel------------------------------------------------------------------------------------------------------------
public void CreateAndSaveExcel(string SaveFilePath, string ExcelName)
{
//创建Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//添加工作簿
Microsoft.Office.Interop.Excel.Workbook NewWorkBook = excel.Application.Workbooks.Add(true);
//获取缺省的object类型
object missing = System.Reflection.Missing.Value;
NewWorkBook.Worksheets.Add(missing, missing, missing, missing);
if (SaveFilePath.EndsWith("\\"))
{
//保存文件
NewWorkBook.SaveCopyAs(SaveFilePath + ExcelName + ".xls");
}
else
{
//保存文件
NewWorkBook.SaveCopyAs(SaveFilePath + "\\" + ExcelName + ".xls");
}
System.Diagnostics.Process[] excelProcess = System.Diagnostics.Process.GetProcessesByName("EXCEL");
//关闭EXCEL进程
foreach (System.Diagnostics.Process p in excelProcess)
{
p.Kill();
}
}
public void OpenAndGetWorkBookFromExcel(string filePath, out _Workbook MyWorkBook, out Microsoft.Office.Interop.Excel.Application MyExcel)
{
Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
MyExcel = m_objExcel;
Microsoft.Office.Interop.Excel.Workbooks m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
m_objBooks.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
MyWorkBook = m_objBooks.get_Item(1);
}
public void AddWorkSheetForExcel(_Workbook MyWorkBook, string WorkSheetName)
{
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Worksheet NewWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Worksheets.Add(missing, missing, 1, missing);
NewWorkSheet.Name = WorkSheetName;
MyWorkBook.Save();
MyWorkBook.Close(false, missing, missing);
}
public void DelWorkSheetForExcel(_Workbook MyWorkBook, string WorkSheetName)
{
object missing = System.Reflection.Missing.Value;
((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[WorkSheetName]).Delete();
MyWorkBook.Save();
MyWorkBook.Close(false, missing, missing);
}
public void WriteStringtoExcelCell(_Workbook MyWorkBook, String SheetName, string Writestring, int x, int y)
{
((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x, y] = Writestring;
}
public void ChangeColorOfExcelCell(_Workbook MyWorkBook, String SheetName, int MyColorIndex, int x, int y)
{
((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).get_Range(((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x, y],
((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x, y]).Cells.Interior.ColorIndex = MyColorIndex;
}
public void WriteCommenttoExcel(_Workbook MyWorkBook , String SheetName, string WriteString , int x, int y)
{
Range range = ((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).get_Range(((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x, y], ((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x, y]);
range.ClearComments();
range.AddComment(WriteString);
}
public void ClearCommentOfExcel(_Workbook MyWorkBook, String SheetName, int x, int y)
{
Range range = ((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).get_Range(((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x, y], ((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x, y]);
range.ClearComments();
}
public void SaveAndQuitWorkBookExcel(_Workbook MyWorkBook , Microsoft.Office.Interop.Excel.Application MyExcel)
{
MyWorkBook.Save();
MyWorkBook = null;
MyWorkBook = null;
MyExcel.Quit();
MyExcel = null;
KillExcelProcess();
}
//--------------------------------------------------内部Private函数------------------------------------------------------------------------------------------------------------
private void KillExcelProcess()
{
System.Diagnostics.Process[] myProcesses;
myProcesses = System.Diagnostics.Process.GetProcessesByName("Excel");
}
}
}
/**************************************************************************************************************************************************/
通过工作中总结常用的,对C#操作Excel一些常用的功能进行了封装,大家可以直接实例化该类,调用里面方法进行使用。
下面是对各个方法的说明:
① 间接Excel操作
1.将Excel中数据导入DateSet中
public void LoadDataToDatasetFromExcel(string filePath,StringSheetName,refDataSetDataFromExcel) filePath:文件路径;
SheetName:要导入的Excel sheet名;
DataFromExcel:带回导入后的DateSet;
2.获取Excel所有的Sheet名
public void ReadAllSheetNameListFromExcel(string filePath,refSystem.Data.DataTable SheetNameList)
filePath:文件路径;
SheetNameList:带回存储所有Sheet名称的List;
② 直接Excel操作
1.打开并得到Excel的WorkBook
public void OpenAndGetWorkBookFromExcel(string filePath,out_Workbook MyWorkBook,outMicrosoft.Office.Interop.Excel.ApplicationMyExcel)
filePath:文件路径;
MyWorkBook:带回得到的WorkBook。
MyExcel:带回Microsoft.Office.Interop.Excel;
2.写字符串到指定的Excel单元格
public void WriteStringtoExcelCell(_WorkbookMyWorkBook,String SheetName,string Writestring,intx,int y)
MyWorkBook:指定Excel的WorkBook;
SheetName :要操作的Excel sheet名;
Writestring :要写的字符串;
x:单元格的横坐标;
y:单元格的纵坐标;
3.改变指定的Excel单元格颜色
public void ChangeColorOfExcelCell(_WorkbookMyWorkBook,String SheetName,int MyColorIndex,intx,int y)
MyWorkBook:指定Excel的WorkBook;
SheetName :要操作的Excel sheet名;
MyColorIndex:颜色代号;
x:单元格的横坐标;
y:单元格的纵坐标;
4.给指定的Excel单元格加入备注
public void WriteCommenttoExcel(_WorkbookMyWorkBook ,String SheetName,string Writestring,intx,int y)
MyWorkBook:指定Excel的WorkBook;
SheetName :要操作的Excel sheet名;
Writestring:要写的字符串;
x:单元格的横坐标;
y:单元格的纵坐标;
5.删除指定的Excel单元格备注
public void ClearCommentOfExcel(_WorkbookMyWorkBook,String SheetName,int x,int y)
MyWorkBook:指定Excel的WorkBook;
SheetName :要操作的Excel sheet名;
x:单元格的横坐标;
y:单元格的纵坐标;
6.退出并保存Excel
public voidSaveAndQuitWorkBookExcel(_WorkbookMyWorkBook, Microsoft.Office.Interop.Excel.Application MyExcel)
MyWorkBook:指定Excel的WorkBook;
MyExcel:指定的Microsoft.Office.Interop.Excel;
7.创建并保存Excel
public void CreateAndSaveExcel(stringSaveFilePath,string ExcelName)
SaveFilePath:要保存的Excel路径;
ExcelName:要创建的Excel名称;
8.添加工作表
public void AddWorkSheetForExcel(_WorkbookMyWorkBook,string WorkSheetName)
MyWorkBook: 指定Excel的WorkBook;
WorkSheetName:要添加的工作表名称;
9.删除工作表
public void DelWorkSheetForExcel(_WorkbookMyWorkBook,string WorkSheetName)
MyWorkBook: 指定Excel的WorkBook;
WorkSheetName:要删除的工作表名称;
Note:如果操作Excel2007可将下面代码
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
改为:
strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=False;IMEX=1'";
需要修改的俩个函数代码如下:
public void LoadDataToDatasetFromExcel(string filePath, String SheetName, ref DataSet DataFromExcel)
{
string strConn;
if (0 == string.Compare(filePath.Substring(filePath.Length - 4, 4), ".xls"))
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
}
else// (0 == string.Compare(filePath.Substring(filePath.Length - 4, 4), "xlsx"))
{
strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=False;IMEX=1'";
}
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
if (SheetName != null)
{
String sql = "SELECT * FROM [" + SheetName + "] ";
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
OleDaExcel.Fill(DataFromExcel, SheetName.Split('$')[0]);
}
else
{
;
}
OleConn.Close();
}
public void ReadAllSheetNameListFromExcel(string filePath, ref System.Data.DataTable SheetNameList)
{
string strConn;
if (0 == string.Compare(filePath.Substring(filePath.Length - 4, 4), ".xls"))
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
}
else// (0 == string.Compare(filePath.Substring(filePath.Length - 4, 4), "xlsx"))
{
strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=False;IMEX=1'";
}
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
SheetNameList = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "Table" });
OleConn.Close();
}
为了实现成块写入数据重写原ExcelOperate类中的Excel处理方法.
重写后的方法如下:
public void WriteStringtoExcelCell(_Workbook MyWorkBook, String SheetName, string[,] Writestring, int x1, int y1, int x2, int y2)
{
((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).get_Range(((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x1, y1], ((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x2, y2]).Value2 = Writestring;
}
public void ChangeColorOfExcelCell(_Workbook MyWorkBook, String SheetName, int MyColorIndex, int x1, int y1, int x2, int y2)
{
((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).get_Range(((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x1, y1],
((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x2, y2]).Cells.Interior.ColorIndex = MyColorIndex;
}
public void ClearCommentOfExcel(_Workbook MyWorkBook, String SheetName, int x1, int y1, int x2, int y2)
{
Range range = ((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).get_Range(((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x1, y1], ((Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[SheetName]).Cells[x2, y2]);
range.ClearComments();
}
注:经实际验证发现C#操作Excel不能成块的加入批注。