功能需求
将数据库查询出来的数据导出并生成 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转二维数组 章节部分。
核心方法实现
代码如下：
代码语言：javascript复制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 "bfileexcel模板.xlsx"），如下图：
如图 <%system.excel.title.dyna.by.craneoffice%> ，表示要替换的标题 key ，下面的二维表格，表示预设好的输出列，下面的行即为数据输出行，在这里，我们预设要从第1列第5行输出数据。以下是调用的示例代码：
代码语言：javascript复制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的参数需求，这里仅作参考，欢迎大家评论指教！
