excel_sample.prg

Excel报表 我做的

 LOCAL hbhj1,hbhj2

        hbhj1 = CHR(64+5+cmsl)+'7:'+CHR(64+5+cmsl)+ALLTRIM(str(7+add_hs))
        hbhj2 = CHR(64+lszs)  +'7:'+CHR(64+lszs)  +ALLTRIM(str(7+add_hs))

        WITH Renk_ole
            .Range(hbhj1).select
            .Selection.merge
            .Range(hbhj2).select
            .Selection.merge
        ENDWITH   

              if .not. used('measures')
                 sele 0
                 use measures ALIAS measures
              endif

              if file('cmmctemp.dbf')
                 if used('cmmctemp')   
                   sele cmmctemp
                   use
                 endif
                 erase cmmctemp.dbf
              endif

       SELECT Exceltemp.measure, Measures.meas_name;
        FROM  exceltemp LEFT OUTER JOIN measures ;
          ON  Exceltemp.measure = Measures.meas_no;
        into dbf cmmctemp noconsole ;  
        GROUP BY Exceltemp.measure;
        ORDER BY Exceltemp.measure

           for i = 1 to cmsl
                GO i
                Renk_ole.cells(6,3+i).value      = ALLTRIM(meas_name)
                Renk_ole.cells(6,9+cmsl+i).value = ALLTRIM(meas_name)
           endfor

                sele cmmctemp
                use
                erase cmmctemp.dbf

              if file('ysmctemp.dbf')
                 if used('ysmctemp')   
                   sele ysmctemp
                   use
                 endif
                 erase ysmctemp.dbf
              endif

SELECT Exceltemp.pack_no, Exceltemp.batch, Tincts.tinct_name, Exceltemp.occur_out;
 FROM  exceltemp LEFT OUTER JOIN tincts ;
   ON  Exceltemp.tinct = Tincts.tinct_no;
 INTO DBF ysmctemp noconsole ;  
 ORDER BY Exceltemp.pack_no

 && sele exceltemp
 && use
 && erase exceltemp.dbf

LOCAL sum_hj_, sum_js_pack

     sele ysmctemp
        
       if recc() < 11
               local hb_2_pack_no
                     hb_2_pack_no = chr(64+7+cmsl)+'7:'+chr(64+7+cmsl)+allt(STR(7+add_hs))
                     Renk_ole.Range(hb_2_pack_no).select
                     Renk_ole.Selection.merge
           for i = 1 to recc()
               go i
               with Renk_ole
                    .cells(6+i,2).value = allt(batch)
                    .cells(6+i,3).value = allt(tinct_name)
                    .cells(6+i,4).value = allt(str(occur_out))
               endwith
           ENDFOR
               Renk_ole.cells(7,5+cmsl).value = allt(str(ckzs))
       else
           for i = 1 to add_hs+1
               go i
               with Renk_ole
                    .cells(6+i,2).value = allt(batch)
                    .cells(6+i,3).value = allt(tinct_name)
                    .cells(6+i,4).value = allt(str(occur_out))
               endwith
           endfor
               SUM occur_out TO sum_hj_ for RECNO() < add_hs + 2
               Renk_ole.cells(7,5+cmsl).value = allt(str(sum_hj_))
           for i = add_hs+2 to recc()
               go i
               with Renk_ole
                    .cells(6+i-add_hs-1,2+6+cmsl).value = allt(batch)
                    .cells(6+i-add_hs-1,3+6+cmsl).value = allt(tinct_name)
                    .cells(6+i-add_hs-1,4+6+cmsl).value = allt(str(occur_out))
               endwith
           endfor
               SUM occur_out TO sum_hj_ for RECNO() > add_hs + 1
               Renk_ole.cells(7,11+cmsl*2).value = allt(str(sum_hj_))
       endif

LOCAL pack_no_temp, hb_pack_rows_no, hb_occur

       go top
       pack_no_temp    = pack_no
       hb_pack_rows_no = 7

       Renk_ole.cells(7,1).value = ALLTRIM(STR(pack_no))

      if recc() < 11

           FOR i = 2 to recc()
               go i
               IF pack_no_temp = pack_no
                  hb_pack_no = 'A'+ALLTRIM(STR(hb_pack_rows_no))+':A'+ALLTRIM(STR(hb_pack_rows_no+1))
                  hb_occur   = chr(64+4+cmsl)+ALLTRIM(STR(hb_pack_rows_no))+':'+chr(64+4+cmsl)+ALLTRIM(STR(hb_pack_rows_no+1))
                      WITH Renk_ole
                           .Range(hb_pack_no).select
                           .Selection.merge
                           .Range(hb_occur).select
                           .Selection.merge
                      ENDWITH
               else
                  Renk_ole.cells(hb_pack_rows_no+1,1).value = ALLTRIM(STR(pack_no))
                  pack_no_temp = pack_no
               endif
                  hb_pack_rows_no = hb_pack_rows_no + 1
           ENDFOR
       else

           go add_hs+2
           Renk_ole.cells(7,7+cmsl).value = ALLTRIM(STR(pack_no))

           for i = 2 to add_hs+1
               go i
               IF pack_no_temp = pack_no
                  hb_pack_no = 'A'+ALLTRIM(STR(hb_pack_rows_no))+':A'+ALLTRIM(STR(hb_pack_rows_no+1))
                  hb_occur   = chr(64+4+cmsl)+ALLTRIM(STR(hb_pack_rows_no))+':'+chr(64+4+cmsl)+ALLTRIM(STR(hb_pack_rows_no+1))
                      WITH Renk_ole
                           .Range(hb_pack_no).select
                           .Selection.merge
                           .Range(hb_occur).select
                           .Selection.merge
                      ENDWITH
               else
                  Renk_ole.cells(hb_pack_rows_no+1,1).value = ALLTRIM(STR(pack_no))
                  pack_no_temp = pack_no
               endif
                  hb_pack_rows_no = hb_pack_rows_no + 1
           ENDFOR

           go add_hs+2
           pack_no_temp    = pack_no
           hb_pack_rows_no = 7

           for i = add_hs+3 to recc()
               go i
               IF pack_no_temp = pack_no
                  hb_pack_no = chr(64+7+cmsl)+ALLTRIM(STR(hb_pack_rows_no))+':'+chr(64+7+cmsl)+ALLTRIM(STR(hb_pack_rows_no+1))
                  hb_occur   = chr(64+10+cmsl*2)+ALLTRIM(STR(hb_pack_rows_no))+':'+chr(64+10+cmsl*2)+ALLTRIM(STR(hb_pack_rows_no+1))
                      WITH Renk_ole
                           .Range(hb_pack_no).select
                           .Selection.merge
                           .Range(hb_occur).select
                           .Selection.merge
                  ENDWITH
               else
                  Renk_ole.cells(hb_pack_rows_no+1,7+cmsl).value = ALLTRIM(STR(pack_no))
                  pack_no_temp = pack_no
               endif
                  hb_pack_rows_no = hb_pack_rows_no + 1
           ENDFOR

      endif

LOCAL hj_occur_out, hj_wz, hj_wz_end
  
       go top
              hj_wz = 7
       pack_no_temp = pack_no
       hj_occur_out = occur_out

       if recc() < 11

           FOR i = 2 to recc()
               go i
               IF pack_no_temp = pack_no
                  hj_occur_out = hj_occur_out + occur_out
               else
                  Renk_ole.cells(hj_wz,4+cmsl).value = ALLTRIM(STR(hj_occur_out))
                  hj_occur_out = occur_out
                         hj_wz = 7+i-1
               endif
                  pack_no_temp = pack_no
           ENDFOR
                  Renk_ole.cells(hj_wz,4+cmsl).value = ALLTRIM(STR(hj_occur_out))
     else

           for i = 2 to add_hs+1
               go i
               IF pack_no_temp = pack_no
                  hj_occur_out = hj_occur_out + occur_out
               else
                  Renk_ole.cells(hj_wz,4+cmsl).value = ALLTRIM(STR(hj_occur_out))
                  hj_occur_out = occur_out
                         hj_wz = 7+i-1
               endif
                  pack_no_temp = pack_no
           ENDFOR
                  Renk_ole.cells(hj_wz,4+cmsl).value = ALLTRIM(STR(hj_occur_out))

           go add_hs+2
                  hj_wz = 7
           pack_no_temp = pack_no
           hj_occur_out = occur_out

           for i = add_hs+3 to recc()
               go i
               IF pack_no_temp = pack_no
                  hj_occur_out = hj_occur_out + occur_out
               else
                  Renk_ole.cells(hj_wz,10+cmsl*2).value = ALLTRIM(STR(hj_occur_out))
                  hj_occur_out = occur_out
                         hj_wz = 7+i-add_hs-1-1
               endif
                  pack_no_temp = pack_no
           ENDFOR
                  Renk_ole.cells(hj_wz,10+cmsl*2).value = ALLTRIM(STR(hj_occur_out))

       endif

       if file('ysmctemp.dbf') 
         if used('ysmctemp')   
           sele ysmctemp
           use
         endif
         erase ysmctemp.dbf
      endif
           

with renk_ole
        .ActiveSheet.Range('A6:'+chr(64+11+cmsl*2)+'6').Borders(3).LineStyle = 9                                              && 字段名行顶
        .ActiveSheet.Range('A6:'+chr(64+11+cmsl*2)+'6').Borders(4).LineStyle = 1                                              && 字段名行底
        .ActiveSheet.Range('A6:A'+ALLTRIM(str(7+add_hs))).Borders(1).LineStyle = 9                                           && 最左
        .ActiveSheet.Range(chr(64+6+cmsl)+'6:'+chr(64+7+cmsl)+ALLTRIM(str(7+add_hs))).Borders(1).LineStyle = 1                && 分栏列
        .ActiveSheet.Range(chr(64+11+cmsl*2)+'6:'+chr(64+11+cmsl*2)+ALLTRIM(str(7+add_hs))).Borders(2).LineStyle = 9          && 最右
        .ActiveSheet.Range('A8:'+chr(64+11+cmsl*2)+ALLTRIM(str(8+add_hs))).Borders(3).LineStyle = 1                           && 数据行
        .ActiveSheet.Range('A6:'+chr(64+4+cmsl)+ALLTRIM(str(7+add_hs))).Borders(2).LineStyle = 1                              && 数据列
        .ActiveSheet.Range(chr(64+7+cmsl)+'6:'+chr(64+10+cmsl*2)+ALLTRIM(str(7+add_hs))).Borders(2).LineStyle = 1             && 数据列
        .ActiveSheet.Range('A'+ALLTRIM(str(7+add_hs))+':'+chr(64+11+cmsl*2)+ALLTRIM(str(7+add_hs))).Borders(4).LineStyle = 9  && 数据行底
      .ActiveSheet.Rows(5).Delete
&&      .ActiveSheet.PageSetup.PrintGridLines = .t.
        .Visible = .T.        
        .ActiveSheet.PageSetup.Orientation = 2
        .ActiveSheet.PrintPreview
        .ActiveWorkBook.SaveAs(lcpathtemp+"test.xls")
        .ActiveWorkbook.saved = .T.
        .Quit
endwith

quit