private List <DbResult> LoadData(IProgressControl pc)
{
pc.SetStatus("Инициализация запроса...");
var dbQuery = new DbQuery("");
dbQuery.Sql = sqlQuery;
return(conn.GetResults(dbQuery, pc));
}
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);
}
// Данные
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++;
}
}
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++;
}
}
