// 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>
