<%@ language="JavaScript" %>
<%
//Create an
instance of the application
var app =
Server.CreateObject("PagosSpreadSheet.Application");
//Add a workbook
var wb =
app.Workbooks.Add();
//Load a
worksheet from added workbook
var ws =
wb.Worksheets("Sheet1");
//Prepare
worksheet data
var data =
new Array([ "Employee",
"Volume Q1", "Sales Q1", "Volume
Q2", "Sales Q2", "Volume Q3", "Sales
Q3", "Volume Q4", "Sales Q4" ], [ "Helen",
5, "=B2*1581.25", 40, "=D2*1581.25", 69, "=F2*1581.25", 31, "=H2*1581.25" ], [ "Fred", 32, "=B3*1426.25", 80, "=D3*1426.25", 45, "=F3*1426.25", 87, "=H3*1426.25" ], [ "John", 60, "=B4*1789.25",
65, "=D4*1789.25", 90, "=F4*1789.25", 50, "=H4*1789.25" ], [ "Jane", 40, "=B5*1369.25",
1, "=D5*1369.25", 22, "=F5*1369.25", 65, "=H5*1369.25" ], [ "Total", "=SUM(B2:B5)",
"=SUM(C2:C5)", "=SUM(D2:D5)", "=SUM(E2:E5)",
"=SUM(F2:F5)", "=SUM(G2:G5)", "=SUM(H2:H5)",
"=SUM(I2:I5)" ]);
var row;
var
column;
//Bind values to
cells
for (row =
1; row < 7; row++)
{
for
(column = 1; column < 10; column++)
{
if
(data[row-1][column-1].toString().indexOf("=")
!= 0)
ws.Cells(row,
column).Value = data[row-1][column-1];
else
ws.Cells(row,
column).Formula = data[row-1][column-1];
}
}
//Apply styles
var
HAlignCenter=-4108;
var a1i6 =
ws.Range("A1:I6");
a1i6.Font.Name = "Arial";
a1i6.Font.Size = 8;
a1i6.HorizontalAlignment = HAlignCenter;
var a1i1 =
ws.Range("A1:I1");
a1i1.Font.Bold = true;
a1i1.Interior.Color = parseInt("33CCCC", 16);
var a6i6 =
ws.Range("A6:I6");
a6i6.Font.Bold = true;
a6i6.Interior.Color = parseInt("33CCCC", 16);
var
numberFormat = "_(\"$\"*
#,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)";
var c2c6 =
ws.Range("C2:C6");
var e2e6 =
ws.Range("E2:E6");
var g2g6 =
ws.Range("G2:G6");
var i2i6 =
ws.Range("I2:I6");
c2c6.NumberFormat = numberFormat;
e2e6.NumberFormat = numberFormat;
g2g6.NumberFormat = numberFormat;
i2i6.NumberFormat = numberFormat;
//Save workbook
to a temporary file for streaming
var
fileName = Server.MapPath("Spreadsheets/Temp/EmployeeDataFormatFormula"
+ Session.SessionID + ".xml");
wb.SaveAs(fileName);
//Prepare
response for streaming
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;
filename=EmployeeDataFormatFormula.xml");
//Stream
workbook to response
var stream
= Server.CreateObject("ADODB.Stream");
stream.Mode = 3;
stream.Open();
stream.LoadFromFile(fileName);
content=stream.ReadText(-1);
Response.BinaryWrite(content);
stream.Close();
stream=null;
content=null;
//Delete
temporary file
var
fso=Server.CreateObject("Scripting.FileSystemObject");
fso.DeleteFile(fileName);
fso=null;
//Close workbook
wb.Close();
//End response
Response.End();
%>