//导出UI层

 

///视图

 

 

 

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Excels.Models;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Newtonsoft.Json;
using NPOI.XSSF.UserModel;

namespace Excels.Controllers
{
public class ExcelController : Controller
{
// GET: Excel
public ActionResult Index()
{
List<Test> list;
using (ExcelModel ctx = new ExcelModel())
{
list = ctx.Test.ToList();
}
return View(list);
}

#region 导出
/// <summary>
/// 导出
/// </summary>
/// <returns></returns>
[HttpGet]
public FileResult GetExecl()
{
List<Test> list;
using (ExcelModel ctx = new ExcelModel())
{
list = ctx.Test.ToList();
}

return ExportStu2(list);
}

/// <summary>
/// 批量导出本校第一批派位学生
/// </summary>
/// <returns></returns>
public FileResult ExportStu2(List<Test> listRainInfo)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//List<TB_STUDENTINFOModel> listRainInfo = GetAll();// m_BLL.GetSchoolListAATQ(schoolname);
//给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("年龄");
row1.CreateCell(4).SetCellValue("地址");
row1.CreateCell(5).SetCellValue("QQ号码");
row1.CreateCell(6).SetCellValue("手机号码");
//将数据逐步写入sheet1各个行
for (int i = 0; i < listRainInfo.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].id.ToString());
rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].Name.ToString());
rowtemp.CreateCell(2).SetCellValue(listRainInfo[i].Sex.ToString());
rowtemp.CreateCell(3).SetCellValue(listRainInfo[i].Age.ToString());
rowtemp.CreateCell(4).SetCellValue(listRainInfo[i].Place.ToString());
rowtemp.CreateCell(5).SetCellValue(listRainInfo[i].QQNumber.ToString());
rowtemp.CreateCell(6).SetCellValue(listRainInfo[i].Phone.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "第一批电脑派位生名册.xls");
}
#endregion


#region 导出
[HttpPost]
public ActionResult ImportExcel(FormCollection form)
{

HttpPostedFileBase file = Request.Files[0];

string path = Server.MapPath("\\Content\\UploadFile");

path += "\\" + file.FileName;
file.SaveAs(path);
DataTable dt= ImportExcelFile(path);//xlsx格式
if (dt==null)
{
return Content("<script>alert('选择的不是Excel文档')</script>");
}
else
{
List<Test> list = JsonConvert.DeserializeObject<List<Test>>(JsonConvert.SerializeObject(dt));

for (int i = 0; i < list.Count; i++)
{
Test test = list[i];
using (ExcelModel ctx = new ExcelModel())
{
ctx.Test.Add(test);
ctx.SaveChanges();
}
}
return Content("<script>alert('导入成功')</script>");
}

}

/// <summary>
/// Excel导入
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public DataTable ImportExcelFile(string file)
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); }
else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); }
else { workbook = null; }
if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheetAt(0);

//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
ICell cell = sheet.GetRow(i).GetCell(j);
dr[j] = GetValueType(cell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}

/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
}
}