将資料視窗的資料導出到EXCEL

将資料視窗的資料導出到EXCEL 2008年12月07日 星期日 16:16

---FUNCTION:integer wf_export(datawindow adw ,ref string as_msg) ---ARGS:   DataWindow adw            reference String as_msg ---RETURN: INTEGER      1    導出資料成功！      -1    沒有另存為的内容！      -2    檔案内容超過了Excel的65536行的範圍      -3    檔案内容超過了Excel的256列的範圍      -4    行資料，超出Excel的儲存範圍！      -5    Excel工作異常!      -6    打開檔案有誤！      -9    儲存為dbase3失敗！      -10   使用者取消操作！      -100   資料視窗對象無效！ ---DES:   将資料視窗中的資料導出到EXCEL中 String    ls_FileName,ls_Named,ls_ColumnName,ls_String,ls_Temp,ls_ftemp Long      ll_Row,ll_Rtn = 0,ll_Handle,i,ll_RowCount OleObject ole_Object If NOt isvalid(adw) Then as_msg = '對象無效！' Return -100 END IF ll_RowCount = adw.RowCount() If ll_RowCount < 1 Then as_msg = "沒有另存為的内容！" Return -1 End If if ll_RowCount > 65536 then as_msg = "檔案内容超過了Excel的65536行的範圍." return -2 end if if long(adw.Object.DataWindow.Column.Count) > 256 then as_msg = "檔案内容超過了Excel的256列的範圍." return -3 end if do while ll_Rtn = 0 ll_Rtn = GetFileSaveName("Select File",ls_ftemp,ls_Named, "Excel",+ &     "Excel Files (*.xls),*.xls,"+ "Text Files (*.txt),*.txt," + "Excel Files (*.csv),*.csv") if ll_rtn = 0 then    as_msg = '使用者取消操作！'    return -10 END IF IF FileExists(ls_ftemp) THEN    IF MessageBox("儲存",'檔案' + ls_ftemp + "已經存在，覆寫該檔案嗎？",Question!,YesNo!,2) = 1 THEN     filedelete(ls_ftemp)    else     ll_rtn = 0    end if end if loop SetPointer(HourGlass!) //If ll_Rtn <> 1 Then Return If Lower(Right(ls_FileName,3)) <> 'txt' Then If ll_RowCount > 65536 Then    as_msg = "共有" + string(ll_RowCount) + "行資料，超出Excel的儲存範圍！"    Return -4 End If ls_FileName = left(ls_ftemp,len(ls_ftemp) - 4) + 'temp.DBF' ll_Rtn = adw.saveas(ls_FileName,dBASE3!,True) If ll_Rtn <> 1 Then    as_msg = '儲存為dbase3失敗！'    Return -9 END IF //建立Excel一個應用 Ole_Object = Create OleObject ll_Handle = Ole_Object.ConnectToObject('Excel.application') If ll_Handle <> 0 Then    ll_Handle = Ole_Object.ConnectToNewObject('Excel.application')    If ll_Handle <> 0 Then     If IsValid(Ole_Object) Then Destroy Ole_Object     as_msg = "請檢查您的Excel是否工作正常!"     Return -5    END IF End If //打開一個Excel檔案 Ole_Object.application.WorkBooks.Open(ls_FileName) //替換頭 For ll_Row = 1 To long(adw.Object.DataWindow.Column.Count)    ls_ColumnName = adw.Describe('#' + string(ll_Row) + '.name') + '_t'    Ole_Object.ActiveSheet.Cells(1,ll_Row).Value = adw.describe(ls_ColumnName + '.text') Next //儲存檔案 For ll_Row = 1 To long(adw.Object.DataWindow.Column.Count)    ole_object.ActiveSheet.Range(String(ll_Row) +":" + String(ll_Row)).EntireColumn.AutoFit //   IF left(adw.Describe('#' + string(ll_Row) + '.coltype'),8) = 'decimal(' THEN //    if long(mid(left(adw.Describe('#' + string(ll_Row) + '.coltype'),len(adw.Describe('#' + string(ll_Row) + '.coltype')) - 1),9)) >=2 then //     Ole_Object.ActiveSheet.range(Ole_Object.ActiveSheet.cells(2,ll_Row),Ole_Object.ActiveSheet.cells(adw.rowcount() + 1,ll_row)).NumberFormatLocal = "0.00_ " //    end if //   End IF    choose case trim(Lower(left(adw.Describe('#' + string(ll_Row) + '.coltype'),8)))     case 'decimal('      if long(mid(left(adw.Describe('#' + string(ll_Row) + '.coltype'),len(adw.Describe('#' + string(ll_Row) + '.coltype')) - 1),9)) >=2 then       Ole_Object.ActiveSheet.range(Ole_Object.ActiveSheet.cells(2,ll_Row),Ole_Object.ActiveSheet.cells(adw.rowcount() + 1,ll_row)).NumberFormatLocal = "0.00_ "      end if     case 'real','number'      Ole_Object.ActiveSheet.range(Ole_Object.ActiveSheet.cells(2,ll_Row),Ole_Object.ActiveSheet.cells(adw.rowcount() + 1,ll_row)).NumberFormatLocal = "0.00_ "    end choose Next choose case right(ls_ftemp,4)    case '.xls'     Ole_Object.application.workbooks(1).SaveAs(ls_ftemp,1)    case '.csv'     Ole_Object.application.workbooks(1).SaveAs(ls_ftemp,6)    case else     Ole_Object.application.workbooks(1).SaveAs(ls_ftemp) end choose Ole_Object.application.workbooks(1).Saved = True //關閉檔案和應用 Ole_Object.application.WorkBooks.close //斷開連接配接 Ole_Object.DisConnectObject() If IsValid(Ole_Object) Then Destroy Ole_Object filedelete(ls_FileName) Else ll_Handle = FileOpen(ls_FileName,LineMode!,Write!,LockWrite!,Append!) If ll_Rtn < 0 Then    as_msg = "打開檔案有誤！"    Return -6 End If ll_Rtn = FileRead(ll_Handle,ls_String) For ll_Row = 1 To long(adw.Object.DataWindow.Column.Count)    ls_ColumnName = adw.Describe('#' + string(ll_Row) + '.name') + '_t'    If ll_Row = long(adw.Object.DataWindow.Column.Count) Then     ls_String += adw.describe(ls_ColumnName + '.text')    Else     ls_String += adw.describe(ls_ColumnName + '.text') + '~t'    End If Next FileWrite(ll_Handle,ls_String) For i = 1 To adw.RowCount()    ls_String = ''    For ll_Row = 1 To long(adw.Object.DataWindow.Column.Count)     ls_temp = String(adw.Object.Data[i,ll_Row])     If isnull(ls_temp) Then ls_temp = 'null'     If ll_Row = long(adw.Object.DataWindow.Column.Count) Then      ls_String += ls_temp     Else      ls_String += ls_temp + '~t'     End If    Next    FileWrite(ll_Handle,ls_String) Next FileClose(ll_Handle) End If as_msg = "導出資料完成！" Return 1