nedbacan
2 years agoFrequent Contributor
Creates the Excel file but cannot rerun code again if Excel sheet has been created already.
Hello, can someone correct my mistake in the following code.
It creates the Excel file and adds the sheet name then enters the data, but I cannot rerun the code again.
It fails because it cannot find read property 'Count' of undefined. See screenshot. .
function ExcelExample_1() {
//Get the data that will be added into Excel file
var curTime = aqDateTime.Now();
var cpu = Sys.CPUUsage;
var fileName = "C:\\temp\\DataStorageExcel.xlsx";
var excelFile;
var excelSheet;
if (aqFile.Exists(fileName)) {
//open the existing file
excelFile = Excel.Open(fileName);
var sheetExist = false;
for (var i = 0; i < excelFile.Sheets.Count; i++) {
if (excelFile.Sheets.Item(i).Name == "CPU Report") {
excelSheet = excelFile.Sheets.Item(i);
sheetExist = true;
break;
}
}
if(!sheetExist){
excelSheet = excelFile.AddSheet("CPU Report");
}
} else {
//Create new excel file
excelFile = Excel.Create(fileName);
excelSheet = excelFile.AddSheet("CPU Report");
}
var rowIndex = excelSheet.RowCount + 1;
excelSheet.Cell("A", rowIndex).Value = curTime;
excelSheet.Cell("B", rowIndex).Value = cpu;
excelFile.Save();
}
Hi,
This should work as expected:
function ExcelExample_1() { //Get the data that will be added into Excel file var curTime = aqDateTime.Now(); var cpu = Sys.CPUUsage; var fileName = "c:\\temp\\DataStorageExcel.xlsx"; var excelFile; var excelSheet; if (aqFile.Exists(fileName)) { //open the existing file excelFile = Excel.Open(fileName); var sheetExist = false; for (var i = 0; i < excelFile.SheetCount; i++) { excelSheet = excelFile.SheetByIndex(i); if (excelSheet.Title == "CPU Report") { sheetExist = true; break; } } if(!sheetExist){ excelSheet = excelFile.AddSheet("CPU Report"); } } else { //Create new excel file excelFile = Excel.Create(fileName); excelSheet = excelFile.AddSheet("CPU Report"); } var rowIndex = excelSheet.RowCount + 1; excelSheet.Cell("A", rowIndex).Value = curTime; excelSheet.Cell("B", rowIndex).Value = cpu; excelFile.Save(); }