/// <summary>
/// 导出Execl文件
/// </summary>
/// <returns></returns>
public ActionResult ImportStudent()
{
string sql = string.Empty;
string Name1 = string.Empty;
sql=string.Format(@"select * from RndSuite.[student]");
List<student> list = new List<student>();
DataTable SHTable1 = new DataTable();
SHTable1 = DBHelpe.GetInstance().ExecuteTable(sql);
foreach (DataRow dr in SHTable1.Rows)
{
student u1 = new student()
{
Name = dr["Name"].ToString(),
Age = dr["Age"].ToString(),
ClassName = dr["ClassName"].ToString()
};
list.Add(u1);
}
//创建excel工作簿
HSSFWorkbook excelBook = new HSSFWorkbook();
//创建工作表名
NPOI.SS.UserModel.ISheet sheet1 = excelBook.CreateSheet("信息");
//创建表头行
NPOI.SS.UserModel.IRow rew1 = sheet1.CreateRow(0);
//设置表头
DataTable SHTable = new DataTable();
rew1.CreateCell(0).SetCellValue("姓名");
rew1.CreateCell(1).SetCellValue("年龄");
rew1.CreateCell(2).SetCellValue("班级");
//为excel表添加数据
for (int i = 0; i < list.Count; i++)
{
//创建行
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);
rowTemp.CreateCell(0).SetCellValue(list[i].Name.ToString());
rowTemp.CreateCell(1).SetCellValue(list[i].Age.ToString());
rowTemp.CreateCell(2).SetCellValue(list[i].ClassName.ToString());
}
//为excel表添加数据
sheet1.SetColumnWidth(0, 20 * 250);
sheet1.SetColumnWidth(1, 30 * 250);
sheet1.SetColumnWidth(2, 45 * 290);
//根据内容动态调整excel单元格⾼度
IRow currentRow = sheet1.GetRow(sheet1.FirstRowNum);
ICell currentCell = currentRow.GetCell(sheet1.FirstRowNum);
int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;
currentRow.HeightInPoints = 20 * (length / 60 + 1);
声明一个存放内存流的容器
MemoryStream ms = new MemoryStream();
excel文件写入内存流
excelBook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
//最后返回页面,以文件形式返回,再返回之前先给excel表格命名一个名字
string fileName = "年级" + DateTime.Now.ToString("yyyyMMdd") + ".xls";
return File(ms, "application/vnd.ms-excel", fileName);
}