ExcelWriterFull features the ability to get cell formats and values from
an existing Excel spreadsheet and paste them into the spreadsheet that is
being formatted on the fly server-side with Active Server Pages scripting.
To read from an existing Excel sheet, you will use the "Cells" object
method: "GetDataFromExcelSheet".
The "GetDataFromExcelSheet" method takes seven parameters. It returns
a "Range" object:
Range = cells.GetDataFromExcelSheet(FileName, [Source], [IncludeFormatting],[FirstRow],
[FirstCol], [MaxRows], [MaxCols])
- Filename: (required) The name of the source file, that is, the spreadsheet
from which ExcelWriter is reading.
- [Source]: (optional) Specifies the first cell in the source range of cells.
Source may include both sheet number and cell name (e.g. "Sheet2!$B$11"),
or a cell name alone (e.g. "B11"). If Source does not include a
sheet reference, ExcelWriter will read from the first sheet in the workbook.
Default: If you do not assign a Source value, ExcelWriter will start reading
at cell A1 in the first worksheet.
- [IncludeFormatting]: (optional) When set to True, ExcelWriter will copy
the original formatting with the data from the existing spreadsheet.
Default: True. Formatting will be copied.
- [FirstRow]: (optional) First row in the destination sheet.
Default: 1st row
- [FirstColumn]: (optional) First column in the destination sheet.
Default: 1st column
- [MaxRows]: (optional) Maximum number of rows to write in the destination
sheet.
Default: 16384 in Excel 95 (BIFF7) and 65536 in Excel 97/2000 (BIFF8)
- [MaxColumns]: (optional) Maximum number of columns to write in the destination
sheet.
Default: 256 (Please note: This limit is imposed by Microsoft's BIFF7 and
BIFF8 formats and not by ExcelWriter)
Here is a simple example ("read.asp") which gets data from an Excel
sheet ("source.xls"). It pastes this data into a new Excel sheet being
formatted server-side with ExcelWriter and then streams it to a client browser
for opening:
<%@ Language=VBScript %>
<!-- METADATA TYPE="TypeLib"
UUID="{7BCD2133-64A0-4770-843C-090637114583}"
-->
<%
'--- Create an instance of the
ExcelApplication object,
'--- representing your Excel workbook.
Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
'--- Create a worksheet in the
workbook.
Set ws = xlw.Worksheets(1)
'--- Assign an existing spreadsheet
to a variable.
'--- Source.xls is in ExcelWriter's
Doc-samples directory.
SourceFile = Server.MapPath(Application("vroot")
& "source.xls")
'--- Get data from source.xls,
sheet 2, starting at cell B3,
'--- with formatting, and copy
to cells B2:C5
'--- in the destination file.
Set ResultRange = ws.Cells.GetDataFromExcelSheet(SourceFile,
"Sheet2!$B$3",
True, 2, 2, 4, 2)
'--- Save the destination file.
xlw.Save "GetDataFromSS.xls",
saOpenInPlace
'--- Error handling.
ScriptName = Request.ServerVariables("Script_Name")
If Err.number <>
0 Then
Response.Status
= "500 Internal Server Error"
Response.Write
"<P><H2>Creation
of Excel Spreadsheet
Failed.<BR>"
& ScriptName & "</H2><BR>"
Response.Write
"Error : '" & Err.description & "'</P>"
Else
Response.Write
"<P><H2>Creation
of Excel Spreadsheet
Successful.<BR>"
& "New spreadsheet:
c:\GetDataFromSS.xls"
& "</H2><BR>"
End IF
Set xlw = Nothing
%> |
Note: "GetDataFromExcelSheet" currently
cannot retrieve formulas or more complex worksheet formats. These can be added
via scripting after the range of data has been pasted into the new excel file.
An upcoming version of Excelwriter will feature full retention of all worksheet
formatting. |
