private XlsFileExBase InitBalanceFreeHierarchy(string balanceUn, out bool isHeaderFormed)
{
isHeaderFormed = false;
if (_balanceCalculated == null)
{
return(null);
}
BalanceFreeHierarchyCalculatedResult balanceCalculatedResult;
if (!_balanceCalculated.CalculatedValues.TryGetValue(balanceUn, out balanceCalculatedResult))
{
return(null);
}
var header = FreeHierarchyFactory.BL_GetBalanceHeader(balanceUn);
var ms = header == null ? null : header.Item2;
XlsFileExBase xls;
var decompressed = CompressUtility.DecompressGZip(ms);
if (decompressed != null)
{
isHeaderFormed = true;
try
{
xls = new XlsFileExBase(decompressed, true);
}
catch (Exception ex)
{
_errors.Append(ex.Message);
xls = new XlsFileExBase(1, TExcelFileFormat.v2010, true);
xls.OptionsPrecisionAsDisplayed = _setPercisionAsDisplayed;
}
}
else
{
xls = new XlsFileExBase(1, TExcelFileFormat.v2013, true);
xls.NewFile(1, TExcelFileFormat.v2013);
}
Init(xls);
if (balanceCalculatedResult != null)
{
if (string.IsNullOrEmpty(balanceCalculatedResult.DocumentName))
{
xls.SheetName = "Документ от " + DateTime.Today.ToShortDateString() + " " + DateTime.Now.ToShortTimeString();
}
else
{
xls.SheetName = balanceCalculatedResult.DocumentName;
}
}
xls.OptionsPrecisionAsDisplayed = _setPercisionAsDisplayed;
return(xls);
}
private void WriteSignatures(XlsFileExBase xls, List <IFreeHierarchyBalanceSignature> signatures, int boldFormat, int leftFormat)
{
if (signatures == null)
{
xls.Row = xls.Row + 9;
xls.SetCellFormat(xls.StartRow, 1, xls.Row, xls.Col, leftFormat);
return;
}
foreach (var signature in signatures.GroupBy(s => s.Группа ?? s.Группа))
{
xls.SetCellValue(xls.Row, 1, "Согласовано:", boldFormat);
xls.SetCellFormat(xls.Row, 1, xls.Row, Math.Max(xls.Col, 5), boldFormat);
xls.MergeCells(xls.Row, 1, xls.Row, 2);
xls.Row++;
xls.SetCellFormat(xls.Row, 1, xls.Row, Math.Max(xls.Col, 5), boldFormat);
foreach (var s in signature)
{
xls.Row++;
xls.SetCellValue(xls.Row, 1, s.Должность, leftFormat);
xls.MergeCells(xls.Row, 1, xls.Row, 2);
//_xls.SetCellValue(xls._row, 2, "_______________________", leftFormat);
xls.SetCellValue(xls.Row, 3, s.ФИО, leftFormat);
xls.MergeCells(xls.Row, 3, xls.Row, 5);
xls.SetCellFormat(xls.Row, 1, xls.Row, Math.Max(xls.Col, 5), leftFormat);
xls.Row++;
xls.SetCellFormat(xls.Row, 1, xls.Row, Math.Max(xls.Col, 5), leftFormat);
}
xls.Row++;
}
}
private void Init(XlsFileExBase xls)
{
if (xls.FormulasSum != null)
{
return;
}
_isFormingFormulasToCell = AdapterType == TExportExcelAdapterType.toXLS || AdapterType == TExportExcelAdapterType.toXLSx;
xls.FormulasSum = new Dictionary <string, List <FormulaRowsRange> >();
xls.SheetOptions = TSheetOptions.ShowGridLines | TSheetOptions.ShowRowAndColumnHeaders | TSheetOptions.ZeroValues | TSheetOptions.AutomaticGridLineColors | TSheetOptions.OutlineSymbols |
TSheetOptions.PageBreakView;
xls.PrintOptions &= ~(TPrintOptions.Orientation | TPrintOptions.NoPls);
xls.PrintScale = 100;
xls.PrintFirstPageNumber = 1;
//_xls.PrintXResolution = 300;
//_xls.PrintYResolution = 300;
xls.PrintPaperSize = TPaperSize.A4;
xls.PrintNumberOfHorizontalPages = 1;
xls.PrintNumberOfVerticalPages = 20;
xls.SetPrintMargins(new TXlsMargins(0.1, 0.1, 0.1, 0.1, 0.1, 0.1));
xls.SheetZoom = 100;
xls.PrintToFit = true;
xls.PrintLandscape = _printLandscape;
xls.PrintXResolution = 300;
xls.PrintYResolution = 300;
xls.Need0 = _need0;
xls.SetPercisionAsDisplayed = _setPercisionAsDisplayed;
xls.DoublePrecisionIntegral = _doublePrecisionIntegral;
xls.DoublePrecisionProfile = _doublePrecisionProfile;
var defaultFormat = xls.GetDefaultFormat;
var defaultFormatId = xls.DefaultFormatId;
defaultFormat.VAlignment = TVFlxAlignment.center;
defaultFormat.Font.Scheme = TFontScheme.None;
defaultFormat.Font.Name = "Segoe UI";
defaultFormat.Font.Size20 = DblFontZoom * 9;
defaultFormat.Font.Family = 0;
defaultFormat.Font.CharSet = 204;
defaultFormat.Font.Scheme = TFontScheme.None;
xls.SetFormat(defaultFormatId, defaultFormat);
var headersAndFooters = new THeaderAndFooter
{
AlignMargins = false,
ScaleWithDoc = true,
DiffFirstPage = false,
DiffEvenPages = false,
FirstHeader = "",
FirstFooter = "",
EvenHeader = "",
EvenFooter = ""
};
xls.SetPageHeaderAndFooter(headersAndFooters);
}
internal void AddRowRangeToFormulaSum(XlsFileExBase xls, string id, FormulaRowsRange addedRange)
{
if (!_isFormingFormulasToCell)
{
return;
}
List <FormulaRowsRange> existsRanges;
if (!xls.FormulasSum.TryGetValue(id, out existsRanges))
{
existsRanges = new List <FormulaRowsRange>();
xls.FormulasSum.Add(id, existsRanges);
}
existsRanges.Add(addedRange);
}
public ExcelAnaliser(XlsFileExBase xls, int colDrum, int colHalfHour,
int colAnalis, double halfhourToIntegralUnitCoeff,
bool isAnalisIntegral, TExportExcelAdapterType adapterType,
StringBuilder errors, int halfhourCount)
{
_colHalfHour = colHalfHour;
_colDrum = colDrum;
_xls = xls;
_colAnalis = colAnalis;
_isAnalisIntegral = isAnalisIntegral;
_errors = errors;
_halfhourToIntegralUnitCoeff = halfhourToIntegralUnitCoeff != 1 ? "/" + halfhourToIntegralUnitCoeff : "";
_halfhourCount = halfhourCount;
_isFormingFormulasToCell = adapterType == TExportExcelAdapterType.toXLS || adapterType == TExportExcelAdapterType.toXLSx;
_formulas = new Dictionary <string, List <FormulaRowsRange> >();
_footers = new Dictionary <string, string>();
}
public MemoryStream Export(XlsFileExBase xls)
{
xls.ActiveSheet = 1;
var destStream = new MemoryStream();
if (AdapterType == TExportExcelAdapterType.toXLS || AdapterType == TExportExcelAdapterType.toXLSx)
{
xls.Save(destStream, AdapterType == TExportExcelAdapterType.toXLS ? TFileFormats.Xls : TFileFormats.Xlsx);
}
if (AdapterType == TExportExcelAdapterType.toHTML)
{
var temps = new MemoryStream();
var htmlExport = new FlexCelHtmlExport(xls, true);
var strHtmlColor = ColorTranslator.ToHtml(_htmlDocBkColor);
var s = new string[1];
s[0] = "<body bgcolor=" + strHtmlColor + ">";
htmlExport.ExtraInfo.BodyStart = s;
using (var sw = new StreamWriter(temps))
{
htmlExport.Export(sw, "", null);
sw.Flush();
var b = temps.ToArray();
destStream.Write(b, 0, b.Length);
}
}
//----- PDF ---
if (AdapterType == TExportExcelAdapterType.toPDF)
{
xls.PrintLandscape = false;
using (var pdfExport = new FlexCelPdfExport(xls, true))
{
pdfExport.Export(destStream);
}
}
destStream.Position = 0;
return(destStream);
}
internal void WriteFormulaToCell(XlsFileExBase xls, string id, int row, int col, int format, double defaultValue)
{
if (_isFormingFormulasToCell)
{
var formula = new StringBuilder("=");
#region Пишем формулу из словаря
List <FormulaRowsRange> existsRanges;
if (xls.FormulasSum.TryGetValue(id, out existsRanges) && existsRanges.Count > 0)
{
foreach (var formulasRange in existsRanges)
{
formula.Append(!string.IsNullOrEmpty(formulasRange.Before) ? formulasRange.Before : "+");
if (!string.IsNullOrEmpty(formulasRange.SheetName))
{
formula.Append("'").Append(formulasRange.SheetName).Append("'!");
}
formula.Append(TCellAddress.EncodeColumn(formulasRange.Col)).Append(formulasRange.Row1);
}
//Обрабатываем последнюю запись
try
{
xls.SetCellValue(row, col, new TFormula(formula.ToString()), format);
}
catch (Exception ex)
{
_errors.Append("Ошибка формирования формулы в '" + TCellAddress.EncodeColumn(col) + row + "': " + ex.Message + "\n" + formula);
xls.SetCellValue(row, col, defaultValue, format);
}
xls.FormulasSum.Remove(id);
return;
}
#endregion
}
//Нет формулы, или не надо ее формировать
xls.SetCellValue(row, col, defaultValue, format);
}
public void Dispose()
{
_xls = null;
_errors = null;
}
private MemoryStream ФормируемАктУчетаЭэ(XlsFileExBase xls, BalanceFreeHierarchyCalculatedResult balanceCalculatedResult,
bool isHeaderFormed)
{
xls.ActiveSheet = 1;
xls.SheetName = "Report";
xls.SheetZoom = 100;
xls.StartRow = 5;
xls.StartCol = 1;
var f = xls.GetCellVisibleFormatDef(3, 6);
f.Font.Style = TFlxFontStyles.Bold;
//if (_adapterType == TExportExcelAdapterType.toHTML)
//{
// f.FillPattern.Pattern = TFlxPatternStyle.Solid;
// f.FillPattern.FgColor = Color.FromArgb(255, 244, 250, 254);
//}
var boldFormat = xls.AddFormat(f);
List <IFreeHierarchyBalanceSignature> signatures = null;
if (_signaturesByBalance != null)
{
_signaturesByBalance.TryGetValue(balanceCalculatedResult.BalanceFreeHierarchyUn, out signatures);
}
#region Шапка
if (!isHeaderFormed)
{
xls.SetCellValue(1, 3, "Акт учета (оборота) электрической энергии ");
xls.SetCellValue(2, 3, "Субъект ОРЭ: " + _branchName);
xls.SetCellValue(3, 3, "Расчетный период: " + _dtStart.ToString("dd-MM-yyyy HH:mm") + "-" + _dtEnd.ToString("dd-MM-yyyy HH:mm"));
}
else
{
//Обрабатываем наши ф-ии
SpreadsheetFunctionHelper.Evaluate(xls, this, out xls.StartRow, _errors);
}
#endregion
//f.Font.Name = "Arial Cyr";
//f.Font.Size20 = 180;
f.Font.Style = TFlxFontStyles.None;
f.VAlignment = TVFlxAlignment.center;
f.HAlignment = THFlxAlignment.center;
f.Borders.Bottom.Color = _borderColor;
f.Borders.Left.Color = _borderColor;
f.Borders.Right.Color = _borderColor;
f.Borders.Top.Color = _borderColor;
f.Borders.Bottom.Style = TFlxBorderStyle.Dotted;
f.Borders.Left.Style = TFlxBorderStyle.Dotted;
f.Borders.Right.Style = TFlxBorderStyle.Dotted;
f.Borders.Top.Style = TFlxBorderStyle.Dotted;
f.WrapText = true;
var centerFormat = xls.AddFormat(f);
f.WrapText = false;
f.HAlignment = THFlxAlignment.right;
var rightFormat = xls.AddFormat(f);
f.Font.Style = TFlxFontStyles.Bold;
var rightBoldFormat = xls.AddFormat(f);
f.Format = "### ### ### ### ##0." + new string('0', _doublePrecisionProfile);
var rightDoubleBoldFormat = xls.AddFormat(f);
f.Font.Style = TFlxFontStyles.None;
var rightDoubleFormat = xls.AddFormat(f);
f.HAlignment = THFlxAlignment.left;
f.Font.Style = TFlxFontStyles.None;
f.WrapText = true;
f.Format = "";
var leftFormat = xls.AddFormat(f);
xls.Row = xls.StartRow;
var days = new List <int>();
var hours = 0;
#region Колонка с датой временем
var prevDay = 0;
var prevRow = -1;
foreach (var dt in _dts)
{
//смена дня
if (dt.Day != prevDay)
{
if (prevRow > 0)
{
xls.MergeCells(prevRow, xls.StartCol, xls.Row, xls.StartCol);
xls.SetCellFormat(prevRow, xls.StartCol, xls.Row, xls.StartCol, centerFormat);
xls.SetCellValue(xls.Row, xls.StartCol + 1, "Итого", rightBoldFormat);
xls.Row++;
days.Add(hours);
xls.Row++;
hours = 0;
}
xls.SetCellValue(xls.Row, xls.StartCol, "Дата", centerFormat);
xls.MergeCells(xls.Row, xls.StartCol, xls.Row + 3, xls.StartCol);
xls.SetCellValue(xls.Row, xls.StartCol + 1, "Время", centerFormat);
xls.MergeCells(xls.Row, xls.StartCol + 1, xls.Row + 3, xls.StartCol + 1);
prevDay = dt.Day;
xls.Row = xls.Row + 4;
prevRow = xls.Row;
xls.SetCellValue(xls.Row, xls.StartCol, dt.ToString("dd.MM.yyyy"), rightFormat);
}
xls.SetCellValue(xls.Row, xls.StartCol + 1, dt.ToString("HH:mm-") + dt.AddMinutes(((int)_discreteType + 1) * 30).ToString("HH:mm"), rightFormat);
hours++;
xls.Row++;
}
//Последняя запись
if (prevRow > 0)
{
xls.MergeCells(prevRow, xls.StartCol, xls.Row, xls.StartCol);
xls.SetCellFormat(prevRow, xls.StartCol, xls.Row, xls.StartCol, centerFormat);
xls.SetCellValue(xls.Row, xls.StartCol + 1, "Итого", rightBoldFormat);
days.Add(hours);
}
//Итого за период
xls.Row++;
xls.SetCellValue(xls.Row, xls.StartCol, "ИТОГО за период, " + _unitDigitName, rightBoldFormat);
xls.MergeCells(xls.Row, xls.StartCol, xls.Row, xls.StartCol + 1);
#endregion
xls.Col = xls.StartCol + 2;
foreach (var itemParams in balanceCalculatedResult.ItemsParamsBySection.Values)
{
foreach (var itemParam in itemParams.OrderBy(itm => itm.SortNumber))
{
if (itemParam.HalfHours == null)
{
continue;
}
var archives = MyListConverters.ConvertHalfHoursToOtherList(_discreteType, itemParam.HalfHours, 0, _intervalTimeList);
xls.SetColWidth(xls.Col, 4364);
xls.Row = xls.StartRow;
var totalHours = 0;
var totalSum = 0.0;
//Перебираем дни
foreach (var dayHours in days)
{
xls.SetCellValue(xls.Row, xls.Col, itemParam.Name + ",\n" + _unitDigitName, centerFormat);
xls.MergeCells(xls.Row, xls.Col, xls.Row + 3, xls.Col);
xls.Row = xls.Row + 4;
var daySum = 0.0;
var row1 = xls.Row;
//Перебираем часы в этом дне
for (var hour = 0; hour < dayHours; hour++)
{
var aVal = archives.ElementAtOrDefault(totalHours + hour);
if (aVal != null)
{
daySum += aVal.F_VALUE;
xls.SetCellValue(xls.Row, xls.Col, aVal.F_VALUE, rightDoubleFormat);
}
else
{
xls.SetCellFormat(xls.Row, xls.Col, rightDoubleFormat);
}
xls.Row++;
}
//Итого
WriteRowRangeFormulaToCell(xls, "-1", xls.Row, xls.Col, rightDoubleFormat, daySum, new FormulaRowsRange {
Col = xls.Col, Row1 = row1, Row2 = xls.Row - 1
});
AddRowRangeToFormulaSum(xls, "1", new FormulaRowsRange {
Col = xls.Col, Row1 = xls.Row, Row2 = xls.Row
});
totalSum += daySum;
xls.Row++;
xls.Row++;
totalHours += dayHours;
}
//Итого за период
WriteRowRangeFormulaToCell(xls, "1", xls.Row - 1, xls.Col, rightDoubleBoldFormat, totalSum);
//_xls.SetCellValue(xls._row - 1, xls._col, totalSum, rightDoubleBoldFormat);
xls.Col++;
}
}
#region Подписанты
xls.StartRow = xls.Row;
xls.Row++;
xls.Row++;
WriteSignatures(xls, signatures, boldFormat, leftFormat);
//_xls.SetCellFormat(startRow, 1, xls._row, 9, leftFormat);
#endregion
xls.SetCellFormat(1, 1, 4, xls.Col - 1, boldFormat);
return(Export(xls));
}
private MemoryStream ФормируемПриложение51(XlsFileExBase xls, BalanceFreeHierarchyCalculatedResult balanceCalculatedResult,
bool isHeaderFormed)
{
xls.ActiveSheet = 1;
var sheetName = xls.SheetName = "Приложение";
xls.SetPrintMargins(new TXlsMargins(0.75, 1, 0.75, 1, 0.5, 0.5));
xls.SheetZoom = 100;
#region Добавление форматов
var f = xls.GetCellVisibleFormatDef(1, 4);
f.Font.Name = "Tahoma";
f.Font.Family = 2;
f.HAlignment = THFlxAlignment.center;
f.WrapText = true;
if (_doublePrecisionProfile == 0)
{
f.Format = "#,##0";
}
else
{
f.Format = "#,##0." + new string(_need0 ? '0' : '#', _doublePrecisionProfile);
}
var centerFormat = xls.AddFormat(f);
f = xls.GetCellVisibleFormatDef(4, 1);
if (_doublePrecisionProfile == 0)
{
f.Format = "#,##0";
}
else
{
f.Format = "#,##0." + new string(_need0 ? '0' : '#', _doublePrecisionProfile);
}
f.Font.Name = "Tahoma";
f.Font.Family = 2;
f.Borders.Left.Style = TFlxBorderStyle.Thin;
f.Borders.Left.Color = Color.FromArgb(0x00, 0x00, 0x00);
f.Borders.Right.Style = TFlxBorderStyle.Thin;
f.Borders.Right.Color = Color.FromArgb(0x00, 0x00, 0x00);
f.Borders.Top.Style = TFlxBorderStyle.Thin;
f.Borders.Top.Color = Color.FromArgb(0x00, 0x00, 0x00);
f.Borders.Bottom.Style = TFlxBorderStyle.Thin;
f.Borders.Bottom.Color = Color.FromArgb(0x00, 0x00, 0x00);
f.HAlignment = THFlxAlignment.center;
f.VAlignment = TVFlxAlignment.center;
f.WrapText = true;
var borderedCenterFormat = xls.AddFormat(f);
f.Font.Style = TFlxFontStyles.Bold;
var boldBorderedCenterFormat = xls.AddFormat(f);
var startRow = 18;
var startCol = 1;
#endregion
xls.ProfileFormat = xls.NoDecimalFormat = borderedCenterFormat;
List <Dict_Balance_FreeHierarchy_Section> sections = null;
if (_balanceCalculated.SectionsByType != null)
{
_balanceCalculated.SectionsByType.TryGetValue(balanceCalculatedResult.BalanceFreeHierarchyType, out sections);
}
if (!isHeaderFormed)
{
#region Шапка
xls.SetColWidth(1, 9142); //(34.96 + 0.75) * 256
xls.SetColWidth(2, 4554); //(11.96 + 0.75) * 256
xls.SetColWidth(3, 6582); //(24.96 + 0.75) * 256
xls.SetColWidth(4, 6582); //(24.96 + 0.75) * 256
xls.MergeCells(10, 1, 10, 3);
xls.MergeCells(12, 1, 12, 3);
xls.MergeCells(1, 2, 3, 3);
xls.MergeCells(4, 1, 4, 3);
xls.MergeCells(6, 1, 6, 3);
xls.MergeCells(8, 1, 8, 3);
xls.SetCellFormat(1, 1, 3, 3, centerFormat);
xls.SetCellValue(1, 2, "Приложение № 63\nк приказу Минэнерго России\nот 23 июля 2012г. №340");
xls.SetCellFormat(4, 1, 4, 3, boldBorderedCenterFormat);
//_xls.Add
//_xls.SetCellFromHtml(4, 1, "<b>121212</b> weqeeqewq <i>wwewewewe</i>");
xls.SetCellValue(4, 1,
"Показатели баланса производства и потребления электроэнергии\n" +
"и отпуска тепловой энергии по субьектам электроэнергетики\n" +
"в границах субьектов Российской Федерации\n" +
"за " + DateTime.Now.Date.Year.ToString() + " год.");
xls.SetRowHeight(4, 1000);
xls.SetCellFormat(6, 1, 6, 3, borderedCenterFormat);
xls.SetCellValue(6, 1, "КОНФИДЕНЦИАЛЬНОСТЬ ГАРАНТИРУЕТСЯ ПОЛУЧАТЕЛЕМ ИНФОРМАЦИИ");
xls.SetCellFormat(8, 1, 8, 3, borderedCenterFormat);
xls.SetCellValue(8, 1, "ВОЗМОЖНО ПРЕДСТАВЛЕНИЕ В ЭЛЕКТРОННОМ ВИДЕ");
xls.SetCellFormat(9, 1, 10, 3, borderedCenterFormat);
xls.SetCellValue(10, 1, "1. Предоставляется электростанциями генерирующих компаний и других собственников, ФГУП `Концерн " +
"Росэнергоатом`, котельными");
xls.SetRowHeight(10, 500);
xls.SetCellFormat(12, 1, 12, 3, borderedCenterFormat);
xls.SetCellValue(12, 1, balanceCalculatedResult.DocumentName.Replace("№51", "№63") + " филиал " + _branchName);
xls.SetCellFormat(13, 1, 14, 3, centerFormat);
xls.SetCellValue(14, 2, string.Format("{0:dd.MM.yyyy}", _dtEnd));
xls.SetCellValue(14, 3, _unitDigitName + ", " + _unitDigitHeatName);
#endregion
}
else
{
//Обрабатываем наши ф-ии
SpreadsheetFunctionHelper.Evaluate(xls, this, out startRow, _errors);
}
#region Заголовок таблицы
xls.SetCellFormat(startRow, 1, startRow + 2, 3, borderedCenterFormat);
xls.SetCellValue(startRow, 1, "Наименование показателя");
xls.MergeCells(startRow, 1, startRow + 1, 1);
xls.SetCellValue(startRow, 2, "Фактические значения показателя");
xls.MergeCells(startRow, 2, startRow, 3);
xls.SetRowHeight(startRow, 400);
xls.SetCellValue(startRow + 1, 2, "за сутки");
xls.SetCellValue(startRow + 1, 3, "нарастающим итогом с начала месяца");
startRow++; startRow++;
xls.SetCellValue(startRow, 1, "А");
xls.SetCellValue(startRow, 2, "1");
xls.SetCellValue(startRow, 3, "2");
startRow++;
#endregion
#region Таблица
if (_doublePrecisionProfile == 0)
{
f.Format = "#,##0";
}
else
{
f.Format = "#,##0." + new string(_need0 ? '0' : '#', _doublePrecisionProfile);
}
var boldBorderedDoubleFormat = xls.AddFormat(f);
xls.ProfileBoldFormat = boldBorderedDoubleFormat;
var sectionNumber = 1;
var row = startRow;
double potreb = 0.0, potrebDaily = 0.0;
foreach (var section in sections.OrderBy(s => s.SortNumber))
{
xls.SetCellValue(row, startCol, section.SectionName, boldBorderedCenterFormat);
xls.SetCellValue(row, startCol + 1, row - startRow + 1, boldBorderedCenterFormat);
var sectionRow = row;
List <BalanceFreeHierarchyItemParams> itemParams; //Все объекты для данного раздела
balanceCalculatedResult.ItemsParamsBySection.TryGetValue(section.Section_UN, out itemParams);
var formulaIdSumm = "summ" + section.SortNumber;
var formulaIdDailySumm = "dailySumm" + section.SortNumber;
double sectionSum = 0.0, sectionDailySum = 0.0;
var itemNumber = 1;
if (itemParams != null && itemParams.Count > 0)
{
//Перебираем объекты в разделе
foreach (var itemParam in itemParams.OrderBy(itm => itm.SortNumber))
{
TVALUES_DB latestDay = null;
var daysSumm = 0.0;
var coeff = itemParam.Coef ?? 1;
if (itemParam.HalfHours != null)
{
var archives = MyListConverters.ConvertHalfHoursToOtherList(_discreteType, itemParam.HalfHours, 0, _intervalTimeList);
latestDay = archives.LastOrDefault();
//Нарастающее с начала месяца
daysSumm = archives.Sum(itm => itm.F_VALUE);
}
//Объекты в разделе отображаем только для поступления
if (Equals(section.MetaString1, "postupilo"))
{
row++;
xls.SetCellValue(row, startCol, sectionNumber + "." + itemNumber + " " + itemParam.Name, borderedCenterFormat);
//_xls.SetCellValue(row, startCol + 1, row - startRow + 1, borderedCenterFormat);
if (latestDay != null)
{
//Факт за последние сутки
xls.SetCellFloatValue(row, startCol + 1, latestDay.F_VALUE * coeff, false);
}
else
{
xls.SetCellFormat(row, startCol + 1, borderedCenterFormat);
}
xls.SetCellFloatValue(row, startCol + 2, daysSumm * coeff, false);
AddRowRangeToFormulaSum(xls, formulaIdSumm, new FormulaRowsRange
{
Row1 = row,
Row2 = row,
Col = startCol + 1,
});
AddRowRangeToFormulaSum(xls, formulaIdDailySumm, new FormulaRowsRange
{
Row1 = row,
Row2 = row,
Col = startCol + 2,
});
itemNumber++;
}
if (latestDay != null)
{
sectionSum += latestDay.F_VALUE * coeff;
}
sectionDailySum += daysSumm * coeff;
}
}
if (Equals(section.MetaString1, "postupilo"))
{
potreb += sectionSum;
potrebDaily += sectionDailySum;
}
else if (Equals(section.MetaString1, "saldo"))
{
potreb -= sectionSum;
potrebDaily -= sectionDailySum;
}
if (Equals(section.MetaString1, "potreb"))
{
sectionSum = potreb;
sectionDailySum = potrebDaily;
}
if (!string.IsNullOrEmpty(section.MetaString1))
{
if (!Equals(section.MetaString1, "potreb"))
{
WriteFormulaToCell(xls, formulaIdSumm, sectionRow, startCol + 1, boldBorderedDoubleFormat, sectionSum);
WriteFormulaToCell(xls, formulaIdDailySumm, sectionRow, startCol + 2, boldBorderedDoubleFormat, sectionDailySum);
}
else
{
xls.SetCellFloatValue(sectionRow, startCol + 1, sectionSum, false);
xls.SetCellFloatValue(sectionRow, startCol + 2, sectionDailySum, false);
}
}
else
{
xls.SetCellFormat(sectionRow, startCol + 1, sectionRow, startCol + 2, boldBorderedDoubleFormat);
}
sectionNumber++;
row++;
}
#endregion
return(Export(xls));
}
/// <summary>
/// Пишем формулу в excel
/// </summary>
/// <param name="id">Идентификатор</param>
/// <param name="row">Ячейка</param>
/// <param name="col">Колонка</param>
/// <param name="format">Формат</param>
/// <param name="defaultValue">Значение по умолчанию, если не excel, или не найдена формула</param>
internal void WriteRowRangeFormulaToCell(XlsFileExBase xls, string id, int row, int col, int format, double defaultValue, FormulaRowsRange rowsRange = null)
{
if (_isFormingFormulasToCell)
{
var formula = new StringBuilder("=SUM(");
#region Формирование формулы
var formingFormulaAction = (Action <string, int, int, int, int?>)((sheetName, row1, row2, col1, col2) =>
{
var sheet = string.Empty;
if (!string.IsNullOrEmpty(sheetName))
{
sheet = "'" + sheetName + "'!";
}
if (row1 == row2 && !col2.HasValue)
{
formula.Append(sheet + TCellAddress.EncodeColumn(col1)).Append(row1)
.Append(",");
}
else
{
formula.Append(sheet + TCellAddress.EncodeColumn(col1)).Append(row1)
.Append(":")
.Append(sheet + TCellAddress.EncodeColumn(col2 ?? col1)).Append(row2)
.Append(",");
}
});
#endregion
#region Если пишем формулу не из словаря
if (rowsRange != null)
{
formingFormulaAction(rowsRange.SheetName, rowsRange.Row1, rowsRange.Row2, rowsRange.Col, rowsRange.Col2);
formula.Replace(",", ")", formula.Length - 1, 1); //Удаляем последний ;
xls.SetCellValue(row, col, new TFormula(formula.ToString()), format);
return;
}
#endregion
#region Пишем формулу из словаря
List <FormulaRowsRange> existsRanges;
if (xls.FormulasSum.TryGetValue(id, out existsRanges) && existsRanges.Count > 0)
{
var prevSheetName = string.Empty;
var prevCol = -1;
var prevRow1 = -1;
var prevRow2 = -1;
foreach (var formulasRange in existsRanges.OrderBy(r => r.SheetName)
.ThenBy(r => r.Row1).ThenBy(r => r.Row2))
{
//Обрабатываем предыдущую запись
if (Equals(prevSheetName, (formulasRange.SheetName ?? string.Empty)) &&
prevCol == formulasRange.Col && prevRow2 >= formulasRange.Row1 - 1)
{
//Пересекаются диапазоны, присоединяем диапазон к предыдущему
prevRow2 = formulasRange.Row2;
}
else
{
//Пишем предыдущую запись
if (prevCol > 0)
{
formingFormulaAction(prevSheetName, prevRow1, prevRow2, prevCol, null);
}
//Сохраняем текущую
prevSheetName = formulasRange.SheetName ?? string.Empty;
prevCol = formulasRange.Col;
prevRow1 = formulasRange.Row1;
prevRow2 = formulasRange.Row2;
}
}
//Обрабатываем последнюю запись
if (prevCol > 0)
{
formingFormulaAction(prevSheetName, prevRow1, prevRow2, prevCol, null);
}
formula.Replace(",", ")", formula.Length - 1, 1); //Удаляем последний ;
xls.SetCellValue(row, col, new TFormula(formula.ToString()), format);
xls.FormulasSum.Remove(id);
return;
}
#endregion
}
//Нет формулы, или не надо ее формировать
xls.SetCellValue(row, col, defaultValue, format);
}