上传的方法
[HttpPost]
public async Task<IActionResult> ImportData(IFormCollection f)
{
var file = Request.Form.Files[0];
bool result = false;
string errorMsg = string.Empty;
if (file.Length > 0)
{
DataTable dt = new DataTable();
string strMsg;
//利用IFormFile里面的OpenReadStream()方法直接读取文件流 Path.GetExtension获取制定路径的扩展名
dt = ExcelToDatatable(file.OpenReadStream(), Path.GetExtension(file.FileName), out strMsg);
if (!string.IsNullOrEmpty(strMsg))
{
result = false;
}
if (dt.Rows.Count > 0)
{
#region 数据处理
foreach (DataRow dr in dt.Rows)
{
string CompanyName= dr["企业名称"].ToString();
}
#endregion
return Json(new ReturnResult<string> { code = 0, message = "请求成功" });
}
else
{
result = false;
return Json(new ReturnResult<string> { code = 1, message = "数据为空" });
}
}
else
{
return Json(new ReturnResult<string> { code = 1, message = "请重新上传" });
}
}
excel转table
public DataTable ExcelToDatatable(Stream stream, string fileType, out string strMsg, string sheetName = null)
{
strMsg = "";
DataTable dt = new DataTable();
ISheet sheet = null;
IWorkbook workbook = null;
try
{
#region 判断excel版本
//2007以上版本excel
if (fileType == ".xlsx")
{
//把流转为excel
workbook = new XSSFWorkbook(stream);
}
//2007以下版本excel
else if (fileType == ".xls")
{
//把流转为excel
workbook = new HSSFWorkbook(stream);
}
else
{
throw new Exception("传入的不是Excel文件！");
}
#endregion
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
//获取第一个sheet页
sheet = workbook.GetSheetAt(0);
}
}
else
{
//获取第一个sheet页
sheet = workbook.GetSheetAt(0);
}
//这里是获取列头
if (sheet != null)
{
//获取第一行
IRow firstRow = sheet.GetRow(0);
//获取最后一列的列数
int cellCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i < cellCount; i++)
{
//获取第一行的第一列 i为1 就是第一列
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
//获取值
string cellValue = cell.StringCellValue.Trim();
if (!string.IsNullOrEmpty(cellValue))
{
//创建一个列
DataColumn dataColumn = new DataColumn(cellValue);
//table加一列
dt.Columns.Add(dataColumn);
}
}
}
DataRow dataRow = null;
//遍历行 因为第一行已经赋值 所以从第二行sheet.FirstRowNum + 1开始
for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
{
//获取第二行的数据 二是j
IRow row = sheet.GetRow(j);
dataRow = dt.NewRow();
if (row == null || row.FirstCellNum < 0)
{
continue;
}
//遍历第二行的列数据 row是j
for (int i = row.FirstCellNum; i < cellCount; i++)
{
//获取当前行的第几列数据
ICell cellData = row.GetCell(i);
if (cellData != null)
{
//判断是否为数字型，必须加这个判断不然下面的日期判断会异常
if (cellData.CellType == CellType.Numeric)
{
//判断是否日期类型
if (DateUtil.IsCellDateFormatted(cellData))
{
dataRow[i] = cellData.DateCellValue;
}
else
{
dataRow[i] = cellData.ToString().Trim();
}
}
else
{
//当前行的第几列数据的值
dataRow[i] = cellData.ToString().Trim();
}
}
}
//table的当前行循环加 直接遍历所有行
dt.Rows.Add(dataRow);
}
}
else
{
throw new Exception("没有获取到Excel中的数据表！");
}
}
catch (Exception ex)
{
strMsg = ex.Message;
}
return dt;
}