Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Create a class firstly :
- // gl00mie, import data from Excel via ADO, 20061220 -->
- // How to use this class:
- // 1. Create class instance, specify filename and (optional) cursor type e.g.
- // doc = new ExcelImportADO(strFilename, #adOpenStatic);
- // 2. Read names of worksheets present in the file, suppress error messages if necessary
- // con = doc.getExcelSheetNames();
- // 3. Specify the name of the worksheet to open ADODB.Recordset on, otherwise the first one will be used
- // doc.excelSheetName(conpeek(con,2));
- // 4. Open the file, i.e. open ADODB.Recordset (and ADODB.Connection if it's not opened yet)
- // suppress error messages if necessary and check for result:
- // if(doc.openFile(false)) ...
- // 5. Check if there is a correct number of columns in the Recordset
- // if(doc.getFieldsCount() > 7) ...
- // 6. Get the total record count if #adOpenStatic cursor type is used
- // cnRecords = doc.getRecordCount();
- // 7. Read records in a loop, use indexes (1..n) or field names to identify fields
- // By default field values are returned as strings
- // int i;
- // real r;
- // str s;
- // while(!doc.eof())
- // {
- // i = doc.getFieldValue(1, false);
- // s = doc.getFieldValue('stringField');
- // r = doc.getFieldValue('numericField', false);
- // doc.moveNext();
- // }
- // 8. Cleanup - close ADODB.Recordset and ADODB.Connection
- // doc.finalize();
- class ExcelImportADO
- {
- COM cnnExcel, rstExcel, flds; // ADO: Connection, Recordset, Fields
- str strFileName, strSheetName;
- int nCursorType; // ADO: CursorTypeEnum
- container conSheetNames; // Excel Worksheets names
- // %1 - full file name
- #localmacro.ADODBExcelConnString
- "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
- #endmacro
- #CCADO
- }
- // gl00mie, import data from Excel via ADO, 20061220 <--
- // take a variant value recieved via ADO and transform it from
- // Excel-related types subset into a suitable type for Axapta
- protected anytype adoValue(COMVariant _val, int _type)
- {
- switch (_type)
- {
- // constants for types recognized by ADO for Excel
- case #adDouble: return _val.double();
- case #adCurrency: return _val.currency();
- case #adDate: return _val.date();
- case #adBoolean: return _val.boolean();
- case #adVarWChar,
- #adLongVarWChar: return _val.bStr();
- }
- return '';
- }
- // take a variant value recieved via ADO and transform it from
- // Excel-related types subset into a STRING value suitable for Axapta info-windows
- protected str adoValueStr(COMVariant _val, int _type, boolean bDblRound = false)
- {
- // if(_type == 5)
- // _type = 202;
- switch (_type)
- {
- case #adDouble: return num2str(_val.double(),-1,(bDblRound ? 0 : -1),1,0);
- case #adCurrency: return num2str(_val.currency(),-1,-1,1,0);
- case #adDate: return date2str(_val.date(),123,2,2,2,2,4);
- case #adBoolean: return int2str(_val.boolean());
- case #adVarWChar,
- #adLongVarWChar: return _val.bStr();
- }
- return '';
- }
- // return Recordset.EOF()
- boolean eof()
- {
- return (rstExcel ? rstExcel.EOF() : true);
- }
- // get Excel book first sheet's name or previously
- // set sheet name to use as a table name in select
- str excelSheetName(str _name = '')
- {
- if(!prmisdefault(_name))
- strSheetName = _name;
- else
- if(!strSheetName) // have we been already called successfully?
- strSheetName = conpeek(this.getExcelSheetNames(), 1);
- return strSheetName;
- }
- // close ADO RecordSet and Connection
- public void finalize()
- {
- // RecordSet is closed automatically upon Connection closure
- if (cnnExcel && cnnExcel.State() != #adStateClosed)
- cnnExcel.Close();
- }
- // return ADO Connection object
- // create and open the connection if necessary
- public COM getConnection()
- {
- if(!cnnExcel)
- {
- cnnExcel = new COM(@"ADODB.Connection");
- cnnExcel.connectionString(#ADODBExcelConnString(strFileName));
- cnnExcel.Open();
- }
- return cnnExcel;
- }
- // get Excel worksheet names
- container getExcelSheetNames(boolean _suppressErrors = false)
- {
- COM sheets, sheet;
- COM adoxCatalog = new COM(@"ADOX.Catalog");
- int cnSheets, i;
- int nLines;
- str strSheet;
- ;
- if(conSheetNames)
- return conSheetNames;
- try
- {
- nLines = infolog.line(); // save current infolog level
- adoxCatalog.ActiveConnection(this.getConnection());
- sheets = adoxCatalog.Tables();
- cnSheets = sheets.count();
- for(i=0; i < cnSheets; i++)
- {
- sheet = sheets.item(i);
- strSheet = sheet.name();
- // strip '$' and anything beyond it (in case there are autofilters in the workbook)
- // also strip "'" for quoted names
- strSheet = strrem(substr(strSheet, 1, strscan(strSheet, '$', 1, strlen(strSheet))-1),'\'');
- if(!confind(conSheetNames, strSheet))
- conSheetNames += strSheet;
- }
- }
- catch (Exception::Error)
- {
- if (cnnExcel && cnnExcel.State() != #adStateClosed)
- cnnExcel.Close();
- cnnExcel = null; // release ADODB.Connection object
- if (_suppressErrors)
- infolog.clear(nLines); // delete infolog messages generated in try block
- else
- error("@SYS59927");
- }
- return conSheetNames;
- }
- // return Recordset.Fields.Count
- int getFieldsCount()
- {
- return (flds ? flds.Count() : -1);
- }
- // read _fldId-th field from the current ADO RecordSet
- // and transform it into a type suitable for Axapta.
- // _fldId can be an int index starting 1 or a column name
- anytype getFieldValue(anytype _fldId, boolean _asString = true)
- {
- COM fld; // ADO: Field
- ;
- setprefix(strfmt(@"%1::%2()", classId2Name(classIdGet(this)), funcname()));
- if(!flds)
- throw error(strfmt('%1 %2', "@SYS68420", @"Fields"));
- if(typeof(_fldId) == Types::Integer)
- _fldId--; // ADO uses indexes starting 0, not 1
- fld = flds.Item(_fldId);
- return (_asString ? this.adoValueStr(fld.Value(), fld.Type(), true) :
- this.adoValue (fld.Value(), fld.Type()));
- }
- // return record count for the opened RecordSet
- // not valid for forward-only cursor or closed recordset
- // MAN
- // If the Recordset object supports approximate positioning or bookmarks - that is,
- // Supports(adApproxPosition) or Supports(adBookmark), respectively, return True
- // this value will be the exact number of records in the Recordset, regardless of
- // whether it has been fully populated. If the Recordset object does not support
- // approximate positioning, this property may be a significant drain on resources
- // because all records will have to be retrieved and counted to return an accurate
- // RecordCount value.
- // NOTE: In ADO versions 2.8 and earlier, the SQLOLEDB provider fetches all records
- // when a server-side cursor is used despite the fact that it returns True for both
- // Supports (adApproxPosition) and Supports (adBookmark).
- int getRecordCount()
- {
- if(rstExcel && rstExcel.State() != #adStateClosed)
- return rstExcel.RecordCount();
- return -1;
- }
- // return ADO Recordset object
- public COM getRecordset()
- {
- return rstExcel;
- }
- // call Recordset.MoveNext()
- void moveNext()
- {
- if (rstExcel)
- rstExcel.MoveNext();
- }
- public void new(str _fileName, int _cursorType = #adOpenForwardOnly)
- {
- rstExcel = null;
- cnnExcel = null;
- strFileName = _fileName;
- nCursorType = _cursorType; // use #adOpenStatic to be able to read RecordCount
- }
- // try to open a sheet from an Excel file which name
- // has been passed to new() or got by excelSheetName()
- boolean openFile(boolean _suppressErrors = false)
- {
- boolean ok = false;
- int nLines;
- ;
- nLines = infolog.line(); // save current infolog level
- try
- {
- if(this.excelSheetName() != '')
- {
- rstExcel = new COM(@"ADODB.Recordset");
- rstExcel.Open(@"SELECT * FROM [" + strSheetName + @"$]", this.getConnection(), nCursorType);
- flds = rstExcel.Fields();
- ok = true;
- }
- }
- catch (Exception::Error)
- {
- if (cnnExcel && cnnExcel.State() != #adStateClosed)
- cnnExcel.Close();
- if (_suppressErrors)
- infolog.clear(nLines); // delete infolog messages generated in try block
- }
- return ok;
- }
- Job that will import data from excel file :
- static void Import_Data(Args _args)
- {
- ExcelImportADO excelimport;
- ;
- excelimport = new ExcelImportADO('D:\\ImportData\\Student.xls');
- excelimport.openFile(false);
- while (!excelimport.eof())
- {
- info(excelimport.getFieldValue(2));
- excelimport.moveNext();
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement