Forum Discussion

shane_manjunath's avatar
shane_manjunath
Occasional Contributor
8 years ago
Solved

How to write into excel file using Javascript?

I have written the following javascript to write into excel.However "book.Sheets" doesn't seem to exist so I am unable to create sheet object

 

Also when It tries to execute the code - app.Cells(1,1) = "test"; --- get the error that the function does not exist

 

var fileName = "C:\\Files\\test.xlsx";
var sheetName = "Sheet1";

 

var app = Sys.OleObject("Excel.Application")

app.Visible = "True";

var book = app.Workbooks.Open(fileName);

var sheet = book.Sheets(sheetName);

 

rowCount = app.ActiveSheet.UsedRange.Rows.Count + 1;
colCount = 1;

app.Cells(1,1) = "test";

 

Please suggest if I am missing anything?Thanks in advance

  • HKosova's avatar
    HKosova
    8 years ago

    In JavaScript (not JScript) you need to access Excel collection items by using the .Items property:

     

    book.Sheets(sheetName) => book.Sheets.Item(sheetName)
    app.Cells(1,1) => app.Cells.Item(1,1)

    etc.

     

    More details here: https://support.smartbear.com/viewarticle/82954/#collections

     

    Also, you can't assign to a cell directly because it's an object, you need to use something like:

    app.Cells.Item(1,1).Value2 = "test";

6 Replies

Sort By
  • HKosova's avatar
    HKosova
    SmartBear Alumni (Retired)
    8 years ago

    In JavaScript (not JScript) you need to access Excel collection items by using the .Items property:

     

    book.Sheets(sheetName) => book.Sheets.Item(sheetName)
    app.Cells(1,1) => app.Cells.Item(1,1)

    etc.

     

    More details here: https://support.smartbear.com/viewarticle/82954/#collections

     

    Also, you can't assign to a cell directly because it's an object, you need to use something like:

    app.Cells.Item(1,1).Value2 = "test";
  • iamraj09's avatar
    iamraj09
    Contributor
    8 years ago

    var fileName = "C:\\Files\\test.xlsx";
    var sheetName = "Sheet1";

     

    var app = Sys.OleObject("Excel.Application")

    app.Visible = "True";

    var book = app.Workbooks.Open(fileName);

    var sheet = book.Sheets(sheetName);    

     

    rowCount = app.ActiveSheet.UsedRange.Rows.Count + 1;
    colCount = 1;

    sheet.Cells(1,1) = "test";  //-----> try sheet.Cells(1,1)= "test";  instead of  app.Cells(1,1)= "test";

      • forrestaustin's avatar
        forrestaustin
        Contributor
        6 years ago

        Can you summarize what he said in that interview? I just watched it and I literally could not understand any of his response due to poor mic quality.