private static void FetchChunk(string expression, int start, int chunkSize, ExcelData xlData, int totalRows, int totalCols) {
IGridData<string> data =
GridDataSource.GetGridDataAsync(expression,
new GridRange(new Range(start, chunkSize),
new Range(0, totalCols))).Result;
if (data != null) {
if (data.RowHeader != null) {
if (xlData.RowNames == null) {
xlData.RowNames = new object[totalRows];
}
for (int r = 0; r < chunkSize && r < totalRows - start; r++) {
xlData.RowNames[r + start] = data.RowHeader[r + start];
}
}
if (data.ColumnHeader != null && xlData.ColNames == null) {
xlData.ColNames = new object[totalCols];
for (int c = 0; c < totalCols; c++) {
xlData.ColNames[c] = data.ColumnHeader[c];
}
}
for (int r = 0; r < chunkSize && r < totalRows - start; r++) {
for (int c = 0; c < totalCols; c++) {
xlData.CellData[r + start, c] = data.Grid[r + start, c];
}
}
}
}
public static ExcelData GenerateExcelData(string expression, int rows, int cols) {
if (rows <= 0 || cols <= 0) {
return null;
}
try {
ExcelData xlData = new ExcelData();
xlData.CellData = new object[rows, cols];
int chunkSize = 1000;
int steps = (rows + chunkSize - 1) / chunkSize;
List<LongAction> actions = new List<LongAction>();
for (int i = 0; i < steps; i++) {
actions.Add(
new LongAction() {
Data = i,
Name = Resources.Progress_PreparingExcelData,
Action = (o) => FetchChunk(expression, ((int)o) * chunkSize, chunkSize, xlData, rows, cols)
}
);
}
if (LongOperationNotification.ShowWaitingPopup(Resources.Progress_PreparingExcelData, actions)) {
if (xlData.CellData[0, 0] == null) {
return null;
}
return xlData;
}
} catch (Exception ex) when (!ex.IsCriticalException()) {
VsAppShell.Current.ShowErrorMessage(Resources.Error_ExcelCannotEvaluateExpression);
GeneralLog.Write(ex);
}
return null;
}
public static ExcelData GenerateExcelData(string expression, int rows, int cols)
{
if (rows <= 0 || cols <= 0)
{
return(null);
}
try {
ExcelData xlData = new ExcelData();
xlData.CellData = new object[rows, cols];
int chunkSize = 1000;
int steps = (rows + chunkSize - 1) / chunkSize;
List <LongAction> actions = new List <LongAction>();
for (int i = 0; i < steps; i++)
{
actions.Add(
new LongAction()
{
Data = i,
Name = Resources.Progress_PreparingExcelData,
Action = (o) => FetchChunk(expression, ((int)o) * chunkSize, chunkSize, xlData, rows, cols)
}
);
}
if (LongOperationNotification.ShowWaitingPopup(Resources.Progress_PreparingExcelData, actions))
{
if (xlData.CellData[0, 0] == null)
{
return(null);
}
return(xlData);
}
} catch (Exception ex) when(!ex.IsCriticalException())
{
VsAppShell.Current.ShowErrorMessage(Resources.Error_ExcelCannotEvaluateExpression);
GeneralLog.Write(ex);
}
return(null);
}
public static void OpenDataInExcel(string variableName, string expression, int rows, int cols)
{
ExcelData xlData = GenerateExcelData(expression, Math.Min(10000, rows), Math.Min(10000, cols));
if (xlData != null)
{
VsAppShell.Current.DispatchOnUIThread(() => {
Workbook wb = RunExcel();
if (wb != null)
{
// Try finding existing worksheet first
Worksheet ws = GetOrCreateWorksheet(wb, variableName);
try {
PopulateWorksheet(ws, xlData.RowNames, xlData.ColNames, xlData.CellData);
_excel.Visible = true;
}
catch (COMException) { }
}
});
}
}
private static void FetchChunk(string expression, int start, int chunkSize, ExcelData xlData, int totalRows, int totalCols)
{
IGridData <string> data =
GridDataSource.GetGridDataAsync(expression,
new GridRange(new Range(start, Math.Min(chunkSize, totalRows - start)),
new Range(0, totalCols))).Result;
if (data != null)
{
if (data.RowHeader != null)
{
if (xlData.RowNames == null)
{
xlData.RowNames = new object[totalRows];
}
for (int r = 0; r < chunkSize && r < totalRows - start; r++)
{
xlData.RowNames[r + start] = data.RowHeader[r + start];
}
}
if (data.ColumnHeader != null && xlData.ColNames == null)
{
xlData.ColNames = new object[totalCols];
for (int c = 0; c < totalCols; c++)
{
xlData.ColNames[c] = data.ColumnHeader[c];
}
}
for (int r = 0; r < chunkSize && r < totalRows - start; r++)
{
for (int c = 0; c < totalCols; c++)
{
xlData.CellData[r + start, c] = data.Grid[r + start, c];
}
}
}
}