前端代码：
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();
???????????????}
???????????}
???????}
.NET Excel导入
原文地址：https://www.cnblogs.com/zhaoyang021/p/9888558.html