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

性能问题

将生成或查询到的数据，导出到 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