How to get last SysExcelWorksheet object row or column[X++]
findLastColumn
int findLastColumn(SysExcelWorksheet _sysExcelWorksheet, boolean _data = true) { #Excel #define.Star('*') SysExcelRange sysExcelRange; int ret; ; sysExcelRange = _data ? _sysExcelWorksheet.cells().range(#ExcelDataRange) : _sysExcelWorksheet.cells().range(#ExcelTotalRange); try { sysExcelRange = sysExcelRange.find(#Star, null, #xlFormulas, #xlWhole, #xlByColumns, #xlPrevious); } catch (Exception::Error) { error("@SYS59926"); } if (sysExcelRange) ret = sysExcelRange.column(); return ret; }
findLastRow
int findLastRow(SysExcelWorksheet _sysExcelWorksheet) { #Excel #define.Star('*') #define.ExcelDataRange("A1:IV65536") SysExcelRange sysExcelRange = _sysExcelWorksheet.cells().range(#ExcelDataRange); int ret; ; try { sysExcelRange = sysExcelRange.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious); } catch (Exception::Error) { error("@SYS59926"); } if (sysExcelRange) ret = sysExcelRange.row(); return ret; }
come on, look at an example
void THK_7519_readExcelFile() { #AviFiles #WinAPI SysExcelApplication application = SysExcelApplication::construct(); SysExcelWorkbooks workbooks = application.workbooks(); SysExcelWorkbook workbook; SysExcelWorksheets worksheets; SysExcelWorksheet worksheet; SysExcelCells cells; COMVariantType type; SysOperationProgress progressBar; Test newTest; FileNameFilter filter = ['All files','*.xls;*.xlsx']; str code, address, precent; Filename fileName; int records,row = 1; ; filename = Winapi::getOpenFileName(0, filter, WinAPI::getFolderPath(#CSIDL_Personal), "Select file for upload", '',''); if (!winAPI::fileExists(filename,false)) return; if (!Box::yesNo(strfmt("Upload this file %1", filename), dialogButton::No)) return; try { workbooks.open(fileName); workbook = workbooks.item(1); worksheets = workbook.worksheets(); worksheet = worksheets.itemFromNum(1); cells = worksheet.cells(); records = this.findLastRow(worksheet);// 2190; progressBar = SysOperationProgress::newGeneral(#AviUpdate, "import data", records); progressBar.setTextLength(80); setPrefix(filename); ttsBegin; delete_from newTest; do { row++; setPrefix(strFmt("Row %1", row)); Code = strLRTrim(cells.item(row, 1).value().bStr()); address = strLRTrim(cells.item(row, 2).value().bStr()); newTest.clear(); newTest.initValue(); newTest.AccountNum = Code; newTest.Name01 = address; newTest.LineNum = row; if (!newTest.validateWrite()) throw Exception::Error; newTest.insert(); progressBar.incCount(1); progressBar.setText(strfmt("Line: %1 - %2[%3 %4]", newTest.LineNum, newTest.AccountNum, newTest.Name01, newTest.Name02)); progressBar.setCaption(strFmt("Process %1%", row / records * 100)); progressBar.update(true); type = cells.item(row + 1, 1).value().variantType(); } while (type != COMVariantType::VT_EMPTY); ttsCommit; } catch (Exception::Error) { application.quit(); } application.quit(); progressBar.finalize(); progressBar = null; Test_ds.executeQuery(); info( strfmt("total read %1 records", int2str(row)) ); }