使用js导入excel和ajax传送数据回数据库保存的代码

来源：互联网发布：淘宝优惠券fanlibao 编辑：程序博客网时间：2024/06/06 10:59

//使用js导入excel文件（要求使用ActiveX的浏览器），然后在layer上的ligerUI grid上显示，保存时先进行判断数据是否已经存在，然后通过ajax将每条记录插回到数据库

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ImportExcel.aspx.cs" Inherits="AfterSalesServiceSys.Web.Module.InfoManager.AddUpdate.ImportExcel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="../../../lib/ligerUI/skins/Aqua/css/ligerui-all.css" rel="stylesheet" type="text/css" />
<script src="../../../lib/jquery/jquery-1.8.3.min.js" type="text/javascript"></script>
<script src="../../../lib/ligerUI/js/ligerui.min.js" type="text/javascript"></script>
<script src="../../../lib/layer/layer.min.js" type="text/javascript"></script>
<script src="../../../jscript/global.js" type="text/javascript"></script>
<script src="../../../jscript/ajaxfileupload.js" type="text/javascript"></script>
<script type="text/javascript">
var Page_name;
$(function () {
Page_name = GetUrlParam("Page_name");
//alert(Page_name);
if (Page_name == "SeriesInfoList")
loadGrid_SeriesinfoImport();
if (Page_name == "ProductTypeList")
loadGrid_ProductTypeImport();

});

//获取文件路径及触发事件
function openBrowse() {
if (document.getElementById("ImportRow").value == "" || document.getElementById("ImportColumn").value == "") {
alert("请先填写导入参数!");
document.getElementById("ImportRow").focus();
return -1;
}
var ie = navigator.appName == "Microsoft Internet Explorer" ? true : false;
if (ie) {
document.getElementById("file").click();
} else {
var a = document.createEvent("MouseEvents");//FF的处理
a.initEvent("click", true, true);
document.getElementById("file").dispatchEvent(a);
}
}

//读取excel并显示在Grid
function ReadExcel(obj) {
var rowBegin = Number(document.getElementById("ImportRow").value);
var columnbegin = Number(document.getElementById("ImportColumn").value);

var str = GetExcelData(rowBegin, columnbegin);
if (str == "") {
alert("获取Excel数据出错!");
return -1;
}

SetGridData(str);

//清空file控件value值
var nf = obj.cloneNode(true);
nf.value = ''; // 设计新控件value为空
obj.parentNode.replaceChild(nf, obj);
}

//获取excel的数据组合成string
function GetExcelData(rowBegin, columnbegin) {
var tempStr = "";
var filePath = document.getElementById("file").value;
//创建操作EXCEL应用程序的实例
try {
var oXL = new ActiveXObject("Excel.Application");
}
catch (e) {
alert("请先启用ActiveX或更换浏览器！");
}

var oWB = oXL.Workbooks.open(filePath);
oWB.worksheets(1).select();
var oSheet = oWB.ActiveSheet;
var rows = oSheet.usedrange.rows.count;
var j = columnbegin;
try {
for (var i = rowBegin; i < rows + rowBegin - 1; i++) {
j = columnbegin;
if (Page_name == "SeriesInfoList")
{
tempStr += "{" +
"series_no:\"" + oSheet.Cells(i, j).value + "\"," +
"series_name:\"" + oSheet.Cells(i, ++j).value + "\"," +
"product_type_code:\"" + oSheet.Cells(i, ++j).value + "\"," +
"product_type_name:\"" + oSheet.Cells(i, ++j).value + "\"," +
"status:\"" + oSheet.Cells(i, ++j).value + "\"," +
"description:\"" + oSheet.Cells(i, ++j).value + "\"," +
"}\n";
}
if (Page_name == "ProductTypeList") {
tempStr += "{" +
"product_name:\"" + oSheet.Cells(i, j).value + "\"," +
"product_code:\"" + oSheet.Cells(i, ++j).value + "\"," +
"product_line:\"" + oSheet.Cells(i, ++j).value + "\"," +
"status:\"" + oSheet.Cells(i, ++j).value + "\"," +
"remark:\"" + oSheet.Cells(i, ++j).value + "\"," +
"}\n";
}

}
} catch (e) {
return "";
}
oXL.Application.Quit();
//手动调用垃圾收集器
CollectGarbage();
return tempStr;
}

//根据json格式设置grid的data
function SetGridData(str) {
var strGrid = "";
var strSplit = "";
var strTmp = "";
var strValue = "";
var index = -1;
var strArray = new Array();
strArray = str.split("\n");
for (var i = 0 ; i < strArray.length;) {
strGrid += strArray[i];
i++;
if (i != strArray.length)
strGrid += ",";
}

var testJson = {};
testJson.Rows = eval('[' + strGrid + ']');

$("#maingrid").ligerGrid({
data: testJson
});
alert("导入完成!");
}

//保存数据
function SaveData() {
var Ret = window.confirm("确认保存数据到后台数据库?\n重复记录将会被忽略");
if (Ret == 1) {

if (Page_name == "SeriesInfoList")
{
for (var i = 0; i < manager.recordNumber ; i++) {
var Series_Code = manager.rows[i].series_no;
if (GetExistCode("t_seriesinfo", "series_no", Series_Code) != "1") {
strData = "&series_no=" + manager.rows[i].series_no + "&series_name=" + manager.rows[i].series_name +
"&product_type_code=" + manager.rows[i].product_type_code + "&product_type_name=" + manager.rows[i].product_type_name +
"&description=" + manager.rows[i].description + "";
if (manager.rows[i].status == "正常")
strData += "&status=1";
else
strData += "&status=0";

if (InsertRecord(strData, "../../../handler/SeriesInfoAddUpdate.ashx") != "执行成功")
manager.rows[i].import = "导入失败";
else
manager.rows[i].import = "导入成功";
}
else {
manager.rows[i].import = "后台已存在该记录";

}
}
}

if (Page_name == "ProductTypeList") {
for (var i = 0; i < manager.recordNumber ; i++) {
var product_code = manager.rows[i].product_code;
if (GetExistCode("t_prod_type", "product_code", product_code) != "1") {
strData = "&product_code=" + manager.rows[i].product_code +
"&name=" + manager.rows[i].product_name +
"&remark=" + manager.rows[i].remark ;
if (manager.rows[i].status == "正常")
strData += "&status=1";
else
strData += "&status=0";
if (manager.rows[i].product_line == "冰箱")
strData += "&product_line=0";
if (manager.rows[i].product_line == "电饭煲")
strData += "&product_line=1";
if (manager.rows[i].product_line == "洗衣机")
strData += "&product_line=2";
if (manager.rows[i].product_line == "电磁炉")
strData += "&product_line=3";

if (InsertRecord(strData, "../../../handler/ProductTypeAddUpdate.ashx") != "执行成功")
manager.rows[i].import = "导入失败";
else
manager.rows[i].import = "导入成功";
}
else {
manager.rows[i].import = "后台已存在该记录";

}
}
}
manager.loadData();//刷新数据
parent.manager.loadData();//父表格刷新数据
alert("保存完成!");
}
}

/*
*检查数据唯一性
*/
function GetExistCode(table, columnNM, columnValue) {
var RetValue = "";
$.ajax({
url: "../../../handler/CheckData.ashx",
async: false,
type: "post",
data: "&table=" + table + "&columnNM=" + columnNM + "&columnValue=" + columnValue + "",
cache: false,
dataType: "json",
success: function (retData) {
if (retData.Result) {
RetValue = retData.Message;
}
},
error: function (e) {
alert(e);
}
});
return RetValue;
}
/*
*导入Excel时插入数据
*/
function InsertRecord(strData,url) {
var RetValue = "";
$.ajax({
url: url,
async: false,
type: "post",
data: strData,
cache: false,
dataType: "json",
success: function (retData) {
if (retData.Result) {
RetValue = retData.Message;
}
},
error: function (e) {
alert(e);
}
});
return RetValue;

}

//加载系列信息表格
function loadGrid_SeriesinfoImport() {
window['g'] =
manager = $("#maingrid").ligerGrid({
rownumbers: true,
columns: [
{ display: 'ID', name: 'id', width: 150, hide: true },
{ display: '保存情况', name: 'import', width: 150 },
{ display: '系列号', name: 'series_no', width: 150 },
{ display: '系列名称', name: 'series_name', width: 150 },
{ display: '产品类型号', name: 'product_type_code', width: 150 },
{ display: '产品类型名称', name: 'product_type_name', width: 150 },
{ display: '状态', name: 'status', width: 150 },
{ display: '备注', name: 'description', width: 150 }
], width: '99%', pageSizeOptions: [3, 10, 20, 50, 100, 200, 500, 1000], height: '97%',
dataAction: 'server', //服务器排序
//usePager: true, //服务器分页
pageSize: 20,
alternatingRow: true,
allowUnSelectRow: true
});
}
//加载产品系列表格
function loadGrid_ProductTypeImport() {
window['g'] =
manager = $("#maingrid").ligerGrid({
rownumbers: true,
columns: [
{ display: 'ID', name: 'id', hide: true, width: 10 },
{ display: '保存情况', name: 'import', width: 150 },
{ display: '产品名称', name: 'product_name', width: 150 },
{ display: '产品编号', name: 'product_code', width: 150 },
{ display: '产品线', name: 'product_line', width: 150 },
{ display: '状态', name: 'status', width: 150 },
{ display: '备注', name: 'remark', width: 150 }
], width: '99%', pageSizeOptions: [3, 10, 20, 50, 100, 200, 500, 1000], height: '97%',
dataAction: 'server', //服务器排序
pageSize: 20,
alternatingRow: true,
allowUnSelectRow: true
});
}

</script>
</head>
<body>
<div >
<div style="display: inline;">
导入初始行是指标题的下一行；导入初始列是指数据表格的第一列；
</div>
<br></br>
<div style="display: inline;">
导入初始行：
</div>&nbsp
<div style="display: inline;">
<input type="text" id="ImportRow" name="ImportRow" value="2"/>
</div>
<div style="display: inline;">
导入初始列：
</div>&nbsp
<div style="display: inline;">
<input type="text" id="ImportColumn" name="ImportColumn" value="2"/>
</div>&nbsp&nbsp
<input type="button" id="btnUpdate" value="导入" class="l-button" style="display: inline;" onclick="openBrowse()" />
&nbsp&nbsp&nbsp&nbsp
<input type="button" id="btnSave" value="保存" class="l-button" style="display: inline" onclick="SaveData()" />
</div>
<br></br>
<div>
<form id="form1" enctype="multipart/form-data" method="post" action="SeriesInfoImportExcel.aspx">
<input type="file" id="file"name="file" style="display:none" onchange="ReadExcel(this)"/>
</form>
<div id="maingrid" ></div>
</div>
</body>
</html>

0 0