关闭

C# COM组件操作Excel

标签: excelc#dictionary
343人阅读 评论(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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:24937次
    • 积分:286
    • 等级:
    • 排名:千里之外
    • 原创:9篇
    • 转载:3篇
    • 译文:0篇
    • 评论:4条
    最新评论