Как разобрать лист excel с помощью javascript и получить данные, а также выполнить запросы GET PUT и POST.
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)
Что я уже пробовал:
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>
OriginalGriff
И что?
Что он делает такого, чего вы не ожидали, или не делает того, что вы сделали?
Какие-нибудь сообщения об ошибках? Где они происходят? Что вы делаете, чтобы это произошло?
Помните, что мы не можем видеть ваш экран, получить доступ к вашему жесткому диску или прочитать ваши мысли - поэтому сброс вашего кода на нас без каких-либо объяснений или доступа к вашим данным никому не поможет!
Используйте виджет" улучшить вопрос", чтобы отредактировать свой вопрос и предоставить более подробную информацию.