using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Net;
|
using System.Net.Http;
|
using System.Web;
|
using System.Web.Http;
|
using VueWebApi.DLL.BLL;
|
using VueWebApi.Models;
|
using VueWebApi.Tools;
|
|
namespace VueWebApi.Controllers
|
{
|
[RoutePrefix(prefix: "api/ImportExcel")]
|
[ControllerGroup("Excel导入模板验证", "在线接口")]
|
public class ImportExcelController : ApiController
|
{
|
//定义全局信息返回变量
|
ToMessage mes = new ToMessage();
|
|
|
#region [Excel导入模板下载]
|
/// <summary>
|
/// Excel导入模板下载
|
/// </summary>
|
/// <param name="FileCode">文件编码</param>
|
/// <returns></returns>
|
[Route(template: "DownLoadExcel")]
|
[HttpGet]
|
public HttpResponseMessage DownLoadExcel(string FileCode = null)
|
{
|
List<ScoreReport> list = ExcelList.ExcelData();
|
list = list.Where(s => s.FileCode == FileCode).ToList();
|
var filename = list[0].FileName + ".xls";
|
string fileip = System.Configuration.ConfigurationManager.AppSettings["FileIP"];
|
var msg = fileip + "/Excel/" + filename;
|
mes.code = "200";
|
mes.data = msg;
|
return TJson.toJson(mes);
|
}
|
#endregion
|
|
#region [Excel导入文件上传、模板验证、数据量、验证]
|
/// <summary>
|
/// Excel导入文件上传、模板验证、数据量、验证
|
/// </summary>
|
/// <param name="FileCode">文件编码</param>
|
/// <param name="files">上传文件</param>
|
/// <returns></returns>
|
[Route(template: "ExcelModelCheck")]
|
[HttpPost]
|
public HttpResponseMessage ExcelModelCheck()
|
{
|
|
string FileCode = HttpContext.Current.Request["FileCode"].ToString();
|
HttpPostedFile files = HttpContext.Current.Request.Files[0];
|
ExcelModelCheck list = new ExcelModelCheck();
|
Dictionary<object, object> dList = new Dictionary<object, object>();
|
list.json1 = ExcelCheckUpload(files, FileCode); //文件上传
|
if (list.json1.code == "300")
|
{
|
list.json1 = list.json1;
|
return TJson.toJson(list);
|
}
|
list.json2 = ExcelCheck(files, FileCode); //模板验证
|
if (list.json2.code == "300")
|
{
|
list.json2 = list.json2;
|
return TJson.toJson(list);
|
}
|
list.json3 = ExcelCheckCount(files, FileCode); //数据量验证
|
if (list.json3.code == "300")
|
{
|
list.json3 = list.json3;
|
return TJson.toJson(list);
|
}
|
return TJson.toJson(list);
|
}
|
#endregion
|
|
#region [Excel导入模板上传]
|
/// <summary>
|
/// Excel导入模板上传
|
/// </summary>
|
/// <param name="files">上传文件</param>
|
/// <param name="FileCode">文件编码</param>
|
/// <returns></returns>
|
[Route(template: "ExcelCheckUpload")]
|
[HttpGet]
|
[HiddenApi]
|
public ToMessage ExcelCheckUpload(HttpPostedFile files, string FileCode = null)
|
{
|
try
|
{
|
string savePath;
|
string filename = Path.GetFileName(files.FileName);
|
|
string path = System.Web.HttpContext.Current.Server.MapPath("/InExcel/" + FileCode + "/");
|
string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
|
string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
|
savePath = Path.Combine(path, filename);
|
if (!FileType.Contains(fileEx))
|
{
|
mes.code = "300";
|
mes.Message = "文件类型不对,上传失败！";
|
System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
|
return mes;
|
}
|
else if (Directory.Exists(path) == false)//如果不存在就创建file文件夹
|
{
|
Directory.CreateDirectory(path); //添加文件夹
|
files.SaveAs(savePath); //将选择的文件保存到指定文件夹下
|
mes.code = "200";
|
mes.Message = "文件上传成功！";
|
}
|
else if (Directory.Exists(savePath) == true) //如果存在重名文件就提示
|
{
|
System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
|
files.SaveAs(savePath); //将选择的文件保存到指定文件夹下
|
mes.code = "200";
|
mes.Message = "文件上传成功！";
|
}
|
else
|
{
|
files.SaveAs(savePath); //将选择的文件保存到指定文件夹下
|
mes.code = "200";
|
mes.Message = "文件上传成功！";
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region [Excel导入模板验证]
|
/// <summary>
|
/// Excel导入模板验证
|
/// </summary>
|
/// <param name="files">上传文件</param>
|
/// <param name="FileCode">文件编码</param>
|
/// <returns></returns>
|
[Route(template: "ExcelCheckUpload")]
|
[HttpGet]
|
[HiddenApi]
|
public ToMessage ExcelCheck(HttpPostedFile files, string FileCode = null)
|
{
|
try
|
{
|
string savePath;
|
|
string filename = Path.GetFileName(files.FileName);
|
string path = System.Web.HttpContext.Current.Server.MapPath("/InExcel/" + FileCode + "/");
|
savePath = Path.Combine(path, filename);
|
if (Directory.Exists(path) == false)//如果不存在就创建file文件夹
|
{
|
Directory.CreateDirectory(path); //添加文件夹
|
}
|
else
|
{
|
mes = ExcelCheckBLL.ExcelCheck(FileCode, savePath);
|
if (mes.code == "300") //上传模板不是指定模板
|
{
|
System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
|
return mes;
|
}
|
}
|
return mes;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[Excel导入模板数据量验证]
|
/// <summary>
|
/// Excel导入模板数据量验证
|
/// </summary>
|
/// <param name="files">上传文件</param>
|
/// <param name="FileCode">文件编码</param>
|
/// <returns></returns>
|
[HiddenApi]
|
public ToMessage ExcelCheckCount(HttpPostedFile files, string FileCode = null)
|
{
|
string savePath;
|
|
string filename = Path.GetFileName(files.FileName);
|
int filesize = files.ContentLength;//获取上传文件的大小单位为字节byte
|
string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
|
int Maxsize = 10000 * 1024;//定义上传文件的最大空间大小为10M
|
string path = System.Web.HttpContext.Current.Server.MapPath("/InExcel/" + FileCode + "/");
|
savePath = Path.Combine(path, filename);
|
IWorkbook Workbook;
|
DataTable table = new DataTable();
|
using (FileStream fileStream = new FileStream(savePath, FileMode.Open, FileAccess.Read))
|
{
|
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
|
string fileExt = Path.GetExtension(savePath).ToLower();
|
if (fileExt == ".xls")
|
{
|
Workbook = new HSSFWorkbook(fileStream);
|
}
|
else if (fileExt == ".xlsx")
|
{
|
Workbook = new XSSFWorkbook(fileStream);
|
}
|
else
|
{
|
Workbook = null;
|
}
|
}
|
//定位在第一个sheet
|
ISheet sheet = Workbook.GetSheetAt(0);
|
//sheet.ShiftRows(sheet.FirstRowNum-1, sheet.LastRowNum,1);
|
//第一行为标题行
|
IRow headerRow = sheet.GetRow(0);
|
int cellCount = headerRow.LastCellNum;
|
//int rowCount0 = sheet.FirstRowNum;
|
int rowCount = sheet.LastRowNum;
|
if (files == null || files.ContentLength <= 0)
|
{
|
mes.code = "300";
|
mes.Message = "文件不能为空!";
|
System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
|
return mes;
|
}
|
mes = ImportExcel.ExcelCheckCountSum(savePath); //空数据文件验证
|
if (mes.code == "300")
|
{
|
|
mes.code = "300";
|
mes.Message = "文件不能为空！";
|
System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
|
return mes;
|
}
|
if (filesize >= Maxsize)
|
{
|
|
mes.code = "300";
|
mes.Message = "上传文件超过10M，不能上传";
|
System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
|
return mes;
|
}
|
mes.code = "200";
|
mes.Message = "数据量验证通过";
|
|
return mes;
|
}
|
#endregion
|
|
#region [Excel导入数据验证]
|
/// <summary>
|
/// Excel导入数据验证
|
/// </summary>
|
/// <param name="FileCode">文件编码</param>
|
/// <param name="FileName">文件名称</param>
|
/// <returns></returns>
|
[Route(template: "ExcelCheckData")]
|
[HttpPost]
|
public HttpResponseMessage ExcelCheckData(string FileCode = null, string FileName = null)
|
{
|
string message = "";
|
string StuCode = "";
|
int count = 0;
|
List<ExcelErro> list = new List<ExcelErro>();
|
string savePath;
|
string filename = FileName;
|
string path = System.Web.HttpContext.Current.Server.MapPath("/InExcel/" + FileCode + "/");
|
savePath = Path.Combine(path, filename);
|
list = ExcelCheckBLL.ExcelCheckData(FileCode, savePath, out StuCode, out message, out count);
|
if (list.Count > 0)
|
{
|
System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
|
}
|
Dictionary<object, object> dList = new Dictionary<object, object>();
|
dList.Add("code", StuCode);
|
dList.Add("Message", message);
|
dList.Add("Count", count);
|
dList.Add("list", list);
|
return TJson.toJson(dList);
|
}
|
#endregion
|
|
#region【Excel导入数据】
|
/// <summary>
|
/// Excel导入数据
|
/// </summary>
|
/// <param name="FileCode">文件编码</param>
|
/// <param name="FileName">文件名称</param>
|
/// <returns></returns>
|
[Route(template: "ExcelImportSubmit")]
|
[HttpPost]
|
public HttpResponseMessage ExcelImportSubmit(string FileCode = null, string FileName = null)
|
{
|
var username = HttpContext.Current.Request.Cookies["admin"].Value.ToString(); //登录用户名
|
string savePath;
|
string filename = FileName;
|
string path = System.Web.HttpContext.Current.Server.MapPath("/InExcel/" + FileCode + "/");
|
savePath = Path.Combine(path, filename);
|
mes = ExcelCheckBLL.ExcelImportSubmit(FileCode, savePath, username);
|
return TJson.toJson(mes);
|
}
|
#endregion
|
}
|
}
|
