you can import xls like this (this is our import function). Of course, you should know where to put each column value from excel into the datawindow window. I think you do not need such a difficult solution, but there are basic recommendations:
int li_rtn, li_i string ls_range long ll_excel_rows, ll_max_rows, ll_max_columns long ll_i, ll_j string lsa_column_names[], ls_mod long lla_column_pos[] long ll_row int li_rc string las_truncated_data[] string ls_errorMessage oleobject lole_excel, lole_workbook, lole_worksheet TRY lole_excel = create oleobject li_rtn = lole_excel.ConnectToNewObject("excel.application") if li_rtn <> 0 then ls_errorMessage = "Error running MS Excel api" li_rtn = -1 else li_rtn = 1 lole_excel.WorkBooks.Open(as_filepath) lole_workbook = lole_excel.application.workbooks(1) int li_sheetnum IF IsNumber(as_sheet) THEN li_sheetnum = Integer(as_sheet) ELSE li_sheetnum = uof_pub_excel_get_sheet_byname(as_sheet, lole_workbook) END IF lole_worksheet = lole_workbook.worksheets(li_sheetnum) ll_max_rows = lole_worksheet.UsedRange.Rows.Count ll_max_columns = lole_worksheet.UsedRange.Columns.Count string lsa_tmp[] lsa_column_names = f_split(f_trim(as_imported_columns,";"), ";") FOR ll_i = 1 TO UpperBound(lsa_column_names) IF (pos(lsa_column_names[ll_i], ":")>0) THEN lsa_tmp = f_split(lsa_column_names[ll_i], ":") lla_column_pos[UpperBound(lla_column_pos)+1] = long(lsa_tmp[2]) lsa_column_names[ll_i] = lsa_tmp[1] END IF NEXT string ls_cellValue, ls_coltype, ls_value int li_idx_col, li_statrRow boolean lb_copyData = false int li_col_offset, li_vlen li_statrRow = ai_start_row IF (UpperBound(lla_column_pos)=0) THEN IF (UpperBound(lsa_column_names)<ll_max_columns) THEN ll_max_columns = UpperBound(lsa_column_names) FOR ll_j = li_statrRow TO ll_max_rows li_col_offset = 0 ll_row = adw_dest.insertRow(0) // insert new row FOR ll_i = 1 TO (ll_max_columns) ls_cellValue = String(lole_worksheet.cells(ll_j,ll_i).value) IF (lsa_column_names[(ll_i)] = "") THEN li_col_offset++ continue END IF // li_rc = adw_dest.SetItem(ll_row, lsa_column_names[(ll_i)], lole_worksheet.cells(ll_j, (ll_i)).value) ls_value = String(lole_worksheet.cells(ll_j, ll_i).value) ls_coltype = adw_dest.describe(lsa_column_names[(ll_i)]+'.ColType') // Checking length of string data IF (pos(ls_coltype, "char")>0) THEN li_vlen = integer(replace(left(ls_coltype, len(ls_coltype)-1), 1, 5, "")) IF (li_vlen > 0 AND len(ls_value)>li_vlen) THEN li_rtn = -2 ls_value = left(ls_value, li_vlen) IF (f_array_indexof(las_truncated_data, lsa_column_names[ll_i])<1) THEN las_truncated_data[UpperBound(las_truncated_data)+1] = lsa_column_names[ll_i] END IF END IF END IF li_rc = guo_common_utilities.uof_pub_set_dw_value(adw_dest, ll_row, lsa_column_names[(ll_i)], ls_value, ls_coltype) NEXT NEXT ELSE FOR ll_j = li_statrRow TO ll_max_rows ll_row = adw_dest.insertRow(0) // insert new row FOR ll_i = 1 TO UpperBound(lla_column_pos) ls_cellValue = String(lole_worksheet.cells(ll_j,lla_column_pos[ll_i]).value) adw_dest.SetItem(ll_row, lsa_column_names[ll_i], ls_cellValue) NEXT NEXT END IF end if CATCH ( runtimeerror lo_rte) li_rtn = -1 ls_errorMessage = "MS Excel api runtime error" FINALLY // Quit IF (IsValid(lole_excel)) THEN lole_excel.application.quit() lole_excel.DisconnectObject() END IF destroy lole_Excel destroy lole_workbook destroy lole_worksheet END TRY uo_nv_response luo_return luo_return = create uo_nv_response luo_return.ii_errorCode = li_rtn IF (UpperBound(las_truncated_data)>0) THEN luo_return.is_errorMessage = "Zeichenkette von " FOR li_i = 1 TO UpperBound(las_truncated_data) luo_return.is_errorMessage += las_truncated_data[li_i] + ", " NEXT luo_return.is_errorMessage = Left(luo_return.is_errorMessage, Len(luo_return.is_errorMessage)-2) luo_return.is_errorMessage += " wurde abgeschnitten" ELSE luo_return.is_errorMessage = ls_errorMessage END IF return luo_return
Here are some missing features:
global type f_array_indexof from function_object end type forward prototypes global function integer f_array_indexof (any aa_array[], any aa_element) end prototypes global function integer f_array_indexof (any aa_array[], any aa_element);int li_count FOR li_count = 1 TO UpperBound(aa_array) IF (lower(aa_array[li_count]) = lower(aa_element)) THEN return li_count END IF NEXT return -1 end function
f_split:
global type f_split from object_function final type
advanced prototypes global function boolean f_split (string as_str) global function any f_split (string as_str, string as_delimiter) end prototypes
global function any f_split (string as_str, string as_delimiter); long ll_pos1, ll_pos2, i String lsa_split []
ll_pos1 = Pos (as_str, as_delimiter) ll_pos2 = 0 i = 1
if (ll_pos1 = 0 and Len (as_str)> 0), then lsa_split [1] = as_str
do while ll_pos1> 0 lsa_split [i] = '' if (i = 1), then lsa_split [i] = mid (as_str, 1, ll_pos1 - 1) ll_pos2 = ll_pos1 still ll_pos2 = Pos (as_str, as_delimiter, ll_pos1 + 1 ) if (ll_pos2 = 0), then // this is the end :) lsa_split [i] = mid (as_str, ll_pos1 + 1, Len (as_str) - ll_pos1) another lsa_split [i] = mid (as_str, ll_pos1 + 1, ll_pos2 - ll_pos1 - 1) end, if end, if ll_pos1 = ll_pos2
i = i + 1 loop // lsa _split [i] = Right (as_str, Len (as_str) - ll_pos1)
return lsa_split final function
f_trim:
global type f_trim from object_function final type
advanced prototypes f_trim global function string (as_string string, ac_remove character) end prototypes
global function string f_trim (string as_string, ac_remove character), if (Isnull (as_string) or Isnull (ac_remove)), then return as_string
do while (left (as_string, 1) = ac_remove) as_string = right (as_string, Len (as_string) -1) loop
do while (right (as_string, 1) = ac_remove) as_string = left (as_string, Len (as_string) -1) loop
return as_string final function
public function integer uof_pub_excel_get_sheet_byname (string as_sheetname, oleobject aole_workbook); int li_sheets_count, li_i
li_sheets_count = aole_workbook.worksheets.count FOR li_i = 1 TO li_sheets_count IF (aole_workbook.worksheets (li_i) .name = as_sheetname) THEN return li_i NEXT
return 0 final function