开发过程中各类报表导入导出防不胜防，网上也是各种解决方法层出不穷，比如Excel，CSV，Word，PDF，HTML等等。。。
网上各种导出插件也是层出不穷，NPOI，微软Microsoft.Office.Interop.Excel，EPPlus等等，其实说起来，微软自带的家伙还是不错的，但是必须要安装office组件，这个不能忍，服务器上安装一个office，呵呵哒！
今天给大家介绍的Excel导入导出用的是NPOI，这个组件还是蛮好用的，而且不依赖office组件。可以根据Excel格式进行自定义。根据平常用到的一些用法进行了封装，旨在更方便的进行文档操作。
我们在Ado.Net里面，DataTable用到的非常多；但是在EF里面，我们更多的是针对List进行操作，毕竟Linq的好处大家都是非常明白，下面通过几个示例分别介绍Excel的导出：
DataTable导出Excel
/// <summary>
/// DataTable导出到Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="filename">文件名</param>
/// <param name="sheetname">表名</param>
/// <param name="maxrow">超过行数新建表</param>
/// <param name="isweb">是否web导出，默认是</param>
/// <returns></returns>
public static void DataTableToExcel(string filename, DataTable dt, string sheetname = "", int maxrow = , bool isweb = true)
{
if (dt == null || dt.Rows.Count < )
return;
if (filename.IndexOf(".xls", StringComparison.OrdinalIgnoreCase) < && filename.IndexOf(".xlsx", StringComparison.OrdinalIgnoreCase) < )
filename += ".xls";
IWorkbook workbook = new HSSFWorkbook();
if (sheetname.IsNullOrEmpty())
sheetname = filename;
if (dt.Rows.Count < maxrow)
WriteExcel(dt, , dt.Rows.Count - , workbook, sheetname);
else
{
int page = dt.Rows.Count / maxrow;
for (int i = ; i < page; i++)
{
int start = i * maxrow;
int end = (i * maxrow) + maxrow - ;
WriteExcel(dt, start, end, workbook, sheetname + i);
}
int lastPageItemCount = dt.Rows.Count % maxrow;
WriteExcel(dt, dt.Rows.Count - lastPageItemCount, lastPageItemCount, workbook, sheetname + page);
} using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
if (!isweb)
{
if (!filename.Contains("\\"))
filename = Config.Config.GenerateFilePath("Office") + filename; if (!File.Exists(filename))
{
using (FileStream fs = new FileStream(filename, FileMode.CreateNew))
{
fs.Write(ms.ToArray(), , ms.ToArray().Length);
fs.Close();
}
}
}
else
{
//Web导出
HttpContext curContext = HttpContext.Current;
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
}
}
} private static void WriteExcel(DataTable dt, int start, int end, IWorkbook book, string sheetName)
{
ISheet sheet = book.CreateSheet(sheetName);
IRow header = sheet.CreateRow();
for (int i = ; i < dt.Columns.Count; i++)
{
ICell cell = header.CreateCell(i);
string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
cell.SetCellValue(val);
}
int rowIndex = ;
for (int i = start; i <= end; i++)
{
DataRow dtRow = dt.Rows[i];
IRow excelRow = sheet.CreateRow(rowIndex++);
for (int j = ; j < dtRow.ItemArray.Length; j++)
excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString().Trim());
}
} 调用方式：
public void Test()
{
Excel.DataTableToExcel("dt7.xls",dt,"人员名录");
}
List导出Excel:
/// <summary>
/// List导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filename">文件名称和路径</param>
/// <param name="sheetname">表名称</param>
/// <param name="data">数据</param>
/// <param name="title">表头</param>
/// <param name="isweb">是否web导出，默认是</param>
public static void ListToExcel<T>(string filename, string sheetname, List<T> data, List<string> title = null, bool isweb = true) where T : new()
{
if (data.Count < )
return;
if (filename.IndexOf(".xls", StringComparison.OrdinalIgnoreCase) < && filename.IndexOf(".xlsx", StringComparison.OrdinalIgnoreCase) < )
filename += ".xls";
var wookbook = new HSSFWorkbook();
var sheet = wookbook.CreateSheet(sheetname);
var entity = new T();
var propertys = entity.GetType().GetProperties(); if (title == null || title.Count < )
{
title = new List<string>();
foreach (PropertyInfo item in propertys)
{
if (!Ignore.IgnoreField(item.Name))
continue; title.Add(item.Name);
}
} var rowtitle = sheet.CreateRow(); for (var i = ; i < title.Count; i++)
rowtitle.CreateCell(i).SetCellValue(title[i]); for (var i = ; i < data.Count; i++)
{
var row = sheet.CreateRow(i + ); //因为表头名称占了一行，所以加1
for (var j = ; j < propertys.Length; j++)
{
if (!Ignore.IgnoreField(propertys[j].Name))
continue; var obj = propertys[j].GetValue(data[i], null);
row.CreateCell(j).SetCellValue(obj.ToString().Trim());
}
}
if (!isweb)
{
if (!filename.Contains("\\"))
filename = Config.Config.GenerateFilePath("Office") + filename; if (!File.Exists(filename))
{
using (var fs = new FileStream(filename, FileMode.CreateNew))
{
wookbook.Write(fs);
}
}
}
else
{
using (MemoryStream ms = new MemoryStream())
{
wookbook.Write(ms);
//Web导出
HttpContext curContext = HttpContext.Current;
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
}
}
}
调用方法：
public void Test()
{
Excel.ListToExcel(path,"",list,newList<string>(){"编号","名字","时间"});
}
OK，各位看官，这一期的文章Excel导出写到这里喏，感谢大家的支持，您的支持是我的动力！
下一期给大家带来的是常用的Excel导入，敬请期待！！！