当前位置:首页 » 《关注互联网》 » 正文

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

14 人参与  2024年09月10日 08:02  分类 : 《关注互联网》  评论

点击全文阅读


目录

功能需求

范例运行环境

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的对应配置名称如下图所示:

1072f229d76a5715dcb6542d89b41c06.png

  

设计实现

组件库引入

796f3a082e903b296b62ca33d4d5dcd8.png

方法设计

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

序号参数名类型说明
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 "; 

_repls[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=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 valueif(_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"),如下图:

39923a9f7e9a43e2939112cca8ee9a66.png

如图  <%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地址。 

 

总结

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

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

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

 


点击全文阅读


本文链接:http://zhangshiyu.com/post/157596.html

<< 上一篇 下一篇 >>

  • 评论(0)
  • 赞助本站

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

关于我们 | 我要投稿 | 免责申明

Copyright © 2020-2022 ZhangShiYu.com Rights Reserved.豫ICP备2022013469号-1