前端代码：
html:
<form enctype="multipart/form-data" id="file-form">
<p>
<h3 style="color:red">上传的Excel要与指标项名字一致</h3>
<input type="file" name="filed" id="filed" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" />
<button type="button" class="btn-sm btn-primary pull-right" id="btn-Search" onclick="importExcel()"><i class="fa fa-upload"></i>导入Excel</button>
</p>
</form>
js:
// 导入Excel
function importExcel() {
var formData = new FormData()
//多个才each
//$.each($("#filed")[0].files, function (index, obj) {
// formData.append("cusFile", obj);
//});
console.log($("#filed")[0].files[0]);
formData.append("cusFile", $("#filed")[0].files[0]);
$.ajax({
url:'/FormInfo/UploadExcelData',
type: 'POST',
data: formData,
async: false,
cache: false, //告诉浏览器不缓存
contentType: false, //因为data值是FormData对象，不需要对数据做处理。
processData: false,
success: function (data) {
alert(data);
$("#filed").val() = "";
$("#importExcelModal").modal('hide');
}
});
}
后端代码：
/// <summary>
/// 导入
/// </summary>
/// <param name="path">没有什么用处</param>
/// <returns></returns>
public ActionResult UploadExcelData(string path)
{
HttpPostedFileBase files = Request.Files["cusFile"];//与formData.append("cusFile", $("#filed")[0].files[0]);的""要一直
if (files == null)
{
return Content("请选择要上传Excel文件");
}
string name = files.FileName;
string itemName = name.Substring(0, name.IndexOf("."));
//判断是不是Excel文件
string currFileExtension = name.Substring(name.LastIndexOf("."));
if (currFileExtension != ".xlsx" && currFileExtension != ".xls")
{
return Content("请上传Excel文件");
}
Session["mathpath"] = name;
//Resources/ImportExcel/ 存放的路径
string mathpath = HttpRuntime.AppDomainAppPath + "Resources/ImportExcel/" + name;
files.SaveAs(mathpath);
path = HttpRuntime.AppDomainAppPath + "Resources/ImportExcel/" + name;
DataTable dt = Import_Excel(path, "sheet1");
if (dt.Rows.Count == 0)
{
return Content("导入失败，请选择有效文件");
}
System.Transactions.TransactionOptions transactionOptions = new System.Transactions.TransactionOptions();
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead;
using (var trans = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, transactionOptions))
{
try
{
foreach (DataRow dr in dt.Rows)
{
//对数据进行操作
......
}
}
trans.Complete();//就这句就可以了。
}
catch (Exception e)
{
return Content("列错误，请选择有效文件");
}
}
return Content("导入成功!");
}
/// <summary>
/// 查询Excel表
/// </summary>
/// <param name="filePath"></param>
/// <param name="SheetName"></param>
/// <returns></returns>
public DataTable Import_Excel(string filePath, string SheetName)
{
try
{
string currFileExtension = filePath.Substring(filePath.LastIndexOf("."));
//连接字符串
string sqlconn = string.Empty;
if (currFileExtension == ".xlsx")
{
sqlconn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; // Office 07及以上版本
}
else if (currFileExtension == ".xls")
{
sqlconn = @"Provider=Microsoft.JET.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; //Office 07以下版本
}
string[] str = GetExcelSheetNames(filePath);//17-4-10 替换
string sql = @"select * from [" + SheetName + "$]";
using (OleDbConnection conn = new OleDbConnection(sqlconn))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn))
{
System.Data.DataTable dt = new System.Data.DataTable();
adapter.Fill(dt);
return dt;
}
}
}
catch
{
DataTable ss = new DataTable();
return ss;
}
}
public String[] GetExcelSheetNames(string fileName)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
string connString = string.Empty;
string FileType = fileName.Substring(fileName.LastIndexOf("."));
if (FileType == ".xls")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
else//.xlsx
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties="Excel 12.0;HDR=YES;IMEX=1"";
// 创建连接对象
objConn = new OleDbConnection(connString);
// 打开数据库连接
objConn.Open();
// 得到包含数据架构的数据表
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// 添加工作表名称到字符串数组
foreach (DataRow row in dt.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
//过滤无效SheetName
if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))
{
excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
}
i++;
}
return excelSheets;
}
catch (Exception ex)
{
return null;
}
finally
{
// 清理
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}