如何使用javascript解析excel表并获取数据并执行GET PUT和POST REQUESTS。 [英] How to parse excel sheet using javascript and get the data and perform GET PUT and POST REQUESTS.
本文介绍了如何使用javascript解析excel表并获取数据并执行GET PUT和POST REQUESTS。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
var fs = require("fs");
var request = require("request");
var inputJSONdata = fs.readFileSync("C:/Users/SESA435429/Documents/Prathamesh_kulkarni/excelsheetinfo.xlsx");
console.log(inputJSONdata);
var inputAttribute = JSON.parse(inputJSONdata);
var userGivenIP = inputAttribute.IPv6Address
var username=inputAttribute.Username;
var password=inputAttribute.password;
var MacAddress = inputAttribute.MACAddress;
var path = inputAttribute.URIvalues;
var GET_Flag;
var PUT_Flag;
var Compare_Flag;
var Invalid_Data_Flag;
if("proceed" == inputAttribute.Home)
{
var jsonContent; //Json object after parsing GET request body.
var getIntoLoop; // Enter into the loop after getting data through web service.
var util = require("util");
var dateFormat = require("dateformat");
var day=dateFormat("yyyy-mm-dd_h.MM.ss");
var log_file = fs.createWriteStream("C:/Users/SESA391442/URI_Testing_Tool/LightWebApp/js/tests/express/Uri_test/T-Case-1_LOG[ "+day+" ].log", {flags : "w"});
log_stdout = process.stdout;
console.print = function(d) { //
log_file.write(util.format(d) );
log_stdout.write(util.format(d));
};
if(typeof require !== "undefined") XLSX = require("xlsx");
var workbook = XLSX.readFile(path); // reading from Excel file.
var URI = workbook.SheetNames[0];
var address_of_cell_1 = "A"+"1";
var address_of_cell_2 = "A"+"2";
var address_of_cell_3 = "A"+"3";
/* Get worksheet */
var worksheet = workbook.Sheets[URI];
/* Find desired cell */
var desired_cell_1 = worksheet[address_of_cell_1];
var desired_cell_2 = worksheet[address_of_cell_2];
var desired_cell_3 = worksheet[address_of_cell_3];
/* Get the value */
var desired_value_1 = desired_cell_1.v; // value-1 URI
var desired_value_2 = desired_cell_2.v; // value-2 KEYS
var desired_value_3 = desired_cell_3.v; // value-3 VALUES
var uri_attributes = new Array();
// this will return an array with strings "1", "2", etc.
uri_attributes = desired_value_2.split(";");
uri_attributes_values = desired_value_3.split(";");
var i = 0;
var stringJSONTemp = "";
for (; i < uri_attributes.length; i++ )
{
stringJSONTemp += uri_attributes[i]+": "+uri_attributes_values[i];
if(i == uri_attributes.length-1)
{
}
else{
stringJSONTemp += ", ";
}
}
console.print("REQUEST...\n");
console.print("PUT : /rs/IPConfiguration;NetIfName=eth0;ProtocolType=IPv4\n");
console.print("PUT Parameters: "+stringJSONTemp);
console.print("RESPONSE...\n");
request({
method: "PUT",
uri: "http://"+username+":"+ password +"@"+userGivenIP+desired_value_1,
headers:
{
"content-type": "application/json"
},
body: JSON.stringify(JSON.parse("{"+stringJSONTemp+"}")),
},
function (error, response, body)
{
if(error)
{
console.print(error);
}
else
{
console.print("Response Code of PUT request: "+response.statusCode);
console.print("\n*******************************************************************************************************\n");
PUT_Flag = true;
fs.readFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", "utf8", function (err,data)
{
if (err)
{
return console.log(err);
}
var result = data.replace(/"PUT_Result":""/g, ""PUT_Result":"pass""); // changing the ip address of DUT dualserver.ini file
fs.writeFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", result,"utf8", function (err)
{
if (err) return console.log(err);
});
});
}
}
)
setTimeout(function()
{
console.print("\nREQUEST...\n");
console.print("GET : /rs/IPConfiguration;NetIfName=eth0;ProtocolType=IPv4\n");
console.print("\nRESPONSE...\n");
var options = {
url: "http://"+username+":"+ password + "@"+userGivenIP+desired_value_1,
method: "GET",
}
request(options, function (error, response, body)
{
if(error)
{
console.print("error:"+error);
}
if(response.statusCode !== 200)
{
console.print("Invalid Status Code Returned:", response.statusCode);
}
if (!error && response.statusCode == 200)
{
// Define to JSON type
var jsonContent = JSON.parse(body);
getIntoLoop = true ;
console.print("\n Response statusCode :"+response.statusCode);
console.print("\nBody :"+body);
console.print("\n*******************************************************************************************************\n");
GET_Flag = true
fs.readFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", "utf8", function (err,data)
{
if (err)
{
return console.log(err);
}
var result = data.replace(/"GET_Result":""/g, ""GET_Result":"pass""); // changing the ip address of DUT dualserver.ini file
fs.writeFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", result, "utf8", function (err)
{
if (err) return console.log(err);
});
});
}
k = 0;
var attrib;
var val;
for (; k < uri_attributes.length; k++ )
{
key = uri_attributes[k].replace(/[""]+/g,"");
val = uri_attributes_values[k].replace(/[""]+/g,"");
//console.log("jsonContent.uri_attributes[k]:", eval("jsonContent." + key));
if (eval("jsonContent." + key) == val)
{
if(k == uri_attributes.length-1)
{
console.print("\ncomparing values of PUT and GET request: \n" );
console.print("attributes matched to the earlier PUT request\n" );
console.print("comparing is successful\n" );
console.print("\n*******************************************************************************************************\n");
console.print("\n");
Compare_Flag = true
setTimeout(function()
{
fs.readFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", "utf8", function (err,data)
{
if (err)
{
return console.log(err);
}
var result = data.replace(/"CMPR_Result":""/g, ""CMPR_Result":"pass""); // changing the ip address of DUT dualserver.ini file
fs.writeFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js";, result, ";utf8", function (err)
{
if (err) return console.log(err);
});
});
}, 3000)
}
}
else
{
console.print("\n attributes did not match to the earlier PUT request \n" );
}
}
});
}, 30000)
setTimeout(function()
{
console.print("sending INVALID data in JSON....\n");
var j = 0;
for (; j < uri_attributes.length-3; j++ )
{
stringJSONTemp += uri_attributes[j]+": "+uri_attributes_values[j];
if(j == uri_attributes.length-4)
{
}
else{
stringJSONTemp += ", ";
}
}
request({
method: "PUT",
uri: "http://"+username+":"+ password + "@"+userGivenIP+desired_value_1,
headers:
{
"content-type": "application/json"
},
//body: JSON.stringify(JSON.parse("{"+stringJSONTemp+"}")),
body: JSON.stringify({
"NetIfName": "eth0",
"AddressOrigin": "Static",
"IPv4Address": "192.168.1.100",
"SubnetMask": "255.255.255.0",
"GatewayAddress": "192.168.1.254"
})
},
function (error, response, body)
{
if(error)
{
console.log(error);
}
if(response.statusCode !== 200)
{
console.print("\nInvalid Status Code Returned while sending invalid values through JSON: "+response.statusCode);
console.print("\nresponse Status Code: "+response.statusCode);
Invalid_Data_Flag = true
fs.readFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", "utf8", function (err,data)
{
if (err)
{
return console.log(err);
}
var result = data.replace(/"INV_Result":""/g, ""INV_Result":"pass""); // changing the ip address of DUT dualserver.ini file
fs.writeFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", result, "utf8", function (err)
{
if (err) return console.log(err);
});
});
}
console.print("\nbody: "+body);
console.print("\n\n*******************************************************************************************************\n");
}
)
}, 40000)
}
setTimeout(function()
{
if((true == GET_Flag) && (true == PUT_Flag) && (true == Invalid_Data_Flag) &&(true == Compare_Flag))
{
console.print('\nTESTCASE is PASSED');
console.print('\n');
var fs = require('fs')
fs.readFile('C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js', 'utf8', function (err,data)
{
if (err)
{
return console.log(err);
}
var result = data.replace(/"TOTAL_Result":""/g, '"TOTAL_Result":"pass"'); // changing the ip address of DUT dualserver.ini file
fs.writeFile('C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js', result, 'utf8', function (err)
{
if (err) return console.log(err);
});
});
}
}, 50000)
我尝试过：
What I have tried:
var fs = require("fs");
var request = require("request");
var inputJSONdata = fs.readFileSync("C:/Users/SESA435429/Documents/Prathamesh_kulkarni/excelsheetinfo.xlsx");
console.log(inputJSONdata);
var inputAttribute = JSON.parse(inputJSONdata);
var userGivenIP = inputAttribute.IPv6Address
var username=inputAttribute.Username;
var password=inputAttribute.password;
var MacAddress = inputAttribute.MACAddress;
var path = inputAttribute.URIvalues;
var GET_Flag;
var PUT_Flag;
var Compare_Flag;
var Invalid_Data_Flag;
if("proceed" == inputAttribute.Home)
{
var jsonContent; //Json object after parsing GET request body.
var getIntoLoop; // Enter into the loop after getting data through web service.
var util = require("util");
var dateFormat = require("dateformat");
var day=dateFormat("yyyy-mm-dd_h.MM.ss");
var log_file = fs.createWriteStream("C:/Users/SESA391442/URI_Testing_Tool/LightWebApp/js/tests/express/Uri_test/T-Case-1_LOG[ "+day+" ].log", {flags : "w"});
log_stdout = process.stdout;
console.print = function(d) { //
log_file.write(util.format(d) );
log_stdout.write(util.format(d));
};
if(typeof require !== "undefined") XLSX = require("xlsx");
var workbook = XLSX.readFile(path); // reading from Excel file.
var URI = workbook.SheetNames[0];
var address_of_cell_1 = "A"+"1";
var address_of_cell_2 = "A"+"2";
var address_of_cell_3 = "A"+"3";
/* Get worksheet */
var worksheet = workbook.Sheets[URI];
/* Find desired cell */
var desired_cell_1 = worksheet[address_of_cell_1];
var desired_cell_2 = worksheet[address_of_cell_2];
var desired_cell_3 = worksheet[address_of_cell_3];
/* Get the value */
var desired_value_1 = desired_cell_1.v; // value-1 URI
var desired_value_2 = desired_cell_2.v; // value-2 KEYS
var desired_value_3 = desired_cell_3.v; // value-3 VALUES
var uri_attributes = new Array();
// this will return an array with strings "1", "2", etc.
uri_attributes = desired_value_2.split(";");
uri_attributes_values = desired_value_3.split(";");
var i = 0;
var stringJSONTemp = "";
for (; i < uri_attributes.length; i++ )
{
stringJSONTemp += uri_attributes[i]+": "+uri_attributes_values[i];
if(i == uri_attributes.length-1)
{
}
else{
stringJSONTemp += ", ";
}
}
console.print("REQUEST...\n");
console.print("PUT : /rs/IPConfiguration;NetIfName=eth0;ProtocolType=IPv4\n");
console.print("PUT Parameters: "+stringJSONTemp);
console.print("RESPONSE...\n");
request({
method: "PUT",
uri: "http://"+username+":"+ password +"@"+userGivenIP+desired_value_1,
headers:
{
"content-type": "application/json"
},
body: JSON.stringify(JSON.parse("{"+stringJSONTemp+"}")),
},
function (error, response, body)
{
if(error)
{
console.print(error);
}
else
{
console.print("Response Code of PUT request: "+response.statusCode);
console.print("\n*******************************************************************************************************\n");
PUT_Flag = true;
fs.readFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", "utf8", function (err,data)
{
if (err)
{
return console.log(err);
}
var result = data.replace(/"PUT_Result":""/g, ""PUT_Result":"pass""); // changing the ip address of DUT dualserver.ini file
fs.writeFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", result,"utf8", function (err)
{
if (err) return console.log(err);
});
});
}
}
)
setTimeout(function()
{
console.print("\nREQUEST...\n");
console.print("GET : /rs/IPConfiguration;NetIfName=eth0;ProtocolType=IPv4\n");
console.print("\nRESPONSE...\n");
var options = {
url: "http://"+username+":"+ password + "@"+userGivenIP+desired_value_1,
method: "GET",
}
request(options, function (error, response, body)
{
if(error)
{
console.print("error:"+error);
}
if(response.statusCode !== 200)
{
console.print("Invalid Status Code Returned:", response.statusCode);
}
if (!error && response.statusCode == 200)
{
// Define to JSON type
var jsonContent = JSON.parse(body);
getIntoLoop = true ;
console.print("\n Response statusCode :"+response.statusCode);
console.print("\nBody :"+body);
console.print("\n*******************************************************************************************************\n");
GET_Flag = true
fs.readFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", "utf8", function (err,data)
{
if (err)
{
return console.log(err);
}
var result = data.replace(/"GET_Result":""/g, ""GET_Result":"pass""); // changing the ip address of DUT dualserver.ini file
fs.writeFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", result, "utf8", function (err)
{
if (err) return console.log(err);
});
});
}
k = 0;
var attrib;
var val;
for (; k < uri_attributes.length; k++ )
{
key = uri_attributes[k].replace(/[""]+/g,"");
val = uri_attributes_values[k].replace(/[""]+/g,"");
//console.log("jsonContent.uri_attributes[k]:", eval("jsonContent." + key));
if (eval("jsonContent." + key) == val)
{
if(k == uri_attributes.length-1)
{
console.print("\ncomparing values of PUT and GET request: \n" );
console.print("attributes matched to the earlier PUT request\n" );
console.print("comparing is successful\n" );
console.print("\n*******************************************************************************************************\n");
console.print("\n");
Compare_Flag = true
setTimeout(function()
{
fs.readFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", "utf8", function (err,data)
{
if (err)
{
return console.log(err);
}
var result = data.replace(/"CMPR_Result":""/g, ""CMPR_Result":"pass""); // changing the ip address of DUT dualserver.ini file
fs.writeFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js";, result, ";utf8", function (err)
{
if (err) return console.log(err);
});
});
}, 3000)
}
}
else
{
console.print("\n attributes did not match to the earlier PUT request \n" );
}
}
});
}, 30000)
setTimeout(function()
{
console.print("sending INVALID data in JSON....\n");
var j = 0;
for (; j < uri_attributes.length-3; j++ )
{
stringJSONTemp += uri_attributes[j]+": "+uri_attributes_values[j];
if(j == uri_attributes.length-4)
{
}
else{
stringJSONTemp += ", ";
}
}
request({
method: "PUT",
uri: "http://"+username+":"+ password + "@"+userGivenIP+desired_value_1,
headers:
{
"content-type": "application/json"
},
//body: JSON.stringify(JSON.parse("{"+stringJSONTemp+"}")),
body: JSON.stringify({
  "NetIfName":  "eth0",
  "AddressOrigin":  "Static",
  "IPv4Address":  "192.168.1.100",
  "SubnetMask":  "255.255.255.0",
  "GatewayAddress":  "192.168.1.254"
})
},
function (error, response, body)
{
if(error)
{
console.log(error);
}
if(response.statusCode !== 200)
{
console.print("\nInvalid Status Code Returned while sending invalid values through JSON: "+response.statusCode);
console.print("\nresponse Status Code: "+response.statusCode);
Invalid_Data_Flag = true
fs.readFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", "utf8", function (err,data)
{
if (err)
{
return console.log(err);
}
var result = data.replace(/"INV_Result":""/g, ""INV_Result":"pass""); // changing the ip address of DUT dualserver.ini file
fs.writeFile("C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js", result, "utf8", function (err)
{
if (err) return console.log(err);
});
});
}
console.print("\nbody: "+body);
console.print("\n\n*******************************************************************************************************\n");
}
)
}, 40000)
}
setTimeout(function()
{
if((true == GET_Flag) && (true == PUT_Flag) && (true == Invalid_Data_Flag) &&(true == Compare_Flag))
{
console.print('\nTESTCASE is PASSED');
console.print('\n');
var fs = require('fs')
fs.readFile('C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js', 'utf8', function (err,data)
{
if (err)
{
return console.log(err);
}
var result = data.replace(/"TOTAL_Result":""/g, '"TOTAL_Result":"pass"'); // changing the ip address of DUT dualserver.ini file
fs.writeFile('C:/Users/SESA391442/URI_Testing_tool/LightWebApp/js/tests/express/sniffing/glanceTestCaseFile.js', result, 'utf8', function (err)
{
if (err) return console.log(err);
});
});
}
}, 50000)</pre>推荐答案
Quote:
How to parse excel sheet using javascript and get the data and perform GET PUT and POST REQUESTS.
How to parse excel sheet using javascript and get the data and perform GET PUT and POST REQUESTS.
My short answer is: you don’t.
My short answer is: you don't.
Quote:
var inputJSONdata = fs.readFileSync("C:/Users/SESA435429/Documents/Prathamesh_kulkarni/excelsheetinfo.xlsx");I see 2 problems here!
- JS/Browser do not have access to local files, it is a security feature.
- An .xlsx file is not a json file, so there is not way you will read an Excel file using a JSon parser.
Asking questions is a skill[^]
I see 2 problems here!
- JS/Browser do not have access to local files, it is a security feature.
- An .xlsx file is not a json file, so there is not way you will read an Excel file using a JSon parser.
Asking questions is a skill[^]
这篇关于如何使用javascript解析excel表并获取数据并执行GET PUT和POST REQUESTS。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
