PB-数据窗口数据导出到word文件中

PB数据窗口数据导出到word,excel博客分类：powerbuilderExcelJ#WindowsPB数据窗口数据导出到word,excel,excel可以自定义修改excel文档的列宽对齐方式,字体样式等//dwsave2word调用oleglobaltypegf_exportdata2wordfromfunction_objectendtypeforwardprototypesglobalsubroutinegf_exportdata2word(datawindowad_datawindow)endprototypesglobalsubroutinegf_exportdata2word(datawindowad_datawindow);stringls_filepathls_filepath=gf_getfilesavename()OleObjectOleObjectWordOleObjectWord=CreateOleObject//连接wordifOleObjectWord.ConnectToNewObject("Word.application")<>0thenMessagebox("提示","ole连接错误！")returnendifOleObjectWord.visible=false//word文档在操作数据过程中是否可见Longcol_colnum,col_rownumConstantLongwdTableBehavior=1ConstantLongwdAutoFitFixed=0ConstantLongwdCell=12Stringstr_value//得到数据窗口数据的列数和行数（行数应该是数据行数＋1）col_colnum=Long(ad_datawindow.object.datawindow.column.count)col_rownum=ad_datawindow.rowcount()+1//先在word文档中画好表格SetPointer(HourGlass!)OleObjectWord.Documents.AddOleObjectWord.ActiveDocument.Tables.Add(OleObjectWord.Selection.Range,&+col_rownum,col_colnum,wdTableBehavior,wdAutoFitFixed)stringls_colnameintegeri,j,k,lfori=1tocol_colnum//得到标题头的名称ls_colname=ad_datawindow.Describe('#'+string(i)+".name")+"_t"strvalue=ad_datawindow.DEscribe(ls_colname+".text")OleObjectWord.Selection.TypeText(str_value)OleObjectWord.Selection.MoveRight(wdCell)Nextad_datawindow.setRedraw(false)OleObjectWord.Selection.MoveLeft(wdCell)SetPointer(HourGlass!)fori=2tocol_rownumforj=1tocol_colnumad_datawindow.Scrolltorow(i-1)ad_datawindow.SetColumn(j)strvalue=ad_datawindow.GetItemstring(i-1,j)ifisnull(str_value)thenstrvalue=''endifOleObjectWord.Selection.MoveRight(wdCell)OleObjectWord.Selection.TypeText(str_value)nextnextad_datawindow.setredraw(true)ConstantlongwdFormatDocument=0//保存新建的文档OleObjectWord.ActiveDocument.SaveAs(ls_filepath,0,false,"",true,"",false,false,false,false,false)booleanlb_existlb_exist=FileExists(ls_filepath)iflb_existthenmessagebox("提示","数据已经保存到"+ls_filepath)endif//断开ole连接OleObjectWord.DisconnectObject()destroyOleObjectWordendsubroutine//dwsave2xls调用oleglobaltypegf_dwsavetoexcelfromfunction_objectendtypeforwardprototypesglobalfunctionintegergf_dwsavetoexcel(datawindowadw)endprototypesglobalfunctionintegergf_dwsavetoexcel(datawindowadw);stringxlsname,namedintegervaluestringcol_del,first_delvalue=GetFileSaveName("另存为",xlsname,named,"XLS","XlsFiles(*.XLS),*.XLS")ifvalue=1thenadw.saveas(xlsname,Excel!,TRUE)elsereturn2endifconstantintegerppLayoutBlank=12OLEObjectole_objectole_object=CREATEOLEObjectintegerli_retli_ret=ole_object.ConnectToObject("","Excel.Application")IFli_ret<>0THENli_ret=ole_object.ConnectToNewObject("Excel.Application")IFli_ret<>0THENmessagebox("OLE错误","OLE无法连接!~r~n错误号:"+string(li_ret))Return0ENDIFole_object.visible=FalseENDIFpointeroldpointeroldpointer=SetPointer(HourGlass!)ole_object.Workbooks.open(xlsname)ole_object.WorkSheets[1].Activatelongcolumncount,rowscountcolumncount=long(adw.object.datawindow.column.count)rowscount=adw.rowcount()+1stringls_colname[],ls_valueintegeri,jlonghandlehandle=OpenChannel("Excel",xlsname)//将列名转化为中文名称，即标题头名称forj=1tocolumncountls_colname[j]=adw.describe("#"+string(j)+".name")ls_value=adw.describe(ls_colname[j]+"_t"+".text")//ole_object.activesheet.cells[1,j].value=ls_value开始的方法SetRemote("R1C"+STRING(J),ls_value,handle)nextdatawindowchildldw_childlongll_foundForj=1Tocolumncount//col_del标识将要删除不可见的列ifadw.Describe("#"+String(j)+".visible")="0"thenIFinteger(j)<27then//当列小于26时，excel中用A-Z表示列号first_del=char(integer(J)+64)else//大于26,小于等于52列时,AA-AZ,大于52列时可能性不大未做考虑first_del="A"+char(integer(J)+38)endifcol_del=col_del+first_del+":"+first_del+","continueendif//当列可见且为下拉数据窗口时，数据值转化为显示值Ifadw.Describe("#"+String(j)+".edit.style")='dddw'Thenadw.GetChild(ls_colname[j],ldw_child)fori=1torowscount-1//"dm","dmyy"是我通常用下拉数据子窗口的值以及显示值//更通用的方法是用DDDW.DataColumn，DDDW.DisplayColumn得到ll_found=ldw_child.Find("dm"+"='"+adw.getitemstring(i,j)+"'",1,ldw_child.RowCount())ifll_found>0thenSetRemote("R"+STRING(i+1)+"C"+STRING(J),ldw_child.getitemstring(ll_found,"dmyy"),handle)endif//另外一种方法，数据量大时比现用方法速度慢//SetRemote("R"+STRING(i+1)+"C"+STRING(J),adw.Describe("Evaluate('LookUpDisplay(#"+string(j)+")',"+string(i)+")"),handle)nextendifnextCloseChannel(handle)ifcol_del<>''thenCOL_DEL=LEFT(COL_DEL,LEN(COL_DEL)-2)//删除不可见列ole_object.activesheet.range(col_del).DeleteendifSetPointer(oldpointer)ole_object.ActiveWorkBook.Save()ole_object.application.quit()ole_object.Disconnectobject()Destroyole_objectReturn1endfunctionstringls_assize,namedintli_value,li_rt,li_rc,li_rt1//livalue=gf_dwsavetoexcel(dw_detail)//ifdw_detail.rowcount()<1thenreturnSetPointer(HourGlass!)livalue=getfilesavename("SaveFile",ls_assize,named,"excel","excelfiles(*.xls),*.xls,"+&"Allfiles(*.*),*.*")IFli_value<>1thenreturnli_rt=dw_detail.saveas(ls_assize,excel!,false)SetPointer(Arrow!)SetPointer(HourGlass!)OLEObjectOle_1Ole_1=CreateOLEObjectli_rc=Ole_1.ConnectToObject(ls_assize)ifli_rc<>0thendestroyole_1returnendifOle_1.Application.Windows(named).Visible=TrueOle_1.Application.DisplayAlerts=Falseifli_rt=1then//aOle.Application.Workbooks[1].worksheets[1].columns(7).InsertSetPointer(HourGlass!)Ole_1.Application.Workbooks[1].worksheets[1].Rows(1).Insert//如何设置导出列的宽度,让列之间有一定的空间?Ole_1.Sheets(1).Columns("A:Z").ColumnWidth=22.00//设置某个区间的列宽Ole_1.Sheets(1).Columns[1].ColumnWidth=13.00//设置某一列的列宽//aOle.Sheets(1).Columns("AG:AI").ColumnWidth=13.00//设置对齐方式//aOle.Sheets(1).Columns(1).Select//sheet1的第一列的对齐方式//aOle.Selection.HorizontalAlignment=-4131//居左//aOle.Sheets(1).Columns("A:Z").HorizontalAlignment=-4152//居右//aOle.Sheets(1).Selection.HorizontalAlignment=-4152//居右//aOle.Selection.HorizontalAlignment=-4108//居中Ole_1.Application.Workbooks[1].worksheets[1].Cells[1,1].value="ICAJCD"Ole_1.Application.Workbooks[1].worksheets[1].Cells[1,1].Font.FontStyle="bold"Ole_1.Application.Workbooks[1].worksheets[1].Cells[1,2].value="ICVICH"Ole_1.Application.Workbooks[1].worksheets[1].Cells[1,2].Font.FontStyle="bold"Ole_1.Application.Workbooks[1].worksheets[1].Cells[1,3].value="ICVJCH"Ol