Forum Discussion

shane_manjunath's avatar
shane_manjunath
Occasional Contributor
9 years ago

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,...
  • HKosova's avatar
    HKosova
    9 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

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
    7 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. 

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      7 years ago

      Question:

       

      What are you trying to achieve by writing out to Excel?  A lot of the suggestions on how to do so and what to do are dependant, somewhat, on the specific use case.

      Generally speaking, to access an excel sheet and write out to it, you're going to want to use the COM object of Excel.  High level is documented here https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sources/excel/working-via-com.html

      • forrestaustin's avatar
        forrestaustin
        Contributor
        7 years ago

        Thanks for the link! I'll check it out today. I have a wpf application that has the option to create a 'print out' form in excel. Each page already has an excel template to sort of mirror the wpf page, but I would want to check and make sure the data from the excel print form matches the data that was just entered into the form. The location the data appears in the print out is hard coded so that should make it relatively easy to do I imagine.