Forum Discussion

Geethanjali's avatar
Geethanjali
Occasional Contributor
5 years ago

Getting "Java Run time Error" when writing to Excel

Hi, Am trying to write my result back to an excel file. Am using the code as below .But am getting "Java Run time error". Any help here would be really helpful.    let Excel = Sys.OleObject("Excel...
workingwithExcel
writingtoExcel
  • BenoitB's avatar
    BenoitB
    5 years ago

    A more complete working sample just for you

    Beware of indexes of Line and Column starting at 0.

     

    function writeExcel(Valeur, Line, Column, ExcelFile, SheetName) {
  let excelObject, excelWorkbookObj, excelMainSheet;
  try {
    excelObject               = Sys.OleObject("Excel.Application");
    if (excelObject == null)
      throw Error("Excel n'est pas installé !");
    excelObject.Visible       = false;
    excelObject.DisplayAlerts = false;
    excelWorkbookObj          = excelObject.Workbooks.Open(ExcelFile);
    excelMainSheet            = excelWorkbookObj.Sheets.item(SheetName);
    excelMainSheet.Cells.Item(Line, Column).Value2 = Valeur;
    excelObject.Application.ActiveWorkbook.Save;
  }
  catch(e) {
    Log.Message("Erreur durant la mise à jour Excel", e.message, pmHigher);
  }
  finally {
    if (excelObject != null) {
      excelObject.Application.ActiveWorkbook.Close;
      excelObject.Quit;
      excelObject      = null;
      excelWorkbookObj = null;
      excelMainSheet   = null;
    }
  }
}

     

BenoitB's avatar
BenoitB
Community Hero

To set a value of a cell :

// Item accepte double syntax, index et name
excelMainSheet.Cells.Item("4","J").Value2 = myValue,
excelMainSheet.Cells.Item(4, 10).Value2 = myValue;
BenoitB's avatar
BenoitB
Community Hero
5 years ago

A more complete working sample just for you

Beware of indexes of Line and Column starting at 0.

 

function writeExcel(Valeur, Line, Column, ExcelFile, SheetName) {
  let excelObject, excelWorkbookObj, excelMainSheet;
  try {
    excelObject               = Sys.OleObject("Excel.Application");
    if (excelObject == null)
      throw Error("Excel n'est pas installé !");
    excelObject.Visible       = false;
    excelObject.DisplayAlerts = false;
    excelWorkbookObj          = excelObject.Workbooks.Open(ExcelFile);
    excelMainSheet            = excelWorkbookObj.Sheets.item(SheetName);
    excelMainSheet.Cells.Item(Line, Column).Value2 = Valeur;
    excelObject.Application.ActiveWorkbook.Save;
  }
  catch(e) {
    Log.Message("Erreur durant la mise à jour Excel", e.message, pmHigher);
  }
  finally {
    if (excelObject != null) {
      excelObject.Application.ActiveWorkbook.Close;
      excelObject.Quit;
      excelObject      = null;
      excelWorkbookObj = null;
      excelMainSheet   = null;
    }
  }
}

 

  • Geethanjali's avatar
    Geethanjali
    Occasional Contributor
    5 years ago

    Thanks very much for the reply. I had found the issue. It was because I didnot know that excel was taking the first row as 1 and not as 0.