PB操作Excel文件

 oleobject xlApp    //声明Excel应用程序对象

 xlApp = Create OLEObject
  OLEObject xlsub1,xlsub2,xlsub3,obook1,obook2,xlsub4,obook3
  ret = xlApp.ConnectToNewObject( "Excel.Sheet" ) //打开自动化对象

  if ret < 0 then

        MessageBox("Connect to Excel Failed !",string(ret))

  return

 

//操作第一个excel文件

string ls_file3="C:/mytest.xls"
obook3=xlApp.Application.Workbooks.Open(ls_file3) //打开文件

xlsub4 = xlapp.Application.ActiveWorkbook.Worksheets[1]  //激活第一页

//对单元格赋值

xlsub4.cells[c,3].Value = string(mydata.getitemdecimal(r,'rs'))
 xlsub4.cells[c,5].Value = string(mydata.getitemdecimal(r,'yp'))
 xlsub4.cells[c,6] = string(mydata.getitemdecimal(r,'jc'))
 xlsub4.cells[c,7] = string(mydata.getitemdecimal(r,'hy'))
 xlsub4.cells[c,8] = string(mydata.getitemdecimal(r,'zl'))

 

 

//合并一个excel文件

string ls_ran
ls_ran = "B6:M"+string(ld_1+1)
obook1 = xlApp.Application.Workbooks.Open(ls_file2)
xlsub1 = xlapp.Application.ActiveWorkbook.Worksheets[1]
xlsub1.Range(ls_ran).Copy()        //把所选内容复制到内存中
 
 
 xlApp.Application.Visible = false
 xlsub2 = obook3.Worksheets.add()
 xlsub2.name = "分类汇总"   
 xlsub2.Range(ls_ran).PasteSpecial()  //把上一个文件内容粘过来
 //加入分类汇总
 obook3.Worksheets("分类汇总").Activate()

 

integer a[6]
    a={8,9,10,11,12,13}
    xlsub2.Range(ls_ran).Select()
    xlApp.Application.Selection.Subtotal(3,-4157,a, True, False, True)
  xlsub2.Rows(1).Insert()
  xlsub2.cells[1,1] = left(gs_shuqi,4)+"年"+right(gs_shuqi,2)+"月份"
  xlsub2.Cells[1,1].Font.Name="宋体"
  xlsub2.Cells[1,1].Font.Size=22
  xlsub2.Range("A1:M1").Select()
  xlApp.Application.Selection.Merge()
  xlApp.Application.Selection.HorizontalAlignment = 2

 oBook1.save()
oBook3.Save()
oBook3.Saveas(is_docname)
oBook1.close()
oBook3.close()
xlApp.Application.quit()
xlApp.DisConnectObject()