开发者

C#实现二维数据数组导出到Excel的详细过程

目录
  • 功能需求
  • 范例运行环境
  • Excel DCOM 配置
  • 设计实现
    • 组件库引入
    • 方法设计
    • 生成二维数据数组
    • 核心方法实现
    • 调用示例
  • 总结

    功能需求

    将数据库查询出来的数据导出并生成 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的对应配置名称如下图所示:

    C#实现二维数据数组导出到Excel的详细过程

    设计实现

    组件库引入

    C#实现二维数据数组导出到Excel的详细过程

    方法设计

    序号参数名类型说明
    1_filenamestringExcel 模板文件的全路径信息
    2dataobjobject[,]生成的二维数据数组
    3ActiveSheetIdint指定要导出的活动的SHEETID,序号从1开始
    4StartRowIdint指定数据导出的开始行ID,序号从1开始
    5StartColIdint指定数据导出的开始列ID,序号从1开始
    6_replsstring[,]

    在EXCEL模板文件里的查找且替换数组,维度1为 key ,维度2 为 value ,系统会根据提供的数组key在模板文件进行查找,并替换对应的 value 值,例如:

    string[,] _repls=new string[1,2];

    _repls[0,0]="模板标题 key "; 

    _rwww.devze.comepls[0,1]="实际输出的标题值 value";

    7drawtypeint

    该值包括0和1。

    0:从原始指定起始位置覆盖粘贴数据

    1:从原始指定起始位置插入粘贴数据

    8AllDataAsStringbool默认为 false,是否将所有数据以文本的形式进行输出
    9DynamicColsbool默认为false,是否按照二维数据数组动态输出行与列
    10DynamicColCfgArrayList

    一个对各列进行配置的参数,每个项至少为两个object(一个为列名,一个为列宽),第三个为数据格式(如文本、数值等),例如:

    ArrayList cfg = new ArrayList();

    string _cname = "列名1";

    string _width = "-1";   //-1 表示自动适应列宽

    cfg.Add(new object[] { _cname, _width });

    11StartAddressstring对 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=falpythonse;
    			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.Apythonctivate();
                
    			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;
    		php	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"),如下图:

    C#实现二维数据数组导出到Excel的详细过程

    如图  <%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.PhysicalApplicatiopythonnPath + "\\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地址。 

    总结

    为保持兼容性,本方法支持旧版本的Word97-2003格式,如需要突破65536行限制,我们可以根据实际需要进行设计调整。

    本方法支持数据输出行样式的持续复制,即我们可以设置单行样式(如字体大小、颜色、边框等),方法会根据数据行数,循环复制样式进行行输出 。

    我们在此仅根据实际项目需要,讲述了一些导出数据到Excel的参数需求,这里仅作参考,欢迎大家评论指教!

    0

    上一篇:

    下一篇:

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    最新开发

    开发排行榜