问题描述
我想将.xlsx文件导入PowerBuilder DataWindow，我知道我们可以使用CSV格式，但是用户希望使用xlsx格式，请告诉我是否有任何导入选项。 / p>
I want to import the .xlsx file into PowerBuilder DataWindow, I know we can able to do it with CSV format, but the user wants to use the xlsx format, Please let me know if there are any options to import.
推荐答案
您可以像这样导入xls（这是我们的导入功能）。当然，您必须知道将各列值从excel放置到数据窗口的位置。我认为您不需要这种复杂的解决方案，但是有主要指导原则：
you can import xls like this (this is our import function). Of course you have to know where to put each columns value from the excel to the datawindow. I think you do not need such a sophisticated solution, but there are the main guidelines:
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 functions:
来自function_object的全局类型f_array_indexof
最终类型
正向原型
全局函数整数f_array_indexof（任何aa_array []，任何aa_element）
最终原型
全局函数整数f_array_indexof（任何aa_array []，任何aa_element）; int li_count
FOR li_count = 1 TO UpperBound（aa_array）
IF（lower（aa_array [li_count ]）= lower（aa_element））然后
返回li_count
结束，如果
NEXT
返回-1
最终函数
f_split：
全局类型f_split from function_object
最终类型
global type f_split from function_objectend type
转发原型
全局函数boolean f_split（string as_str）
全局函数any f_split（字符串as_str，字符串as_delimiter）
最终原型
forward prototypesglobal function boolean f_split (string as_str)global function any f_split (string as_str, string as_delimiter)end prototypes
全局函数any f_split（字符串as_str，字符串as_delimiter）；长ll_pos1 ，ll_pos2，i
字符串lsa_split []
global function any f_split (string as_str, string as_delimiter);long ll_pos1, ll_pos2, iString lsa_split[]
ll_pos1 = Pos（as_str，as_delimiter）
ll_pos2 = 0
i = 1
ll_pos1 = Pos(as_str, as_delimiter)ll_pos2 = 0i = 1
如果（ll_pos1 = 0且Len（as_str）> 0），则lsa_split [1] = as_str
if (ll_pos1 = 0 and Len(as_str)>0) then lsa_split[1] = as_str
ll_pos1> 0
lsa_split [i] =''
if（i = 1）然后
lsa_split [i] =中（as_str，1，ll_pos1-1）
ll_pos2 = ll_pos1
else
ll_pos2 = Pos（as_str，as_delimiter，ll_pos1 + 1）
if（ll_pos2 = 0）然后//结束：)
lsa_split [i] =中（as_str，ll_pos1 + 1，Len（as_str）-ll_pos1）
else
lsa_split [i] =中（as_str，ll_pos1 + 1，ll_pos2-ll_pos1-1-）
如果$ b结尾如果
ll_pos1 = ll_pos2
i = i + 1
循环
，则$ b结尾// lsa_split [i] =右（as_str，Len（as_str）-ll_pos1）
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 else ll_pos2 = Pos (as_str, as_delimiter, ll_pos1 + 1) if( ll_pos2 = 0 )then // it's the end :) lsa_split[i] = mid (as_str, ll_pos1 + 1, Len(as_str) - ll_pos1 ) else lsa_split[i] = mid (as_str, ll_pos1 + 1, ll_pos2 - ll_pos1 - 1) end if end if ll_pos1 = ll_pos2
i = i + 1loop//lsa_split[i] = Right( as_str, Len(as_str) - ll_pos1 )
返回lsa_split
结束函数
return lsa_splitend function
f_trim：
全局类型f_trim from function_object
最终类型
global type f_trim from function_objectend type
转发原型
全局函数字符串f_trim（字符串as_string，字符ac_remove）
最终原型
forward prototypesglobal function string f_trim (string as_string, character ac_remove)end prototypes
全局函数字符串f_trim（字符串as_string，字符ac_remove）;如果（Isnull（as_string）或Isnull（ac_remove））然后返回as_string
global function string f_trim (string as_string, character ac_remove);if (Isnull(as_string) or Isnull(ac_remove)) then return as_string
执行while（left（as_string，1）= ac_remove）
as_string = right（as_string，Len（as_string）-1）
循环
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）
循环
do while (right(as_string, 1) = ac_remove) as_string = left(as_string, Len(as_string)-1)loop
返回as_string
结束函数
return as_stringend function
公共函数整数uof_pub_excel_get_sheet_byname（字符串as_sheetname，oleobject aole_workbook）; int li_sheets_count，li_i
public function integer uof_pub_excel_get_sheet_byname (string as_sheetname, oleobject aole_workbook);int li_sheets_count, li_i
li_ sheet_count = aole_workbook.worksheets.count
FOR li_i = 1 TO li_sheets_count
如果（aole_workbook.worksheets（li_i）.name = as_sheetname）然后返回li_i
下一步
li_sheets_count = aole_workbook.worksheets.countFOR li_i = 1 TO li_sheets_count IF (aole_workbook.worksheets(li_i).name = as_sheetname) THEN return li_iNEXT
返回0
结束函数
return 0end function
这篇关于如何将Excel文件导入DataWindow的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！