Reading Excel files is another side of Excel
file manipulation. It is equally important as file creation. Usage
could vary from importing simple data to processing user-filled files
like timesheets, purchase orders, etc.
In this recipe, we will use the file created in the previous recipe. We will read customer data using SysExcel classes and will show it on the screen.
How to do it...
1. In AOT, create a new class called ReadExcelFile with the following code:
class ReadExcelFile
{
}
public static void main(Args args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
CustAccount account;
CustName name;
#define.Filename('<documents>\\customers.xlsx')
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
Excel fileExcel filereading, steps{
workbooks.open(#Filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
account = cells.item(row, 1).value().bStr();
name = cells.item(row, 2).value().bStr();
info(strfmt('%1 - %2', account, name));
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
2. Run the class and check the results displayed in the Infolog:
How it works...
In the variable declaration section in main(), replace<documents> with your own directory path and customers.xlsx with your file saved in the previous recipe. Do not forget to use double backslashes for folder separation, i.e. \\.
The first few lines of code in the main() method creates a new Excel application object application, opens first workbook, and gets a reference to the cell collection object cells. This is done very much in the same way as in the previous recipe.
Next, we use a do…while
loop until the first cell of the next row is empty. Inside the loop, we
read the customer account from the first and customer name from the
second cell of each row and output them on the screen using the info() function. The value() method of cells returns an object of type COMVariant. We know that both columns are formatted as text, so we use bStr() to get cell content.
Speaking about cell formats, the COMVariant
class is used for storing various types of data when dealing with
external objects. It could be of any type like string, integer, decimal,
etc. In most cases when we do not know what type of data to expect for
each cell, we may call variantType(), which returns a COMVariantType enumeration. This enumeration shows what kind of data is stored in the cell, and depending on the result, we may use bStr(), int(), float(), or other relevant methods of COMVariant.
Normally, a whole range of checks has to be performed to determine the
correct data type. A good example of such checks could be convertVariant2Str() of COSExcelImport or variant2Str() of GanttVcDataTableField in the standard application.
