delete rows in excel sheet using javascript

Question

Hello ,

I am trying to edit an excel sheet using activex object in Javascript.

The function below opens an excel sheet and creates a few entries.

function test() {
var ExcelApp = new ActiveXObject("Excel.Application");
var ExcelSheet = ExcelApp.Workbooks.Open("c:\\jan29.xls");
ExcelSheet.Application.Visible = true;

ExcelSheet.ActiveSheet.Cells(0,1).Value = i;

ExcelSheet.Save;
ExcelSheet.Application.Quit();
}

But how do I delete rows, get the number of sheets and get value by rowColumn .Where can I get the list of APIS ?

Regards, Mithun

Answer 1

You can find the Developer Reference for Excel 2007 on MSDN along with the (somewhat unruly) API Reference

To answer your question directly, I've created a demo that includes row deletion.

Working Demo

(Requires IE, Excel and ActiveX privileges)
http://jsbin.com/izule (editable via http://jsbin.com/izule)

JavaScript Source

function test() {
  if (!window['ActiveXObject']) {
    log('Error: ActiveX not supported');
    return;
  }

  try {
    var
      ExcelApp = new ActiveXObject("Excel.Application"),
      ExcelBook = ExcelApp.Workbooks.Add();

    ExcelBook.Application.Visible = true;
    log('Opened Excel');

    wait(2, enterData);
  }
  catch (ex) {
    log('An error occured while attempting to open Excel');
    console.log(ex);
  }

  function enterData() {
    try {
      ExcelBook.ActiveSheet.Cells(1, 1).Value = 'foo';
      ExcelBook.ActiveSheet.Cells(2, 1).Value = 'bar';
      log('Entered data');
    }
    catch (ex) {
      log('An error occured while attempting to enter data');
      console.log(ex);
    }

    wait(2, deleteRow);
  }

  function deleteRow () {
    try {
      ExcelBook.ActiveSheet.Rows(1).Delete();
      log('Deleted first row');
    }
    catch (ex) {
      log('An error occured while attempting to delete a row');
      console.log(ex);
    }

    wait(2, quitExcel);
  }

  function quitExcel () {
    try {
      // Allow excel to quit without prompting user to save.
      ExcelBook.Saved = true;
      ExcelBook.Application.Quit();
      log('Quit excel');
    }
    catch (ex) {
      log('An error occured while attempting to quit excel');
      console.log(ex);
    }
  }
}

function wait (time, action) {
  setTimeout(action, time * 1000);
}

function log (message) {
  var
    list = document.getElementById('log'),
    newLog = document.createElement('li');
  newLog.innerHTML = message;
  list.appendChild(newLog);
}
if (!window['console'] || !window.console['log']) { console = {log: log}; }