导入 导出 压缩 解压

--DAL

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using Newtonsoft.Json;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
namespace DAL
{
public class stuDAL
{
public SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=lala;Integrated Security=True");
/// <summary>
/// 显示
/// </summary>
/// <returns></returns>
public List<stuModel> show()
{
string sql = "select * from stu";
SqlDataAdapter dr = new SqlDataAdapter(sql, conn);
DataTable ds = new DataTable();
dr.Fill(ds);
var p = JsonConvert.SerializeObject(ds);
var pp = JsonConvert.DeserializeObject<List<stuModel>>(p);
return pp;
}
/// <summary>
/// 添加
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
public int add(stuModel m)
{
string sql = "insert into stu values('" + m.Name + "','" + m.age + "')";
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
/// <summary>
/// 表
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public DataTable biao(string path)
{

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";

OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet0$]", conn);
DataSet ds = new DataSet();
oda.Fill(ds);
return ds.Tables[0];
}
}
}

--控制器

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using BLL;
using System.Data;
using Model;
namespace 练习上传.Controllers
{
public class showController : Controller
{
stuBLL bll = new stuBLL();
// GET: show
/// <summary>
/// 显示 压缩 解压
/// </summary>
/// <returns></returns>
public ActionResult Index()
{
var reust = new BLL.stuBLL().show();
return View(reust);
}
// GET: show
/// <summary>
/// 压缩
/// </summary>
/// <returns></returns>
public ActionResult ya()
{
ZipHandler.ZipDirectory(@"C:Users过客Desktop练习练习上传 压缩 解压 简便", @"C:Users过客Desktop练习Demo1.zip", "123");
List<stuModel> ls = bll.show(); //返回显示
return View("Index", ls);

}
// GET: show
/// <summary>
/// 解压
/// </summary>
/// <returns></returns>
public ActionResult jie()
{
ZipHandler.UnZipFile(@"C:Users过客Desktop练习Demo1.zip", @"C:Users过客Desktop练习Demo1", "123");
//要解压的文件路径 2 解压成功的路径
List<stuModel> ls = bll.show();//返回显示
return View("Index",ls);
}
/// <summary>
/// 导出
/// </summary>
/// <returns></returns>
public FileResult DaoChu()
{
var p = new BLL.stuBLL().show();
if (p != null || p.Count > 0)
{
HSSFWorkbook book = new HSSFWorkbook();

ISheet sheet = book.CreateSheet();

IRow row = sheet.CreateRow(0);

row.CreateCell(0).SetCellValue("编号");
row.CreateCell(1).SetCellValue("名称");
row.CreateCell(2).SetCellValue("年龄");
for (int i = 0; i < p.Count; i++)
{
IRow rows = sheet.CreateRow(i + 1);
rows.CreateCell(0).SetCellValue(p[i].ID);
rows.CreateCell(1).SetCellValue(p[i].Name);
rows.CreateCell(2).SetCellValue(p[i].age);
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd-excel", "学生.xls");
}
else
{
MemoryStream ms = new MemoryStream();

ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application", "");
}
}
/// <summary>
/// 导入
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public ActionResult DaoRu(HttpPostedFileBase path)
{

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

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

#region /// Execl导入部分

//execl文件读取
stuBLL bll = new stuBLL();

DataTable dt = bll.biao(strfileName + fName);//把数据获取到

//把读取的数据导入到数据库
foreach (DataRow dr in dt.Rows)
{
stuModel m = new stuModel();
m.ID = Convert.ToInt32(dr[0]);
m.Name = dr[1].ToString();
m.age = dr[2].ToString();

bll.add(m);
}

#endregion

List<stuModel> ls = bll.show();

return View("Index", ls);
}
}
}

--前台

@{
Layout = null;
}
@model List<Model.stuModel>
<!DOCTYPE html>

<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
</head>
<body>
<div>
<form action="/show/DaoRu" enctype="multipart/form-data" method="post">
<input ;

}

</script>
</body>
</html>