AX代码导出导入Excel

Excel仍是目前异构数据交流和数据汇报的重要工具，把AX界面数据转成Excel文件，以及把Excel文件里的资料导入AX系统，是比较多发的事件。

一、把AX界面数据直接变成Excel文件：选中资料范围，点击复制（或ctrl+C），在Excel中点击粘贴或(ctrl+V)。这种方法灵活简单，但不能按规则输出到有格式要求的Excel文件中。

二、用代码，把附合条件的AX数据输出到指定格式的某个Excel文件中：

void ToExcel0()
{
    SPL_OrderList       _orderList;
    tempStr             _exportFile;
    SysExcelApplication m_application;
    SysExcelWorkbooks   m_workbooks;
    SysExcelWorkbook    m_workbook;
    SysExcelWorksheets  m_worksheets;
    SysExcelWorksheet   m_worksheet;
    SysExcelCells       m_cells;
    int                 m_row;
    SalesQty            _sumQty;
    str                 _subItem, _subCurr;
    ;
    _exportFile = "\//192.168.1.88\\AxShare\\OrderList0.xls";

    StartLengthyOperation();
    m_application   = SysExcelApplication::construct();
    m_workbooks     = m_application.workbooks();
    m_workbooks.open(_exportFile);
    m_workbook      = m_workbooks.item(1);
    m_worksheets    = m_workbook.worksheets();
    m_worksheet     = m_worksheets.itemFromNum(1);
    m_cells         = m_worksheet.cells();
    m_row   = 2;
    _subItem= '';
    _subCurr= '';
    _sumQty = 0;

    m_cells.item(1,1).value('訂單統計表 ('+Date2Str(DateFrom.dateValue(),321,2,3,2,3,4)+')--('
                            +Date2Str(DateTo.dateValue(),321,2,3,2,3,4)+')');
//把當前界面數據輸出
    for(_orderList = SPL_OrderList_ds.getFirst();
        _orderList;
        _orderList = SPL_OrderList_ds.getNext())
    {
        _subCurr = _orderList.SPL_Model;
        if (m_row == 2) _subItem = _subCurr;
        //插入合計
        if (_subCurr != _subItem)
        {
            m_row ++;
            m_cells.item(m_row,2).value(_subItem);
            m_cells.item(m_row,3).value('小計');
            m_cells.item(m_row,4).value(_sumQty);
            _subItem = _subCurr;
            _sumQty  = 0;
        }
        m_row ++;
        m_cells.item(m_row,1).value(_orderList.SalesId);
        m_cells.item(m_row,2).value(_subCurr);
        m_cells.item(m_row,3).value(_orderList.SPL_CustModelNo);
        m_cells.item(m_row,4).value(_orderList.QtyOrdered);
        m_cells.item(m_row,5).value(_orderList.ConfirmedDlv);
        m_cells.item(m_row,6).value(_orderList.SPL_Brand);
        m_cells.item(m_row,7).value(_orderList.PurchOrderFormNum);
        m_cells.item(m_row,8).value(_orderList.ItemId);
        m_cells.item(m_row,9).value(_orderList.SPL_Area);
        m_cells.item(m_row,10).value(date2str(_orderList.ReceiveDate,321,2,3,2,3,4));
        m_cells.item(m_row,11).value(_orderList.SoStatu);
        m_cells.item(m_row,12).value(date2str(_orderList.SPL_PlanInspecDate,321,2,3,2,3,4));
        m_cells.item(m_row,13).value(date2str(_orderList.SPL_ActInspecDate,321,2,3,2,3,4));
        m_cells.item(m_row,14).value(_orderList.SPL_InspecQty);
        m_cells.item(m_row,15).value(_orderList.SPL_SpeciaInstru);
        _sumQty = _sumQty + _orderList.QtyOrdered;
    }
    //最后一個產品小計
    {
        m_row ++;
        m_cells.item(m_row,2).value(_subItem);
        m_cells.item(m_row,3).value('小計');
        m_cells.item(m_row,4).value(_sumQty);
        _subItem = _subCurr;
        _sumQty  = 0;
    }
    _subCurr = 'A4:N' + Int2Str(m_row);
    m_cells.range(_subCurr).comObject().RowHeight(17.5);
    m_application.visible(true);
    EndLengthyOperation();
}

三、把Excel文件里的资料导入AX系统。特别留意，如果Excel里有中文字，导入AX后可能會亂碼，把Excel转存为csv格式再导入即可。

void clicked()
{
    tempStr             ImportFileName;
    SysExcelApplication m_application;
    SysExcelWorkbooks   m_workbooks;
    SysExcelWorkbook    m_workbook;
    SysExcelWorksheets  m_worksheets;
    SysExcelWorksheet   m_worksheet;
    SysExcelCells       m_cells;
    ComVariantType      m_type;
    int                 m_row = 1;
    BomID               m_BomID;
    qty                 m_Scrap;
    ;
    ImportFileName  = WinAPI::getOpenFileName(element.hWnd(),["Excel文件","*.xls","*.xlsx"],"","需要導入的文件");
    m_application   = sysExcelApplication::construct();
    m_workbooks     = m_application.workbooks();
    m_workbooks.open(ImportFileName);
    m_workbook      = m_workbooks.item(1);
    m_worksheets    = m_workbook.worksheets();
    m_worksheet     = m_worksheets.itemFromNum(1);
    m_cells         = m_worksheet.cells();
    m_type          = m_cells.item(m_row+1,1).value().variantType();
    ttsbegin;
    m_BomID = SPL_CIQBomTable.BOMId;
    while (m_type != COMVariantType::VT_EMPTY)
    {
        m_row++;
        if (m_row >= 3)
        {
            SPL_CIQBOMLine.clear();
            SPL_CIQBOMLine.BomID        = m_BomID;
            SPL_CIQBOMLine.ItemId       = m_cells.item(m_row,1).value().bStr();
            SPL_CIQBOMLine.UnitID       = m_cells.item(m_row,3).value().bStr();
            SPL_CIQBOMLine.BOMQty       = m_cells.item(m_row,4).value().double();
            SPL_CIQBOMLine.SPL_OneNw    = m_cells.item(m_row,5).value().double();
            SPL_CIQBOMLine.SPL_CIQNW    = m_cells.item(m_row,6).value().double();
            m_Scrap                     = m_cells.item(m_row,7).value().double();
            if (m_Scrap > 0)
                SPL_CIQBOMLine.SPL_Scrap    = num2str(m_Scrap,1,NumOfDec(m_Scrap),0,0);
            SPL_CIQBOMLine.insert();
        }
        m_type = m_cells.item(m_row+1,1).value().variantType();
    }
    ttscommit;
    m_application.quit();
    SPL_CIQBOMLine_ds.research();
}

四、AX代码控制Excel实测有效的函数：

1. 合并单元格：m_cells.range(‘A3:C10’).comObject().mergeCells(true);
2. 内容居中：m_cells.range(‘A1:C10’).comObject().horizontalAlignment(3);
3. 设置栏宽：m_worksheet.columns().comObject().ColumnWidth(12);
4. 设置栏宽：m_worksheet.columns().item(m_col).comObject().ColumnWidth(30);
5. 设置行高：m_cells.range(‘A1:C10’).comObject().RowHeight(17.5);
6. 填满：m_worksheet.columns().item(m_col).autoFit();
7. 内容水平居中：m_cells.range(‘A1:C10’).comObject().horizontalAlignment(3);