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;
}
