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