Reading an Existing Spreadsheet**This feature is not available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree. |
 |
GetDataFromExcelSheet
With Cells.GetDataFromExcelSheet you can read data from an existing Excel spreadsheet and copy it to another spreadsheet. GetDataFromExcelSheet can be called more than once in the process of creating a new spreadsheet. GetDataFromExcelSheet can only retrieve cell values. To retrieve charts, formulas, macros, and VBA, use ExcelTemplate or ExcelApplication.Open.
GetDataFromExcelSheet takes seven parameters, and returns a range
object:
Range = cells.GetDataFromExcelSheet(FileName, [Source], [IncludeFormatting] [FirstRow], [FirstCol], [MaxRows], [MaxCols])
Once the range has been imported into the new worksheet, it can be accessed via server side scripting. This enables setting formulas or charts that reference imported cell values, updating a database via ADO code that parses out the data, and adding or modifying formatting.
| Parameter | Definition | Default Value |
|---|---|---|
| FileName | 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. | "" 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. | True |
| [FirstRow] | Optional. First row in the destination sheet. | 1 |
| [FirstColumn] | Optional. First column in the destination sheet. | 1 |
| [MaxRows] | Optional. Maximum number of rows to write in the destination sheet. | 16384 in Excel 95 (BIFF7) 65536 in Excel 97/2000 (BIFF8) |
| [MaxColumns] | Optional. Maximum number of columns to write in the destination sheet. | 256 (Limit imposed by Microsoft's BIFF7 and BIFF8 formats, not by ExcelWriter) |
How to Read from a Spreadsheet with GetDataFromExcelSheet
To get data from an existing Excel spreadsheet,
- Use
GetDataFromExcelSheetto specify the source file, and, optionally, the first source cell, the first destination cell, and the maximum numbers of rows and columns to copy.
Example: To get data from C:\spreadsheets\workbook1.xls, sheet 2, starting at cell B3, without formatting, and copy to cells B3:F12 in the destination file, use,
Set ResultRange = ws.Cells.GetDataFromExcelSheet("C:\spreadsheets\workbook1.xls", "Sheet2!$B$3", False, 3, 2, 10, 5)
- Save the destination file.
Example:
xlw.Save "c:\workbook2.xls"
Importing Formulas
Formulas contained in a cell that is imported as part of the range object can be copied. However, the cell values that are the result of the execution of the formula will not be available on the server. This means, for example, that if the requirement is to obtain the "sum" of a certain set of cells server-side for reference in an ADO update to a database, the true value will not be accessible and the cell value for the cell containing the formula will read as "0".
Example: Getting Data from an Existing Excel Spreadsheet
The following script gets data from the sheet source.xls, and stream back to browser. Source.xls is included in ExcelWriter's Doc-samples directory.
Copyright © 2003, SoftArtisans, Inc.