public async Task OutputResultsAsync(IQueryRunner runner, IQueryTextProvider textProvider)
{
var dlg = new Microsoft.Win32.SaveFileDialog
{
DefaultExt = ".xlsx",
Filter = "Excel file (*.xlsx)|*.xlsx"
};
string fileName = "";
long durationMs = 0;
// Show save file dialog box
var result = dlg.ShowDialog();
// Process save file dialog box results
if (result == true)
{
// Save document
fileName = dlg.FileName;
await Task.Run(() =>
{
try
{
runner.OutputMessage("Query Started");
var sw = Stopwatch.StartNew();
var daxQuery = textProvider.QueryText;
var reader = runner.ExecuteDataReaderQuery(daxQuery);
using (var statusProgress = runner.NewStatusBarMessage("Starting Export"))
{
try
{
if (reader != null)
{
int iFileCnt = 1;
runner.OutputMessage("Command Complete, writing output file");
bool moreResults = true;
using (var stream = new FileStream(fileName, FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream))
{
while (moreResults)
{
// create a worksheet for the current resultset
xlsxWriter.BeginWorksheet($"Query{iFileCnt}", 1);
// write out the current resultset
var iRowCnt = WriteToWorksheet(reader, xlsxWriter, statusProgress, runner);
// setup Excel Autofilters
xlsxWriter.SetAutoFilter(1, 1, xlsxWriter.CurrentRowNumber, reader.FieldCount);
runner.OutputMessage(
string.Format("Query {2} Completed ({0:N0} row{1} returned)"
, iRowCnt
, iRowCnt == 1 ? "" : "s", iFileCnt)
);
runner.RowCount = iRowCnt;
moreResults = reader.NextResult();
iFileCnt++;
}
}
sw.Stop();
durationMs = sw.ElapsedMilliseconds;
runner.SetResultsMessage("Query results written to file", OutputTarget.File);
runner.ActivateOutput();
}
else
{
runner.OutputError("Query Batch Completed with errors listed above (you may need to scroll up)", durationMs);
}
}
finally
{
reader?.Dispose();
}
}
}
catch (Exception ex)
{
runner.ActivateOutput();
runner.OutputError(ex.Message);
#if DEBUG
runner.OutputError(ex.StackTrace);
#endif
}
finally
{
runner.OutputMessage("Query Batch Completed", durationMs);
runner.QueryCompleted();
}
});
}
// else dialog was cancelled so return an empty task.
await Task.Run(() => { });
}
