protected void Button_导入_Click(object sender, EventArgs e)
{
if (UploadExcel.FileName == "")
{
ClassLibrary1.ClassMessage.HinXi(Page, "excel文件导入不存在");
return;
}
var a = Path.GetExtension(UploadExcel.FileName);
if (a != ".xlsx" && a != ".xls")
{
ClassLibrary1.ClassMessage.HinXi(Page, "excel格式不对");
}
//选择上传的图片
if (UploadExcel.PostedFile.ContentLength > (4 * 1024 * 1024))
{
ClassLibrary1.ClassMessage.HinXi(Page, "excel文件过大不能导入");
}
else
{
string rootPath = Path.Combine(HttpRuntime.AppDomainAppPath.ToString(), "UploadFile");
if (!Directory.Exists(rootPath))
{
Directory.CreateDirectory(rootPath);
}
try
{
var path = Path.Combine(rootPath, UploadExcel.FileName);
UploadExcel.SaveAs(path);
var ExcelData = ExcelToDS(path);
if (ExcelData == null)
{
ClassLibrary1.ClassMessage.HinXi(Page, "excel导入失败");
return;
}
// 操作数据源
for (int index = 0; index < ExcelData["交易方卡号"].Count(); index++)
{
AddNewRowToGrid2(
index,
ExcelData["交易方卡号"][index],
ExcelData["交易方姓名"][index],
ExcelData["交易方银行"][index],
ExcelData["交易金额"][index],
ExcelData["备注"][index]);
}
}
catch (Exception ex)
{
ClassLibrary1.ClassMessage.HinXi(Page, "excel导入失败,请你的excel 文档符合本页面表格排版要求(相同),并且必须符合表格所要求的,头行列 交易方卡号 交易方姓名 交易方银行 交易金额， 备注 字段必须有");
System.IO.File.Delete(Path.Combine(rootPath, UploadExcel.FileName));
}
System.IO.File.Delete(Path.Combine(rootPath, UploadExcel.FileName));
ClassLibrary1.ClassMessage.HinXi(Page, "excel导入成功");
}
}
public async Task InsertExcel(UploadExcel uploadExcel)
{
var conn = _connectionFactory.GetConnection;
var query = $"[InsertExcelData]";
var param = new DynamicParameters();
param.Add("@Details", uploadExcel.DetailsDT.AsTableValuedParameter("ClaimUploadDetailType"));
await conn.QueryFirstOrDefaultAsync <UploadExcel>(query, param, commandType : CommandType.StoredProcedure);
}
public async Task <IActionResult> InsertExcel()
{
try
{
var file = HttpContext.Request.Form.Files[0];
var uploadPath = Path.Combine(_webHostEnvironment.ContentRootPath, "Files", "Request Book");
var ext = Path.GetExtension(file.FileName)?.ToLower();
var extensions = new string[] { ".xls", ".xlsx" };
var flag = false;
foreach (var item in extensions)
{
if (item != ext)
{
continue;
}
flag = true;
break;
}
if (!flag)
{
throw new Exception("Not a valid extension !");
}
var fileName = DateTime.UtcNow.AddMinutes(345).ToString("yyyyMMddhhmmssfff") + ext;
await using var ms = new MemoryStream();
await file.CopyToAsync(ms);
var package = new ExcelPackage(ms);
var workbook = package.Workbook;
var worksheet = workbook.Worksheets.FirstOrDefault(x => x.Name == "STUDENT_INFO");
if (worksheet is null)
{
throw new Exception("A worksheet with name Request Book cannot be found!");
}
var rows = worksheet.Dimension.Rows;
var columns = worksheet.Dimension.Columns;
if (rows <= 1 || columns <= 1)
{
throw new Exception("Unable to find data!");
}
var dt = new DataTable();
for (var row = 1; row <= 1; row++)
{
for (var col = 1; col <= columns; col++)
{
dt.Columns.Add(worksheet.Cells[row, col].Value.ToString()?.Trim());
}
}
for (var row = 2; row <= rows; row++)
{
var dr = dt.NewRow();
for (var col = 1; col <= columns; col++)
{
dr[col - 1] = worksheet.Cells[row, col].Value.ToString()?.Trim();
}
dt.Rows.Add(dr);
}
var objUploadExcel = new UploadExcel();
objUploadExcel.DetailsDT = dt;
await _uploadDownloadExcel.InsertExcel(objUploadExcel);
var filePath = Path.Combine(uploadPath, fileName);
await using var fileStream = new FileStream(filePath, FileMode.Create);
await file.CopyToAsync(fileStream);
return(Ok("Save Changes Successfully"));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}