/*--------------------------------------------------------------*/
---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