Setting Values
Methods for Assigning Cell Values
Cell values can be assigned:
- Programmatically, on a cell-by-cell basis
with data that has been parsed from a CSV file or an XML file
- By iterating through an
ADO Recordset and assigning field values to cells on a row-by-row basis
- By importing a recordset in a single step with
CopyFromRecordset or
GetDataFromRecordset*
- By importing a recordset in a single step using
ExcelTemplate
- By copying data from an existing Excel file into the newly created Excel sheet
using
GetDataFromExcelSheet*
- By
opening*
an existing Excel file, and saving it with a new name
ExcelWriter allows you to format these imported cell values as virtually any data type
that are supported by Excel. This includes currency, date/time, numeric data types such as
integer or floating point number, strings, text, etc. Casting - changing cell values
from one data type to another - can be accomplished by means of the server-side scripting
functions (i.e. VBScript's CStr() or CINT() functions).
* This feature is not available in
ExcelWriterSE.
Example 1: Data Types
Microsoft Excel supports many different types of data in a cell: integer and floating point numbers, text, date/time,
currency. The following example shows a broad range of different data types.
<%@ Language=VBScript %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
Dim xlw
Dim cells
set xlw = Server.CreateObject("SoftArtisans.ExcelWriter")
set cells = xlw.worksheets(1).cells
'--- Assign some values and styles
cells("A1").Value = "Type"
cells("B1").Value = "Value"
'--- Integer value
cells("A2").Value = "Integer"
cells("B2").value = 123
'--- Float value
cells("A3").Value = "Float"
cells("B3").value = 1.05e12
'--- String value
cells("A4").Value = "String"
cells("B4").value = "some text"
'--- Date value using VBScript's CDate() function
cells("A5").Value = "Date(ASP)"
cells("B5").value = CDate("1/1/2000")
'--- Large integer
cells("A8").Value = "Big integer"
cells("B8").Value = 999999999999999
'--- Currency
cells("A9").Value = "Currency"
cells("B9").Value = "$5000"
'--- Negative integer
cells("A10").Value = "Neg. Integer"
cells("B10").Value = "-2"
'--- Negative floating point
cells("A11").Value = "Neg. Float"
cells("B11").Value = "-2.5e-23"
'--- Open the spreadsheet in the browser
xlw.Save "value1.xls", saOpenInPlace
'--- Clean up
Set xlw = nothing
Response.end
%>Example2: Changing from One Type to Another
The previous example showed how to enter values with default interpretation by ExcelWriter. It is also
possible to explicitly convert one type into another - a process known as casting.
<%@ Language=VBScript %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
Dim xlw, cells
set xlw = Server.CreateObject("SoftArtisans.ExcelWriter")
set cells = xlw.worksheets(1).cells
cells("A1").Value = "Cast"
cells("B1").Value = "Value"
'--- You can use VBScript's casting functions
'--- to assign strings as integers and vice versa
cells("A2").Value = "String to Integer"
cells("B2").value = CInt("123")
cells("A3").Value = "Integer to String"
cells("B3").value = CStr(123)
'--- Use VBScript's CCur() to cast numbers to currency
cells("A4").Value = "Currency"
cells("B4").value = CCur(1000000.45)
'--- Open spreadsheet in the browser
xlw.Save "value2.xls", saOpenInPlace
'--- Clean Up
Set xlw = nothing
Response.end
%>
Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.
|
