xls通过web导入mysql_excel 在web导入到数据库的操作方法

publicJsonResult 你的方法()

{var httpRequest =HttpContext.Request;string fileName = string.Format("{0}_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), httpRequest.Form["fileName"].ToString());//文件夹

string dir = Server.MapPath("~/temp");if (!Directory.Exists(dir))

{

Directory.CreateDirectory(dir);

}//文件全路径，获取了本地的文件路径

string filePath =Path.Combine(dir, fileName);foreach (string key in httpRequest.Files) //文件键

{var postedFile = httpRequest.Files[key]; //获取文件键对应的文件对象

postedFile.SaveAs(filePath);

} 读取Excel文件，导入数据

ExcelToDB(filePath);return Json(new{

status= true,

message= "导入成功！"});

}private void ExcelToDB(stringfilePath)

{

FileStream fs= null;

IWorkbook workbook= null;

ISheet sheet= null;

IRow row= null;//ICell cell = null;

int startRow = 1;try{using (fs =System.IO.File.OpenRead(filePath))

{//2007版本

if (filePath.IndexOf(".xlsx") > 0)

workbook= newXSSFWorkbook(fs);//2003版本

else if (filePath.IndexOf(".xls") > 0)

workbook= newHSSFWorkbook(fs);if (workbook != null)

{

workbook.MissingCellPolicy=MissingCellPolicy.RETURN_BLANK_AS_NULL;//sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet

sheet = workbook.GetSheet("Sheet1");if (sheet != null)

{int rowCount = sheet.LastRowNum;//总行数

if (rowCount > 0)

{

IRow firstRow= sheet.GetRow(0);//第一行

int cellCount = firstRow.LastCellNum;//列数

WriteData(sheet, row, startRow, rowCount);

}else{throw new Exception("无信息");

}

}else{throw new Exception("没有找到Sheet1");

}

}else{throw new Exception("加载Excel失败");

}

}

}catch(Exception ex)

{if (fs != null)

{

fs.Close();

}throwex;

}

}///

///从excel写入登记表///

///

///

///

///

///

private void WriteData(ISheet sheet, IRow row, int startRow, introwCount)

{

List 你的插入类 = new List();string Title = string.Empty;string Time = string.Empty;string EndTime = string.Empty;string Range = string.Empty;//填充行

for (int i = startRow; i <= rowCount; ++i)

{

row=sheet.GetRow(i);if (row == null) continue;try{//读取一行每个字段值

Title = row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString(); // Time = row.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();// EndTime = row.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();// taskType = 1;// signInRange = row.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();//

//没有信息，直接跳过

if (string.IsNullOrEmpty(Title) || string.IsNullOrEmpty(Time) || string.IsNullOrEmpty(EndTime))

{continue;

}

info item= newinfo()

{

读取的行在这里和实体类赋值

};

list.Add(item);

}catch(Exception ex)

{

}

}//批量插入

Dal.BatchInsert(list);

}