CODEWITHSUNDEEP
exceloffice-interopaxaptax++
sohail.hussain.dyn
sohail.hussain.dyn

Reputation: 1531

Fast access to excel data in X++

Can someone give me a clue how can I get the fast access of the excel data. Currently the excel contains more than 200K records and when I retrieve from the X++ code it takes a lot of time to retrieve all the records.

Following are the classes I am using to retrieve the data. 1 - SysExcelApplication, SysExcelWorksheet and SysExcelCells.

I am using the below code to retrieve cells. 

excelApp.workbooks().open(filename);
excelWorksheet  = excelApp.worksheets().itemFromName(itemName);
excelCells      = excelWorkSheet.cells();
///pseudo code
loop
    excelCells.item(rowcounter, column1);
    similar for all columns;
end of loop

If any of the special property needs to be set here please tell me.

Upvotes: 1

Views: 3027

Answers (3)

j.a.estevan
j.a.estevan

Reputation: 3097

Overall performance will be a lot better (huge!) if you can use CSV files. If you are forced to use Excel files, you can easy and straigforward convert this excel file to a csv file and then read the csv file. If you can't work that way, you can read excel files throug ODBC (using a query string like connecting to a database) that will perform better that the Office API.

Upvotes: 2

alphaprolix
alphaprolix

Reputation: 601

use CSV, it is faster, below is code example:

     /* Excel Import*/
#AviFiles

#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
#endmacro

FilenameOpen    filename;

CommaIo         file;
Container       con;

/* File Open Dialog */
Dialog  dialog;
dialogField dialogFilename;
dialogField dialogSiteID;
dialogField dialogLocationId;
DialogButton dialogButton;
InventSite objInventSite;
InventLocation objInventLocation;
InventSiteID objInventSiteID;
InventLocationId objInventLocationID;
int row;
str sSite;
NoYes IsCountingFound;
int iQty;
Counter insertCounter;
Price itemPrice;
ItemId _itemid;
EcoResItemColorName _inventColorID;
EcoResItemSizeName _inventSizeID;


dialog              =   new Dialog("Please select file");
dialogSiteID        =   dialog.addField(extendedTypeStr(InventSiteId), objInventSiteId);
dialogLocationId    =   dialog.addField(extendedTypeStr(InventLocationId), objInventLocationId);
dialogFilename      =   dialog.addField(extendedTypeStr(FilenameOpen));

dialog.filenameLookupFilter(["@SYS100852","*.csv"]);
dialog.filenameLookupTitle("Please select file");
dialog.caption("Please select file");
dialogFilename.value(filename);

if(!dialog.run())
return;

objInventSiteID = dialogSiteID.value();
objInventLocationID = dialogLocationId.value();

/*----- validating warehouse*/
while
select maxof(InventSiteId) from objInventLocation where objInventLocation.InventLocationId == objInventLocationId
{
    If(objInventLocation.InventSiteID != objInventSiteID)
    {
        warning("Warehouse not belongs to site. Please select valid warehouse." ,"Counting lines import utility");
        return;
    }
}

filename  =   dialogFilename.value();
file = new commaIo(filename,'r');
file.inFieldDelimiter(',');

try
{

    if (file)
    {
        ttsbegin;
        while(file.status() == IO_Status::OK)
        {

           con = file.read();

            if (con)
            {
                row ++;

                if(row == 1)
                {
                    if(
                       strUpr(strLtrim(strRtrim( conpeek(con,1) ))) != "ITEM"
                    || strUpr(strLtrim(strRtrim( conpeek(con,2) ))) != "COLOR"
                    || strUpr(strLtrim(strRtrim( conpeek(con,3) ))) != "SIZE"
                    || strUpr(strLtrim(strRtrim( conpeek(con,4) ))) != "PRICE"
                    )
                    {
                        error("Imported file is not according to given format.");
                        ttsabort;
                        return;
                    }
                }
                else
                {


                    IsCountingFound = NoYes::No;
                    _itemid = "";
                    _inventColorID = "";
                    _inventSizeID = "";


                    _itemid = strLtrim(strRtrim(conpeek(con,1) ));
                    _inventColorID = strLtrim(strRtrim(conpeek(con,2) ));
                    _inventSizeID = strLtrim(strRtrim(conpeek(con,3) ));
                    itemPrice = any2real(strLtrim(strRtrim(conpeek(con,4) )));

                }
           }
         }

         if(row <= 1)
         {
            ttsabort;
            warning("No data found in excel file");
         }
         else
         {
            ttscommit;

         }
    }
 }
catch
{
    ttsabort;
    Error('Upload Failed');
}

Upvotes: 0

Jan B. Kjeldsen
Jan B. Kjeldsen

Reputation: 18051

First things, reading Excel files (and any other file) will take a while for 200 K records.

You can read an Excel file using ExcelIo, but with no performance guaranties :)

As I see it, you have 3 options (best performance listed first):

  1. Convert your Excel file to CSV file, then read with CommaIo.
  2. Read the Excel file using C#, then call back to X++
  3. Accept the fact and take the time

Upvotes: 0

Related Questions