将数据窗口的数据导出到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中 /*------------------------HDC----------------------------------*/ 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