C# 实现二维数据数组导出到 Excel

初九之潜龙勿用

发布于 2024-09-11 08:27:44

1270

发布于 2024-09-11 08:27:44

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

功能需求

将数据库查询出来的数据导出并生成 Excel 文件，是项目中经常使用的一项功能。本文将介绍通过数据集生成二维数据数组并导出到 Excel。

主要实现如下功能：

1、根据规则设计EXCEL数据导出模板

2、查询数据，并生成 object, 二维数据数组

3、将二维数据数组，其它要输出的数据导出写入到模板 Excel 文件

范例运行环境

操作系统： Windows Server 2019 DataCenter

操作系统上安装 Office Excel 2016

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

开发工具：VS2019 C#

Excel DCOM 配置

请参考我的文章《C# 读取Word表格到DataSet》有对Office DCOM详细配置介绍，这里不再赘述，Excel的对应配置名称如下图所示：

设计实现

组件库引入

方法设计

设计 expExcel 方法实现数据的导出，方法返回生成的全文件路径信息。其调用参数据详细说明见下表：

序号	参数名	类型	说明
1	_filename	string	Excel 模板文件的全路径信息
2	dataobj	object,	生成的二维数据数组
3	ActiveSheetId	int	指定要导出的活动的SHEETID，序号从1开始
4	StartRowId	int	指定数据导出的开始行ID，序号从1开始
5	StartColId	int	指定数据导出的开始列ID，序号从1开始
6	_repls	string,	在EXCEL模板文件里的查找且替换数组，维度1为 key ，维度2 为 value ，系统会根据提供的数组key在模板文件进行查找，并替换对应的 value 值，例如： string, _repls=new string1,2; _repls0,0="模板标题 key "; _repls0,1="实际输出的标题值 value";
7	drawtype	int	该值包括0和1。 0:从原始指定起始位置覆盖粘贴数据 1：从原始指定起始位置插入粘贴数据
8	AllDataAsString	bool	默认为 false，是否将所有数据以文本的形式进行输出
9	DynamicCols	bool	默认为false，是否按照二维数据数组动态输出行与列
10	DynamicColCfg	ArrayList	一个对各列进行配置的参数，每个项至少为两个object（一个为列名，一个为列宽），第三个为数据格式（如文本、数值等），例如： ArrayList cfg = new ArrayList(); string _cname = "列名1"; string _width = "-1"; //-1 表示自动适应列宽 cfg.Add(new object[] { _cname, _width });
11	StartAddress	string	对 StartRowId 参数和 StartColId 参数

生成二维数据数组

如何生成二维数据数组，请参阅我的文章《C# 读取二维数组集合输出到Word预设表格》中的DataSet转二维数组章节部分。

核心方法实现

代码如下：

public string expExcel(string _filename,object[,] dataobj,int ActiveSheetId,int StartRowId,int StartColId,string[,] _repls,int drawtype,bool AllDataAsString,bool DynamicCols,ArrayList DynamicColCfg,string StartAddress)
		{
			string AsString=(AllDataAsString?"'":"");
			string _file="",_path=Path.GetDirectoryName(_filename)+"\\tempbfile\\",_ext="";
			if(!Directory.Exists(_path))
			{
				Directory.CreateDirectory(_path);
			}

			_file=Path.GetFileNameWithoutExtension(_filename);
			_ext=Path.GetExtension(_filename);
			
			string _lastfile=_path+System.Guid.NewGuid()+_ext;
			File.Copy(_filename,_lastfile,true);
			if(!File.Exists(_lastfile))
			{
				return "";
			}
			//取得Word文件保存路径
			object filename=_lastfile;
			//创建一个名为ExcelApp的组件对象
			DateTime beforetime=DateTime.Now;
			Excel.Application excel=new Excel.Application();
			
            
			excel.DisplayAlerts=false;
			excel.AskToUpdateLinks=false;

			excel.Visible=true;
			
			DateTime aftertime=DateTime.Now;


			Excel.Workbook xb=excel.Workbooks.Add(_lastfile);
            
			Worksheet worksheet = (Worksheet) excel.Worksheets[ActiveSheetId];
			sheetCount=excel.Sheets.Count;
			worksheet.Activate();
            
			if(_repls!=null)
			{
				for(int i=0;i<_repls.GetLength(0);i++)
				{
					worksheet.Cells.Replace(_repls[i,0],_repls[i,1],Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); 
				}
			}

			Excel.Range _range;
			Excel.Range srange;
			if(StartAddress!="")
			{
				Excel.Range _range_s=worksheet.Range[StartAddress,StartAddress];
				StartRowId=_range_s.Row;
				StartColId=_range_s.Column;
			}


			int arraywidth=dataobj.GetLength(1);
			int arrayheight=dataobj.GetLength(0);
			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
					if(_fvalue==null)
					{
						continue;
					}
					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.ToString().IndexOf("=")==0?"":AsString)+_fvalue;
				}//end columns
			}// end rows 
			
			if(DynamicCols==true)
			{
				srange=excel.Range[excel.Cells[StartRowId,StartColId],excel.Cells[StartRowId,StartColId]];
				for(int i=1;i<arraywidth;i++)
				{
					_range=excel.Range[excel.Cells[StartRowId,StartColId+i],excel.Cells[StartRowId,StartColId+i]];
					copyRangeStyle(srange,_range);
				}
			}
			
			object _copyheight=excel.Range[excel.Cells[StartRowId,StartColId],excel.Cells[StartRowId,StartColId+arraywidth-1]].RowHeight;

			
			if(drawtype==1)   //取startrow的格式
			{
				_range=excel.Range[excel.Cells[StartRowId+1,StartColId],excel.Cells[StartRowId+arrayheight-1,StartColId]];
				if(arrayheight>1)
				{
					_range.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown,Type.Missing);
				}
				for(int i=0;i<arraywidth;i++)
				{
					srange=excel.Range[excel.Cells[StartRowId,StartColId+i],excel.Cells[StartRowId,StartColId+i]];
					_range=excel.Range[excel.Cells[StartRowId+1,StartColId+i],excel.Cells[StartRowId+arrayheight-1,StartColId+i]];
					copyRangeStyle(srange,_range);
				}
				_range=excel.Range[excel.Cells[StartRowId+1,StartColId],excel.Cells[StartRowId+arrayheight-1,StartColId+arraywidth-1]];
				_range.RowHeight=_copyheight;
			}
			
			_range=excel.Range[excel.Cells[StartRowId,StartColId],excel.Cells[StartRowId+arrayheight-1,StartColId+arraywidth-1]];
			_range.get_Resize(arrayheight,arraywidth);
			_range.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault,dataobj);


			
			for(int j=0;j<ex_value.Count;j++)
			{
				excel.Cells[ex_x[j],ex_y[j]]=ex_value[j].ToString();
			}

			if(DynamicCols==true)
			{
				if(DynamicColCfg!=null)
				{
					for(int j=0;j<DynamicColCfg.Count;j++)
					{
						_range=excel.Range[excel.Cells[StartRowId,StartColId+j],excel.Cells[StartRowId,StartColId+j]];
						object[] cfg=(object[])DynamicColCfg[j];
						string _title=cfg[0].ToString();
						_range.Value2=_title;
						_range=excel.Range[excel.Cells[StartRowId,StartColId+j],excel.Cells[65536,StartColId+j]];
						if(cfg.Length>1)
						{
							int _width=int.Parse(cfg[1].ToString());
							if(_width!=-1)
							{
								_range.ColumnWidth=_width;
							}
							else
							{
                                _range.ColumnWidth = 255;
								_range.Columns.AutoFit();
							}
						}
						if(cfg.Length>2)
						{
							_range.NumberFormatLocal=cfg[2].ToString();
						}
						//NumberFormatlocal						
					}
				}
			}




            if (WritePassword != "")
            {
                xb.WritePassword = WritePassword;
            }
            if (ProtectPassword != "")
            {
                worksheet.Protect(ProtectPassword);
                xb.Protect(ProtectPassword,true,true);

            }
            worksheet.SaveAs(@_lastfile, Missing.Value,WritePassword==""?(object)Missing.Value:(object)WritePassword, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
			xb.Close(null,null,null);
			excel.Workbooks.Close();
			int pid=0;
			IntPtr a = new IntPtr(excel.Parent.Hwnd);
			UInt32[] processId = new UInt32[1];
			GetWindowThreadProcessId((IntPtr)excel.Hwnd,processId);


			excel.Quit();

			if(worksheet != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
				worksheet = null;
			}
			if(xb != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(xb);
				xb = null;
			}
			if(excel != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
				excel = null;
			}
			GC.Collect();
			

			KillProcessByStartTime("EXCEL",beforetime,aftertime);
			

			return _lastfile;

			
		}

public string KillProcessByStartTime(string processName,DateTime beforetime,DateTime aftertime)
		{
			Process[] ps = Process.GetProcesses();
			foreach (Process p in ps)  
			{
				if(p.ProcessName.ToUpper()!=processName) continue;
				if(p.StartTime > beforetime && p.StartTime < aftertime)
				{
					try
					{
						p.Kill();
					}
					catch(Exception e)
					{
						return e.Message;
					}
				}
			}  
			return "";
		}

调用示例

我们设计Web应用中的输出模板（Request.PhysicalApplicationPath + "\bfile\excel\模板.xlsx"），如下图：

如图 <%system.excel.title.dyna.by.craneoffice%> ，表示要替换的标题 key ，下面的二维表格，表示预设好的输出列，下面的行即为数据输出行，在这里，我们预设要从第1列第5行输出数据。以下是调用的示例代码：

object[,] rv = DataSetToOject();    //这个是初始化二维数据数组的

    string[,] _repls = new string[1, 2];
    _repls[0, 0] = "<%system.excel.title.dyna.by.craneoffice%>";
    _repls[0, 1] = "考察对象家庭成员及主要社会关系人基本情况";

    string ModuleFile = Request.PhysicalApplicationPath + "\\bfile\\excel\\模板.xlsx";
    string _lastfile = er.Jree(@ModuleFile, rv, 1, 5, 1, _repls, 1, true, false, null);
    string _url = "/bfile/excel/tempbfile/" + Path.GetFileName(_lastfile);

_lastfile 为最终生成的 excel 数据导出文件全路径地址，_url 为转化的可下载URL地址。