将数据导出到Excel，供用户下载是经常要使用到的一个功能，我这里进行了一些封装，目前已满足项目中导出的需求，使用DataReader导出Excel，支持自定义多表头，使用委托处理字段格式化及字段值的计算；
导出执行的代码如下：
/// <summary>
/// 将DataReader数据写入到 excel book 中
/// 一般数据量小的导出用这个就可以啦
/// </summary>
/// <param name="dataReader"></param>
/// <param name="book">Excel Book</param>
/// <param name="excelName">excelName文件名字</param>
/// <param name="sheetSize">每个Sheet放多少条记录</param>
/// <param name="excelHeaderModels">Excel表头实体，Select字段可以从这里获取</param>
/// <param name="func">字段格式化委托函数</param>
/// <param name="funcCalc">字段的值计算委托函数</param>
/// <returns></returns>
public Hashtable CommonDataWrite2Sheet(IDataReader dataReader, IWorkbook book, string excelName, int sheetSize, List<ExcelHeaderModel> excelHeaderModels = null, Func<string, object, string> func = null, Func<string, object, IDataReader, object> funcCalc = null)
{
string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, BusinessSystemInfo.ExportDir, DateTime.Now.ToString(BaseSystemInfo.DateFormat));
string fileName = Path.Combine(filePath, excelName);
//创建表头
int totalCount = ;
//表头的要去掉 否则默认的是65535条记录 一个表头 所以从1开始
if (excelHeaderModels != null)
{
totalCount = excelHeaderModels.Max(c => c.RowStart) + ;
}
Hashtable ht = new Hashtable();
//给book 创建Sheet
int sheetIndex = ;
string sheetName = "sheet";
ISheet sheet = book.CreateSheet(sheetName + sheetIndex);
//在创建的sheet里面创建表头
CreateHeader(dataReader, book, excelHeaderModels, sheet);
//写入到book中 逐行
while (dataReader.Read())
{
// 输出的总数量大于每页数量（如65535），换一页写入 2003版本的才会要求限制每sheet放65535条记录
if (book.GetType() == typeof(HSSFWorkbook))
{
if (totalCount > sheetSize)
{
//换一页写
sheetIndex++;
totalCount = ;
sheet = book.CreateSheet(sheetName + sheetIndex);
CreateHeader(dataReader, book, excelHeaderModels, sheet);
}
}
//创建Excel的行这一行是sheet创建的第rowIndex++行
IRow excelRow = sheet.CreateRow(totalCount);
for (int i = ; i < dataReader.FieldCount; i++)
{
//在rowIndex++行的给第j列 写入 dt的第i行第j列的值
ICell cell = excelRow.CreateCell(i);
string colName = dataReader.GetName(i);
object value = dataReader.GetValue(i);
if (funcCalc != null)
{
value = funcCalc(colName, value, dataReader);
}
SetCellValue(func, cell, value, colName);
}
//自动调整单元格大小 有些耗时
//for (int i = 0; i < dataReader.FieldCount; i++)
//{
// sheet.AutoSizeColumn(i,true);
//}
totalCount++;
} // book写入到文件中
WriteToFile(book, fileName); //获取扩展名
//string fileExt = Path.GetExtension(fileName);
//using (ZipFile zip = ZipFile.Create(fileName.Replace(fileExt, ".zip")))
//{
// zip.BeginUpdate();
// zip.SetComment("这是我的压缩包");
// zip.Add(fileName); //添加一个文件
// zip.CommitUpdate();
//} fileName = fileName.Replace(AppDomain.CurrentDomain.BaseDirectory + "", "");
ht.Add("Code", "");
ht.Add("Detail", "数据导出完毕，请下载。");
ht.Add("fileName", fileName);
ht.Add("Count", totalCount);
return ht;
}
WriteToFile 将book写入到文件的方法,注意，这里使用了 ms.ToArray()。
/// <summary>
/// Excel book写入到Excel文件
/// </summary>
/// <param name="book"></param>
/// <param name="fileName"></param>
/// <returns></returns>
private bool WriteToFile(IWorkbook book, string fileName)
{
bool result = true;
try
{
using (MemoryStream ms = new MemoryStream())
{
string filePath = Path.GetDirectoryName(fileName);
if (filePath != null && !Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
book.Write(ms);
var buf = ms.ToArray();
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, , buf.Length);
fs.Flush();
}
}
}
catch (Exception e)
{
LogHelper.WriteErrorLog(e.Message, e);
result = false;
}
finally
{
GC.Collect();
}
return result;
}
ExcelHeaderModel 是定义的表头实体，支持自定义多表头，如下：
/// <summary>
/// 导出配置
///
/// 修改纪录
///
/// 2015-12-19版本：1.0 SongBiao 创建文件。
///
/// <author>
/// <name>SongBiao</name>
/// <date>2015-12-19</date>
/// </author>
/// </summary>
[Serializable]
public class ExcelHeaderModel
{
/// <summary>
/// 数据库里面的字段名称
/// </summary>
public string SrcName { get; set; } /// <summary>
/// 名称
/// </summary>
public string Name { get; set; } /// <summary>
/// 行开始
/// </summary>
public int RowStart { get; set; } /// <summary>
/// 行结束
/// </summary>
public int RowEnd { get; set; } /// <summary>
/// 列开始
/// </summary>
public int ColStart { get; set; } /// <summary>
/// 列结束
/// </summary>
public int ColEnd { get; set; } /// <summary>
/// 是否显示在表头 有些字段不需要显示 但是需要用来做计算
/// </summary>
[DefaultValue(false)]
public bool Hidden { get; set; }
}
Controller中的业务调用
/// <summary>
/// 导出操作,支持导出当前页,导出全部,导出文件格式excel2003,excel2007及以上
/// </summary>
/// <param name="searchModel"></param>
/// <param name="pager"></param>
/// <param name="sort"></param>
/// <param name="direction"></param>
/// <param name="isPage">true:导出当前页,false:导出全部</param>
/// <returns></returns>
[CustomerResource("ExportData")]
public ActionResult ExportData(PriceSearchEntity searchModel, QuiGridPager pager, string sort, string direction,bool isPage)
{
PriceManager priceManager = new PriceManager(BusinessDbHelper, UserInfo);
// 使用DataReader导出
IDataReader dr ;
//要导出的字段
string selectField = GetExportField();
string tableName = PriceEntity.TableName + " A";
// 导出当前页
if (isPage)
{
dr = priceManager.ExecuteReaderByPage(searchModel,tableName, pager, sort, direction, selectField);
}
else
{
dr = priceManager.ExecuteReader(searchModel, tableName, sort, direction, selectField);
}
using (dr)
{
IWorkbook book = new HSSFWorkbook();
// 2007的导出
// IWorkbook book = new XSSFWorkbook();//.xlsx string fileName = UserInfo.NickName+"_数据导出-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // GetSelectField()
// 导出的文件自动压缩
// var hashTable = new ExportHelper().DataWrite2Excel(dr, book, fileName, 5, 65530, null, null, null); //一般用这个
var hashTable = new ExportHelper().CommonDataWrite2Sheet(dr, book, fileName, );
hashTable.Add("TotalTime", CalcTimeSpan(StartTime));
return Json(hashTable, JsonRequestBehavior.AllowGet);
}
}
这里使用了通用权限系统的权限控制功能及底层的数据访问功能，解决导出权限控制的问题。
代码里可根据需要控制输出的文件使用Excel2003还是Excel2007的文件格式。
前端导出完成展示的效果：
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAXcAAADXCAIAAABuyYbzAAAVYElEQVR4nO2d74scx5nH968IRkoCIWAImDiWo8gaEQZsDDHdsl/ci7xpOnCsk0086fOZ4Mg7mCZw0eXuNBajF0vEkSOyJa1DPLE2zgo5cWSt10QxkdFGyiIt8V4coj1kr+TVWN7xsvti7kXNVFdXV1VXz3T1j5nvhwdpprv6x/b085mnq3/MRMsAx48fNzFbIGMcNvg4/I2jyoSJmWKHyJhx2ODHQWmBZUYBbHBQZExZBmSMic8RgFQwYhkAAKDAMgAAs8AyAACzwDIAALPAMgAAs8AyAACzwDIAALPAMgAAs8AyAACzwDIAALPAMgAAs8AyAACzwDIAALPAMgAAs0zk/cQCAMCIg1oGAGAWWAYAYBZYBgBgFlgGAGAWWAYAYJYsLHNg8jQCgShUZJD4FFgGgRjHyCDxKbAMAjGOkUHiU2AZBGIcI4PEp8AyCMQ4RgaJT5n4jt8yHblvUAQCwUUGiU9j4qcvvWI6ct+gCASCiwwSnwYsg0CMY8AyCATCbMAyCARCHA99+xfC4XunknWAwjIIBEIQXz208Pybdx78/m+44XuePudfuPPA9KL+rGAZBALBx/4nX/7x+Vutla1/e/PW3qd+TYfvefpc4+LHrZWt539/e993dNMNlhkwut1ut9ulr2/d6bBD2Ga37nSik8hmpbPEboTcd0rE6MXe2pmji+utla0fvfHhvqnWgcnTD/zruaN/vNta2XrhD3f2ePP6s4JlehFNXVkycwNfOrtMVcI1o6Poa7VlFAuNNuMGxv4hue+1iNLF3u/+6siFm69c36rPrz347PkXLn78yvWt5sU7X/mXs4nmA8sIQictuVSnBUu0mX4tI9OKsBkMgsgg9k298u+//b/Ty5+SeOEP7fuffj3pTDK1zOQP/tN0pLJlFakrqxS4nI8OpLWMrFm0gWJuXXktg0CkG3ufnv/Znz/9nz9/+t9Lm/c/e36AOWSQ+DQm/uOnLdORymaNJrOwAft2cemGYj4H5AWLcJRw0QrpRFdbRu67LKJc8dXar59/49bxy50TVz89frnz1Oz/7n/y5aQzySDxaZTMMgf0ej1IDbK4dCOqHrY84YYoFifzQlJNQCuIIeNBb/7Z1281/7T57GtrByZP//jtj5t/2pw8vZpUNLCMIKIFAjeWk0XUMlGzsP8KLcOOEgqCayaTkfCvQCCSxh5v/plzt468s/nDcx9+7bu/OjB56sDk6R+91f6vP27+80vJRAPLCEJdRHDpLatlhBNG9URY+ftH0Tlz0umG+5hj5aJ2FgKhiIe+/Yvv/+bDn1zc/OFvb+95ao4d5S+0f3Jx81svvacvGlhGEGrLyNoQU7Bjo7KQ1TKcZWSLoOfLuYpGuGKKWSEQsfFQba52Zu1+T3DS+gev3040K1hGEDo5GTXIyt8/EiZ2bLbTaYWVDtssahnFmuu4EoFQxP7JWcHAJ2f3al/1SwKWEYQiIaMVygFRRy/XONqMmydnGWGdEmsZtVZgGUReAcsIQich1aUK6wh1kcIJRd2Mu4xYKCNutrnvYQgELINAIMwGLINAIMwGLINAIMwGLINAIMwGLINAIMwGLINAIMwGLINAIMwGLINAIMwGLINAIMzGqFlm6tAxBAJRqBhBy0SvvgcA5AUsAwAwCywDADALLAMAMAssAwAwS9aW+afvHTEdsAwAhWLq0LEMEp8GahkAxg4cMQEAzALLAADMAssAAMwCywAAzALLAADMAssAAMwCywAAzALLAADMAssAAMwCywAAzALLAADMAssAAMwCywAAzALLAADMAssAAMwCy4CiU61W9ccmaqxupjOrVBY38sAyoAQo0pXLdmFL9djB2idqM+bAMmBw5qarDM7M0sBzWppxQrOoxkGbyV6wxBpBc1nqNsIh0r92ek5325QfWAYMw9w0FcPSzPQQmuFmRohmaVVyfKSwg2ws10C9XM026lWibWAZWAbo0xfD0szM8FmT0DLCkiF2EuGSTVhGtejIXzrawDJgGIJjJvLdTI58nJk5cgTU+8LutwpSKzqkK849fYMIh+irga042HokURvFCnOWGadSBpYBQyGqZeamp+eY46elGacnj6UZhyRXkGShwywDltEcyylDZpnYNooVDjVLpfQrD7AMGAZx6T83HSpb+CbhTuNpRk/C4wguq4WjhLDNFGO7EoMM0KYLy4iAZcAwsGKYmyEve7UMU8HQNqQJrWkUMwsTW4PEttScz5AvupEDK80VG21gGTA44aKkSuRSrVadmSX6oktPU7N1i7g/h2vUQ1Y1CAcOYxmqBmG1EtuGM4vCMnPT0b9ylMnaMkfNA8uMDNG8jS0Qhqxloq9lBYtirQZYsdFm6tCxDBKfMtEyDywzAghTNzoqNpmrcXTl+mCH6LRRr4PwbxkTpg4dyyDxKbAMAGMHLAMAMAssAwAwCyxTeto7i7e3X23vLG5sz29sz9O3ea8XAD1gmbLS3llc3z6x2pm8dvfge51vcnHt7sHVzuT69gnoBuQOLFM+2juLa1uHr909SFRCtLLacVc7k6udydWO2x8ySdqsbR0upmvG/MzL+ADLlIyN7Xnijvc7zxCVXP/k8Wt3D15ef/TSzYcv3Xz48vqj1+4evP7J40RA73eeIe03tudNrI/6JDHXIDqtiVUCRSMvyzQ92/aaRpYxwpZZ2zq8svnE9U8eJ8XLcttaWN135tK9Jy/sOrWwm8bJC7vOXLp3YXXfctsipc31Tx5f2Xxibetw6qukuFZN9kLYfkB61xA7/Xsb9J6lFZ4q0fJmEk4juO55/MivlvHdihnPjKplbmw9R0qY1c7k1Y3HfnftgZff/jw1y4vn7zl5YdfJC7tePH8PHfjy25//3bUHrm48ttqZJEXNja3n0l2rpJaRlTwDQe/KnJuu9u/2jldHdCrdhSU2U3DHFns319iRtWUqUlJTzkha5sbWc7S35d0PHmm980W2eHnx/D1nr9x35aNvXPnoG2ev3MeK5tTC7tY7X3z3g0doD066otE5YuJesNMq57004yhLAMYSfbtoWEYwlTZJJ2DcMvyjBMtLUfplfBeWkbK2dWRl8wlSxVz8x9dnFz/HSuT0W589e+U+tv3ZK/edfuuzbJvZxc9d/MfXSUWzsvnE2taRtNZNp5Yx1C/Dlgf9omFu2pmedoLjIe5+TnIbZ3iqpRmn6jhOtdr7d3pOMFVvkWHLBEdDkmUxK0seqSNYVnQ+o0dOlvHdiuuzI2AZGe2dRdqb++4Hj3CKIb0wy22LnWS5bXE9NUQ0pKIhvcVpnXXSsUzsi8EQWya4D1xcBQmmIu7o/6s6iApZJqhO4h59x6yMYFn68ykruVgm4phUGTHLkJPW73W+eXXjsV9e/ALnDn3LnFrY/cuLX7i68Ri5miatnuDBjpiq8oc5JEB1xCS1jGAqktzsv/IlhqoN/pcXZMtjGgiXpTWfElOcfpnUemZGyTKkkHm/88xqx33zr3uj4ji1sPvnb3wmesT08zc+I2z85l/3rnZc0hOcSjkzQC3TTcsybKcv3/srt0x0qgEtQ883KXp2mdVYmpsTG01nPuUm81rG5LESYZQss759gnTZLret6LES2/t77i9fXm5by23r3F++zPX+csdNy22LzHN9+8Twa6hjGa66EbYXEdf72xWdyWaeoSWdNjTV0oxTrZLenN6/4kwPul0CkYn7YETTMOvFL0tjPiUna8t4nusGlYvvVtI/dholy/Qv551cWN0nEwcVDQ11y4XVfXS2w6/hYLWMbAgYSfI6x+S79EApbc2MjGXI4RK5pu7MpXvViiEXy0QvmYnGmUv3kuv6hj9oEmpCVrkkbQNGhrz6ZQK1ND071ctlRscyt7dfpbc+Cntz6Znsxb/tJ4dLJBb/tp87k831FtMbLG9vv5r3XwlGn6JcL5MiI2OZ9s4iuZPg8vqjitqEWIOdUG2lUwu7L68/Su48KOZdlGDEgGWKy8b2PLnY99LNh9WW0TyTTePSzYfJpcCGbqEEgAWWKS6wDBgNYJnion/ElNQyOGICWQLLFBfN3t+klkm991fzFLXsnDcdImP4NQT5AssUF80z2Uktk+KZbEqsQYT/6swBlhkBYJlCo3NVXlLLpH5VnmYNomOi6NuhLSP7bdzEz6MCA5O1ZTL4/cpRsozOHQaJLGP0DgPhcKF9FG/TPmhampnh7zMY5HlUYAiy/gXbWfOMkmV07pZMZBmjd0sqhrOyENYpgx4xxd7rJLBMtzvAA6zA4EwdOpZB4lNgmcQU/8kPscPZgoUbwrYxUMh0u925mdBjrehgWCY7YJmiU/ynWKntIDSL0DKxrweDPHGBf64CLJMhsEwJUD+RU+cOA3NP5NQkegDVzaiWCQg9eReWyRBYphwoni5+8sKu1y5/6fcrD9J47fKXWMsYfbo4i7AGURQ4MoOkaBbmyXioZXIDlikNil9KYR8uwz5iJrNfSokeHwkHKg6FBq1lNHp/I4/ujjyPCpgFlikThf3VN80DIvVU3FgwMsAyJaNQv2Cr6FjhhrPthbpRk/qagyzJwzJ1p+LUyftGzeq/FKAeK2O0LdPtdts7i+T09rW7B2lvy2rH7V/R69IeHNJmbesw7ooEOZK1ZVS/YWDVGuHWsIyC9s7i+vYJohKiFTaIgNa3T8AvIHcKVcs0apbKQkITRRkTy1DaO4u3t19t7yxubM9vbM/Tt3mvFwA9ClTLRMsW1DIAjABF6f0VCgWWAWAEKF4tU3f0ix0hsExe6JwMyuiE0cQE/5odkgT1CgtP4QOO7GuZfudL0MPSqFnMO6bXhkUyWAAskxc6Z6A1TSSYycRETAibdbuhUepFyNdHPaoquuwQULKvZfo+Cbp6wz26sExpUVxll+hCmPj8jy1MorVMkqUoVphtr3gBWDKvZegBkVWr1yynPlt3wpqBZUqLZp2i0yamxJBZRrPe0VuQ0D7Ct/oCHU8yr2UYVfTF0ahZTKcL+mVKRewXfrS95jxVLdRdLdxwZeEzsGXohNxAxbLGljzOMQXHSoE36k7/HWqZUhGbogoNyTJc1zJdkVBk/TLK4ybZctQG0RkCusU5k50isEyWJK1Z0qxloq+7IqfAMnkDy4Ch0LGJzmtu2gSW6Uackl4tQ1dSVnN15cWaav3HDFgGDIXOodBgllElamzPi17XjKYR9OsyyEUILAOMM7BlpEmrcyabu4gmMpVm3RFby6iHgC4sA4aES8Loi+5w18uI2+icyeYahwfqr4bsbReW0QaWAYMjLEyEoomdPBn6V+VpXJ4XRVM9sIwmsAwAwCxZWyaD36+EZQAoFFn/gm0Gv8UNywBQKKYOHcsg8SmwDABjBywDADALLAMAMAssAwAwCywDADALLAMAMEtJLdP0bNtrisfBMgAUigJYhhrDdwUPyLO9ZjDc9nzPjjYIzw+WAaBQFMAy6spE3IgxEywDQLHJ2zJM/RKqWnq4ftDO9pricoerZ2AZAApF3pbp0atNwpVJ07N7lvHdwDdse9QyABSfYlimbxFJLUMHo18GgPJRBMv4bsW27XCxIm5HjYJzTACUhuwtE9QrTPlie564x6Xi+v0jp6Znu76gY4bXDSwDQKHIu5bxXdfv97/Q3hf+RdOzbc/3mHIHtQwApSFvy7RaQS+v8AQS0zPj+pJzTOGDLVgGgEJRMMuIaxkqIPTLAFA+CmYZYZ3Su1am1WpGTjCJihlYBoBCUQTLpAwsA0ChgGUAAGaBZQAAZoFlAABmgWUAAGaBZQAAZoFlAABmydoyGfx+JSwDQKHI+hdsM/gtblgGgEIxdehYBolPgWUAGDtgGQCAWWAZAIBZYBkAgFlgGQCAWWAZAIBZYBkAgFmyt0yjZlm1RjCo7oTexlN3KhWn3nvTqFm913Q+sAwAhSKPWqZRs3qaaNSsCu+YuiN+Hh6jFjKd41jCVrAMAIUia8vIDBK2SCCTuDoHtQwARSe/fpnADwzyQoZg1Rp1h5Q/0qawDACFoji1TFg5dadSsRzHsmo1J1LlBIdcs3WnPw61DADFpFi1TFCfOPVZ5oiJGy5oz4yGZQAoFMWpZSr9fuBGTdiry/TQEPsQxbBnm6xazbFqsAwAhaJYtYx8ROh8d92pWJZVcepcLWPVGrMNWAaAYlHGWoY9r8TVMo3Z2dmpQ8cQCEShIlPLUCNUrFq9ZglOX8fWMsz5bdYy/DgwNEePHk1xbuQjbNQsq1YnH3GjvwM02D2hUbOcGmkQ+dTrjlWr0Q892FOYfaY/Tf//6O6U+DpQTSKbi1+QtHxHRqTKRKSPhTno0atlmBNMotPZso8RJCdVyzRqTqgadbhzh/0rFMLGEHfMMbMJ1BRqFySXaHfK1DKSSy5ozyIyIm2yuI8JpEW6tUyWMN/gOdYyIB9gmTKBtEkENldBgGXKBNImEdhcBQGWKRNIm0RgcxUEWKZMIG0Sgc1VEGCZMoG0SQQ2V0GAZcoE0iYR2FwFIYtfsAUAjDMTJn58Gxji6NGjea9CmcDmKgiwTJlA2iQCm6sgwDJlAmmTCGyuggDLlAmkTSKwuQoCLFMmkDaJwOYqCLBMmUDaJAKbqyDAMmUCaZMIbK6CAMuUCaRNIrC5CsJEq9X0bNtrBoN8N3jru8IH/jC4fjAlnRM/S5AOaacN/aibnu16ns1/uPRD9F3mg256NvupMwRjIm2ank1mJ9mlJHMcisjmYndt4VrSwciINJlotcgeQLYN3RcE9Lc191E1ozundGcFw2HCMr5n254fpJsg83y34vqyz5lLKpllWv2dy3Oju4PcW0MxqGWQESmjfLp4+BOQbFMGgbmbnmdg9xlXDFuG/552/RabcH2S1TKhMihox8w9JLk0kVpG4AHba2r4ARkxEEy/jHjnUVeI/RpYXnc2PXe8vG0S45ZhsqW3O/gusUGzKU5B1w/nZsgyvtv/2vbd3ijp/mRAM+zmYpdLFxVTQyEjUkJRy5DtRDcY3eTBvth7pSyo6ZYHw5OJZXpfu2yK+W6l4nqeTZRie81eUz6bQrUM/8FHiiJJLqYHv7kinSNRjSirGWTEgOjUMv3StrdpRNu09yGQL76+rHv7sNk9aazIxzK+W+kNkGZeD3W/THTp2Vqm/weI+rn5v6Q/ATIiDZT9MsHXFfs9xO5tgZObnu36TDFpe03ftV13/DapQVK2TNOzXb8psAzJjN4RkzgD5TPkXkWbZPb1Ht5cvmvbtu01GblxaxnfbYuMGAitWsZ1xfuA79oec7jeN3erJ29V9zwYhEws08vCJh3k95qJEy90WMX1y4RfZn42l91cvZwPeok0JIeMSIn/Bw8F2mG3YqVGAAAAAElFTkSuQmCC" alt="" />