Engineer / Solution Architect / Dynamics 365-AX Technical Consultant / Instructor (MCT)

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
{
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.

Source

Microtsoft Dynamics AX 2009 Development Cookbook