Excel导入导出数据库（MVC）
后端
using Dapper; using Newtonsoft.Json; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Diagnostics; using System.IO; using System.Linq; using System.Web; using System.Web.Mvc; using static Test.SqlConCty; namespace Test.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } // GET: UploadExcel public ActionResult TestExcel(string filePath) { return View(); } /// <summary> /// 根据Excel列类型获取列的值 /// </summary> /// <param name="cell">Excel列</param> /// <returns></returns> private static string GetCellValue(ICell cell) { if (cell == null) return string.Empty; switch (cell.CellType) { case CellType.Blank: return string.Empty; case CellType.Boolean: return cell.BooleanCellValue.ToString(); case CellType.Error: return cell.ErrorCellValue.ToString(); case CellType.Numeric: case CellType.Unknown: default: return cell.ToString(); case CellType.String: return cell.StringCellValue; case CellType.Formula: try { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return cell.ToString(); } catch { return cell.NumericCellValue.ToString(); } } } /// <summary> /// Excel导入 /// </summary> /// <param name="filePath"></param> /// <returns></returns> public DataTable ImportExcelFile(string filePath) { HSSFWorkbook hssfworkbook; #region//初始化信息 try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } } catch (Exception e) { throw e; } #endregion ISheet sheet = hssfworkbook.GetSheetAt(3); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(0);//第一行为标题行 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells int rowCount = sheet.LastRowNum - 2; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 4); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = GetCellValue(row.GetCell(j)); } } table.Rows.Add(dataRow); } using (SqlBulkCopy abc = new SqlBulkCopy(SqlConnectionFactory.Connection)) { abc.BatchSize = table.Rows.Count; abc.BulkCopyTimeout = 11; abc.DestinationTableName = "ExcelTable"; for (int i = 0; i < table.Columns.Count; i++) { abc.ColumnMappings.Add(table.Columns[i].ColumnName, i); } abc.WriteToServer(table); } return table; } [HttpPost] public ActionResult TestExcel(FormCollection form) { HttpPostedFileBase file = Request.Files[0]; string path = Server.MapPath("\\Models"); path += "\\" + file.FileName; file.SaveAs(path); ImportExcelFile(path); return View(); } public string EE() { using (SqlConnection con = SqlConnectionFactory.Connection) { string sql = "select TsetId,TheDate, Tnumber, Tname, Depter, Bdate, Beonduty, GetoffWork, BeondutyTwo, GetoffWorkTwo, Belate, Leaver, Absenceoftime, Total, BText from ExcelTable"; var list = con.Query(sql); return JsonConvert.SerializeObject(list); } } public ActionResult Detailss() { ExcelExprot(); return View(); } protected void ExcelExprot() { string schoolname = "401"; //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 List<Models.TesModel> listRainInfo = GetAlll();// m_BLL.GetSchoolListAATQ(schoolname); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("工号"); row1.CreateCell(1).SetCellValue("姓名"); row1.CreateCell(2).SetCellValue("所属部门"); row1.CreateCell(3).SetCellValue("时间"); row1.CreateCell(4).SetCellValue("上班"); row1.CreateCell(5).SetCellValue("下班"); row1.CreateCell(6).SetCellValue("上班"); row1.CreateCell(7).SetCellValue("下班"); row1.CreateCell(8).SetCellValue("迟到时间(分钟)"); row1.CreateCell(9).SetCellValue("早退时间(分钟)"); row1.CreateCell(10).SetCellValue("缺勤时间(分钟)"); row1.CreateCell(11).SetCellValue("合计"); row1.CreateCell(12).SetCellValue("备注"); //将数据逐步写入sheet1各个行 年龄>备注 for (int i = 0; i < listRainInfo.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].TsetId.ToString()); rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].TheDate.ToString()); rowtemp.CreateCell(2).SetCellValue(listRainInfo[i].Tnumber.ToString()); rowtemp.CreateCell(2).SetCellValue(listRainInfo[i].Tnumber.ToString()); rowtemp.CreateCell(3).SetCellValue(listRainInfo[i].Tname.ToString()); rowtemp.CreateCell(4).SetCellValue(listRainInfo[i].Depter.ToString()); rowtemp.CreateCell(5).SetCellValue(listRainInfo[i].Bdate.ToString()); rowtemp.CreateCell(6).SetCellValue(listRainInfo[i].Beonduty.ToString()); rowtemp.CreateCell(7).SetCellValue(listRainInfo[i].GetoffWork.ToString()); rowtemp.CreateCell(8).SetCellValue(listRainInfo[i].BeondutyTwo.ToString()); rowtemp.CreateCell(9).SetCellValue(listRainInfo[i].Belate.ToString()); rowtemp.CreateCell(10).SetCellValue(listRainInfo[i].Leaver.ToString()); rowtemp.CreateCell(11).SetCellValue(listRainInfo[i].Absenceoftime.ToString()); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); ms.Flush(); ms.Position = 0; //编辑完后 通过response输出 Response.Clear(); Response.Buffer = true; Response.Charset = "UTF8"; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.ContentType = "application/msexcel"; Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("考勤明细.xls")); Response.BinaryWrite(ms.ToArray()); Response.Flush(); Response.End(); } private List<Models.TesModel> GetAlll() { List<Models.TesModel> list = new List<Models.TesModel>(); string sql = "select TsetId,TheDate, Tnumber, Tname, Depter, Bdate, Beonduty, GetoffWork, BeondutyTwo, GetoffWorkTwo, Belate, Leaver, Absenceoftime from ExcelTable"; SqlConnection DBper = SqlConnectionFactory.Connection; var oo = DBper.Query(sql); var json = JsonConvert.SerializeObject(oo); list = JsonConvert.DeserializeObject<List<Models.TesModel>>(json); return list; } } }
前段
@{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Index</title> <script src="~/Scripts/jquery-3.3.1.js"></script> </head> <body> <div> <form action="/Home/TestExcel" enctype="multipart/form-data" method="post"> <text>选择：（工作表名“Sheet1”）</text> <input name="file" type="file" id="file" /> <input type="submit" name="Upload" value="导入" /> </form> <input id="Button1" type="button" value="导出" onclick="location.href='/Home/Detailss'" /> <table> <thead> <tr> <th>工号</th> <th>姓名</th> <th>所属部分</th> <th>时间</th> <th>上班</th> <th>下班</th> <th>上班</th> <th>下班</th> <th>迟到时间(分钟)</th> <th>早退时间(分钟)</th> <th>缺勤时间(分钟)</th> <th>合计(分钟)</th> <th>备注</th> </tr> </thead> <tbody id="tb"></tbody> </table> </div> <script> $(document).ready(function () { $.ajax({ url: '/Home/EE', dataType: 'json', type: 'get', success: function (data) { $(data).each(function () { var tr = '<tr>' + '<td>' + this.TsetId + '</td>' + '<td>' + this.TheDate+'</td>' + '<td>' + this.Tnumber + '</td>' + '<td>' + this.Tname + '</td>' + '<td>' + this.Depter + '</td>' + '<td>' + this.Bdate + '</td>' + '<td>' + this.Beonduty + '</td>' + '<td>' + this.GetoffWork + '</td>' + '<td>' + this.BeondutyTwo + '</td>' + '<td>' + this.GetoffWorkTwo + '</td>' + '<td>' + this.Belate + '</td>' + '<td>' + this.Leaver + '</td>' + '<td>' + this.Absenceoftime + '</td>' + '<td>' + this.Total + '</td>' + '<td>' + this.BText + '</td>' + '</tr>'; $("#tb").append(tr); }) } }) }) </script> </body> </html>
