Downloading Data into Excel using Passthru X++

  • Comments 2
 
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
 
 
  • The advantage of WCF services is type-safety, error handling, authentication and lots of flexibility. I wouldn't give it up without much better reasons.

  • Agreed, and I'm not giving it up, however, in some production environments you don't have a lot of flexibility to change things once they go live. I've worked for banks that code-freeze their production environment for 3 months at a time. You would not be able to implement a WCF service in this scenario, but you would be able to use my solution to get round the problem.