C# 快速将数据写入 Excel 单元格

初九之潜龙勿用

发布于 2024-06-20 13:20:27

1060

发布于 2024-06-20 13:20:27

文章被收录于专栏：技术文章

性能问题

将生成或查询到的数据，导出到 Excel 是应用中常用的一项功能。其中一些标准的写入单元格的方法如下：

Worksheet worksheet = (Worksheet) excel.Worksheets[1];

//引用坐标对Cells集合进行写入

worksheet.Cells[1, 1] = "test";

//还可以引用地址进行写入

worksheet.Cells["A1",Type.Missing]="test";

请注意，office 元素大多的起始序号均从 1 开始，而不是 0，如 worksheet，cells等。

现在我们假设有二维数组 object[,] dataobj ，遍历式的写法如下：

int arraywidth=dataobj.GetLength(1);
int arrayheight=dataobj.GetLength(0);
Worksheet worksheet = (Worksheet) excel.Worksheets[1];

for(int j=0;j<arrayheight;j++)
{
	for(int k=0;k<arraywidth;k++)
	{
		worksheet.Cells[j+1,k+1]=dataobj[j,k]; 
	}//end columns
}// end rows

此种写法在数据较少的时候在写入速度方面没有明显差异，但当大数据量的情况，多次的读写CELL单元格 IO 是一项非常耗时的操作，甚至会无法完成应用的预期目标。

Excel元素结构及写入原理

元素结构如下图：

其中Range对象，代表了Excel单元格集合的指定区域。

如图选中的Range范围起始的单元格是第5行第3列，结束于第17行第8列。它相当于object[13,6] 的一个二维数组，Excel的Range提供了get_Resize方法并通过Value2可以一次性的设置它们。

范例运行环境

操作系统： Windows Server 2019 DataCenter

操作系统上安装 Office Excel 2016

.net版本： .netFramework4.7.1 或以上

开发工具：VS2019 C#

配置Office DCOM

配置方法可参照我的文章《C# 读取Word表格到DataSet》进行处理和配置。

实现代码

组件库引入

核心代码

WriteArrayToExcel

public void WriteArrayToExcel(object[,] dataobj,ExcelApplication excel,int ActiveSheetId,int StartRowId,int StartColId),该方法参数说明见下表：

序号	参数	说明
1	object[,] dataobj	传递要写入的二维对象数组
2	ExcelApplication excel	表示Excel应用程序对象
3	int ActiveSheetId	指定要写入哪个工作ID，ID从1开始
4	int StartRowId	指定要写入的开始行ID，ID从1开始
5	int StartColId	指定要写入的开始列ID，ID从1开始

示例代码如下：

public void WriteArrayToExcel(object[,] dataobj,ExcelApplication excel,int ActiveSheetId,int StartRowId,int StartColId)
{
			Excel.Range _range;
			int arraywidth=dataobj.GetLength(1);
			int arrayheight=dataobj.GetLength(0);
			Worksheet worksheet = (Worksheet) excel.Worksheets[ActiveSheetId];


			worksheet.Activate();
			_range=excel.Range[excel.Cells[StartRowId,StartColId],excel.Cells[StartRowId+arrayheight-1,StartColId+arraywidth-1]];
			_range.get_Resize(arrayheight,arraywidth);
			_range.Value2=dataobj;

} //end writearraytoexcel

神奇的 911 事件

在 Excel 的早期版本（如Excel 2003）写入的时，我们发现了一个现象，每写入第 911个单元格的时候，且第911个单元数据为字符串类型的时候，会出现无响应的情况。当时的解决方案是将原始数组的每911元素值进行备份，并设置为空字符串，然后再对Cells单元格进行重写，修改后的代码如下：

public void WriteArrayToExcel(object[,] dataobj,ExcelApplication excel,int ActiveSheetId,int StartRowId,int StartColId)
		{
			Excel.Range _range;
			int arraywidth=dataobj.GetLength(1);
			int arrayheight=dataobj.GetLength(0);
			Worksheet worksheet = (Worksheet) excel.Worksheets[ActiveSheetId];


			worksheet.Activate();

			ArrayList ex_x = new ArrayList();
			ArrayList ex_y = new ArrayList();
			ArrayList ex_value = new ArrayList();

			object _fvalue="";
			int _maxlen=910;				
			for(int j=0;j<arrayheight;j++)
			{
				for(int k=0;k<arraywidth;k++)
				{
					_fvalue=dataobj[j,k];// field value
//备份每个第911个单元数据
					if(_fvalue.GetType().ToString()=="System.String")
					{
						if(((string)_fvalue).Length>_maxlen)
						{
							ex_x.Add(j+StartRowId);
							ex_y.Add(k+StartColId);
							ex_value.Add(_fvalue);
							_fvalue="";
						}// end maxlen 
					}
					dataobj[j,k]=_fvalue;
				}//end columns
			}// end rows 
			_range=excel.Range[excel.Cells[StartRowId,StartColId],excel.Cells[StartRowId+arrayheight-1,StartColId+arraywidth-1]];
			_range.get_Resize(arrayheight,arraywidth);
			_range.Value2=dataobj;
//恢复重写对应的单元格
			for(int j=0;j<ex_value.Count;j++)
			{
				excel.Cells[ex_x[j],ex_y[j]]=ex_value[j].ToString();
			}
} //end writearraytoexcel