// This script uses an ActiveXObject to create an Excel spreadsheet

// ---------------------------------
// Some constants
var debug = 1; // make it 0 to turn the debug messages off

var AlignCenter = -4108;
var AlignLeft   = -4131;
var AlignRight  = -4152;

var xlEdgeLeft         = 7;
var xlEdgeTop          = 8;
var xlEdgeRight        = 10;
var xlEdgeBottom       = 9;
var xlInsideVertical   = 11;
var xlInsideHorizontal = 12;
var xlContinuous       = 1;
var xlThin             = 2;
var xlThick            = 4;

var CLetters = new Array("", "A", "B", "C", "D", "E", "F", "G", "H", "I", 
                         "J", "K", "L", "M", "N", "O", "P", "Q", "R", 
                         "S", "T", "U", "V", "W", "X", "Y", "Z");

// ---------------------------------

// start the Excel
var excel = new ActiveXObject("Excel.Application");

// create a new worksheet
var ws = new ActiveXObject("Excel.Sheet");

// make the application visible
ws.Application.Visible = true;

print("Entering headres");
// Put some headers in the cells
for(i=1;i<=10;i++)
  ws.ActiveSheet.Cells(1, i) = "Test "+i;
ws.ActiveSheet.Cells(1, 11) = "Sum";
WScript.Sleep(1000);

// enter some data in the cells
print("Entering data");
for(i=2;i<=10;i++){
  for(j=1;j<=10;j++)
    ws.ActiveSheet.Cells(i, j) = Math.random()*100;
  // also add some formulae
  ws.ActiveSheet.Cells(i, 11) = "=sum(a"+i+":j"+i+")";
}
WScript.Sleep(2000);

// set the width of columns
print("set the width of columns");
ws.ActiveSheet.Range("A1:K10").Select();
excel.Selection.ColumnWidth = 6;
WScript.Sleep(1000);
excel.Columns("K:K").Select();
excel.Selection.ColumnWidth = 7;

// do some data alignment
print("Set format for all numbers");
ws.ActiveSheet.Range("A2:K10").Select();
excel.Selection.NumberFormat = "0.00";
excel.Selection.HorizontalAlignment = AlignRight;
WScript.Sleep(1000);

// format the Sum coulmn
print("Formatting the SUM column");
ws.ActiveSheet.Range("K2:K10").Select();
excel.Selection.Font.Bold = true;
excel.Selection.NumberFormat = "0.0";
WScript.Sleep(1000);

// make these cells bold and colorful
print("Set format of the header");
//ws.ActiveSheet.Range("A1:K1").Select();
excel.Rows("1:1").Select();
excel.Selection.Font.Bold = true;
excel.Selection.Font.Color = 255+12*255*255;
excel.Selection.HorizontalAlignment = AlignCenter;
WScript.Sleep(1000);


// add borders
// left edge
print("Set borders");
excel.Range("A1:K10").Borders(xlEdgeLeft).LineStyle = xlContinuous;
excel.Range("A1:K10").Borders(xlEdgeLeft).Weight = xlThick;
// right edge
excel.Range("A1:K10").Borders(xlEdgeRight).LineStyle = xlContinuous;
excel.Range("A1:K10").Borders(xlEdgeRight).Weight = xlThick;
// top edge
excel.Range("A1:K10").Borders(xlEdgeTop).LineStyle = xlContinuous;
excel.Range("A1:K10").Borders(xlEdgeTop).Weight = xlThick;
// bottom edge
excel.Range("A1:K10").Borders(xlEdgeBottom).LineStyle = xlContinuous;
excel.Range("A1:K10").Borders(xlEdgeBottom).Weight = xlThick;
// inner lines: vertical and horizontal
excel.Range("A1:K10").Borders(xlInsideVertical).LineStyle = xlContinuous;
excel.Range("A1:K10").Borders(xlInsideHorizontal).Weight = xlThin;


// save the document
print("Saving file");
ws.SaveAs("c:\\temp\\test.xls");

// close the document
print("Exiting Excel");
excel.Quit();

// ---------------------------------------------
// additional functions
function print(msg)
{
   if( debug )
      WScript.Echo(msg);
}