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++; } }