Excel表格导入数据库

// GET: UploadExcel
        public ActionResult TestExcel(string filePath)
        {
            return View();
        }
        [HttpPost]
        public ActionResult TestExcel(FormCollection form)
        {
            HttpPostedFileBase file = Request.Files[0];
            string path = Server.MapPath("\\Models");
            path += "\\" + file.FileName;
            file.SaveAs(path);
            ImportExcelFile(path);
            return View();
        }

        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }


        /// <summary>
        /// Excel导入
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public DataTable ImportExcelFile(string filePath)
        {
            HSSFWorkbook hssfworkbook;
            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion

            ISheet sheet = hssfworkbook.GetSheetAt(3);
            DataTable table = new DataTable();
            IRow headerRow = sheet.GetRow(0);//第一行为标题行
            int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
            int rowCount = sheet.LastRowNum - 2;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            for (int i = (sheet.FirstRowNum + 4); i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = GetCellValue(row.GetCell(j));
                    }
                }

                table.Rows.Add(dataRow);
            }
            using (SqlBulkCopy abc = new SqlBulkCopy(SqlConnectionFactory.Connection))
            {
                abc.BatchSize = table.Rows.Count;
                abc.BulkCopyTimeout = 11;
                abc.DestinationTableName = "ExcelTable";
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    abc.ColumnMappings.Add(table.Columns[i].ColumnName, i);
                }
                abc.WriteToServer(table);
            }
            return table;
        }


        public string ExcelSelect()
        {
            using (SqlConnection con = SqlConnectionFactory.Connection)
            {
                string sql = "select Tnumber, Tname, Depter, Bdate, Beonduty, GetoffWork, BeondutyTwo, GetoffWorkTwo, Belate, Leaver, Absenceoftime, Total, BText from ExcelTable";
                var list = con.Query(sql);
                return JsonConvert.SerializeObject(list);
            }
        }
        public string Test(TestModel test, string url)
        {
            HttpClient httpClient = new HttpClient();
            var list = httpClient.GetStringAsync(url);
            string bb = list.Result;

            return "";
        }
        public string A2(TestModel test)
        {   
            HttpClient client = new HttpClient();
            var aaa = client.GetStringAsync("https://www.layui.com/test/table/demo1.json");
            var bbb = aaa.Result;
            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();
            var json = JsonConvert.DeserializeObject<dynamic>(bbb);

            for (int i = 0; i < json.data.Count; i++)
            {
                test.username = json.data[i].username;
                test.email = json.data[i].email;
                test.sex = json.data[i].sex;
                test.city = json.data[i].city;
                test.sign = json.data[i].sign;
                test.ip = json.data[i].ip;
                test.logins = json.data[i].logins;
                test.joinTime = json.data[i].joinTime;
                string sql = "insert into sssss values(@username,@email,@sex,@city,@sign,@experience,@ip,@logins,@joinTime)";
                using (SqlConnection con = SqlConnectionFactory.Connection)
                {
                    con.Execute(sql, test);
                }
                Console.WriteLine(Convert.ToDateTime(json.data[i].joinTime));
            }

            stopWatch.Stop();

            TimeSpan ts = stopWatch.Elapsed;

            string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                ts.Hours, ts.Minutes, ts.Seconds,
                ts.Milliseconds / 10);
            Console.WriteLine("RunTime " + elapsedTime);
            return bbb;
        }
        public string Cast(string url)
        {
            var test = HttpClientHelp.GetApi("get", url, "");
            var list = Newtonsoft.Json.JsonConvert.DeserializeObject<TestDataModel>(test);
            string sql = "select * from sssss";
            DataTable dt = DBhelper.QuerySql(sql);
            using (SqlBulkCopy abc = new SqlBulkCopy(SqlConnectionFactory.Connection))
            {
                abc.BatchSize = dt.Rows.Count;
                abc.BulkCopyTimeout = 10;
                abc.DestinationTableName = "sssss";
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    abc.ColumnMappings.Add(dt.Columns[i].ColumnName, i);
                }
                abc.WriteToServer(dt);
            }

            return "";
        }

        [HttpGet]
        public string tests(string url)
        {
            var testtt = HttpClientHelp.GetApi("get", url, "");
            var list = Newtonsoft.Json.JsonConvert.DeserializeObject<TestDataModel>(testtt);
            var test = HttpClientHelp.GetApi("post", url, list);
            DataTable table = new DataTable();


            return testtt;
        }

        [HttpGet]
        public string test(string url, TestModel m)
        {
            if (url.StartsWith("https"))
                System.Net.ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls;
            HttpClient httpClient = new HttpClient();
            httpClient.DefaultRequestHeaders.Accept.Add(
              new MediaTypeWithQualityHeaderValue("application/json"));
            HttpResponseMessage response = httpClient.GetAsync(url).Result;

            if (response.IsSuccessStatusCode)
            {
                string result = response.Content.ReadAsStringAsync().Result;
                var listto = Newtonsoft.Json.JsonConvert.DeserializeObject<TestDataModel>(result);
                foreach (var item in listto.data)
                {
                    m.id = item.id;
                    m.username = item.username;
                    m.email = item.email;
                    m.sex = item.sex;
                    m.city = item.city;
                    m.sign = item.sign;
                    m.experience = item.experience;
                    m.ip = item.ip;
                    m.logins = item.logins;
                    m.joinTime = item.joinTime;
                    string sql = "insert into sssss values(@username,@email,@sex,@city,@sign,@experience,@ip,@logins,@joinTime)";
                    using (SqlConnection con = SqlConnectionFactory.Connection)
                    {
                        con.Execute(sql, m);
                    }
                }

                return result;

            }
            return null;

        }

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <script src="~/Scripts/jquery-3.3.1.js"></script>
</head>
<body>
    <div>
        <form action="/Home/TestExcel" enctype="multipart/form-data" method="post">
            <text>选择上传文件：（工作表名为“Sheet1”，“电脑号”在A1单元格。）</text>
            <input name="file" type="file" id="file" />
            <input type="submit" name="Upload" value="批量导入第一批电脑派位名册" />
        </form>

        <table>
            <thead>
                <tr>
                    <th>工号</th>
                    <th>姓名</th>
                    <th>所属部门</th>
                    <th>日期</th>
                    <th>上班</th>
                    <th>下班</th>
                    <th>上班</th>
                    <th>下班</th>
                    <th>下班</th>
                    <th>迟到时间(分钟)</th>
                    <th>早退时间(分钟)</th>
                    <th>缺勤时间(分钟)</th>
                    <th>合计(分钟)</th>
                    <th>备注</th>
                </tr>
            </thead>
            <tbody id="tb"></tbody>
        </table>
    </div>
    <script>
        $(document).ready(function () {
            $.ajax({
                url: '/Home/ExcelSelect',
                dataType: 'json',
                type: 'get',
                success: function (data) {
                    $(data).each(function () {
                        var tr = '<tr>'
                            + '<td>' + this.Tnumber + '</td>'
                            + '<td>' + this.Tname + '</td>'
                            + '<td>' + this.Depter + '</td>'
                            + '<td>' + this.Bdate + '</td>'
                            + '<td>' + this.Beonduty + '</td>'
                            + '<td>' + this.GetoffWork + '</td>'
                            + '<td>' + this.BeondutyTwo + '</td>'
                            + '<td>' + this.GetoffWorkTwo + '</td>'
                            + '<td>' + this.Belate + '</td>'
                            + '<td>' + this.Leaver + '</td>'
                            + '<td>' + this.Absenceoftime + '</td>'
                            + '<td>' + this.Total + '</td>'
                            + '<td>' + this.BText + '</td>'
                            + '</tr>';
                        $("#tb").append(tr);
                    })
                }
            })
        })
    </script>
</body>

</html>