powerbuilder - How to import Excel file into DataWindow -
i want import .xlsx file powerbuilder datawindow, know can able csv format, user wants use xlsx format, please let me know if there options import.
you can import xls (this our import function). of course have know put each columns value excel datawindow. think not need such sophisticated solution, there 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 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) 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,";"), ";") ll_i = 1 upperbound(lsa_column_names) if (pos(lsa_column_names[ll_i], ":")>0) 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) if (upperbound(lsa_column_names)<ll_max_columns) ll_max_columns = upperbound(lsa_column_names) ll_j = li_statrrow ll_max_rows li_col_offset = 0 ll_row = adw_dest.insertrow(0) // insert new row ll_i = 1 (ll_max_columns) ls_cellvalue = string(lole_worksheet.cells(ll_j,ll_i).value) if (lsa_column_names[(ll_i)] = "") 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) li_vlen = integer(replace(left(ls_coltype, len(ls_coltype)-1), 1, 5, "")) if (li_vlen > 0 , len(ls_value)>li_vlen) li_rtn = -2 ls_value = left(ls_value, li_vlen) if (f_array_indexof(las_truncated_data, lsa_column_names[ll_i])<1) 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 ll_j = li_statrrow ll_max_rows ll_row = adw_dest.insertrow(0) // insert new row ll_i = 1 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" // quit if (isvalid(lole_excel)) 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) luo_return.is_errormessage = "zeichenkette von " li_i = 1 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 missing functions:
global type f_array_indexof function_object end type forward prototypes global function integer f_array_indexof (any aa_array[], aa_element) end prototypes global function integer f_array_indexof (any aa_array[], aa_element);int li_count li_count = 1 upperbound(aa_array) if (lower(aa_array[li_count]) = lower(aa_element)) return li_count end if next return -1 end function
f_split:
global type f_split function_object end type
forward prototypes global function boolean f_split (string as_str) global function f_split (string as_str, string as_delimiter) end prototypes
global function f_split (string as_str, string as_delimiter);long ll_pos1, ll_pos2, string 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
do while ll_pos1 > 0 lsa_split[i] = '' if( = 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 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
= + 1 loop //lsa_split[i] = right( as_str, len(as_str) - ll_pos1 )
return lsa_split end function
f_trim:
global type f_trim function_object end type
forward prototypes global function string f_trim (string as_string, character ac_remove) end prototypes
global function string f_trim (string as_string, character ac_remove);if (isnull(as_string) or isnull(ac_remove)) 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 end 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 li_i = 1 li_sheets_count if (aole_workbook.worksheets(li_i).name = as_sheetname) return li_i next
return 0 end function
Comments
Post a Comment