通过Excel导入，入库数据
cshtml代码：
<div class="modal-header">
<h4 class="modal-title">批量入库</h4>
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
</div>
<div class="modal-body">
<div class="card">
<input type="file" id="ImportFile" name="ImportFile" class="dropify" />
</div>
所选文件:<p id="CunFileName">未上传</p>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-info waves-effect" data-dismiss="modal" onclick="ImportCardInfo()">确定</button>
<button type="button" class="btn btn-default waves-effect" data-dismiss="modal">取消</button>
</div>
<script>
$("#ImportFile").change(function () {
var FileSrcs = $("#ImportFile").val();
var FileArr = new Array();
FileArr = FileSrcs.split('\');
var FileSrc = FileArr[FileArr.length - 1];
$("#CunFileName").text(FileSrc);
});
function ImportCardInfo()
{
var files = $('input[name="ImportFile"]').prop('files');//获取到文件列表
if (files.length == 0) {
alert('请选择文件');
return;
} else {
var formFile = new FormData();
formFile.append("DrawingFilePath", files[0]); //加入文件对象
$.ajax({
url: "/Card/ImportCardInfo",
data: formFile,
type: "Post",
dataType: "json",
cache: false,//上传文件无需缓存
processData: false,//用于对data参数进行序列化处理 这里必须false
contentType: false, //必须
beforeSend: function () {
//提示上传中…
alert("正在上传，请稍后…");
},
success: function (data) {
if (data.succ) {
alert(data.msg);
$("#ContentInfoDiv").load("/Card/CardPage");
}
else {
ErrorMsg(data.msg);
return false;
}
}
});
}
}
</script>
controller：
#region 批量入库
/// <summary>
/// 批量入库
/// </summary>
public JsonResult ImportCardInfo(HttpPostedFileBase[] DrawingFilePath)
{
string msg = null;//返回的信息
bool succ = false;//是否成功
string UpFileSrc = "";
try
{
if (DrawingFilePath != null && DrawingFilePath.Count() > 0)
{
if (DrawingFilePath[0] != null)
{
#region excel存至本地
//目录
string directoryPathTEST = Server.MapPath("~/tempExc");
if (!Directory.Exists(directoryPathTEST))
Directory.CreateDirectory(directoryPathTEST);
//文件路径
UpFileSrc = directoryPathTEST + "/" + Guid.NewGuid() + ".xls";
DrawingFilePath[0].SaveAs(UpFileSrc);
#endregion
int rowscount = 0;
string returnInfo = ImportDataInfo(UpFileSrc,out rowscount);
if (returnInfo == "succ")
{
succ = true;
msg = "批量上传成功！";
}
else
{
succ = false;
if (rowscount == 0)
{
msg = "未检测到数据源！";
}
else
{
msg = "批量上传失败！";
}
}
}
else
{
succ = false;
msg = "文件不能为空！";
}
}
else
{
succ = false;
msg = "文件不能为空！";
}
}
catch (Exception ex)
{
succ = false;
msg = ex.Message;
}
var json = new { msg = msg, succ = succ };
return Json(json);//返回json
}
#region 批量上传设备
public string ImportDataInfo(string FileSrc,out int rowscount)
{
rowscount = 0;
try
{
if (!string.IsNullOrWhiteSpace(FileSrc))
{
//以本地路径上传的excel做为数据源
string conStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data source={0}; Extended Properties=Excel 12.0;", FileSrc);
using (OleDbConnection conn = new OleDbConnection(conStr))
{
conn.Open();
////获取所有Sheet的相关信息
//DataTable dtSheet = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
////获取第一个 Sheet的名称
//string sheetName = dtSheet.Rows[0]["Table_Name"].ToString();
string sheetName = "CardTemp$";
string sql = string.Format("select * from [{0}]", sheetName);
using (OleDbDataAdapter oda = new OleDbDataAdapter(sql, conn))
{
DataTable dt = new DataTable();
oda.Fill(dt);
rowscount = dt.Rows.Count;
List<CardTable> cardList = new List<CardTable>();
//i代表行 自动剔除标题行
for (int i = 0; i < dt.Rows.Count; i++)
{
CardTable cardInfo = new CardTable();
cardInfo.CardID = CommHelper.CreatePKID("card");
cardInfo.CCID = dt.Rows[i][0].ToString();
cardInfo.Operator = dt.Rows[i][1].ToString();
cardInfo.CardType = dt.Rows[i][2].ToString();
cardInfo.MealID = dt.Rows[i][3].ToString();
cardInfo.SleepLong = Convert.ToDateTime("2000-01-01");
cardInfo.IOStatus = 1;
db.CardTable.Add(cardInfo);
}
if (db.SaveChanges() > 0)
{
return "succ";
}
else
{
return "失败";
}
}
}
}
else
{
return "未检测到数据源！";
}
}
catch (Exception ex)
{
return ex.Message;
}
}
#endregion
#endregion