excel导入导出mysql_Excel导入导出数据库（MVC）

usingDapper;usingNewtonsoft.Json;usingNPOI.HSSF.UserModel;usingNPOI.SS.UserModel;usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Diagnostics;usingSystem.IO;usingSystem.Linq;usingSystem.Web;usingSystem.Web.Mvc;using staticTest.SqlConCty;namespaceTest.Controllers

{public classHomeController : Controller

{publicActionResult Index()

{returnView();

}//GET: UploadExcel

public ActionResult TestExcel(stringfilePath)

{returnView();

}///

///根据Excel列类型获取列的值///

/// Excel列

///

private static stringGetCellValue(ICell cell)

{if (cell == null)return string.Empty;switch(cell.CellType)

{caseCellType.Blank:return string.Empty;caseCellType.Boolean:returncell.BooleanCellValue.ToString();caseCellType.Error:returncell.ErrorCellValue.ToString();caseCellType.Numeric:caseCellType.Unknown:default:returncell.ToString();caseCellType.String:returncell.StringCellValue;caseCellType.Formula:try{

HSSFFormulaEvaluator e= newHSSFFormulaEvaluator(cell.Sheet.Workbook);

e.EvaluateInCell(cell);returncell.ToString();

}catch{returncell.NumericCellValue.ToString();

}

}

}///

///Excel导入///

///

///

public DataTable ImportExcelFile(stringfilePath)

{

HSSFWorkbook hssfworkbook;#region//初始化信息

try{using (FileStream file = newFileStream(filePath, FileMode.Open, FileAccess.Read))

{

hssfworkbook= newHSSFWorkbook(file);

}

}catch(Exception e)

{throwe;

}#endregionISheet sheet= hssfworkbook.GetSheetAt(3);

DataTable table= newDataTable();

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= newDataColumn(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 = newSqlBulkCopy(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);

}returntable;

}

[HttpPost]publicActionResult TestExcel(FormCollection form)

{

HttpPostedFileBase file= Request.Files[0];string path = Server.MapPath("\\Models");

path+= "\\" +file.FileName;

file.SaveAs(path);

ImportExcelFile(path);returnView();

}public stringEE()

{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);returnJsonConvert.SerializeObject(list);

}

}publicActionResult Detailss()

{

ExcelExprot();returnView();

}protected voidExcelExprot()

{string schoolname = "401";//创建Excel文件的对象

NPOI.HSSF.UserModel.HSSFWorkbook book = newNPOI.HSSF.UserModel.HSSFWorkbook();//添加一个sheet

NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");//获取list数据

List 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 = newSystem.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 ListGetAlll()

{

List list = new List();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>(json);returnlist;

}

}

}