C# COM组件操作Excel

一、使用微软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
阅读更多
文章标签: excel c# dictionary
个人分类: Microsoft Windows开发
上一篇C# NPOI操作Office
下一篇VBA 读取文本文件
想对作者说点什么? 我来说一句

COM组件读取、保存等Excel操作

2017年02月24日 7KB 下载

没有更多推荐了,返回首页

关闭
关闭