|
導(dǎo)入導(dǎo)出引用NPOI
? ?視圖 
1 <input type="button" name="name" class="btn btn-success btn-sm" onclick="GetExcel()" value="導(dǎo)出" />
2 <form action="/Default/Import" method="post" enctype="multipart/form-data">
3 <input type="file" name="file" id="file" />
4 <input type="submit" name="name" class="btn btn-success btn-sm" value="導(dǎo)入" />
5 </form>
6 <script>
7 //導(dǎo)出
8 function GetExcel() {
9 //window.location.href刷新當(dāng)前頁面,當(dāng)前頁面打開URL頁面,同步提交
10 window.location.href = "@Url.Action("ExportByNPOI")";
11 }
12 </script>
Index.cshtml
控制器
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Web;
using System.Web.Mvc;
using 導(dǎo)入導(dǎo)出.Models;
namespace 導(dǎo)入導(dǎo)出.Controllers
{
public class DefaultController : Controller
{
/// <summary>
/// 數(shù)據(jù)存儲(chǔ)
/// </summary>
//List<StudentViewModel> stu = new List<StudentViewModel>()
//{
// new StudentViewModel{Id=1,Name="小明",Sex="男",DateTime="2019-11-07",Static=1},
// new StudentViewModel{Id=2,Name="小紅",Sex="女",DateTime="2019-11-08",Static=0},
// new StudentViewModel{Id=3,Name="小蘭",Sex="女",DateTime="2019-11-09",Static=0},
// new StudentViewModel{Id=4,Name="小天",Sex="男",DateTime="2019-11-11",Static=1},
// new StudentViewModel{Id=5,Name="阿亮",Sex="男",DateTime="2019-11-12",Static=0},
// new StudentViewModel{Id=6,Name="王大力",Sex="男",DateTime="2019-11-13",Static=1}
//};
string sql = "select * from student";
// GET: Default
public ActionResult Index()
{
List<StudentViewModel> stu = MySqlDBHelper.GetList<StudentViewModel>(sql);
return View(stu);
}
/// <summary>
/// 導(dǎo)出Excel
/// </summary>
/// <returns></returns>
public ActionResult ExportByNPOI()
{
List<StudentViewModel> stu = MySqlDBHelper.GetList<StudentViewModel>(sql);
//1、獲取數(shù)據(jù)源
var result = stu;
var list = result.Select(x => new { x.Id, x.Name, x.Sex, x.DateTime, x.Static }).ToList();
//2、創(chuàng)建Excel文件的對(duì)象
NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook();
//3、添加一個(gè)sheet
NPOI.SS.UserModel.ISheet sheet = excel.CreateSheet("Sheet1");
//給sheet1添加標(biāo)題行
NPOI.SS.UserModel.IRow head = sheet.CreateRow(0);
head.CreateCell(0).SetCellValue("編號(hào)");
head.CreateCell(1).SetCellValue("姓名");
head.CreateCell(2).SetCellValue("性別");
head.CreateCell(3).SetCellValue("入學(xué)時(shí)間");
head.CreateCell(4).SetCellValue("狀態(tài)");
//將數(shù)據(jù)逐步寫入sheet1各個(gè)行
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].Sex);
row.CreateCell(3).SetCellValue(list[i].DateTime);
row.CreateCell(4).SetCellValue(list[i].Static);
}
//寫入到客戶端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
excel.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "顧客信息表.xls");
}
/// Excel導(dǎo)入
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public ActionResult Import(HttpPostedFileBase file)
{
string msg="";
if (file == null)
{
msg = "導(dǎo)入失敗";
}
else
{
//1、先保存上傳的excel文件（這一步與上傳圖片流程一致）
string extName = file.FileName;
string path = Server.MapPath("~/Content/Files");
string filename = Path.Combine(path, extName);
file.SaveAs(filename);
//2、讀取excel文件（通過oledb將excel數(shù)據(jù)填充到datatable）
//HDR=Yes，這代表第一行是標(biāo)題，不做為數(shù)據(jù)使用,IMEX的含義（0：寫入，1：讀取，2：讀取與寫入）
string filePath = filename;//必須是物理路徑
string conStr = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=False;Data Source=" filePath "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
OleDbDataAdapter adp = new OleDbDataAdapter("select * From[Sheet1$]", conStr);
//默認(rèn)讀取的Sheet1,你也可以把它封裝變量,動(dòng)態(tài)讀取你的Sheet工作表
DataTable dt = new DataTable();
adp.Fill(dt);
//3、將table轉(zhuǎn)化成list
List<StudentViewModel> list = new List<StudentViewModel>();
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
list.Add(new StudentViewModel()
{
//有哪個(gè)寫哪個(gè)
Id = int.Parse(item["編號(hào)"].ToString()),
Name = item["姓名"].ToString(),
Sex = item["性別"].ToString(),
DateTime = item["入學(xué)時(shí)間"].ToString(),
Static = int.Parse(item["狀態(tài)"].ToString())
});
}
}
//4、跨action傳值用tempdata
//TempData["list"] = list;
//return RedirectToAction("List");
//如果不直接導(dǎo)入數(shù)據(jù)庫這里不用寫
StudentViewModel model = new StudentViewModel();
for (int i = 0; i < list.Count; i )
{
model.Id = list[i].Id;
model.Name = list[i].Name;
model.Sex = list[i].Sex;
model.DateTime = list[i].DateTime;
model.Static = list[i].Static;
//調(diào)用添加方法
//var result = await baseRepository.Add(model);
//if (result > 0)
//{
// msg = "導(dǎo)入成功";
//}
DAL dal = new DAL();
int result = dal.Create(model);
if (result > 0)
{
msg = "導(dǎo)入成功！";
}
}
}
return Json(msg);
}
public class DAL {
public int Create(StudentViewModel model)
{
string sql = string.Format("insert into Student(Id,Name,Sex,DateTime,Static) values('{0}','{1}','{2}','{3}','{4}')", model.Id, model.Name, model.Sex, model.DateTime, model.Static);
int result = MySqlDBHelper.ExecuteNonQuery(sql);
return result;
}
}
}
}
Controller
? ? 來源：https://www./content-1-550351.html |
|
|
