AX 2009 报表替换Excel显示

报表替换Excel显示

报表还是不够灵活，不够好用。特别是当项目内容过多，显示的数值过长时候都会用##替代，而打印不出来。

而且定宽，定高，不方便调整。我们开发的辛苦，客户用的也不舒服。

双方都不爽，最后弄了个Excel，算是解决了这个问题。

先要弄个Excel模板文件，然后把数据内容逐个填充到单元格即可，客户爱拖爱调，那就是他的事了。

续上篇，调用接收参数报表类。

public   class  ReportRun extends ObjectRun
{
     int                          g_Year;
    PurchYearCollectTable       g_PurchYCTable;
    Array                       g_Arr;
    SysExcelApplication         excel;
    SysExcelWorkbooks           books;
    SysExcelWorkbook            book;
    SysExcelWorksheets          sheets;
    SysExcelWorksheet           sheet;
    SysExcelCells               cells;
    SysExcelCell                cell;
    SysExcelRange               columns;
    SysExcelRange               column;
    COM                         range,row;
    FileName                    fileName;
     int                          rowNum;
    real                        pricesCount;

     private   void  excelHead()
    {
      container       m_Title;
      ;
      cell  =  cells.item( 1 , 5 );

      m_Title  =   this .read(cell);

      cell.value(strfmt(conpeek (m_Title, 1 ),g_Year));
    }

     private   void  excelTable()
    {
      PurchYearCollect                m_PurchYearC;
     int                                  i,arrLeng,rowN,rowR;
    ;

    rowN  =   4 ;

    arrLeng  =  g_Arr.lastIndex()  +   1 ;

     for (i  =   1 ;i < arrLeng;i ++ )
    {
        m_PurchYearC  =  g_Arr.value(i);

         if (rowN  >   5 )
        {
            range  =  sheet.range(strfmt( " A%1:N%1 " ,rowN - 1 )).comObject();
            row  =  range.EntireRow();
            row.copy();
            row.insert();
        }

        cell  =  cells.item(rowN, 1 );
        cell.value(m_PurchYearC.getItemName());

        cell  =  cells.item(rowN, 2 );
        cell.value(m_PurchYearC.getPrice( 1 ));

        cell  =  cells.item(rowN, 3 );
        cell.value(m_PurchYearC.getPrice( 2 ));

        cell  =  cells.item(rowN, 4 );
        cell.value(m_PurchYearC.getPrice( 3 ));

        cell  =  cells.item(rowN, 5 );
        cell.value(m_PurchYearC.getPrice( 4 ));

        cell  =  cells.item(rowN, 6 );
        cell.value(m_PurchYearC.getPrice( 5 ));

        cell  =  cells.item(rowN, 7 );
        cell.value(m_PurchYearC.getPrice( 6 ));

        cell  =  cells.item(rowN, 8 );
        cell.value(m_PurchYearC.getPrice( 7 ));

        cell  =  cells.item(rowN, 9 );
        cell.value(m_PurchYearC.getPrice( 8 ));

        cell  =  cells.item(rowN, 10 );
        cell.value(m_PurchYearC.getPrice( 9 ));

        cell  =  cells.item(rowN, 11 );
        cell.value(m_PurchYearC.getPrice( 10 ));

        cell  =  cells.item(rowN, 12 );
        cell.value(m_PurchYearC.getPrice( 11 ));

        cell  =  cells.item(rowN, 13 );
        cell.value(m_PurchYearC.getPrice( 12 ));

        rowN ++ ;
    }

    rowN  +=   3 ;
    rowR  =  rowN  +   1 ;

     for (i  =   1 ;i < arrLeng;i ++ )
    {
        m_PurchYearC  =  g_Arr.value(i);

         if (rowN  >  rowR)
        {
            range  =  sheet.range(strfmt( " A%1:M%1 " ,rowN - 1 )).comObject();
            row  =  range.EntireRow();
            row.copy();
            row.insert();
        }

        cell  =  cells.item(rowN, 1 );
        cell.value(m_PurchYearC.getItemName());

        cell  =  cells.item(rowN, 2 );
        cell.value(m_PurchYearC.getRate( 1 ));

        cell  =  cells.item(rowN, 3 );
        cell.value(m_PurchYearC.getRate( 2 ));

        cell  =  cells.item(rowN, 4 );
        cell.value(m_PurchYearC.getRate( 3 ));

        cell  =  cells.item(rowN, 5 );
        cell.value(m_PurchYearC.getRate( 4 ));

        cell  =  cells.item(rowN, 6 );
        cell.value(m_PurchYearC.getRate( 5 ));

        cell  =  cells.item(rowN, 7 );
        cell.value(m_PurchYearC.getRate( 6 ));

        cell  =  cells.item(rowN, 8 );
        cell.value(m_PurchYearC.getRate( 7 ));

        cell  =  cells.item(rowN, 9 );
        cell.value(m_PurchYearC.getRate( 8 ));

        cell  =  cells.item(rowN, 10 );
        cell.value(m_PurchYearC.getRate( 9 ));

        cell  =  cells.item(rowN, 11 );
        cell.value(m_PurchYearC.getRate( 10 ));

        cell  =  cells.item(rowN, 12 );
        cell.value(m_PurchYearC.getRate( 11 ));

        cell  =  cells.item(rowN, 13 );
        cell.value(m_PurchYearC.getRate( 12 ));

        rowN ++ ;
    }

    excel.visible( true );
    }

    Container read(SysExcelCell sysExcelCell)
    {
    container       line;
     int              intvalue;
    real            realvalue;
    ;
     switch  (sysExcelCell.value().variantType())
    {
         case  COMVariantType::VT_EMPTY:
            line  +=   0 ;
             break ;

         case  COMVariantType::VT_I1:
            line  +=  sysExcelCell.value(). char ();
             break ;

         case  COMVariantType::VT_I2:
            line  +=  sysExcelCell.value(). short ();
             break ;

         case  COMVariantType::VT_I4:
            intValue  =  sysExcelCell.value(). int ();
             if  (intValue  ==   0 )
            {
                intValue  =  sysExcelCell.value(). long ();
            }
            line  +=  intValue;
             break ;

         case  COMVariantType::VT_UI1:
            line  +=  sysExcelCell.value(). byte ();
             break ;

         case  COMVariantType::VT_UI2:
            line  +=  sysExcelCell.value().uShort();
             break ;

         case  COMVariantType::VT_UI4:
            intValue  =  sysExceLCell.value().uInt();
             if  (intValue  ==   0 )
            {
                intValue  =  sysExcelCell.value().uLong();
            }
            line  +=  intValue;
             break ;

         case  COMVariantType::VT_R4 :
            realValue  =  sysExcelCell.value(). float ();
            line  +=  realValue;
             break ;

         case  COMVariantType::VT_R8 :
            realValue  =  sysExcelCell.value(). double ();
            line  +=  realValue;
             break ;

         case  COMVariantType::VT_DECIMAL :
            realValue  =  sysExcelCell.value(). decimal ();
            line  +=  realValue;
             break ;

         case  COMVariantType::VT_BSTR :
            line  +=  SysExcelCell.value().bstr();
             break ;
         default :
             throw  error(strfmt( " @SYS26908 " , sysExcelCell.value().variantType()));
    }

     return   line;
    }

     private   void  excelImport()
    {
    FilenameOpen                m_file;
    VendParameters              m_parameter;
    ;

    select firstonly m_parameter;
    m_file  =  m_parameter.PurchYearPrintModel;

    excel  =  SysExcelApplication::construct();

    books  =  excel.workbooks();

     if ( ! books.open(m_file))
         return ;

     if ( ! books.count())
    {
        info( " no content! " );
         return ;
    }

    book  =  books.item( 1 );
    sheets  =  book.worksheets();
    sheet  =  sheets.itemFromNum( 1 );
    cells  =  sheet.cells();

    }

     private   void  PriceRedution()
    {
    PurchLineView           m_PurchLineView;
    utcDateTime                 m_BeginTime,m_EndTime,m_BeginYear;
    Date                        m_BeginDate,m_EndDate;
     int                          i,r;
    real                        downPrice,total,lastPrice;
    str                         m_ItemName;
    MainTypeTable           m_MainType;
    PurchYearCollect        m_PurchYear;
    PurchLine                   m_PurchLine,t_PurchLine;
    real                        tmpPrice,tmpQty;
    ;

    g_Arr  =   new  Array(Types::Class);

    r  =   1 ;

    m_BeginYear  =  DateTimeUtil::newDateTime(mkdate( 1 , 1 ,g_Year), 0 );

     while  select m_MainType
    {
        m_PurchYear  =   new  PurchYearCollect();

        m_PurchYear.setItemName(m_MainType.Name);

        m_BeginDate  =  mkdate( 1 , 1 ,g_Year);
        m_EndDate  =  mkdate( 31 , 1 ,g_Year);

         for (i  =   1 ;i < 13 ;i ++ )
        {
            m_BeginTime  =  DateTimeUtil::newDateTime(m_BeginDate, 0 );
            m_EndTime  =  DateTimeUtil::newDateTime(m_EndDate, 3600 * 24 - 1 );

            total  =   0 ;
            downPrice  =   0 ;

             // while select sum(PurchQty) from m_PurchLineView
             // group by m_PurchLineView.MainTypeId
             // where m_PurchLineView.createdDateTime1 > m_BeginTime && m_PurchLineView.createdDateTime1 < m_EndTime
             // && m_PurchLineView.MainTypeId == m_MainType.MainTypeId
             // {
                 while  select m_PurchLineView
                 where  m_PurchLineView.createdDateTime  >  m_BeginTime  &&  m_PurchLineView.createdDateTime  <  m_EndTime
                 &&  m_PurchLineView.MainTypeId  ==  m_MainType.MainTypeId
                {
                    lastPrice  =   0 ;

                     while  select firstonly t_PurchLine order by t_PurchLine.createdDateTime desc  where  t_PurchLine.ItemId  ==  m_PurchLineView.ItemId
                     &&  t_PurchLine.createdDateTime  <  m_BeginYear
                    {
                        lastPrice  =  t_PurchLine.PurchPrice;
                    }

                     if ( ! t_PurchLine)
                    {
                         while  select firstonly t_PurchLine order by t_PurchLine.createdDateTime asc
                         where  t_PurchLine.ItemId  ==  m_PurchLineView.ItemId
                        {
                            lastPrice  =  t_PurchLine.PurchPrice;
                        }
                    }

                    tmpPrice  =  m_PurchLineView.PurchPrice;
                    tmpQty  =  m_PurchLineView.PurchQty;

                    downPrice  +=  (tmpPrice  -  lastPrice)  *  tmpQty;
                    total  +=  tmpPrice  *  tmpQty;
                }
             // }

            m_BeginDate  =  nextmth(m_BeginDate);
            m_EndDate  =  endmth(nextmth(m_EndDate));

            m_PurchYear.setPrice(i,downPrice);
            m_PurchYear.setTotal(i,total);
        }

        g_Arr.value(r,m_PurchYear);

         ++ r;
     }
    }

     public  boolean fetch()
    {
    ;
     this .excelImport();
     this .excelHead();
     this .excelTable();

     throw   "" ;   // 出处抛出空异常，报表最后就不显示

     return   false ;
    }

     public   void  init()
    {
     PurYearClass        m_PurYear;
    ;

    super();

    m_PurYear  =  element.args().caller();

    g_Year  =  m_PurYear.getYear();

    Title.text(strfmt( " @SYS1303 " ,g_Year));

       this .PriceRedution();   // 技术月降价汇总的方法
    }
}

 

 

转载于:https://www.cnblogs.com/Kurodo/archive/2011/08/15/2139750.html