Excel导入
一、上传excel表到服务器；通过读取excel表获得数据；记录到数据库。（包含模板下载）
1、视图代码
@using (Html.BeginForm("Index", "Excel", FormMethod.Post, new { enctype = "multipart/form-data" })) { <h2> 基站信息导入</h2> <div> <fieldset id="myfieldset"> <legend>excel模版格式 </legend><font color="red">导入基站的模板格式如下，若模板格式不正确，则相应的基站不能导入!</font> <p style="color: Red; text-align: center;">@Html.ActionLink("下载模版", "GetFile")</p> </fieldset> </div> <div style="margin-top: 20px;"> <fieldset id="myfieldset1"> <legend>基站批量信息导入</legend> <p> 选择文件：<input id="FileUpload" type="file" name="files" style="width: 250px; height: 24px; background: White" class="easyui-validatebox" /></p> <p> <input id="btnImport" type="submit" value="导入" style="width: 60px; height: 28px;" /></p> <p style="color: Red; text-align: center;">@ViewBag.error</p> </fieldset> </div> }
2、数据模型
public class ListModel { public int Id { get; set; } public string Head { get; set; } public string Center { get; set; } }
3、控制器代码
using System; using System.Data; using System.Data.OleDb; using System.IO; using System.Transactions; using System.Web; using System.Web.Mvc; using UploadExcel.Models; namespace UploadExcel.Controllers { public class ExcelController : Controller { public ActionResult Index() { return View(); } [HttpPost] public ActionResult Index(HttpPostedFileBase filebase) { HttpPostedFileBase file = Request.Files["files"]; string FileName; string savePath; if (file == null || file.ContentLength <= 0) { ViewBag.error = "文件不能为空"; return View(); } else { string filename = Path.GetFileName(file.FileName); int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名 string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名 int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M string FileType = ".xls,.xlsx";//定义上传文件的类型字符串 FileName = NoFileName + fileEx; if (!FileType.Contains(fileEx)) { ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件"; return View(); } if (filesize >= Maxsize) { ViewBag.error = "上传文件超过4M，不能上传"; return View(); } string path = AppDomain.CurrentDomain.BaseDirectory + "/App_Data/"; savePath = Path.Combine(path, FileName); file.SaveAs(savePath); } string strConn; strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";" + "Extended Properties=Excel 12.0"; //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";" + "Extended Properties=Excel 8.0"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn); DataSet myDataSet = new DataSet(); try { myCommand.Fill(myDataSet, "ExcelInfo"); } catch (Exception ex) { ViewBag.error = ex.Message; return View(); } DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable(); //引用事务机制，出错时，事物回滚 using (TransactionScope transaction = new TransactionScope()) { for (int i = 0; i < table.Rows.Count; i++) { int id = int.Parse(table.Rows[i][0].ToString()); string head = table.Rows[i][1].ToString(); string center = table.Rows[i][2].ToString(); var model = new ListModel() { Id = id, Head = head, Center = center }; //此处写录入数据库代码； } transaction.Complete(); } ViewBag.error = "导入成功"; System.Threading.Thread.Sleep(2000); return RedirectToAction("Index"); } public FileResult GetFile()//获得模板下载地址 { string path = AppDomain.CurrentDomain.BaseDirectory + "/App_Data/"; string fileName = "配置信息.xls"; return File(path + fileName, "text/plain", fileName); }
4、注意事项
数据模型、模板、数据库表字段 要一致；模板工作簿名（如Sheet1）要和代码的一致；
原文：http://www.cnblogs.com/bianlan/archive/2012/05/14/2500705.html
二、上传excel表到服务器；通过NPOI组件获得数据；记录到数据库。
1、视图代码、数据模型代码都和上面的一样；添加NPOI组件（到网上下载）;添加ICSharpCode.SharpZipLib.dll
2、控制器代码
using System; using System.Data; using System.IO; using System.Transactions; using System.Web; using System.Web.Mvc; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel; using UploadExcel.Models; namespace UploadExcel.Controllers { public class NpoiController : Controller { // // GET: /Npoi/ public ActionResult Index() { return View(); } [HttpPost] public ActionResult Index(HttpPostedFileBase filebase) { HttpPostedFileBase file = Request.Files["files"]; string FileName; string savePath; if (file == null || file.ContentLength <= 0) { ViewBag.error = "文件不能为空"; return View(); } else { string filename = Path.GetFileName(file.FileName); int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名 string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名 int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M string FileType = ".xls,.xlsx";//定义上传文件的类型字符串 FileName = NoFileName + fileEx; if (!FileType.Contains(fileEx)) { ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件"; return View(); } if (filesize >= Maxsize) { ViewBag.error = "上传文件超过4M，不能上传"; return View(); } string path = AppDomain.CurrentDomain.BaseDirectory + "/App_Data/"; savePath = Path.Combine(path, FileName); file.SaveAs(savePath); } DataTable table = ExcelToDataTable(savePath); //引用事务机制，出错时，事物回滚 using (TransactionScope transaction = new TransactionScope()) { for (int i = 0; i < table.Rows.Count; i++) { int id = int.Parse(table.Rows[i][0].ToString()); string head = table.Rows[i][1].ToString(); string center = table.Rows[i][2].ToString(); var model = new ListModel() { Id = id, Head = head, Center = center }; //此处写录入数据库代码； } transaction.Complete(); } ViewBag.error = "导入成功"; System.Threading.Thread.Sleep(2000); return RedirectToAction("Index"); } /// <summary>读取excel /// 根据索引读取Sheet表数据，默认读取第一个sheet /// </summary> /// <param name="strFileName">excel文档路径</param> /// <param name="sheetIndex">sheet表的索引，从0开始</param> /// <returns>数据集</returns> public static DataTable ExcelToDataTable(string strFileName, int sheetIndex = 0) { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook = null; XSSFWorkbook xssfworkbook = null; string fileExt = Path.GetExtension(strFileName);//获取文件的后缀名 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { if (fileExt == ".xls") hssfworkbook = new HSSFWorkbook(file); else if (fileExt == ".xlsx") xssfworkbook = new XSSFWorkbook(file);//初始化太慢了，不知道这是什么bug } if (hssfworkbook != null) { HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex); if (sheet != null) { System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { HSSFCell cell = (HSSFCell)headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } } } else if (xssfworkbook != null) { XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex); if (xSheet != null) { System.Collections.IEnumerator rows = xSheet.GetRowEnumerator(); XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { XSSFCell cell = (XSSFCell)headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++) { XSSFRow row = (XSSFRow)xSheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } } } return dt; } } }
3、参考：http://www.cnblogs.com/wohexiaocai/p/3529641.html
