对C#操作Excel常用的功能进行整理与总结并封装成类

本文介绍了一种利用C#操作Excel文件的方法,包括读取数据到DataSet、获取所有Sheet名称、创建及保存Excel文件等功能,并提供了针对不同Excel版本的兼容性解决方案。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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不能成块的加入批注。


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值