public List <DbResult> SelectSimple(
Connection conn, IProgressControl pc,
params string[] localParameterValues)
{
string savedInnerSql = innerSQL;
List <DbResult> results = null;
var q = new DbQuery(name);
if (localParameterValues != null && localParameterNames != null)
{
if (localParameterNames.Count == localParameterValues.Length)
{
SetLocalParameterValues(localParameterValues);
}
else if (localParameterNames.Count == 1)
{
SetParameter(localParameterNames[0],
string.Join(",", localParameterValues).Trim('"'), true);
}
else
{
return(null);
}
}
q.Sql = innerSQL;
results = conn.GetResults(q, pc);
innerSQL = savedInnerSql;
return(results);
}
private List <DbResult> LoadData(IProgressControl pc)
{
pc.SetStatus("Инициализация запроса...");
var dbQuery = new DbQuery("");
dbQuery.Sql = sqlQuery;
return(conn.GetResults(dbQuery, pc));
}
protected void LongOperation_Completed(object sender, LongOperationCompletedEventArgs e) {
progressControl.Dispose();
progressControl = null;
((DevExpress.ExpressApp.Demos.LongOperation)sender).CancellingTimeoutExpired -= LongOperation_CancellingTimeoutExpired;
((DevExpress.ExpressApp.Demos.LongOperation)sender).Completed -= LongOperation_Completed;
((DevExpress.ExpressApp.Demos.LongOperation)sender).Dispose();
waitLongOperationCompleted.PostOperationCompleted(WorkCompleted, null);
waitLongOperationCompleted = null;
}
private void LongOperation_Completed(object sender, LongOperationCompletedEventArgs e)
{
progressControl.Dispose();
progressControl = null;
((LongOperation)sender).CancellingTimeoutExpired -= new EventHandler(LongOperation_CancellingTimeoutExpired);
((LongOperation)sender).Completed -= new EventHandler <LongOperationCompletedEventArgs>(LongOperation_Completed);
((LongOperation)sender).Dispose();
waitLongOperationCompleted.PostOperationCompleted(WorkCompleted, null);
waitLongOperationCompleted = null;
}
protected void LongOperation_Completed(object sender, LongOperationCompletedEventArgs e)
{
_ProgressControl.Dispose();
_ProgressControl = null;
((DevExpress.ExpressApp.Demos.LongOperation)sender).CancellingTimeoutExpired -= LongOperation_CancellingTimeoutExpired;
((DevExpress.ExpressApp.Demos.LongOperation)sender).Completed -= LongOperation_Completed;
((DevExpress.ExpressApp.Demos.LongOperation)sender).Dispose();
_WaitLongOperationCompleted.PostOperationCompleted(WorkCompleted, null);
_WaitLongOperationCompleted = null;
}
protected void StartLongOperation(LongOperation longOperation)
{
//waitLongOperationCompleted = AsyncOperationManager.CreateOperation(null);
longOperation.CancellingTimeoutMilliSeconds = 10000;
longOperation.CancellingTimeoutExpired += new EventHandler(LongOperation_CancellingTimeoutExpired);
longOperation.Completed += new EventHandler <LongOperationCompletedEventArgs>(LongOperation_Completed);
progressControl = CreateProgressControl();
progressControl.ShowProgress(longOperation);
longOperation.StartAsync();
OnOperationStarted();
}
protected void StartLongOperation(List<string> strings) {
waitLongOperationCompleted = AsyncOperationManager.CreateOperation(null);
var longOperation = new DevExpress.ExpressApp.Demos.LongOperation(DoWork) { CancellingTimeoutMilliSeconds = 2000 };
longOperation.CancellingTimeoutExpired += LongOperation_CancellingTimeoutExpired;
longOperation.Completed += LongOperation_Completed;
ChangedProps = strings;
progressControl = CreateProgressControl();
progressControl.ShowProgress(longOperation);
longOperation.StartAsync();
OnOperationStarted();
}
protected void StartLongOperation(List <string> strings)
{
_WaitLongOperationCompleted = AsyncOperationManager.CreateOperation(null);
var longOperation = new DevExpress.ExpressApp.Demos.LongOperation(DoWork)
{
CancellingTimeoutMilliSeconds = 2000
};
longOperation.CancellingTimeoutExpired += LongOperation_CancellingTimeoutExpired;
longOperation.Completed += LongOperation_Completed;
ChangedProps = strings;
_ProgressControl = CreateProgressControl();
_ProgressControl.ShowProgress(longOperation);
longOperation.StartAsync();
OnOperationStarted();
}
public List <DbResult> GetResults(DbQuery query, IProgressControl pc = null)
{
var results = new List <DbResult>();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query.Sql;
if (pc != null)
{
int total = GetCountObjects(query.Sql);
pc.SetMaximum(total);
pc.SetStatus("Идет выполнение запроса...");
}
int index = 0;
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var res = new DbResult();
for (int i = 0; i < reader.FieldCount; i++)
{
try
{
res.Fields.Add(reader[i]);
res.FieldNames.Add(reader.GetName(i));
}
catch (Exception ex)
{
Dispose();
throw ex;
}
}
index++;
if (pc != null)
{
pc.SetProgress(index);
}
results.Add(res);
}
}
return(results);
}
/// <inheritdoc/>
public virtual Tuple <string, IWorkbook> Execute(IProgressControl pc)
{
try
{
// Инициализация временных данных(таблиц) для запросов
InitTempQueryData();
foreach (IWorksheet sheet in workBook.Worksheets)
{
if (sheet.UsedCells.Length != 0)
{
ProcessWorkSheet(pc, sheet);
}
}
}
catch (Exception ex)
{
return(new Tuple <string, IWorkbook>(ex.Message, null));
}
return(new Tuple <string, IWorkbook>(null, workBook));
}
// Данные
private void FillTable(IProgressControl pc, IWorksheet sheet, int startRowIndex, List <DbResult> dbData)
{
pc.SetStatus("Обработка данных...");
pc.SetProgress(0);
int progress = 0;
int rowIndex = startRowIndex;
foreach (DbResult dbResult in dbData)
{
int j = 1;
foreach (var obj in dbResult.Fields)
{
string colName = GetCachedColumnName(j);
string index = string.Format("{0}{1}", colName, rowIndex);
sheet[index].Value2 = obj.ToString() == "$id" ? rowIndex - startRowIndex + 1 : obj;
sheet[index].BorderAround(ExcelLineStyle.Thin);
j++;
}
pc.SetProgress(progress++);
rowIndex++;
}
}
/// <inheritdoc/>
public override Tuple <string, IWorkbook> Execute(IProgressControl pc)
{
if (fields != null && fields.Count != 0)
{
List <DbResult> dbData = null;
try
{
dbData = LoadData(pc);
}
catch (Exception ex)
{
if (ex.GetType().FullName == "Npgsql.NpgsqlException")
{
return(new Tuple <string, IWorkbook>(sqlQuery + Environment.NewLine + ex.Message, null));
}
else
{
throw ex;
}
}
IWorksheet sheet = PrepareWorkSheet();
SetWorkSheetHeader(sheet);
int rowIndex = SetWorkSheetParameters(sheet);
SetColumnNames(sheet, ++rowIndex);
FillTable(pc, sheet, ++rowIndex, dbData);
// Auto Column Width
sheet.UsedRange.AutofitColumns();
}
else
{
base.Execute(pc);
}
return(new Tuple <string, IWorkbook>(null, workBook));
}
private int ProcessWorkSheetList(
IProgressControl pc, IWorksheet sheet, T query,
params string[] localParameterValues)
{
pc.SetStatus("Инициализация запроса...");
List <DbResult> results = query.SelectSimple(conn, pc, localParameterValues);
pc.SetStatus("Идет обработка данных...");
int firstColNum = 0;
int firstRowNum = 0;
foreach (var cell in sheet.UsedCells)
{
string value = cell.Value;
if (string.IsNullOrEmpty(value))
{
continue;
}
if (value.Equals(string.Format("#{0}", query.Name)) ||
value.IndexOf(string.Format("#{0}(", query.Name)) >= 0)
{
firstRowNum = cell.LastRow;
break;
}
else if (cell.Value.StartsWith("#"))
{
// Установка параметров в отчете
ReplaceWithParameterValues(cell);
}
}
string xlFieldValue;
Dictionary <int, string> fieldPositions = new Dictionary <int, string>();
int j = 0;
while (j < sheet.Rows[firstRowNum - 1].Columns.Length &&
sheet.Rows[firstRowNum - 1].Columns[j].Value != string.Empty)
{
fieldPositions[j] = sheet.Rows[firstRowNum - 1].Columns[j].Value;
j++;
}
firstColNum = j;
sheet.DeleteRow(firstRowNum);
if (results.Count != 0)
{
sheet.InsertRow(firstRowNum, results.Count);
}
Func <DbResult, int, int, object> GetValue = (dbResult, xlRowNum, xlColNum) =>
{
fieldPositions.TryGetValue(xlColNum, out xlFieldValue);
// В ячейке формула
if (xlFieldValue.StartsWith("="))
{
return(EvaluateFormula(xlFieldValue, xlRowNum));
}
if (xlFieldValue.StartsWith("#") && !xlFieldValue.Equals(string.Format("#{0}", query.Name)))
{
// В ячейке находится скалярная функция (запрос)
var funcQuery = FindQuery(xlFieldValue);
if (funcQuery != null)
{
return(funcQuery.ExecuteScalarSQL(conn,
query.FieldNames,
dbResult.Fields,
ExtractParameterValues(xlFieldValue)));
}
}
string searchFormat = xlColNum == 0 ? "#{0}" : "#:{0}:";
int index = query.FieldNames.FindIndex(fn =>
xlFieldValue.Equals(string.Format(searchFormat, fn)));
if (index < 0)
{
return(xlFieldValue);
}
if (dbResult.Fields[index].Equals("$id"))
{
return(xlRowNum - firstRowNum + 2);
}
return(dbResult.Fields[index]);
};
pc.SetProgress(0);
int i = firstRowNum - 1;
int progress = 0;
foreach (DbResult result in results)
{
for (j = 0; j < firstColNum; j++)
{
sheet.Rows[i].Columns[j].Value2 = GetValue(result, i, j);
sheet.Rows[i].Columns[j].BorderAround(ExcelLineStyle.Thin);
}
pc.SetProgress(progress++);
i++;
}
for (j = 0; j < firstColNum; j++)
{
if (sheet.Rows[i].Columns[j].Value.StartsWith("=SUM", StringComparison.InvariantCultureIgnoreCase))
{
string addressLocal = sheet.Rows[i].Columns[j].AddressLocal;
string column = GetColumn(addressLocal);
sheet.Rows[i].Columns[j].Formula = string.Format("=SUM({0}{1}:{2}{3})",
column, firstRowNum, column, i);
}
}
// Количество новых ячеек
return(results.Count * query.FieldNames.Count);
}
private void ProcessWorkSheet(IProgressControl pc, IWorksheet sheet)
{
int progress = 0;
pc.SetStatus(string.Format("Обработка данных... ({0})", sheet.Name));
if (!queries.Any(q => q.FieldNames != null && q.FieldNames.Count > 1))
{
pc.SetMaximum(sheet.UsedCells.Length);
}
int i = 0;
while (i < sheet.UsedCells.Count())
{
IRange cell = sheet.UsedCells[i]; string cellValue = cell.Value;
if (!string.IsNullOrEmpty(cellValue) && cellValue.StartsWith("#"))
{
if (cellValue.StartsWith("#="))
{
cell.Value2 = EvaluateFormula(cell.Column, cell.Row, cellValue);
}
var query = FindQuery(cellValue);
if (query == null)
{
ReplaceWithParameterValues(cell); i++;
continue;
}
// Дополнительные параметры
string[] localParVals = ExtractParameterValues(cellValue);
if (query.NonQuery)
{
query.ExecuteNonQuery(conn);
cell.Value2 = null;
}
else if (query.IsEmpty)
{
cell.Value2 = query.EmptyValue;
}
else if (query.IsScalar)
{
cell.Value2 = query.ExecuteScalarSQL(conn, null, null, localParVals);
}
else if (query.FieldNames.Count == 0)
{
// sql: select (...) as f1, (...) as f2, (...) as f3,
var dbResults = query.SelectSimple(conn, null, localParVals);
var dbResult = dbResults.FirstOrDefault();
cell.Value2 = dbResult != null ? dbResult.Fields[0] : null;
for (int j = 1; j < dbResult.FieldNames.Count; j++)
{
paramValues[string.Format(":{0}:", dbResult.FieldNames[j])] = new Tuple <string, object>(Utils.ToString(dbResult.Fields[j]), null);
}
}
else
{
// Иначе списочный тип
i += ProcessWorkSheetList(pc, sheet, query, localParVals);
}
}
if (!queries.Any(q => q.FieldNames != null && q.FieldNames.Count > 1))
{
pc.SetProgress(progress++);
}
i++;
}
}
public static void ThrowIfCancellationRequested <T>(this IProgressControl <T>?Control) => Control?.Cancel.ThrowIfCancellationRequested();
public static void Report <T>(this IProgressControl <T>?Control, T Value) => Control?.Progress?.Report(Value);
