一· 通过构造table表格导出Excel
//1、获取数据源(List变量)
//List<Person> list = new List<Person>() {
// new Person(){ID=1,Name="张三",Age=20,Sex="男"},
// new Person(){ID=2,Name="李四",Age=21,Sex="女"},
// new Person(){ID=3,Name="王五",Age=22,Sex="男"},
// new Person(){ID=4,Name="赵六",Age=23,Sex="女"}
//};
//2、构造table表格
string sHtml = "";
StringBuilder sTable = new StringBuilder();
// sTable.Append("<table border=\"1\">");
sHtml += "<table border=\"1\">";
//定义列头
// sTable.Append("<tr><td>编号</td><td>姓名</td><td>年龄</td><td>性别</td></tr>");
sHtml += "<tr><td>编号</td><td>姓名</td><td>年龄</td><td>性别</td></tr>";
//定义数据行
foreach (var item in list)
{
sTable.Append("<tr><td>"+item.ID+"</td><td>"+item.Name+"</td><td>"+item.Age+"</td><td>"+item.Sex+"</td></tr>");
}
sTable.Append("</table>");
//3、通过file输出excel文件
byte[] fileContents = Encoding.Default.GetBytes(sTable.ToString());
return File(fileContents, "application/ms-excel", "人员表.xls");
二·通过NPOI第三方组件导出excel
添加NPOI引用
public ActionResult ExportByNPOI()
{
//1、获取数据源(List变量)
//List<Person> list = new List<Person>() {
// new Person(){ID=1,Name="张三",Age=20,Sex="男"},
// new Person(){ID=2,Name="李四",Age=21,Sex="女"},
// new Person(){ID=3,Name="王五",Age=22,Sex="男"},
// new Person(){ID=4,Name="赵六",Age=23,Sex="女"}
//};
//2、创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet = excel.CreateSheet("Sheet1");
//给sheet1添加标题行
NPOI.SS.UserModel.IRow head = sheet.CreateRow(0);
head.CreateCell(0).SetCellValue("编号");
head.CreateCell(1).SetCellValue("姓名");
head.CreateCell(2).SetCellValue("年龄");
head.CreateCell(3).SetCellValue("性别");
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(list[i].ID);
row.CreateCell(1).SetCellValue(list[i].Name);
row.CreateCell(2).SetCellValue(list[i].Age);
row.CreateCell(3).SetCellValue(list[i].Sex);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
excel.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "人员表.xls");
}