C# COM组件操作Excel

最新推荐文章于 2024-10-02 10:20:18 发布

LoveFlyingBoy

最新推荐文章于 2024-10-02 10:20:18 发布

阅读量1.3k

点赞数 1

分类专栏： Microsoft Windows开发文章标签： excel c# dictionary

本文链接：https://blog.csdn.net/ecjtuAndy/article/details/46810095

版权

Microsoft Windows开发专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、使用微软COM组件读Excle

将Excle中的变量信息读入varInfoDic, 其定义如下：

Dictionary<int, List<VarInfo>> varInfoDic = new Dictionary<int, List<VarInfo>>();

VarInfo类的定义如下：

public class VarInfo

{

public string varType;

public string varName;

public int varByteOffset;

}

这个读Excle的函数，实现如下：

private void readVarInfoXLSX()
{
	int canId = 0;
	string strCanId;
	string strVarByteOffset;
	Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
	Workbook tempWorkbook = null;
	Worksheet tempSheet = null;
	Range tempRange = null;

	try
	{
		if (varInfoDic.Count != 0)
		{
			varInfoDic.Clear();
		}

		if (canIdValidList.Count != 0)
		{
			canIdValidList.Clear();
		}

		// 打开Excel文件
		tempWorkbook = excelApp.Workbooks.Open(varInfoFile, Missing.Value, Missing.Value, Missing.Value,
			Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
			Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

		excelApp.Visible = false;  //false
		// 获取第一个sheet
		tempSheet = (Worksheet)tempWorkbook.Sheets[1];
		tempSheet.Activate();

		for (int row = varInfoValidStartRow; row <= tempSheet.UsedRange.Rows.Count; row++)
		{
			VarInfo tempVarInfo = new VarInfo();
			tempRange = (Range)tempSheet.Cells[row, 1];
			strCanId = tempRange.Text.ToString().Trim();

			if (!string.IsNullOrWhiteSpace(strCanId))
			{
				canId = Convert.ToInt32(strCanId, 16);
				tempRange = (Range)tempSheet.Cells[row, 2];
				tempVarInfo.varType = tempRange.Text.ToString().Trim();
				if (!canIdValidList.Contains(canId))
				{
					canIdValidList.Add(canId);
				}
				
				tempRange = (Range)tempSheet.Cells[row, 3];
				tempVarInfo.varName = tempRange.Text.ToString().Trim();

				tempRange = (Range)tempSheet.Cells[row, 4];
				strVarByteOffset = tempRange.Text.ToString().Trim();
				tempVarInfo.varByteOffset = Convert.ToInt32(strVarByteOffset);

				if (varInfoDic.ContainsKey(canId))
				{
					List<VarInfo> tempList;
					varInfoDic.TryGetValue(canId, out tempList);
					tempList.Add(tempVarInfo);
				}
				else
				{
					List<VarInfo> tempVarInfoList = new List<VarInfo>();
					tempVarInfoList.Add(tempVarInfo);
					varInfoDic.Add(canId, tempVarInfoList);
				}
			}
			else
			{
				//待加入容错处理
				continue;
			}
		}
	}
	catch (Exception ex)
	{
		MessageBox.Show("Read variable information error! Please do it again!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
		Console.WriteLine("[readVarInfoXLSX] Function Error!");
	}
	finally
	{
		// 释放资源
		tempSheet = null;
		tempWorkbook.Close();
		tempWorkbook = null;
		excelApp.Workbooks.Close();
		excelApp.Quit();
		excelApp = null;
	}
}

二、使用微软COM组件写Excle