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的对应配置名称如下图所示:
设计实现
组件库引入
方法设计
序号 | 参数名 | 类型 | 说明 |
---|---|---|---|
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 string[1,2]; _repls[0,0]="模板标题 key "; _rwww.devze.comepls[0,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=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"),如下图:
如图 <%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的参数需求,这里仅作参考,欢迎大家评论指教!
精彩评论