Downloading Data into Excel using Passthru X++

Continuing from my previous blog post, I’m going to show you how you can download complex data from Dynamics without implementing document or query services.

I’m going to setup a spreadsheet that’s going to allow me to run some dynamic X++ code.

I’ve given a named range to the expanded cell above so that I can refer to it in VSTO I’ve also placed a button on the spreadsheet that will run the following X++ dynamic script (this needs to be pasted into the cell before clicking the button):

Dynamic script

str runDynamic()

{

// query tables

CustTable objCustTable;

ProjTable objProjTable;

// csv objects

str csv = '';

TextIo objtextIO;

#File

;

objtextIO = new TextIo(@"c:\temp\data.csv", #IO_WRITE);

// iterate through sql

while select AccountNum, CustGroup from objCustTable

where objCustTable.CustGroup == 'DOM'

join ProjId, Name, Created from objProjTable

where objProjTable.CustAccount == objCustTable.AccountNum

{

// compose and output csv line detail

csv = '"' + objCustTable.AccountNum + '"';

csv += ',"' + objCustTable.CustGroup + '"';

csv += ',' + num2str(objCustTable.RecId,0,1,1,0);

csv += ',"' + objProjTable.ProjId + '"';

csv += ',"' + objProjTable.Name + '"';

csv += ',"' + date2str(objProjTable.Created, 321, DateDay::Digits2, DateSeparator::Hyphen, DateDay::Digits2, DateSeparator::Hyphen, DateYear::Digits4) + '"';

objtextIO.write(csv);

}

return 'Complete';

}

The function executes a simple query and creates a comma separated string for each record which is dumped out into a local temp folder... nothing too complex.

Apart from the standard data retrieval and manipulation you should be able to access table methods (demonstrated in the attached video).

· You cannot include table-methods into queries and as a result you need to write and implement a custom service to get hold of this information directly in Excel.

The code behind the Excel button uses the proxy class defined in the previous article to dynamically execute the script above. On completion, it reads the locally created text file back into the spreadsheet:

private void DownloadData_Click(object sender, EventArgs e)

{

DynScriptClass objDynScriptClass;

// get script from spreadsheet

string _script = "";

Excel.Worksheet activeSheet = ((Excel.Worksheet)Application.ActiveSheet);

Excel.Range range1 = activeSheet.get_Range("DynamicScript");

Excel.Range range2 = activeSheet.get_Range("Status");

_script = range1.Value2;

// attempt compile and execute

range2.Value = "Running...";

string result = objDynScriptClass.runScript(_script);

range2.Value = result;

if (result.Substring(0,5) == "Error")

{

return;

}

// dump results onto data sheet

Excel.Worksheet destinationSheet = (Excel.Worksheet)Application.Worksheets["Data"];

Excel.Range destinationRange = (Excel.Range)destinationSheet.Cells[1, 1];

ImportCSV("C:\\temp\\data.csv", destinationSheet, destinationRange, false);

destinationSheet.Activate();

}

There are a variety of different ways that data can be imported back into Excel. Here is one of the quickest (QueryTable import):

public void ImportCSV(string importFileName, Excel.Worksheet destinationSheet, Excel.Range destinationRange, bool autoFitColumns)

{

destinationSheet.Range["A1","ZZ999999"].ClearContents();

destinationSheet.QueryTables.Add("TEXT;" + Path.GetFullPath(importFileName), destinationRange, System.Type.Missing);

destinationSheet.QueryTables[1].Name = "data";

destinationSheet.QueryTables[1].FieldNames = true;

destinationSheet.QueryTables[1].RowNumbers = false;

destinationSheet.QueryTables[1].FillAdjacentFormulas = false;

destinationSheet.QueryTables[1].PreserveFormatting = true;

destinationSheet.QueryTables[1].RefreshOnFileOpen = false;

destinationSheet.QueryTables[1].RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;

destinationSheet.QueryTables[1].SavePassword = false;

destinationSheet.QueryTables[1].SaveData = true;

destinationSheet.QueryTables[1].AdjustColumnWidth = false;

destinationSheet.QueryTables[1].RefreshPeriod = 0;

destinationSheet.QueryTables[1].TextFilePromptOnRefresh = false;

destinationSheet.QueryTables[1].TextFilePlatform = 437;

destinationSheet.QueryTables[1].TextFileStartRow = 1;

destinationSheet.QueryTables[1].TextFileParseType = Excel.XlTextParsingType.xlDelimited;

destinationSheet.QueryTables[1].TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierDoubleQuote;

destinationSheet.QueryTables[1].TextFileConsecutiveDelimiter = false;

destinationSheet.QueryTables[1].TextFileTabDelimiter = false;

destinationSheet.QueryTables[1].TextFileSemicolonDelimiter = false;

destinationSheet.QueryTables[1].TextFileCommaDelimiter = true;

destinationSheet.QueryTables[1].TextFileSpaceDelimiter = false;

destinationSheet.QueryTables[1].Refresh(false);

if (autoFitColumns == true)

destinationSheet.QueryTables[1].Destination.EntireColumn.AutoFit();

Excel.Range range = destinationSheet.QueryTables[1].ResultRange;

range.Name = "Results";

// cleanup

destinationSheet.QueryTables[1].Delete();

}

We’ve gone “round-the-houses” a little to get hold of this data but there are some fundamental advantages:

1.) You don’t need a development licence on the machine running the script.

2.) No additional objects or contracts need to be added, deloyed or compiled in the AOT.

3.) Table-method data can be returned as part of the data download. Queries cannot accommodate this.

The following video demonstrates the solution concept: [http://youtu.be/Ag44HQOZC70?t=4m30s&hd=1]

REGARDS

Microsoft Dynamics AX

About Dynamics AX WPF and .Net Innovations

Discussing new and innovative ways to integrate WPF and .Net into Dynamics AX<o:p></o:p>