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.Application");
var results = Excel.Workbooks.Open("Excel file Path name");

var sheet = results.Sheets.Item("Sheet1");

Excel.Cells.$set("Item" , 0, 18, "Generate Claim Number");

 

I have also tried 

sheet.Cells.$set("Item" , 0, 18, "Generate Claim Number");
sheet.Cells.Item(0, 18).Value = "Generate Claim Number";

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
    5 years ago

    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.