如何将Excel文件导入DataWindow [英] How to import Excel file into DataWindow

问题描述

我想将.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_object end type

转发原型
全局函数boolean f_split（string as_str）
全局函数any f_split（字符串as_str，字符串as_delimiter）
最终原型

forward prototypes global 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, i String lsa_split[]

ll_pos1 = Pos（as_str，as_delimiter）
ll_pos2 = 0
i = 1

ll_pos1 = Pos(as_str, as_delimiter) ll_pos2 = 0 i = 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 + 1 loop //lsa_split[i] = Right( as_str, Len(as_str) - ll_pos1 )

返回lsa_split
结束函数

return lsa_split end function

f_trim：

全局类型f_trim from function_object
最终类型

global type f_trim from function_object end type

转发原型
全局函数字符串f_trim（字符串as_string，字符ac_remove）
最终原型

forward prototypes global 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_string end 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.count FOR li_i = 1 TO li_sheets_count IF (aole_workbook.worksheets(li_i).name = as_sheetname) THEN return li_i NEXT

返回0
结束函数

return 0 end function

这篇关于如何将Excel文件导入DataWindow的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！