使用NPOI完成导出Excel文件

<button type="button" class="bk-margin-5 btn btn-success" onclick="printExcel()">Excel导出</button> <script type="text/javascript">
function printExcel() {
post("PrintExcel", null); //如果调用时有参数，则post("PrintExcel",{id:id});
}
function post(URL, PARAMS) {
var temp = document.createElement("form");
temp.action = URL;
temp.method = "post";
temp.style.display = "none";
for (var x in PARAMS) {
var opt = document.createElement("textarea");
opt.name = x;
opt.value = PARAMS[x];
temp.appendChild(opt);
}
document.body.appendChild(temp);
temp.submit();
return temp;
}
</script>

[HttpPost]//只接受post请求
public void PrintExcel()
{
//接收参数 int id=Int32.Parse(Requert.Form["id"]);
string trainShift = Session["trainshiftNum"].ToString();
#region 转换成DataSet
var query = from s in db.Students
where s.IsDelete == false && s.TrainShiftNum == trainShift
select new Stu
{
StudentDepartment = s.StudentDepartment,
StudentGender = s.StudentGender,
StuDentIdCard = s.StuDentIdCard,
StudentName = s.StudentName,
StudentNation = s.StudentNation,
StudentPosition = s.StudentPosition,
StudentUnit = s.StudentUnit,
TrainShiftNum = s.TrainShiftNum,
Remark = s.Remark
};
DataTable dt = query.ToDataTable(rec => new object[] { query });//调用转换DataTable方法 #endregion HSSFWorkbook book = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = book.CreateSheet("sheet1"); // 首列
NPOI.SS.UserModel.IRow row = sheet.CreateRow();
row.CreateCell().SetCellValue("序号");
row.CreateCell().SetCellValue("所在单位");
row.CreateCell().SetCellValue("所在部门");
row.CreateCell().SetCellValue("姓名");
row.CreateCell().SetCellValue("职务");
row.CreateCell().SetCellValue("性别");
row.CreateCell().SetCellValue("民族");
row.CreateCell().SetCellValue("出生年月");
row.CreateCell().SetCellValue("组");
row.CreateCell().SetCellValue("备注");
#region
//// 第一列
//NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(1);
//row1.CreateCell(0).SetCellValue("所在单位"); //// 第二列
//NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(2);
//row2.CreateCell(0).SetCellValue("所在部门"); //// 第三列
//NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(3);
//row3.CreateCell(0).SetCellValue("姓名"); //// 第四列
//NPOI.SS.UserModel.IRow row4 = sheet.CreateRow(4);
//row4.CreateCell(0).SetCellValue("职务"); //// 第五列
//NPOI.SS.UserModel.IRow row5 = sheet.CreateRow(5);
//row5.CreateCell(0).SetCellValue("性别"); //// 第六列
//NPOI.SS.UserModel.IRow row6 = sheet.CreateRow(6);
//row6.CreateCell(0).SetCellValue("民族"); //// 第七列
//NPOI.SS.UserModel.IRow row7 = sheet.CreateRow(7);
//row7.CreateCell(0).SetCellValue("出生年月"); //// 第八列
//NPOI.SS.UserModel.IRow row8 = sheet.CreateRow(8);
//row8.CreateCell(0).SetCellValue("组"); //// 第九列
//NPOI.SS.UserModel.IRow row9 = sheet.CreateRow(9);
//row9.CreateCell(0).SetCellValue("备注");
#endregion
int rowIndex = ; foreach (DataRow r in dt.Rows)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
string str = r["StuDentIdCard"].ToString().Substring(, );
str = str.Substring(, ) + "-" + str.Substring(, ) + "-" + str.Substring(, );
dataRow.CreateCell().SetCellValue(rowIndex.ToString());
dataRow.CreateCell().SetCellValue(r["StudentUnit"].ToString());
dataRow.CreateCell().SetCellValue(r["StudentDepartment"].ToString());
dataRow.CreateCell().SetCellValue(r["StudentName"].ToString());
dataRow.CreateCell().SetCellValue(r["StudentPosition"].ToString());
dataRow.CreateCell().SetCellValue(r["StudentGender"].ToString() == "" ? "男" : "女");
dataRow.CreateCell().SetCellValue(r["StudentNation"].ToString());
dataRow.CreateCell().SetCellValue(str);
dataRow.CreateCell().SetCellValue(r["TrainShiftNum"].ToString());
dataRow.CreateCell().SetCellValue(r["Remark"].ToString());
rowIndex++;
} // 写入到客户端
book.Write(ms);
//Response.ClearContent();
//Response.Clear();
//Response.Buffer = true;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.DateTime.Now.ToString("yyyymmddhhmmss")));
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}

public static class ListToDataTable //可以直接使用
{
public static DataTable ToDataTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)
{ DataTable dtReturn = new DataTable(); // column names PropertyInfo[] oProps = null; // Could add a check to verify that there is an element 0 foreach (T rec in varlist)
{ // Use reflection to get property names, to create table, Only first time, others will follow if (oProps == null)
{ oProps = ((Type)rec.GetType()).GetProperties(); foreach (PropertyInfo pi in oProps)
{ Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{ colType = colType.GetGenericArguments()[]; } dtReturn.Columns.Add(new DataColumn(pi.Name, colType)); } } DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
{ dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null); } dtReturn.Rows.Add(dr); } return (dtReturn); } public delegate object[] CreateRowDelegate<T>(T t); }

/// <summary>
/// 打印学员名单
/// </summary>
public class Stu
{
private string studentUnit; public string StudentUnit
{
get { return studentUnit; }
set { studentUnit = value; }
}
private string studentDepartment; public string StudentDepartment
{
get { return studentDepartment; }
set { studentDepartment = value; }
}
private string studentName; public string StudentName
{
get { return studentName; }
set { studentName = value; }
}
private string studentPosition; public string StudentPosition
{
get { return studentPosition; }
set { studentPosition = value; }
}
private int studentGender; public int StudentGender
{
get { return studentGender; }
set { studentGender = value; }
}
private string studentNation; public string StudentNation
{
get { return studentNation; }
set { studentNation = value; }
}
private string stuDentIdCard; public string StuDentIdCard
{
get { return stuDentIdCard; }
set { stuDentIdCard = value; }
}
private string trainShiftNum; public string TrainShiftNum
{
get { return trainShiftNum; }
set { trainShiftNum = value; }
}
private string remark; public string Remark
{
get { return remark; }
set { remark = value; }
}
}