下载本地

namespace MvcApplication1_EXCEL.Controllers
{
public class ExcelController : Controller
{
//
// GET: /Excel/

public ActionResult Index()
{
return View();
}

[HttpPost]
public ActionResult UpExcel(HttpPostedFileBase ExcFile)
{

string strfileName = Server.MapPath("/EXCEL/"); //存储文件的地方

if (!Directory.Exists(strfileName)) //判断文件路径是否存在
{
Directory.CreateDirectory(strfileName);
}
string fName = Path.GetFileName(ExcFile.FileName); //获取文件名
ExcFile.SaveAs(strfileName + fName);

#region /// Execl导入部分

//execl文件读取
ExcelDAL exc = new ExcelDAL();
DataTable dt = exc.ExcelToDS(strfileName + fName);

//把读取的数据导入到数据库
List<Student> sdList = new List<Student>();

foreach (DataRow dr in dt.Rows)
{
Student student = new Student();
student.id = Convert.ToInt32(dr[0]);
student.name = dr[1].ToString();
student.sex = dr[2].ToString();
student.age = Convert.ToInt32(dr[3]);
sdList.Add(student);

}

#endregion

return View("Index");
}

/// <summary>
/// 导出 export
/// </summary>
/// <returns></returns>
public ActionResult ExcInput()
{
#region /// 查询部分

List<Student> ls = GetStudentList();

#endregion

ExcelDAL exc = new ExcelDAL();

exc.ExcelToAdd("D:/studentDemo.xls", ls);
return View();
}

public FileResult NpoiToExcel()
{

//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//--
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("编号");
row1.CreateCell(1).SetCellValue("姓名");
row1.CreateCell(2).SetCellValue("性别");
row1.CreateCell(3).SetCellValue("年龄");
List<Student> sList = GetStudentList();

//将数据逐步写入sheet1各个行
for (int i = 0; i < sList.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(sList[i].id);
rowtemp.CreateCell(1).SetCellValue(sList[i].name);
rowtemp.CreateCell(2).SetCellValue(sList[i].sex);
rowtemp.CreateCell(3).SetCellValue(sList[i].age);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "学生信息.xls");
}

List<Student> GetStudentList()
{
List<Student> retList = new List<Student>();
Student sd1 = new Student();
sd1.id = 1;
sd1.name = "学生1";
sd1.age = 20;
sd1.sex = "男";

Student sd2 = new Student();
sd2.id = 2;
sd2.name = "学生2";
sd2.age = 20;
sd2.sex = "女";

Student sd3 = new Student();
sd3.id = 3;
sd3.name = "学生3";
sd3.age = 20;
sd3.sex = "男";

Student sd4 = new Student();
sd4.id = 4;
sd4.name = "学生4";
sd4.age = 20;
sd4.sex = "女";

retList.Add(sd1);
retList.Add(sd2);
retList.Add(sd3);
retList.Add(sd4);

return retList;

}
}
}