Forum Discussion

nedbacan's avatar
nedbacan
Frequent Contributor
2 years ago
Solved

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();
    }
    

     

4 Replies

    • nedbacan's avatar
      nedbacan
      Frequent Contributor

      AlexKaras Thank you for your help. I was able to correct the typo, ⁣but now it's failing when I re-run the script and detects the CPU report tab already exist. I am new to this …can you explain and update it?

       

       

      • AlexKaras's avatar
        AlexKaras
        Champion Level 3

        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();
        }