C#實現(xiàn)二維數(shù)據(jù)數(shù)組導(dǎo)出到Excel的詳細(xì)過程
更新時間：2024年09月10日 11:24:25 作者：初九之潛龍勿用
將數(shù)據(jù)庫查詢出來的數(shù)據(jù)導(dǎo)出并生成?Excel?文件,是項目中經(jīng)常使用的一項功能,本文將介紹通過數(shù)據(jù)集生成二維數(shù)據(jù)數(shù)組并導(dǎo)出到?Excel,文中有詳細(xì)的代碼供大家參考,需要的朋友可以參考下
功能需求
將數(shù)據(jù)庫查詢出來的數(shù)據(jù)導(dǎo)出并生成 Excel 文件，是項目中經(jīng)常使用的一項功能。本文將介紹通過數(shù)據(jù)集生成二維數(shù)據(jù)數(shù)組并導(dǎo)出到 Excel。
主要實現(xiàn)如下功能：
1、根據(jù)規(guī)則設(shè)計EXCEL數(shù)據(jù)導(dǎo)出模板
2、查詢數(shù)據(jù)，并生成 object[,] 二維數(shù)據(jù)數(shù)組
3、將二維數(shù)據(jù)數(shù)組，其它要輸出的數(shù)據(jù)導(dǎo)出寫入到模板 Excel 文件
范例運行環(huán)境
操作系統(tǒng)： Windows Server 2019 DataCenter
操作系統(tǒng)上安裝 Office Excel 2016
.net版本： .netFramework4.7.2 或以上
開發(fā)工具：VS2019 C#
Excel DCOM 配置
請參考文章《C# 讀取Word表格到DataSet》有對Office DCOM詳細(xì)配置介紹，這里不再贅述，Excel的對應(yīng)配置名稱如下圖所示：
設(shè)計實現(xiàn)
組件庫引入
方法設(shè)計
| 序號 | 參數(shù)名 | 類型 | 說明 |
|---|---|---|---|
| 1 | _filename | string | Excel 模板文件的全路徑信息 |
| 2 | dataobj | object[,] | 生成的二維數(shù)據(jù)數(shù)組 |
| 3 | ActiveSheetId | int | 指定要導(dǎo)出的活動的SHEETID，序號從1開始 |
| 4 | StartRowId | int | 指定數(shù)據(jù)導(dǎo)出的開始行ID，序號從1開始 |
| 5 | StartColId | int | 指定數(shù)據(jù)導(dǎo)出的開始列ID，序號從1開始 |
| 6 | _repls | string[,] | 在EXCEL模板文件里的查找且替換數(shù)組，維度1為 key ，維度2 為 value ，系統(tǒng)會根據(jù)提供的數(shù)組key在模板文件進行查找，并替換對應(yīng)的 value 值，例如： string[,] _repls=new string[1,2]; _repls[0,0]="模板標(biāo)題 key "; _repls[0,1]="實際輸出的標(biāo)題值 value"; |
| 7 | drawtype | int | 該值包括0和1。 0:從原始指定起始位置覆蓋粘貼數(shù)據(jù) 1：從原始指定起始位置插入粘貼數(shù)據(jù) |
| 8 | AllDataAsString | bool | 默認(rèn)為 false，是否將所有數(shù)據(jù)以文本的形式進行輸出 |
| 9 | DynamicCols | bool | 默認(rèn)為false，是否按照二維數(shù)據(jù)數(shù)組動態(tài)輸出行與列 |
| 10 | DynamicColCfg | ArrayList | 一個對各列進行配置的參數(shù)，每個項至少為兩個object（一個為列名，一個為列寬），第三個為數(shù)據(jù)格式（如文本、數(shù)值等），例如： ArrayList cfg = new ArrayList(); string _cname = "列名1"; |
| 11 | StartAddress | string | 對 StartRowId 參數(shù)和 StartColId 參數(shù) |
生成二維數(shù)據(jù)數(shù)組
如何生成二維數(shù)據(jù)數(shù)組，請參閱文章《C# 讀取二維數(shù)組集合輸出到Word預(yù)設(shè)表格》中的DataSet轉(zhuǎn)二維數(shù)組 章節(jié)部分。
核心方法實現(xiàn)
代碼如下：
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;
//創(chuàng)建一個名為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 "";
}調(diào)用示例
我們設(shè)計Web應(yīng)用中的輸出模板（Request.PhysicalApplicationPath + "\\bfile\\excel\\模板.xlsx"），如下圖：
如圖 <%system.excel.title.dyna.by.craneoffice%> ，表示要替換的標(biāo)題 key ，下面的二維表格，表示預(yù)設(shè)好的輸出列，下面的行即為數(shù)據(jù)輸出行，在這里，我們預(yù)設(shè)要從第1列第5行輸出數(shù)據(jù)。以下是調(diào)用的示例代碼：
object[,] rv = DataSetToOject(); //這個是初始化二維數(shù)據(jù)數(shù)組的
string[,] _repls = new string[1, 2];
_repls[0, 0] = "<%system.excel.title.dyna.by.craneoffice%>";
_repls[0, 1] = "考察對象家庭成員及主要社會關(guān)系人基本情況";
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 數(shù)據(jù)導(dǎo)出文件全路徑地址，_url 為轉(zhuǎn)化的可下載URL地址。
總結(jié)
為保持兼容性，本方法支持舊版本的Word97-2003格式，如需要突破65536行限制，我們可以根據(jù)實際需要進行設(shè)計調(diào)整。
本方法支持?jǐn)?shù)據(jù)輸出行樣式的持續(xù)復(fù)制，即我們可以設(shè)置單行樣式（如字體大小、顏色、邊框等），方法會根據(jù)數(shù)據(jù)行數(shù)，循環(huán)復(fù)制樣式進行行輸出 。
我們在此僅根據(jù)實際項目需要，講述了一些導(dǎo)出數(shù)據(jù)到Excel的參數(shù)需求，這里僅作參考，歡迎大家評論指教！
相關(guān)文章
這篇文章主要介紹了C#淺拷貝和深拷貝,是比較重要的概念,需要的朋友可以參考下2014-08-08
利用Distinct()內(nèi)置方法對List集合的去重問題詳解
這篇文章主要給大家介紹了關(guān)于利用Distinct()內(nèi)置方法對List集合的去重問題的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-06-06
這篇文章主要介紹了c# SpinWait的相關(guān)資料,幫助大家更好的理解和學(xué)習(xí)c# 編程，感興趣的朋友可以了解下2020-10-10
對于XML讀寫操作，項目中經(jīng)常要用到，之前木有好好總結(jié)過，例如LINQ TO XML也用過，這篇文章主要介紹了C#操作XML的方法2021-11-11
C#中IEnumerable、ICollection、IList、List之間的區(qū)別
這篇文章主要介紹了C#中IEnumerable、ICollection、IList、List之間的區(qū)別,本文分別分析了它的實現(xiàn)源碼,從而總結(jié)出了它們之間的關(guān)系和不同之處,需要的朋友可以參考下2015-06-06
這篇文章主要介紹了C#寫日志類,實現(xiàn)將日志信息寫入文本文件的功能,非常具有實用價值,需要的朋友可以參考下2014-10-10
使用C#和Jieba.NET實現(xiàn)中英文混合文本關(guān)鍵詞的提取功能
Jieba.NET?是一個在?C#?中實現(xiàn)的分詞庫,它基于?Java?的?jieba?分詞庫,并進行了?C#?語言的移植,Jieba?是一個高效的中文分詞工具,能夠處理全模式、精確模式以及搜索引擎模式,本文給大家介紹了如何使用C#和Jieba.NET實現(xiàn)中英文混合文本關(guān)鍵詞的提取功能2025-03-03
C# 批量生成隨機密碼必須包含數(shù)字和字母并用加密算法加密
這篇文章主要介紹了C# 批量生成隨機密碼必須包含數(shù)字和字母并用加密算法加密，需要的朋友參考下2017-01-01
winform多線程組件BackgroundWorker使用
這篇文章介紹了winform多線程組件BackgroundWorker的使用方法，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-05-05
C#如何使用Bogus創(chuàng)建模擬數(shù)據(jù)示例代碼
這篇文章主要給大家介紹了關(guān)于C#如何使用Bogus創(chuàng)建模擬數(shù)據(jù)的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用C#具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04