<div>
<form action="/Emp/Excel" method="post" enctype="multipart/form-data">
选择文件:<input id="exceFile" name="excelFile" type="file" /> <input type="submit" value="导入客户信息..." id="Submit"/> </form> <form action="/Emp/DownLoad" method="post" enctype="multipart/form-data"> <input type="submit" value="导出客户信息..." /> </form></div>
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using NPOI.XWPF.UserModel; using System.IO;
/// <summary> /// 导入 /// </summary> /// <returns></returns> [HttpPost] public ActionResult Excel() { for (int i = 0; i < Request.Files.Count; i++) { var exceFile = Request.Files[i]; if (exceFile != null && !string.IsNullOrEmpty(exceFile.FileName)) { IWorkbook wk = null; var subName = exceFile.FileName.Substring(exceFile.FileName.LastIndexOf('.'), exceFile.FileName.Length - exceFile.FileName.LastIndexOf('.')); if (subName.Equals(".xls")) { wk = new HSSFWorkbook(exceFile.InputStream); } else if(subName.Equals(".xlsx")) { wk = new XSSFWorkbook(exceFile.InputStream); } else { return View("Index"); }
ISheet sheet = wk.GetSheetAt(0);
IRow row = sheet.GetRow(1); for (int k = 0; k <= sheet.LastRowNum; k++) { row = sheet.GetRow(k); string strSql = "insert into Emp(Ename,Ephone,Eaddress,Ezhiwu,Emoney,Egongsi) values("; if (row != null) { for (int j = 0; j < row.LastCellNum; j++) { if (j == 0) { continue; } string value = row.GetCell(j).ToString(); strSql += "'" + value + "',"; } strSql = strSql.Substring(0, strSql.Length - 1) + ")"; ExcelDBHelper.ExecuteNonQuery(strSql); } } } } return View("Add"); }
/// <summary> /// 导出 /// </summary> /// <returns></returns> public ActionResult DownLoad() { NPOI.HSSF.UserModel.HSSFWorkbook HSSFWorkbook = new HSSFWorkbook();
ISheet sheet = HSSFWorkbook.CreateSheet("32562");
IRow cells = sheet.CreateRow(0);
cells.CreateCell(0).SetCellValue("编号"); cells.CreateCell(1).SetCellValue("职员名称"); cells.CreateCell(2).SetCellValue("电话"); cells.CreateCell(3).SetCellValue("地址"); cells.CreateCell(4).SetCellValue("职务"); cells.CreateCell(5).SetCellValue("费用"); cells.CreateCell(6).SetCellValue("公司名称");
List<Emps> list = ShowExcel(); //需要绑定数据源
for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].EmpId.ToString()); rowtemp.CreateCell(1).SetCellValue(list[i].Ename.ToString()); rowtemp.CreateCell(2).SetCellValue(list[i].Ephone.ToString()); rowtemp.CreateCell(3).SetCellValue(list[i].Eaddress.ToString()); rowtemp.CreateCell(4).SetCellValue(list[i].Ezhiwu.ToString()); rowtemp.CreateCell(5).SetCellValue(list[i].Emoney.ToString()); rowtemp.CreateCell(6).SetCellValue(list[i].Egongsi.ToString()); }
MemoryStream memoryStream = new MemoryStream(); HSSFWorkbook.Write(memoryStream); memoryStream.Seek(0,SeekOrigin.Begin); return File(memoryStream,"application/vnd.ms-excel","22356.xls"); }
//导出时获取的数据源 public List<Emps> ShowExcel() { string str = string.Format("select * from Emp"); return DBHelper.GetList<Emps>(str); }