学步园

public static server PwC_Import readexcel(filenameopen _file)
{
    #Excel
    #AviFiles
    SysExcelApplication          excelApp = SysExcelApplication::construct();
    SysOperationProgress         progress = new SysOperationProgress();
    SysExcelWorkSheet            excelWorksheet;
    SysExcelRange                excelRange;
    SysExcelCells                excelCells;
    SysExcelCell                 excelCell;
    ComVariant                   cellValue = new ComVariant() ;
    boolean                      Flag_Found ;
    PwC_Import                   PwC_ImportLocal;
    int                          colno, colno_excel, colno_grid;
    int                          i;
    int                          TotalRow ;
    str                          strvalue ;
    COMVariant                   VariantValue ;
    Description                  Error;

    excelApp.workbooks().open( _File);
    excelWorksheet = excelApp.worksheets().itemFromNum(1);
    excelCells = excelWorksheet.cells();
    startlengthyoperation();

    flag_found=true ; TotalRow=1 ;
    //________________________________________________________
    excelCell=excelWorksheet.cells().item(TotalRow+1,1);
    strvalue=StrUpr(excelCell.value().bStr());
    if(strvalue!="**********")
        {
        excelworksheet = null;
        excelapp.quit();
        Throw Error("Invalid Excel Template. Please use template specified");
        }
    //________________________________________________________
    progress.setCaption("Data Import");
    progress.setAnimation(#AviFormLetter);
    startlengthyoperation();
    ttsbegin;
    while (Flag_Found==true)
    {
    progress.setText(strfmt("Scanning Records %1", TotalRow));
    //Marked____________________________________________________
    PwC_ImportLocal.Marked                  =NoYes::Yes;
    //TransDate_________________________________________________
    if(excelWorksheet.cells().item( TotalRow+2,1).value().date())
        {
        PwC_ImportLocal.Transdate           =excelWorksheet.cells().item( TotalRow+2,1).value().date();
        }
    else
        {
        Error=Error+"Date ; ";
        PwC_ImportLocal.Transdate           =datenull();
        PwC_ImportLocal.Marked              =NoYes::No;
        }
     //Account___________________________________________________
    If(LedgerTable::exist(excelWorksheet.cells().item( TotalRow+2,2).value().bStr()))
        {
        PwC_ImportLocal.Account=excelWorksheet.cells().item( TotalRow+2,2).value().bStr();
        }
    else
        {
        Error                               =Error+"<A/C>; ";
        PwC_ImportLocal.Account             ="";
        PwC_ImportLocal.Marked              =NoYes::No;
        }
     //Transaction Text_____________________________________________
     PwC_ImportLocal.TransTxt               =excelWorksheet.cells().item( TotalRow+2,3).value().bStr();
     //Offset Account_______________________________________________
     If(LedgerTable::exist(excelWorksheet.cells().item( TotalRow+2,4).value().bStr()))
        {
        PwC_ImportLocal.OffsetAccount       =excelWorksheet.cells().item( TotalRow+2,4).value().bStr();
        }
    else
        {
        Error                               =Error+"<Offset A/C>; ";
        PwC_ImportLocal.OffsetAccount       ="";
        PwC_ImportLocal.Marked              =NoYes::No;
        }
    //Amount Check_________________________________________________
    if( (excelWorksheet.cells().item(TotalRow+2,5).value().double() && excelWorksheet.cells().item(TotalRow+2,6).value().double())   ||
        (!excelWorksheet.cells().item(TotalRow+2,5).value().double() && !excelWorksheet.cells().item(TotalRow+2,6).value().double())  )
        {
        Error                               =Error+"<Dr & Cr>; ";
        PwC_ImportLocal.Marked              =NoYes::No;
        }

    //DebitAmount__________________________________________________
    PwC_ImportLocal.Debit                   = excelWorksheet.cells().item(TotalRow+2,5).value().double();
    //Credit Amount________________________________________________
    PwC_ImportLocal.Credit                  = excelWorksheet.cells().item(TotalRow+2,6).value().double();
    //Dimensions___________________________________________________
    PwC_ImportLocal.Dimensions[1]           =excelWorksheet.cells().item(TotalRow+2,7).value().bStr();
    PwC_ImportLocal.Dimensions[2]           =excelWorksheet.cells().item(TotalRow+2,8).value().bStr();
    PwC_ImportLocal.Dimensions[3]           =excelWorksheet.cells().item(TotalRow+2,9).value().bStr();
    //Error Description____________________________________________
    PwC_ImportLocal.ErrorDesc=Error;

    Error="";  // Reset to Null

    TotalRow++;
    PwC_ImportLocal.insert();
    progress.incCount();

    if(excelWorksheet.cells().item(TotalRow+2,2).value().bstr()=="")
       Flag_Found=false;
    }
    ttscommit;
    endlengthyoperation();
    excelworksheet = null;
    excelapp.quit();

    return PwC_ImportLocal;
}