网上搜的类似资料一大堆，能用没几个。这个是自己工作中用到的，特此记录。
(
本文章以下Js方法需要自己改写, 或者注释掉, 或者使用Console.log() 来查看执行步骤，
Loading(true, "拼命导入中..请稍后...");
$.download("../../TicketManage/SendGoods/DownLoadTemplate", 'post');
dialogMsg(e, -1);
)
Util.Offices.ExcelHelper.ExcelToDt(stream); 使用Epplus把流转换成DataTable(代码下面贴出了)
----------------------------------------------------------------------------不华丽的代码分割线------------------------------------------------------------------------------------------
uploadForm.cshtml
@{
ViewBag.Title = "数据导入";
Layout = "~/Views/Shared/_Index.cshtml";
}
<script src="~/Content/scripts/plugins/ajaxfileupload/ajaxfileupload.js"></script>
<script>
$(function () {
$('#uploadFile').bind("change", function (e) {
//alert("a");
});
});
//开始导入
function btn_importdata() {
Loading(true, "拼命导入中..请稍后...");
$.ajaxFileUpload({
url: '../../TicketManage/SendGoods/Import',//后台请求地址
type: 'POST',//请求方式 当要提交自定义参数时，这个参数要设置成post
secureuri: false,//是否启用安全提交，默认为false。
fileElementId: 'uploadFile',// 需要上传的文件域的ID，即<input type="file">的ID。
dataType: 'json',
success: function (data) {//提交成功后自动执行的处理函数，参数data就是服务器返回的数据。
$("#msg").html("");
var d = data.msg.split('\n');
if (data.state) {
$("#msg").attr("class", "alert alert-success").append(data.msg);
} else {
$("#msg").attr("class", "alert alert-danger");
for (var i = 0; i < d.length; i++) {
if (+d[i] != '') {
$("#msg").append("<strong>警告！</strong>" + d[i] + "<br/>");
}
}
}
$("#uploadFile").val('');//解决同名文件只发送一次请求的问题
$("#filetext").val('');//友好提示文件名
Loading(false);
},
error: function (json, status, e) {//提交失败自动执行的处理函数。
dialogMsg(e, -1);
$("#msg").html(e);
// console.log(data);
$("#uploadFile").val('');//解决同名文件只发送一次请求的问题
$("#filetext").val('');//友好提示
Loading(false);
}
});
}
//设置显示
function onchange_ShowFile(e) {
var file = $("#uploadFile").val();
//var pos = file.lastIndexOf("\\");
//console.log(file);
$("#filetext").val(file);
//console.log(file.substring(pos + 1));
}
//选择文件
function btn_getimportdata() {
$("#uploadFile").trigger('click');
}
//下载模板
function btn_downLoadTempte() {
$.download("../../TicketManage/SendGoods/DownLoadTemplate", 'post');
}
</script>
<div class="formHead">
<div class="mcp_container">
<div style="padding-left:5px;padding-bottom:20px">
<input id="uploadFile" name="uploadFile" onchange="onchange_ShowFile()" style="opacity: 0" type="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" />
<input readonly type="text" style="width: 350px;height:28px" id="filetext" />
<button id="choose" class="btn btn-default" onclick="btn_getimportdata()"> <i class="fa fa-hand-paper-o"></i> 选择</button>
<button id="lr-choose" class="btn btn-default" onclick="btn_importdata()"><i class="fa fa-sign-in"></i> 开始导入</button>
<button id="lr-import" class="btn btn-default" onclick="btn_downLoadTempte()"> <i class="fa fa-download"></i> 下载模板</button>
</div>
<div id="tips" class="alert alert-success">
<strong> 提示:</strong>1.下载模板文件并将相关数据正确填充 2.点击选择文件。3.点击开始导入。4.返回结果
</div>
<div class="alert alert-info" id="msg">
<strong>返回结果:</strong>在这里看返回结果哦<br />
</div>
</div>
</div>
后台代码:
#region 导入数据
[HttpPost]
public ActionResult Import()
{
var files = Request.Files;
JsonResult json = new JsonResult
{
ContentType = "text/html",
};
var currentUser = OperatorProvider.Provider.Current();
if (files.Count > 0)
{
var stream = files[0].InputStream;
var name = files[0].FileName;
if (name.IsEmpty())
{
json.Data = new { state = false, msg = "请先选择文件" }; return json;
}
if (name.Length <= 4)
{
json.Data = new { state = false, msg = "文件格式不正确" }; return json;
}
if (name.Substring(name.Length - 4).ToUpper() != "XLSX")
{
json.Data = new { state = false, msg = "文件格式不正确,请使用xlsx文件" }; return json;
}
var dt = Util.Offices.ExcelHelper.ExcelToDt(stream);
List<DataRow> removelist = new List<DataRow>();
for (int i = 0; i < dt.Rows.Count; i++)
{
bool IsNull = true;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
{
IsNull = false;
}
}
if (IsNull)
{
removelist.Add(dt.Rows[i]);
}
}
for (int i = 0; i < removelist.Count; i++)
{
dt.Rows.Remove(removelist[i]);
}
if (dt.Rows.Count == 0)
{
json.Data = new { state = false, msg = "文件中无数据,请重新上传。" }; return json;
}
var res = sendgoodsbll.Import(dt);
json.Data = new { state = res.Item1, msg = res.Item2 };
return json;
}
else
{
json.Data = new { state = false, msg = "上传失败,无文件" }; return json;
}
}
[HttpPost]
public void DownLoadTemplate()
{
var fileName = Server.MapPath("~/Resource/Template/导入终端发货单模板.xlsx");
FileHelper.DownLoadold(fileName, "导入终端发货单模板.xlsx");
}
#endregion
把文件流转换成DataTable帮助类:
public static DataTable ExcelToDt(Stream stream)
{
ExcelPackage package = new ExcelPackage(stream);
ExcelWorksheet sheet = package.Workbook.Worksheets[1];
DataTable dt = new DataTable();
//for (int col = 0; col < sheet.Dimension.End.Column; col++)
//{
// dt.Columns.Add(col.ToString(), );
//}
foreach (var firstRowCell in sheet.Cells[1, 1, 1, sheet.Dimension.End.Column])
{
dt.Columns.Add(firstRowCell.Text, Type.GetType("System.String"));
}
int startRowIndx = sheet.Dimension.Start.Row + 1;//去掉列头
for (int r = startRowIndx; r <= sheet.Dimension.End.Row; r++)
{
DataRow dr = dt.NewRow();
for (int c = sheet.Dimension.Start.Column; c <= sheet.Dimension.End.Column; c++)
{
if (sheet.Cells[r, c].Style.Numberformat.Format.IndexOf("yyyy") > -1
&& sheet.Cells[r, c].Value != null)//注意这里，是处理日期时间格式的关键代码
{
dr[c - 1] = sheet.Cells[r, c].GetValue<DateTime>();
}
else
dr[c - 1] = (sheet.Cells[r, c].Value ?? DBNull.Value)?.ToString()?.Trim();
}
dt.Rows.Add(dr);
}
return dt;
}
本人修改后的ajaxfileupload.js文件
jQuery.extend({
createUploadIframe: function (id, uri) {
//create frame
var frameId = 'jUploadFrame' + id;
var iframeHtml = '<iframe id="' + frameId + '" name="' + frameId + '" style="position:absolute; top:-9999px; left:-9999px"';
if (window.ActiveXObject) {
if (typeof uri == 'boolean') {
iframeHtml += ' src="' + 'javascript:false' + '"';
}
else if (typeof uri == 'string') {
iframeHtml += ' src="' + uri + '"';
}
}
iframeHtml += ' />';
jQuery(iframeHtml).appendTo(document.body);
return jQuery('#' + frameId).get(0);
},
createUploadForm: function (id, fileElementId, data) {
//create form
var formId = 'jUploadForm' + id;
var fileId = 'jUploadFile' + id;
var $form = jQuery('<form action="" method="POST" name="' + formId + '" id="' + formId + '" enctype="multipart/form-data"></form>');
if (data) {
for (var i in data) {
jQuery("<input type='hidden' name='" + i + "' value='" + data[i] + "' />").appendTo($form);
}
}
//console.log(data);
//console.log("基于官网的二次修改，作者:HandLoong");
var oldElement = jQuery('#' + fileElementId);
var newElement = jQuery(oldElement).clone();
jQuery(oldElement).attr('id', fileId);
jQuery(oldElement).before(newElement);
jQuery(oldElement).appendTo($form);
//var form = jQuery.createUploadForm(id, s.fileElementId, s.data);
//set attributes
$form.css('position', 'absolute');
$form.css('top', '-1200px');
$form.css('left', '-1200px');
$form.appendTo('body');
//console.log("创建一个from");
return $form;
},
ajaxFileUpload: function (s) {
// TODO introduce global settings, allowing the client to modify them for all requests, not only timeout
s = jQuery.extend({}, jQuery.ajaxSettings, s);
var id = new Date().getTime()
var $form = jQuery.createUploadForm(id, s.fileElementId, (typeof (s.data) == 'undefined' ? false : s.data));
var io = jQuery.createUploadIframe(id, s.secureuri);
var frameId = 'jUploadFrame' + id;
var formId = 'jUploadForm' + id;
// Watch for a new set of requests
if (s.global && !jQuery.active++) {
jQuery.event.trigger("ajaxStart");
}
var requestDone = false;
// Create the request object
var xml = {}
if (s.global)
jQuery.event.trigger("ajaxSend", [xml, s]);
// Wait for a response to come back
var uploadCallback = function (isTimeout) {
var io = document.getElementById(frameId);
try {
if (io.contentWindow) {
xml.responseText = io.contentWindow.document.body ? io.contentWindow.document.body.innerHTML : null;
xml.responseXML = io.contentWindow.document.XMLDocument ? io.contentWindow.document.XMLDocument : io.contentWindow.document;
} else if (io.contentDocument) {
xml.responseText = io.contentDocument.document.body ? io.contentDocument.document.body.innerHTML : null;
xml.responseXML = io.contentDocument.document.XMLDocument ? io.contentDocument.document.XMLDocument : io.contentDocument.document;
}
} catch (e) {
jQuery.handleError(s, xml, null, e);
}
if (xml || isTimeout == "timeout") {
requestDone = true;
var status;
try {
status = isTimeout != "timeout" ? "success" : "error";
// Make sure that the request was successful or notmodified
if (status != "error") {
// process the data (runs the xml through httpData regardless of callback)
var data = jQuery.uploadHttpData(xml, s.dataType);
// If a local callback was specified, fire it and pass it the data
if (s.success)
s.success(data, status);
// Fire the global callback
if (s.global)
jQuery.event.trigger("ajaxSuccess", [xml, s]);
} else
jQuery.handleError(s, xml, status);
} catch (e) {
status = "error";
jQuery.handleError(s, xml, status, e);
}
// The request was completed
if (s.global)
jQuery.event.trigger("ajaxComplete", [xml, s]);
// Handle the global AJAX counter
if (s.global && ! --jQuery.active)
jQuery.event.trigger("ajaxStop");
// Process result
if (s.complete)
s.complete(xml, status);
jQuery(io).unbind()
setTimeout(function () {
try {
jQuery(io).remove();
jQuery(form).remove();
} catch (e) {
jQuery.handleError(s, xml, null, e);
}
}, 100)
xml = null
}
}
// Timeout checker
if (s.timeout > 0) {
setTimeout(function () {
// Check to see if the request is still happening
if (!requestDone) uploadCallback("timeout");
}, s.timeout);
}
try {
//var form = jQuery('#' + formId);
$form.attr('action', s.url);
$form.attr('method', 'POST');
$form.attr('target', frameId);
if ($form.encoding) {
$form.attr('encoding', 'multipart/form-data');
}
else {
$form.attr('enctype', 'multipart/form-data');
}
console.log($form);
$form.submit();
} catch (e) {
jQuery.handleError(s, xml, null, e);
}
jQuery('#' + frameId).load(uploadCallback);
return { abort: function () { } };
},
uploadHttpData: function (r, type) {
var data = !type;
data = type == "xml" || data ? r.responseXML : r.responseText;
// If the type is "script", eval it in global context
if (type == "script")
jQuery.globalEval(data);
// Get the JavaScript object, if JSON is used.
if (type == "json")
eval("data = " + data);
// evaluate scripts within html
if (type == "html")
jQuery("<div>").html(data).evalScripts();
return data;
},
handleError: function (s, xhr, status, e) {
// If a local callback was specified, fire it
if (s.error) {
s.error.call(s.context || s, xhr, status, e);
}
// Fire the global callback
if (s.global) {
(s.context ? jQuery(s.context) : jQuery.event).trigger("ajaxError", [xhr, s, e]);
}
}
})
最后结果:
