你可以像这样导入xls(这是我们的导入功能)。当然,您必须知道将 Excel 中的每列值放置到数据窗口的位置。我认为您不需要如此复杂的解决方案,但有以下主要准则:
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
以下是一些缺失的功能:
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:
来自 function_object 的全局类型 f_split
端部类型
正向原型
全局函数布尔f_split(字符串as_str)
全局函数any f_split(字符串as_str,字符串as_delimiter)
最终原型
全局函数any f_split (string as_str, string as_delimiter);long ll_pos1, ll_pos2, i
字符串 lsa_split[]
ll_pos1 = Pos(as_str, as_delimiter)
ll_pos2 = 0
我 = 1
if (ll_pos1 = 0 且 Len(as_str)>0) 则 lsa_split[1] = as_str
当 ll_pos1 > 0 时执行
lsa_split[i] = ''
如果( i = 1 ) 那么
lsa_split[i] = mid (as_str, 1, ll_pos1 - 1)
ll_pos2 = ll_pos1
别的
ll_pos2 = Pos (as_str, as_delimiter, ll_pos1 + 1)
if( ll_pos2 = 0 )then // 结束了:)
lsa_split[i] = mid (as_str, ll_pos1 + 1, Len(as_str) - ll_pos1 )
别的
lsa_split[i] = mid (as_str, ll_pos1 + 1, ll_pos2 - ll_pos1 - 1)
万一
万一
ll_pos1 = ll_pos2
我=我+1
环形
//lsa_split[i] = Right( as_str, Len(as_str) - ll_pos1 )
返回 lsa_split
结束函数
f_trim:
来自 function_object 的全局类型 f_trim
端部类型
正向原型
全局函数字符串f_trim(字符串as_string,字符ac_remove)
最终原型
全局函数 string f_trim (string as_string, 字符 ac_remove);if (Isnull(as_string) 或 Isnull(ac_remove)) then return as_string
执行 while (left(as_string, 1) = ac_remove)
as_string = 右(as_string, Len(as_string)-1)
环形
执行 while (right(as_string, 1) = ac_remove)
as_string = 左(as_string, Len(as_string)-1)
环形
返回 as_string
结束函数
公共函数整数 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 返回 li_i
下一个
返回0
结束函数