导入弹窗
<div id="uploadExcel" class="easyui-window" title="导入" closed="true" modal="true" iconcls="fa fa-navicon" style="width: 450px; height: 300px; padding: 5px; background: #fff;">
<div region="center">
<form name="FUP1" method="post" id="FUP1">
<table>
<tr>
<th style=" padding:10px;">上传Excel：</th>
<td style=" padding-top:20px; padding-bottom:20px; padding-right:20px;">
<input type="file" id="ImportingFile" name="ImportingFile" />
<a href="#" onclick="uploadWindow()" style=" text-decoration:none; color:Black">
[下载模板]
</a>
</td>
</tr>
</table>
<div class="endbtndiv">
<span style="margin-right:30px;">注意：请保持模板正确及导入数据与模板中一致</span>
<a id="btnSave" href="javascript:ImportData()" class="easyui-linkbutton btns">上 传</a>
<a id="btnReturn" class="easyui-linkbutton btnc">关 闭</a>
</div>
</form>
</div>
</div>
下载模板
function uploadWindow() {
window.open("/GreenNJInfo/DownFile?filePath=@Url.Content("~/ExcelFolder/FileDown/绿色企业抽检信息模板.xlsx")&fileName=3");
}
接口
public ActionResult DownFile(string filePath, string fileName)
{
try
{
if (fileName == "3")
fileName = "绿色企业抽检信息模板.xlsx";
filePath = Server.MapPath(filePath);
FileStream fs = new FileStream(filePath, FileMode.Open);
byte[] bytes = new byte[(int)fs.Length];
fs.Read(bytes, 0, bytes.Length);
fs.Close();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(fileName));
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
return Json("", JsonRequestBehavior.AllowGet);
// Json(dic, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(ex.Message);
}
}
上传
function ImportData() {
var inputType = "1";//$('#inputType').combobox('getValue');
//if (inputType == null || inputType == '' || inputType == 'undefined') {
// $.messager.alert('提示', '请选择导入类型!');
// return;
//}
var win = $.messager.progress({
title: '请稍后',
msg: '数据加载中...'
});
$.ajaxFileUpload({
url: '../GreenNJInfo/Upload', //用于文件上传的服务器端请求地址up参数标记此次是上传操作还是裁剪操作
type: 'post',
secureuri: false, //一般设置为false，是否安全上传
fileElementId: 'ImportingFile', //文件上传控件的id属性
dataType: 'json', //返回值类型 一般设置为json 期望服务器传回的数据类型
success: function (data, status) //服务器成功响应处理函数
{
if (typeof (data.error) != 'undefined') {
$.messager.progress('close');
if (data.error != '') {
if (data.error == "0") {
$('#FUP1').form('clear');
$('#uploadExcel').window('close');
$.messager.alert('提示', data.msg, 'info');
$('#dataPage').datagrid('load');
$('#dataPage').datagrid('clearSelections');
$('#ImportingFile').val("");
} else {
$.messager.alert('提示', data.msg, 'info');
}
} else {
$('#FUP1').form('clear');
$('#uploadExcel').window('close');
$.messager.alert('提示', data.msg, 'info');
$('#dataPage').datagrid('load');
$('#dataPage').datagrid('clearSelections');
$('#ImportingFile').val("");
}
}
},
error: function (data, status, e) {
$.messager.progress('close');
$.messager.alert('系统提示', e);
}
}
);
}
上传接口
[HttpPost]
[ValidateInput(false)]
public ActionResult Upload()
{
string result = "";
string error = "";//返回结果
string msg = "";//状态，
int errormsg = 0;
string fileUrl = "";
try
{
string newFileName = System.Guid.NewGuid().ToString();//名称
string strFileName = Request.Files[0].FileName;
string ExtensionName = Path.GetExtension(Request.Files[0].FileName).ToLower();//后缀扩展名小写
string ImgPath = "";
DataTable exTable = null;
if (string.IsNullOrWhiteSpace(strFileName))
{
result = "没有要导入的数据";
error = "0";
msg = "请选择要导入的Excel";
errormsg = 4;
}
else {
if(ExtensionName!=".xls"&&ExtensionName!=".xlsx")
{
error = "1";
msg = "选择的文件类型有误";
errormsg = 1;
}
else
{
fileUrl = "/ExcelFolder/" + newFileName + ExtensionName;
ImgPath = Server.MapPath(fileUrl);
Request.Files[0].SaveAs(ImgPath);
}
string tip = "";
exTable = GetExcelTalbeByEPPlus(ImgPath, out tip);//EPPlus导入
if (!string.IsNullOrWhiteSpace(tip))
{
error = "0";
msg = tip;
errormsg = -1;
}
else {
if (exTable != null) {
string stMmessage = "";
stMmessage = ExcelForInsert(exTable, fileUrl);
if (stMmessage == "1")
{
msg = "导入成功";
errormsg = 0;
}
else
{
error = "0";
msg = stMmessage;
errormsg = -1;
}
}
else
{
error = "0";
msg = "读取Execl内容失败";
errormsg = 3;
}
}
}
}
catch (Exception e)
{
result = e.Message.ToString();
}
string res = "{ error:'" + errormsg + "', msg:'" + msg + "', strPath:'" + fileUrl + "'}";
return Content(res);
}
private DataTable GetExcelTalbeByEPPlus(string filePath,out string tip)
{
DataTable dt = null;
tip = "";
string error1 = "";
string error3 = "";
try
{
//打开文件
FileStream fileStream = new FileStream(filePath, FileMode.Open);
//读取文件流
ExcelPackage package = new ExcelPackage(fileStream);
//获取 sheet 表
ExcelWorksheets worksheets = package.Workbook.Worksheets;
ExcelWorksheet worksheet = null;
for (int i = 1; i <= 1; i++)
{
dt = new DataTable();
dt.TableName = "table" + i.ToString();
worksheet = worksheets[i];
if (worksheet.Dimension != null)
{
//获取行数
int rowCount = worksheet.Dimension.End.Row;
//获取列数
int colCount = worksheet.Dimension.End.Column;
//起始行为 1
int rowIndex = worksheet.Dimension.Start.Row;
//起始列为 1
int colIndex = worksheet.Dimension.Start.Column;
DataColumn dc = null;
string dtTitle = "";
string okTitle = "";
string strName = "";
strName = "绿色企业抽检信息模板";
okTitle = "抽样编号,企业名称,抽样人员,抽样产品,抽样地址,抽样所属环节,样本来源名称,任务来源,检测类型,证书类型,证书编号,抽样时间,检测人,检测时间,检测结果";
for (int j = colIndex; j <= colCount; j++)
{
if (worksheet.Cells[rowIndex, j].Value != null && worksheet.Cells[rowIndex, j].Value != "")
{
dc = new DataColumn(worksheet.Cells[rowIndex, j].Value.ToString());
dt.Columns.Add(dc);
dtTitle = dtTitle + dc + ",";
}
}
if (!string.IsNullOrWhiteSpace(dtTitle))
{
dtTitle = dtTitle.Substring(0, dtTitle.Length - 1);
if (dtTitle != okTitle)
{
tip = tip + "导入【" + strName + "】文件有问题,请下载正确模板后进行操作";
return dt;
}
}
string lastName = "";
string lastName7 = "";
string lastName8 = "";
string lastName10 = "";
string lastName11 = "";
string lastName12 = "";
string lastName13 = "";
for (int k = rowIndex + 1; k <= rowCount; k++)
{
int emptyNum = 0;
DataRow dr = dt.NewRow();
for (int l = colIndex; l <= colCount; l++)
{
string strvalue = "";
if (worksheet.GetValue(k, l) == null || worksheet.GetValue(k, l).ToString().Trim() == "")
{
emptyNum++;
if (l == 4)
{
strvalue = lastName;
}
else
{
continue;
}
}
else
{
strvalue = worksheet.GetValue(k, l).ToString();
if (l == 1)
{
lastName = strvalue;
}
else if (l == 7)
{
lastName7 = strvalue;
}
else if (l == 8)
{
lastName8 = strvalue;
}
else if (l == 10)
{
lastName10 = strvalue;
}
else if (l == 11)
{
lastName11 = strvalue;
}
else if (l == 12)
{
lastName12 = strvalue;
}
else if (l == 13)
{
lastName13 = strvalue;
}
}
dr[l - 1] = strvalue;
}
if (emptyNum < colCount)
{
dt.Rows.Add(dr);
}
}
}
}
package.Dispose();
worksheet = null;
worksheets = null;
package = null;
fileStream.Close();
fileStream.Dispose();
}
catch (Exception ex)
{
tip = "模板存在异常：" + ex.Message;
}
return dt;
}
public string ExcelForInsert(DataTable dtExcel, string ImgPath) {
try
{
DateTime dtDate;
string result = "1";
string filePath = "";
int? addNum = 0;
int? errorNum = 0;
int? allNum = 0;
if (dtExcel != null)
{
//DataRow dtRow = null;
if (dtExcel.Rows.Count <= 0)
{
//dtRow = dtExcel.Rows[0];//取第一行的值
result = "信息有误！";
//return result;
errorNum = -1;
return "导入失败：" + result;
}
else
{
var query = (from q in dtExcel.AsEnumerable()
select new
{
CheckNum = q["抽样编号"].ToString().Trim(),
EnterpriseId = q["企业名称"].ToString().Trim(),
Sampler = q["抽样人员"].ToString().Trim(),
ProductId = q["抽样产品"].ToString().Trim(),
SamplePlace = q["抽样地址"].ToString().Trim(),
SampleSourceType = q["抽样所属环节"].ToString().Trim(),
SampleSource = q["样本来源名称"].ToString().Trim(),
AssignmentSource = q["任务来源"].ToString().Trim(),
MonitorTypeId = q["检测类型"].ToString().Trim(),
AttestationType = q["证书类型"].ToString().Trim(),
CertificateNo = q["证书编号"].ToString().Trim(),
SampleDate = q["抽样时间"].ToString().Trim(),
Checker = q["检测人"].ToString().Trim(),
CheckCreateTime = q["检测时间"].ToString().Trim(),
CheckResult = q["检测结果"].ToString().Trim(),
}).ToList();
List<GreenSpotCheck> check = new List<GreenSpotCheck>();
for (int i = 0; i <query.Count(); i++)
{
int tipNumber = 0;
string errorTip = "";
allNum++;
if (!string.IsNullOrWhiteSpace(query[i].EnterpriseId))
{
var EnterpriseNameById = GetEnterpriserNamrById(query[i].EnterpriseId);
if (!string.IsNullOrWhiteSpace(EnterpriseNameById))
{
GreenSpotCheck spot = new GreenSpotCheck();
spot.SpotCheckId = Guid.NewGuid().ToString().ToLower().Replace("-", "");
spot.EnterpriseId = EnterpriseNameById;
spot.CheckNum = query[i].CheckNum;
spot.Sampler = query[i].Sampler;
spot.ProductId = query[i].ProductId;
spot.SamplePlace = query[i].SamplePlace;
spot.SampleSourceType = query[i].SampleSourceType;
spot.SampleSource = query[i].SampleSource;
spot.AssignmentSource = query[i].AssignmentSource;
spot.MonitorTypeId = query[i].MonitorTypeId;
spot.AttestationType = query[i].AttestationType;
spot.CertificateNo = query[i].CertificateNo;
if (query[i].SampleDate != "" && query[i].SampleDate != null)
{
spot.SampleDate = Convert.ToDateTime(query[i].SampleDate);
}
else {
spot.SampleDate = null;
}
spot.Checker = query[i].Checker;
spot.IsActive = 1;
if (query[i].CheckCreateTime != "" && query[i].CheckCreateTime != null)
{
spot.CheckCreateTime = Convert.ToDateTime(query[i].CheckCreateTime);
}
else
{
spot.CheckCreateTime = null;
}
spot.CheckResult = query[i].CheckResult;
check.Add(spot);
}
else
{
tipNumber++;
errorTip = errorTip + "表格行" + (tipNumber + 1) + "该企业不存在";
return errorTip;
}
}
else {
tipNumber++;
errorTip = errorTip + "表格行" + (tipNumber + 1) + "企业名称不能为空";
return errorTip;
}
}
foreach (var item in check)
{
db.GreenSpotCheck.Add(item);
}
db.SaveChanges();
}
}
return result;
}
catch (Exception)
{
throw;
}
}
public string GetEnterpriserNamrById(string EnterpriseName)
{
string EnterpriseId="";
try
{
var query=from a in db.EnterpriseInfo.Where(a=>a.EnterpriseName==EnterpriseName&&a.AuthenticationType.Contains("2") &&a.IsActive==1)select a;
if (query.ToList().Count > 0) {
EnterpriseId = query.ToList()[0].EnterpriseId;
}
return EnterpriseId;
}
catch (Exception)
{
throw;
}
}
扫一扫
11万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
