.net导入excel数据到数据库中

 

在开发过程中我们经常面临着需要将数据导出或者导入到系统中,例如一些生产管理系统,项目管理系统等等都会有这样的需求;

将excel数据到系统中思路:获取excel中每一行的数据,然后存入集合中,批量添加到数据库对应的表中;

下面的demo是将财务部的工资表导入到系统中,细节有待完善。

 

 

 

 

 protected void BtnUpload_Click(object sender, EventArgs e)

{

#region 导入数据到工资表中
string filepath = salaryDAL.GetFilePathByKeyField(this.Number.Text);
string path = filepath.Replace("HumanResources/Gongzi/", "");
path = Server.MapPath(path);//获取excel文件路径
FileStream files = new FileStream(path, FileMode.Open, FileAccess.Read);//读取文件流
NPOI.SS.UserModel.IWorkbook workbook = WorkbookFactory.Create(files);
//读取sheet
var sheet = workbook.GetSheetAt(0);
var index = sheet.LastRowNum;
for (int i = 1; i <= index; i++)
{

  //注意:for循环中变量 i 的初始值为excel中元数据的行号,我的excel格式:0为表头 ,所以从1开始读取
  var row = sheet.GetRow(i);
  Salary model = new Salary();
  #region 获取单元格的值并赋值給实体对象
  model.RealName = row.GetCell(1).StringCellValue;
  model.UserNo = row.GetCell(2).NumericCellValue.ToString();
  model.Property = row.GetCell(3).StringCellValue;
  model.WorkDaies = Convert.ToInt32(row.GetCell(4).NumericCellValue);
  model.Holidaies = Convert.ToInt32(row.GetCell(5).NumericCellValue);
  model.BasicSalary = Convert.ToDecimal(row.GetCell(6).NumericCellValue);
  model.PostSalary = Convert.ToDecimal(row.GetCell(7).NumericCellValue);
  model.PostAllowance = Convert.ToDecimal(row.GetCell(8).NumericCellValue);
  model.Pension = Convert.ToDecimal(row.GetCell(9).NumericCellValue);
  model.Medical = Convert.ToDecimal(row.GetCell(10).NumericCellValue);

  ...........

   model.DaoZhangDate = Convert.ToDateTime(DateTime.Now.ToShortDateString());
  #endregion

      //也可以将model存到集合中,将集合包含在事务中,循环将集合中的元素新增到数据库中,从而保证数据一致性
  int id = salaryDAL.Add(model);
}

files.Dispose();
base.Response.Write("<script language=javascript>alert('工资导入成功!');window.location.href='SalaryExport.aspx'</script>");
#endregion

 

}

转载于:https://www.cnblogs.com/niguang/p/5553171.html

ASP.NET MVC导入Excel文件到数据库功能可以通过以下步骤实现: 1. 创建一个控制器和视图来上传Excel文件。 2. 使用第三方库如EPPlus来解析Excel文件并将其转换为数据表。 3. 使用ADO.NET连接到数据库并将数据数据插入到数据库。 以下是一个示例控制器和视图的代码: 控制器: ``` public class ExcelController : Controller { public ActionResult Index() { return View(); } [HttpPost] public ActionResult Import(HttpPostedFileBase file) { if (file != null && file.ContentLength > 0) { string fileName = Path.GetFileName(file.FileName); string fileExtension = Path.GetExtension(fileName); if (fileExtension == ".xls" || fileExtension == ".xlsx") { string filePath = Server.MapPath("~/Content/" + fileName); file.SaveAs(filePath); DataTable dt = ReadExcelFile(filePath); InsertDataIntoDatabase(dt); return RedirectToAction("Index"); } else { ViewBag.Message = "Please upload a valid Excel file."; return View("Index"); } } else { ViewBag.Message = "Please select an Excel file to upload."; return View("Index"); } } private DataTable ReadExcelFile(string filePath) { using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath))) { ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; DataTable dt = new DataTable(); bool hasHeaderRow = true; foreach (var firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column]) { dt.Columns.Add(hasHeaderRow ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } int startRow = hasHeaderRow ? 2 : 1; for (int rowNum = startRow; rowNum <= worksheet.Dimension.End.Row; rowNum++) { var wsRow = worksheet.Cells[rowNum, 1, rowNum, worksheet.Dimension.End.Column]; DataRow row = dt.Rows.Add(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } } return dt; } } private void InsertDataIntoDatabase(DataTable dt) { string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "INSERT INTO MyTable (Column1, Column2, Column3) VALUES (@Column1, @Column2, @Column3)"; foreach (DataRow row in dt.Rows) { command.Parameters.AddWithValue("@Column1", row["Column1"]); command.Parameters.AddWithValue("@Column2", row["Column2"]); command.Parameters.AddWithValue("@Column3", row["Column3"]); command.ExecuteNonQuery(); command.Parameters.Clear(); } } } } ``` 视图: ``` @{ ViewBag.Title = "Import Excel File"; } <h2>Import Excel File</h2> @using (Html.BeginForm("Import", "Excel", FormMethod.Post, new { enctype = "multipart/form-data" })) { @Html.AntiForgeryToken() <div class="form-group"> <label for="file">Select Excel File:</label> <input type="file" name="file" id="file" /> </div> <button type="submit" class="btn btn-primary">Import</button> <div class="alert alert-danger" role="alert">@ViewBag.Message</div> } ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值