后台代码如下:
#region 将EXCEL表格导入数据库
public void ImportFirstinspection()
{
try
{
Context context = new Context();
//获取选中文件
HttpFileCollectionBase file = Request.Files;
//声明首检台账实体
FirstinspectionA firstinspectionAccount = new FirstinspectionA();
if (file.Count > 0)
{
//加上文件前缀,得到新的文件名
string _guid = DateTime.Now.ToString("yyyy年MM月dd日") + "-" + Guid.NewGuid().ToString().ToUpper().Replace("-", "");
string fileName = file[0].FileName;
Stream fs = file[0].InputStream;
if (file != null && file[0].ContentLength > 0)
{
using (StreamReader sr = new StreamReader(fs, Encoding.UTF8))
{
//上传文件地址
string save_diretion = Server.MapPath("../upload/");
string save_path = save_diretion + _guid + "-" + fileName;
//保存该路径下的文件
file[0].SaveAs(save_path);
//第一种表格后缀,写入数据方法
if (fileName.EndsWith(".xls"))
{
//XSSFWorkbook workBook = new XSSFWorkbook(new FileStream(Path.GetFullPath(save_path), FileMode.Open));
HSSFWorkbook workbook = new HSSFWorkbook(new FileStream(Path.GetFullPath(save_path), FileMode.Open));
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
int rowCount = sheet.LastRowNum;
//开始添加
for (int j = 0; j < rowCount; j++)
{
firstinspectionAccount.BarCode = sheet.GetRow(j + 1).GetCell(1).ToString().Trim().Replace("\n", ";");
firstinspectionAccount.ModelCode = sheet.GetRow(j + 1).GetCell(2).ToString().Trim().Replace("\n", ";");
firstinspectionAccount.VersionNumber = sheet.GetRow(j + 1).GetCell(3).ToString().Trim().Replace("\n", ";");
firstinspectionAccount.Number = int.Parse(sheet.GetRow(j + 1).GetCell(4).ToString().Trim().Replace("\n", ";"));
//string str = firstinspectionAccount.ProductionDate.ToString();
firstinspectionAccount.ProductionDate = DateTime.Parse(sheet.GetRow(j + 1).GetCell(5).ToString().Trim().Replace("\n", ";"));
firstinspectionAccount.FyStation = sheet.GetRow(j + 1).GetCell(6).ToString().Trim().Replace("\n", ";");
firstinspectionAccount.ReceivingTeam = sheet.GetRow(j + 1).GetCell(7).ToString().Trim().Replace("\n", ";");
firstinspectionAccount.TypeName = sheet.GetRow(j + 1).GetCell(8).ToString().Trim().Replace("\n", ";");
firstinspectionAccount.FyState = "正常";
firstinspectionAccount.Status = 0;
context.FirstinspectionAccounts.Add(firstinspectionAccount);
context.SaveChanges();
}
}
//第二种表格后缀,写入数据方法
//if (fileName.EndsWith(".xlsx"))
//{
// XSSFWorkbook workBook = new XSSFWorkbook(new FileStream(Path.GetFullPath(save_path), FileMode.Open));
// //HSSFWorkbook workbook = new HSSFWorkbook(new FileStream(Path.GetFullPath(save_path), FileMode.Open));
// XSSFSheet sheet = (XSSFSheet)workBook.GetSheetAt(0);
// int rowCount = sheet.LastRowNum;
// //开始添加
// for (int j = 0; j < rowCount; j++)
// {
// firstinspectionAccount.BarCode = sheet.GetRow(j + 1).GetCell(1).ToString().Trim().Replace("\n", ";");
// firstinspectionAccount.ModelCode = sheet.GetRow(j + 1).GetCell(2).ToString().Trim().Replace("\n", ";");
// firstinspectionAccount.VersionNumber = sheet.GetRow(j + 1).GetCell(3).ToString().Trim().Replace("\n", ";");
// firstinspectionAccount.Number = int.Parse(sheet.GetRow(j + 1).GetCell(4).ToString().Trim().Replace("\n", ";"));
// firstinspectionAccount.ProductionDate = DateTime.Parse(sheet.GetRow(j + 1).GetCell(5).ToString().Trim().Replace("\n", ";"));
// firstinspectionAccount.FyStation = sheet.GetRow(j + 1).GetCell(6).ToString().Trim().Replace("\n", ";");
// firstinspectionAccount.ReceivingTeam = sheet.GetRow(j + 1).GetCell(7).ToString().Trim().Replace("\n", ";");
// firstinspectionAccount.TypeName = sheet.GetRow(j + 1).GetCell(8).ToString().Trim().Replace("\n", ";");
// firstinspectionAccount.Status = 0;
// context.FirstinspectionAccounts.Add(firstinspectionAccount);
// context.SaveChanges();
// }
//}
}
}
}
//弹出导出成功提示!
string msg = "导入Excel文件成功!";
object JSONObj = JsonConvert.SerializeObject(msg);
Response.Write(JSONObj);
Response.End();
}
catch (Exception ex)
{
var msg = ex.Message.ToString();
object JSONObj = JsonConvert.SerializeObject(msg);
Response.Write(JSONObj);
Response.End();
}
}
#endregion
#region 将数据库里的数据导出到Excel表格
public void ExportFirstinspection()
{
try
{
//创建工作簿对象
IWorkbook workbook = new HSSFWorkbook();
//创建工作表
ISheet sheet = workbook.CreateSheet("Sheet1");
//创建表头行
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("序号");
row0.CreateCell(1).SetCellValue("条码");
row0.CreateCell(2).SetCellValue("机型编码");
row0.CreateCell(3).SetCellValue("版本号");
row0.CreateCell(4).SetCellValue("数量");
row0.CreateCell(5).SetCellValue("制作日期");
row0.CreateCell(6).SetCellValue("封样状态");
row0.CreateCell(7).SetCellValue("封样位置");
row0.CreateCell(8).SetCellValue("领用班组");
row0.CreateCell(9).SetCellValue("领用人");
row0.CreateCell(10).SetCellValue("领用时长");
row0.CreateCell(11).SetCellValue("领用状态");
row0.CreateCell(12).SetCellValue("领用时间");
row0.CreateCell(13).SetCellValue("类别");
Context context = new Context();
//设置行数
int r = 0;
//遍历首检台账
foreach (var item in context.FirstinspectionAccounts)
{
//创建行row
IRow row = sheet.CreateRow(r + 1);
row.CreateCell(0).SetCellValue(item.Id);
row.CreateCell(1).SetCellValue(item.BarCode);
row.CreateCell(2).SetCellValue(item.ModelCode);
row.CreateCell(3).SetCellValue(item.VersionNumber);
row.CreateCell(4).SetCellValue(item.Number.ToString());
row.CreateCell(5).SetCellValue(item.ProductionDate.ToString());
row.CreateCell(6).SetCellValue(item.FyState);
row.CreateCell(7).SetCellValue(item.FyStation);
row.CreateCell(8).SetCellValue(item.ReceivingTeam);
row.CreateCell(9).SetCellValue(item.Receiver);
row.CreateCell(10).SetCellValue(item.ReceivingDuration);
row.CreateCell(11).SetCellValue(item.ReceivingState);
row.CreateCell(12).SetCellValue(item.ReceivingTime.ToString());
row.CreateCell(13).SetCellValue(item.TypeName);
r++;
}
string path = @"D:\首检台账" + DateTime.Now.ToString("yyyy年MM月dd日") + ".xls";
//创建流对象并设置存储Excel文件的路径
using (FileStream fs = System.IO.File.OpenWrite(path))
{
//导出Excel文件
workbook.Write(fs);
};
//弹出导出成功提示!
string msg = "导出Excel文件到D盘成功!";
object JSONObj = JsonConvert.SerializeObject(msg);
Response.Write(JSONObj);
Response.End();
}
catch (Exception ex)
{
var msg = ex.Message.ToString();
object JSONObj = JsonConvert.SerializeObject(msg);
Response.Write(JSONObj);
Response.End();
}
}
#endregion
前端代码如下:
@model KZQFirstTestPaperless.Domain.ViewModel.FirstinspectionAccountViewModel
@using KZQFirstTestPaperless.Common
@{
AjaxOptions ajax = new AjaxOptions()
{
UpdateTargetId = "ContentRow",
HttpMethod = "Get"
};
ViewBag.Title = "首检台账";
Layout = null;
}
@*<link href="~/Content/layui/css/layui.css" rel="stylesheet" />
<script src="~/Content/layui/layui.js"></script>*@
<link href="~/Scripts/fileinput/fileinput.css" rel="stylesheet" />
<script src="~/Scripts/fileinput/fileinput.min.js"></script>
<script src="~/Scripts/fileinput/zh.js"></script>
<div class="panel panel-info" style="margin:20px">
<div class="panel-heading text-center">
<h4>首检台账</h4>
</div>
<div class="panel-body">
<div id="tableRow" style="margin-top:10px">
<div class="row" style="margin:10px 0px;">
@using (Ajax.BeginForm("FirstinspectionIndex", "Firstinspection", new { selectType = 1 }, ajax))
{
<div class="col-xs-4 col-sm-3 col-md-2">
<input class="form-control" name="barCode" placeholder="条码" value="@ViewBag.BarCode" />
</div>
<div class="col-xs-4 col-sm-3 col-md-2">
<input class="form-control" name="modelCode" placeholder="机型编码" value="@ViewBag.ModelCode" />
</div>
<div class="col-xs-4 col-sm-3 col-md-1">
<input class="form-control" name="versionNumber" placeholder="版本号" value="@ViewBag.VersionNumber" />
</div>
<div class="col-xs-4 col-sm-3 col-md-2">
<input class="form-control" name="receivingState" placeholder="领用状态" value="@ViewBag.ReceivingState" />
</div>
<div class="col-xs-4 col-sm-3 col-md-2">
<input class="form-control" name="fyStation" placeholder="封样位置" value="@ViewBag.FyStation" />
</div>
<div class="col-xs-4 col-sm-3 col-md-2">
<select class="form-control" name="typeName">
<option value="">--全部类别--</option>
<option value="变频">变频</option>
<option value="内机主板">内机主板</option>
<option value="显示器分体机">显示器分体机</option>
<option value="显示器柜机">显示器柜机</option>
<option value="遥控器">遥控器</option>
</select>
<span hidden>@ViewBag.TypeName</span>
</div>
<div class="col-xs-4 col-sm-3 col-md-1">
<button class="form-control btn btn-primary" type="submit">查询</button>
</div>
}
</div>
<table id="print" class="table table-bordered table-hover table-striped table-responsive table2excel">
<tr>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].Id)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].BarCode)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ModelCode)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].VersionNumber)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].Number)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ProductionDate)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].FyState)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].FyStation)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ReceivingTeam)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].Receiver)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ReceivingDuration)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ReceivingState)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ReceivingTime)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].TypeName)
</th>
<td class="not"></td>
</tr>
@foreach (var item in Model.FirstinspectionAccounts)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.Id)
</td>
<td>
@Html.DisplayFor(modelItem => item.BarCode)
</td>
<td>
@Html.DisplayFor(modelItem => item.ModelCode)
</td>
<td>
@Html.DisplayFor(modelItem => item.VersionNumber)
</td>
<td>
@Html.DisplayFor(modelItem => item.Number)
</td>
<td>
@Html.DisplayFor(modelItem => item.ProductionDate)
</td>
<td>
@Html.DisplayFor(modelItem => item.FyState)
</td>
<td>
@Html.DisplayFor(modelItem => item.FyStation)
</td>
<td>
@Html.DisplayFor(modelItem => item.ReceivingTeam)
</td>
<td>
@Html.DisplayFor(modelItem => item.Receiver)
</td>
<td>
@Html.DisplayFor(modelItem => item.ReceivingDuration)
</td>
<td>
@Html.DisplayFor(modelItem => item.ReceivingState)
</td>
<td>
@Html.DisplayFor(modelItem => item.ReceivingTime)
</td>
<td>
@Html.DisplayFor(modelItem => item.TypeName)
</td>
<td class="not">
<a href="/Firstinspection/EditFirstinspection?id=@Html.DisplayFor(modelItem => item.Id,ajax)"
data-target="#ajax"
data-toggle="modal">
编辑
</a>
@*@Html.ActionLink("编辑", "EditFirstinspection", new { id = item.Id })*@ |
@Ajax.ActionLink("删除", "DeleteFirstinspection", new { id = item.Id }, ajax)
</td>
</tr>
}
<tr class="not">
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>
<a href="/Firstinspection/AddFirstinspection"
data-target="#ajax"
data-toggle="modal">
新增
</a>
@*@Html.ActionLink("新增", "AddFirstinspection")*@
</td>
</tr>
</table>
<div class="col-xs-4 col-sm-3 col-md-1">
@*<input type="file" value="导入Excel" class="form-control btn btn-primary" id="import">*@
<input type="file" value="导入Excel" id="import" multiple class="file" />
</div>
<div class="col-xs-4 col-sm-3 col-md-1">
<button type="button" class="form-control btn btn-primary" id="export">导出Excel</button>
</div>
@*<button type="button" class="layui-btn" id="import"><i class="layui-icon layui-icon-upload"></i>导入Excel</button>*@
@*<button type="button" class="form-control btn btn-primary" id="export"><i class="arrow-down-circle"></i>导出Excel</button>*@
</div>
<div class="text-right">
@Html.PageAjaxLinks(Model.PagingInfo, "#ContentRow", x => Url.Action("FirstinspectionIndex", new { page = x, barCode = ViewBag.BarCode, modelCode = ViewBag.ModelCode, versionNumber = ViewBag.VersionNumber, receivingState = ViewBag.ReceivingState, typeName = ViewBag.TypeName, fyStation = ViewBag.FyStation }))
</div>
</div>
</div>
<div class="modal" id="ajax" role="dialog" aria-hidden="true">
<div class="modal-dialog" role="document">
<div class="modal-content">
</div>
</div>
</div>
<script>
//导入Excel文件
$('#import').fileinput({
language: 'zh', //设置语言
dropZoneEnabled: false, //是否显示拖拽区域
dropZoneTitle: "可以将Excel拖放到这里", //拖拽区域显示文字
uploadUrl: '/Firstinspection/ImportFirstinspection', //上传路径
allowedFileExtensions: ['xls'], //指定上传文件类型
maxFileSize: 0,
maxFileSize: 2048, //上传文件最大值,单位kb
uploadAsync: true, //异步上传
maxFileCount: 1 //上传文件最大个数。
}).on("fileuploaded", function (event, data) { //异步上传成功后回调
if (data.response) {
alert('导入Excel文件成功!');
}
else {
alert('导入Excel文件失败!');
}
location.reload();
});
//layui.use(['upload', 'element', 'layer'], function () {
// var $ = layui.jquery
// , upload = layui.upload
// , element = layui.element
// , layer = layui.layer;
// upload.render({
// elem: '#import'
// ,type:'POST'
// , url: '/Firstinspection/ImportFirstinspection' //调用方法
// , accept: 'file' //普通文件
// , dataType: 'json'
// , contentType: 'application/json; charset=utf-8'
// , success: function (msg) {
// layer.msg(msg);
// }
// });
//})
导出Excel文件
//$('#export').click(function () {
// $("#print").table2excel({ //这里要选择table标签 我这里是用id选择 也可$('table').table2excel()
// exclude: '.not', //不被导出表格行的class类
// name: '首检台账' + new Date().getFullYear() + (new Date().getMonth()+1) + new Date().getDate(), //文档名称
// filename: '首检台账' + new Date().getFullYear() + (new Date().getMonth()+1) + new Date().getDate() + '.xls', //文件名称
// fileext: ".xls",//文件类型
// exclude_img: false, //是否导出图片
// exclude_links: false,//是否导出超链接
// exclude_inputs: false//是否导出input框中的内容
// });
//});
//导出Excel文件
$(function () {
$("#export").click(function () {
$.ajax({
type: "POST",
url: "/Firstinspection/ExportFirstinspection",
dataType: "json",
contentType: "application/json; charset=utf-8",
success: function (msg) { alert(msg); }
})
})
})
</script>
实现效果图如下: