Шаблон реализации вывода данных в MS Excel через .NET для Dynamics AX

Так или иначе перед программистом встает задача, в которой необходимо вывести данные в MS Excel. На данный момент существует множество инструментов и библиотек для выполнения данной задачи. Но в данной статье будет рассмотрен шаблон класса для экспорта данных из Microsoft Dynamics AX, реализованный на основе .net-библиотеки Microsoft.Office.Interop.Excel.

Contents

1 Объявление переменных
2 Основные функции
3 Дополнительные функции
4 Завершение работы с документом
5 Примечания
6 См. также

Объявление переменных

class DotNetExcel
{
    Microsoft.Office.Interop.Excel.ApplicationClass xlApplication;
    Microsoft.Office.Interop.Excel.Workbooks        xlWorkbooks;
    Microsoft.Office.Interop.Excel._Workbook        xlWorkbook;
    System.Object                                   oMissing;
}

Основные функции

Пустое значение

Внимание! Во многих функциях, есть необязательные значения, но AX все равно требует их заполнения. Для этого нужно создать переменную Missing.

public void init()
{
    System.Type                     type;
    System.Reflection.FieldInfo     fieldInfo;
    ;

    type        = System.Type::GetType('System.Reflection.Missing');
    fieldInfo   = type.GetField('Value');
    oMissing    = fieldInfo.GetValue(null);
}

Открытие или создание нового документа

public boolean openDocument( Filename _template = ""
                           , boolean _visible = true
                           , boolean _readOnly = false
                           , boolean _displayAlerts = -1
                           )
{
    System.Object   oReadOnly   = _readOnly;
    System.Object   oFalse      = false;
    System.Object   oTrue       = true;
    boolean         ret         = true;
    str             exception;
    ;

    try
    {
        if(_template && !WinApi::fileExists_RU(_template))
        {
            throw error(strfmt("Файл «%1» не найден", _template));
        }

        xlApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();

        xlApplication.set_Visible(_visible);
        xlApplication.set_DisplayAlerts(_displayAlerts);

        xlWorkbooks = xlApplication.get_Workbooks();
        xlWorkbook  = xlWorkbooks.Open(_template,
            oFalse, //обновление ссылок
            oReadOnly, //только для чтения
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            true, //редактируемый
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            oMissing);
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
        ret = false;
    }

    return ret;
}

Получение диапазона

protected Microsoft.Office.Interop.Excel.Range getRange( MSOfficeBookMark_RU _bookmark
                                                       , int _worksheet = 1
                                                       )
{
    Microsoft.Office.Interop.Excel.Worksheets   xlWorksheets;
    Microsoft.Office.Interop.Excel._Worksheet   xlWorksheet;
    Microsoft.Office.Interop.Excel.Range        xlRange;
    str                                         exception;
    ;

    new InteropPermission(InteropKind::ClrInterop).assert();
    try
    {
        xlWorksheets = xlWorkbook.get_Worksheets();
        xlWorksheet = xlWorksheets.get_Item(_worksheet);

        xlRange = xlWorksheet.get_Range(_bookmark, oMissing);
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
    }
    CodeAccessPermission::revertAssert();

    return xlRange;
}

Сохранение документа

public boolean saveDocumentAs( FilenameSave _filenameSave
                             , str          _fileFormat = ""
                             )
{
    Microsoft.Office.Interop.Excel.XlFileFormat fileFormat;
    str                                         exception;
    ;

    new InteropPermission(InteropKind::ClrInterop).assert();
    try
    {
        if (!_fileFormat)
        {
            fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat::xlOpenXMLWorkbookMacroEnabled;
        }
        else
        {
            fileFormat = CLRInterop::parseClrEnum("Microsoft.Office.Interop.Excel.XlFileFormat", _fileFormat);
        }

        xlWorkbook.SaveAs(_filenameSave,
            fileFormat,
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode::xlNoChange,
            oMissing,
            oMissing,
            oMissing,
            oMissing,
            true);
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
        return false;
    }
    CodeAccessPermission::revertAssert();

    return true;
}

Заполнение документа

public void pasteToRange( list _dataList
                        , MSOfficeBookMark_RU _bookmark
                        , int _worksheet = 1
                        , container _colFormat = connull()
                        , int _colCount = 0
                        )
{
    ADODB.RecordsetClass    recordset;
    ADODB.Fields            fields;
    int                     colCount = _colCount;
    str                     exception;

    ADODB.DataTypeEnum adoTypeToExcel(Types _type)
    {
        ADODB.DataTypeEnum  ret;
        ;

        switch (_type)
        {
            case Types::String:
                ret = ADODB.DataTypeEnum::adBSTR;
                break;
            case Types::Enum:
                ret = ADODB.DataTypeEnum::adBSTR;
                break;
            case Types::Date:
                ret = ADODB.DataTypeEnum::adDBDate;
                break;
            case Types::Integer:
                ret = ADODB.DataTypeEnum::adInteger;
                break;
            case Types::Real:
                ret = ADODB.DataTypeEnum::adDouble;
                break;
            case Types::Int64:
                ret = ADODB.DataTypeEnum::adBigInt;
                break;
            default:
                ret = ADODB.DataTypeEnum::adBSTR;
        }

        return ret;
    }

    int getColCount(List _list)
    {
        int maxCol = 0;
        int curCol;
        ListEnumerator le;
        ;

        le = _list.getEnumerator();

        while (le.moveNext())
        {
            curCol = conLen(le.current());
            if (maxCol < curCol)
            {
                maxCol = curCol;
            }
        }

        return maxCol;
    }
    
    void addColumns(ADODB.Fields _fields, int _columns)
    {
        ADODB.DataTypeEnum excFormat;
        Types colFormat;
        int j;
        int i;
        int colNum;
        int format;
        ;

        for (i = 1; i <= _columns; i++)
        {
            excFormat = ADODB.DataTypeEnum::adBSTR;
            j = 1;
            while (j <= conlen(_colFormat))
            {
                [colNum, colFormat] = conpeek(_colFormat, j);
                if (colNum == i)
                {
                    excFormat = adoTypeToExcel(colFormat);
                    break;
                }
                j++;
            }

            _fields.Append(strfmt('Column%1', i),
                excFormat,
                150,
                ADODB.FieldAttributeEnum::adFldFixed,
                null);
        }
    }
    //заполнение набора записей
    void fillData(ADODB.RecordsetClass _recordset, ADODB.Fields _fields, List _list)
    {
        ADODB.Field field;
        ListEnumerator le;
        container line;
        str data;
        Column colNum;
        ;

        le = _list.getEnumerator();
        while (le.moveNext())
        {
            _recordset.AddNew(oMissing, oMissing);

            line = le.current();
            for (colNum = 1; colNum <= conLen(line); colNum++)
            {
                data = conPeek(line, colNum);
                if (data)
                {
                    field = _fields.get_Item(strFmt('Column%1', colNum));

                    if(field.get_Type() == ADODB.DataTypeEnum::adDouble)
                    {
                        data = strRem(data, " ");
                        data = strReplace(data, ",", ".");
                    }
                    field.set_Value(data);
                    _recordset.Update(oMissing, oMissing);
                }
            }
        }
    }
    //вставка в Excel из набора записей
    void paste(ADODB.RecordsetClass _recordset
        ,  MSOfficeBookMark_RU _bookmarkLoc
        , int _worksheetLoc
        )
    {
        Microsoft.Office.Interop.Excel.Range    xlRange;
        ;

        xlRange = this.getRange(_bookmarkLoc, _worksheetLoc);
        xlRange.CopyFromRecordset(_recordset, oMissing, oMissing);
    }
    ;

    new InteropPermission(InteropKind::ClrInterop).assert();
    try
    {
        if (!colCount)
        {
            colCount = getColCount(_dataList);
        }

        if (colCount)
        {
            recordset = new ADODB.RecordsetClass();
            fields = recordSet.get_Fields();

            addColumns(fields, colCount);
            recordSet.Open(oMissing,
                oMissing,
                ADODB.CursorTypeEnum::adOpenKeyset,
                ADODB.LockTypeEnum::adLockOptimistic,
                1);
            fillData(recordset, fields, _dataList);
            paste(recordset, _bookmark, _worksheet);
        }
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
    }
    CodeAccessPermission::revertAssert();
}

Дополнительные функции

Добавление и копирование листа

public void addWorksheet( int _copyFromSheet = 0
                        , int _after = 1
                        , str _newName = ''
                        )
{
    Microsoft.Office.Interop.Excel.Worksheets   xlWorksheets;
    Microsoft.Office.Interop.Excel._Worksheet   xlWorksheetAfter;
    Microsoft.Office.Interop.Excel._Worksheet   xlWorksheetCopy;
    str                                         exception;
    ;

    new InteropPermission(InteropKind::ClrInterop).assert();
    try
    {
        xlWorksheets = xlWorkbook.get_Worksheets();

        xlWorksheetAfter = xlWorksheets.get_Item(_after);
        if (_copyFromSheet)
        {
            xlWorksheetCopy = xlWorksheets.get_Item(_copyFromSheet);
            xlWorksheetCopy.Copy(oMissing, xlWorksheetAfter);
        }
        else
        {
            xlWorksheets.Add(oMissing, 
                xlWorksheetAfter, 
                oMissing, 
                oMissing);
        }
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
    }
    CodeAccessPermission::revertAssert();
}

Активация листа

Данная функция делает активным лист, идентификатор которого соответствует передаваемому параметру.

public void activateWorksheet(int _worksheet)
{
    Microsoft.Office.Interop.Excel.Worksheets   xlWorksheets;
    Microsoft.Office.Interop.Excel._Worksheet   xlWorksheet;
    str                                         exception;
    ;

    new InteropPermission(InteropKind::ClrInterop).assert();
    try
    {
        xlWorksheets = xlWorkbook.get_Worksheets();
        xlWorksheet = xlWorksheets.get_Item(_worksheet);

        xlWorksheet.Activate();
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
    }
    CodeAccessPermission::revertAssert();
}

Удаление листа

public void deleteWorksheet(int _worksheet)
{
    Microsoft.Office.Interop.Excel.Worksheets   xlWorksheets;
    Microsoft.Office.Interop.Excel._Worksheet   xlWorksheet;
    str                                         exception;
    ;
    
    new InteropPermission(InteropKind::ClrInterop).assert();
    try
    {
        xlWorksheets = xlWorkbook.get_Worksheets();
        xlWorksheet = xlWorksheets.get_Item(_worksheet);
        xlWorksheet.Delete();
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
    }
    CodeAccessPermission::revertAssert();
}

Добавление таблицы

public void addTable( MSOfficeBookMark_RU   _sourceRange
                    , MSOfficeBookMark_RU   _tableName
                    , int                   _worksheet = 1
                    )
{
    Microsoft.Office.Interop.Excel.Worksheets   xlWorksheets;
    Microsoft.Office.Interop.Excel._Worksheet   xlWorksheet;
    Microsoft.Office.Interop.Excel.ListObjects  xlListObjects;
    Microsoft.Office.Interop.Excel.ListObject   xlListObject;
    str                                         exception;
    ;

    new InteropPermission(InteropKind::ClrInterop).assert();
    try
    {
        xlWorksheets  = xlWorkbook.get_Worksheets();
        xlWorksheet   = xlWorksheets.get_Item(_worksheet);
        xlListObjects = xlWorksheet.get_ListObjects();

        xlListObject = xlListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType::xlSrcRange,
            xlWorksheet.get_Range(_sourceRange, oMissing),
            oMissing,
            Microsoft.Office.Interop.Excel.XlYesNoGuess::xlYes,
            oMissing);
        xlListObject.set_Name(_tableName);
    }
    catch
    {
        exception = AifUtil::getClrErrorMessage();
        if (exception)
        {
            info(exception);
        }
    }
    CodeAccessPermission::revertAssert();
}

Установка видимости документа

Для того, чтобы пользователь смог увидеть документ, вызываем необходмиую функцию.

public void showDocument()
{
    ;
    xlApplication.set_Visible(true);
    WinApi::setForegroundWindow(xlApplication.get_Hwnd()); //установка приоритетного окна
}

Завершение работы с документом

При окончании работы класса необходимо закрыть приложение и очистить переменные, иначе останется висеть процесс Excel.

public void closeDocument()
{
    ;

    xlWorkbook = null;
    xlWorkbooks.Close();
    xlWorkbooks = null;
    xlApplication.Quit();
    xlApplication = null;

    System.GC::Collect();
    System.GC::WaitForPendingFinalizers();
}

Примечания

Microsoft.Office.Interop.Excel Namespace

См. также

Подключение .Net-библиотеки в Dynamics AX 2012