internal void Import(XmlReader reader)
{
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
switch (xa.LocalName)
{
case "Color":
{
this.Color = XmlStyle.ExcelFormatToColor(xa.Value);
break;
}
case "PatternColor":
{
this.PatternColor = XmlStyle.ExcelFormatToColor(xa.Value);
break;
}
case "Pattern":
{
this.Pattern = ObjectExtensions.ParseEnum <Pattern>(xa.Value);
break;
}
}
}
}
internal void CreateRange(int row, int column, object value, XmlStyle style)
{
sheet[column - 1, row - 1].Value = value;
Range r = new Range(sheet[column - 1, row - 1], sheet[column - 1, row - 1]);
r.Style = style;
}
public override void CreateHeader()
{
CreateRange(1, 1, 1, 1, String.Format("Экспортировано: {0}", DateTime.Now));
XmlStyle titleStyle = new XmlStyle();
titleStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Center, Vertical = VerticalAlignment.Center, WrapText = true
};
titleStyle.Font.Size = 14;
CreateRange(2, 1, 1, 15,
String.Format("Список объектов, оборудованных СДСП, в {0}", this.exportInfo.ElementName),
titleStyle);
XmlStyle tableHeaderStyle = new XmlStyle();
tableHeaderStyle.Font.Bold = true;
tableHeaderStyle.Font.Size = 12;
tableHeaderStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Center, Vertical = VerticalAlignment.Center, WrapText = true
};
tableHeaderStyle.Border.LineStyle = Borderline.Continuous;
tableHeaderStyle.Border.Sides = BorderSides.All;
tableHeaderStyle.Border.Weight = 1;
tableHeaderStyle.Interior.Color = System.Drawing.Color.Azure;
CreateRange(4, 2, 1, 4, "Модем");
CreateRange(4, 6, 1, 5, "Объект");
CreateRange(4, 11, 1, 5, "Счётчик");
CreateRange(4, 1, 2, 1, "№ п/п");
CreateRange(5, 2, 1, 1, "Статус");
CreateRange(5, 3, 1, 1, "УСПД");
CreateRange(5, 4, 1, 1, "Сетевой адрес");
CreateRange(5, 5, 1, 1, "№ GSM");
CreateRange(5, 6, 1, 1, "Адрес объекта");
CreateRange(5, 7, 1, 1, "№ договора");
CreateRange(5, 8, 1, 1, "Наименование абонента");
CreateRange(5, 9, 1, 1, "Объект учета");
CreateRange(5, 10, 1, 1, "Номер ТП");
CreateRange(5, 11, 1, 1, "Статус счётчика");
CreateRange(5, 12, 1, 1, "Присоединение");
CreateRange(5, 13, 1, 1, "Тип");
CreateRange(5, 14, 1, 1, "Сетевой адрес");
CreateRange(5, 15, 1, 1, "Заводской номер");
ChangeRangeStyle(4, 1, 5, 15, tableHeaderStyle);
}
internal object GetCellStyleProperty(StylePropertyAccessor getDelegate)
{
if (this.GetParentBook() == null)
{
return(getDelegate(this.Parent.FirstCell()));
}
XmlStyle style = this.GetParentBook().GetStyleByID(this.Parent.StyleID);
return(getDelegate(style));
}
private void ImportStyles(XmlReader reader)
{
while (reader.Read() && !(reader.Name == "Styles" && reader.NodeType == XmlNodeType.EndElement))
{
XmlStyle style = XmlStyle.Import(reader);
if (style != null)
{
this.Styles.Add(style);
}
}
}
internal void SetCellStyleProperty(StylePropertyAccessor setDelegate)
{
if (this.GetParentBook() == null)
{
this.Parent.IterateAndApply(cell => setDelegate(cell));
}
else
{
XmlStyle style = new XmlStyle(this.GetParentBook().GetStyleByID(this.Parent.StyleID));
setDelegate(style);
this.Parent.StyleID = this.GetParentBook().AddStyle(style);
}
}
public static void SaveProductsToXml(string filename)
{
var book = new ExcelXmlWorkbook {
Properties = { Author = "AdvantShop.Net", LastAuthor = "AdvantShop.Net" }
};
//-----------------------------------------------
// Properties
//-----------------------------------------------
var style = new XmlStyle {
Font = { Name = "Calibri", Size = 11 }
};
book.DefaultStyle = style;
GenerateProductWorksheet(book[0], ProductService.GetProducts());
book.Export(filename);
}
private void SetStyle(XmlStyle style)
{
switch (style)
{
case XmlStyle.Formatted:
FormatAsXML();
break;
case XmlStyle.Html:
FormatAsHtml();
break;
case XmlStyle.Esc:
FormatAsEsc();
break;
case XmlStyle.Mini:
FormatAsMini();
break;
}
}
internal void Export(XmlWriter writer)
{
// Font
writer.WriteStartElement("Font");
writer.WriteAttributeString("ss", "FontName", null, this.Name);
if (this.Size != 0)
{
writer.WriteAttributeString("ss", "Size", null, this.Size.ToString(
CultureInfo.InvariantCulture));
}
// Color
writer.WriteAttributeString("ss", "Color", null, XmlStyle.ColorToExcelFormat(this.Color));
// Bold?
if (this.Bold)
{
writer.WriteAttributeString("ss", "Bold", null, "1");
}
// Italic?
if (this.Italic)
{
writer.WriteAttributeString("ss", "Italic", null, "1");
}
// Underline?
if (this.Underline)
{
writer.WriteAttributeString("ss", "Underline", null, "Single");
}
if (this.Strikeout)
{
writer.WriteAttributeString("ss", "Strikeout", null, "1");
}
// Font end
writer.WriteEndElement();
}
internal void Export(XmlWriter writer)
{
if (this.Color != Color.Empty || this.PatternColor != Color.Empty)
{
// Interior start
writer.WriteStartElement("Interior");
if (this.Color != Color.Empty)
{
writer.WriteAttributeString("ss", "Color", null, XmlStyle.ColorToExcelFormat(this.Color));
}
if (this.PatternColor != Color.Empty)
{
writer.WriteAttributeString("ss", "PatternColor", null, XmlStyle.ColorToExcelFormat(this.PatternColor));
}
writer.WriteAttributeString("ss", "Pattern", null, this.Pattern.ToString());
// Interior end
writer.WriteEndElement();
}
}
public ExportadorXls()
{
Normal = new XmlStyle();
Destaque = new XmlStyle();
Destaque.Font.Bold = true;
}
public override void CreateBody()
{
// создание стилей ячеек
XmlStyle textStyle = new XmlStyle();
textStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Left, Vertical = VerticalAlignment.Center, WrapText = true
};
textStyle.Border.LineStyle = Borderline.Continuous;
textStyle.Border.Sides = BorderSides.All;
textStyle.Border.Weight = 1;
textStyle.Font.Size = 12;
textStyle.Font.Name = "Calibri";
XmlStyle numbersStyle = new XmlStyle(textStyle);
numbersStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Center, Vertical = VerticalAlignment.Center, WrapText = true
};
numbersStyle.Font.Name = "Century Gothic";
XmlStyle valuesStyle = new XmlStyle(numbersStyle);
valuesStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Right, Vertical = VerticalAlignment.Center, WrapText = false
};
valuesStyle.DisplayFormat = DisplayFormatType.Custom;
valuesStyle.CustomFormatString = "# ##0";
XmlStyle highlightValuesStyle = new XmlStyle(valuesStyle);
highlightValuesStyle.Font.Bold = true;
highlightValuesStyle.Interior.Color = System.Drawing.Color.LightGray;
int rowCount = 0;
foreach (var item in this.collectors)
{
rowCount += item.CountersCount;
}
const int startRow = 7;
int rowIndex = startRow;
int collectorIndex = 0;
int columnIndex, oldObjectColumnIndex, countersCount, objectRowSpan = 1, oldRowIndex, oldCounterColumnIndex, collectorRowSpan, counterRowSpan;
bool isCounterMultiTariff, hasTarifDifference;
XmlStyle _valueStyle;
Range r;
foreach (Collector collector in this.collectors)
{
columnIndex = 1;
oldRowIndex = rowIndex;
collectorRowSpan = collector.Objects.Sum(o => o.Counters.Sum(c => c.TarifsCount == 1 ? c.HasDifferenceBetweenT1AndT0 ? 2 : 1 : c.TarifsCount + 1));
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collectorIndex + 1, numbersStyle);
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collector.IsAnswered == true ? "OK" : "не отвечает", textStyle);
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collector.IsUSPD == true ? "УСПД" : string.Empty, textStyle);
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collector.NetworkAddress, numbersStyle);
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collector.PhoneNumber, numbersStyle);
oldObjectColumnIndex = columnIndex;
for (int objectIndex = 0; objectIndex < collector.Objects.Count; objectIndex++)
{
AccountingObject aobject = collector.Objects[objectIndex];
countersCount = aobject.CountersCount;
objectRowSpan = aobject.Counters.Sum(c => c.TarifsCount == 1 ? c.HasDifferenceBetweenT1AndT0 ? 2 : 1 : c.TarifsCount + 1);
columnIndex = oldObjectColumnIndex;
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.City, textStyle);
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.Contract, textStyle);
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.Subscriber, textStyle);
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.Name, textStyle);
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.Tp, textStyle);
isCounterMultiTariff = false;
oldCounterColumnIndex = columnIndex;
for (int counterIndex = 0; counterIndex < countersCount; counterIndex++)
{
Counter counter = aobject.Counters[counterIndex];
isCounterMultiTariff = aobject.Counters[counterIndex].TarifsCount != 1;
counterRowSpan = isCounterMultiTariff ? aobject.Counters[counterIndex].TarifsCount + 1 : counter.HasDifferenceBetweenT1AndT0 ? 2 : 1;
hasTarifDifference = counter.HasDifferenceBetweenT1AndT0;
_valueStyle = counter.MissingPersonalAccount || hasTarifDifference ? highlightValuesStyle : valuesStyle;
columnIndex = oldCounterColumnIndex;
CreateRange(rowIndex, columnIndex++, counterRowSpan, 1, counter.IsAnswered == true ? "OK" : "не отвечает", textStyle);
CreateRange(rowIndex, columnIndex++, counterRowSpan, 1, counter.AccountingPoint, textStyle);
CreateRange(rowIndex, columnIndex++, counterRowSpan, 1, counter.CounterType, textStyle);
CreateRange(rowIndex, columnIndex++, counterRowSpan, 1, counter.CounterNetworkAdress, numbersStyle);
CreateRange(rowIndex, columnIndex++, counterRowSpan, 1, counter.SerialNumber, numbersStyle);
// тариф
if (isCounterMultiTariff)
// счётчик многотарифный
{
// TƩ
CreateRange(rowIndex, columnIndex++, 1, 1, "TƩ", textStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.PreviousIndications.Tarriff0), _valueStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.NextIndications.Tarriff0), _valueStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.Difference), _valueStyle);
rowIndex++;
for (int i = 1; i <= aobject.Counters[counterIndex].TarifsCount; i++)
{
columnIndex = oldCounterColumnIndex + 5;
CreateRange(rowIndex, columnIndex++, 1, 1, "T" + i.ToString(), textStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.PreviousIndications[i]), _valueStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.NextIndications[i]), _valueStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.Differences(i)), _valueStyle);
rowIndex++;
}
}
// однотарифный
else
{
CreateRange(rowIndex, columnIndex++, 1, 1, "TƩ", textStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.PreviousIndication), _valueStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.NextIndication), _valueStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.Difference), _valueStyle);
rowIndex++;
if (hasTarifDifference)
{
// T1
columnIndex = oldCounterColumnIndex + 5;
CreateRange(rowIndex, columnIndex++, 1, 1, "T1", textStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.PreviousIndications.Tarriff1), _valueStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.NextIndications.Tarriff1), _valueStyle);
CreateRange(rowIndex, columnIndex++, 1, 1, GetIndication(counter.DifferenceT1), _valueStyle);
rowIndex++;
}
}
}
}
collectorIndex++;
}
}
public override void CreateHeader()
{
CreateRange(1, 1, 1, 1, String.Format("Экспортировано: {0}", DateTime.Now));
XmlStyle titleStyle = new XmlStyle();
titleStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Center, Vertical = VerticalAlignment.Center, WrapText = true
};
titleStyle.Font.Size = 14;
string paramName = string.Empty;
switch (this.exportInfo.Param)
{
case ProfileType.Current:
paramName = "параметр: текущие показания";
break;
case ProfileType.Days:
paramName = "параметр: показания на начало суток";
break;
case ProfileType.Months:
paramName = "параметр: показания на начало месяца";
break;
}
CreateRange(2, 1, 1, 19,
String.Format("Отчет по результатам опроса объектов, оборудованных СДСП, у которых показание по Т1 не совпадает с Тсумма, в {0}", this.exportInfo.ElementName),
titleStyle);
CreateRange(3, 1, 1, 19, paramName, titleStyle);
XmlStyle tableHeaderStyle = new XmlStyle();
tableHeaderStyle.Font.Bold = true;
tableHeaderStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Center, Vertical = VerticalAlignment.Center, WrapText = true
};
tableHeaderStyle.Border.LineStyle = Borderline.Continuous;
tableHeaderStyle.Border.Sides = BorderSides.All;
tableHeaderStyle.Border.Weight = 1;
tableHeaderStyle.Interior.Color = System.Drawing.Color.Azure;
CreateRange(4, 2, 1, 9, "Модем");
CreateRange(4, 11, 1, 9, "Счётчик");
CreateRange(5, 17, 1, 3, "Показания");
CreateRange(4, 1, 3, 1, "№ п/п");
CreateRange(5, 2, 2, 1, "Статус");
CreateRange(5, 3, 2, 1, "УСПД");
CreateRange(5, 4, 2, 1, "Сетевой адрес");
CreateRange(5, 5, 2, 1, "№ GSM");
CreateRange(5, 6, 2, 1, "Адрес объекта");
CreateRange(5, 7, 2, 1, "№ договора");
CreateRange(5, 8, 2, 1, "Наименование абонента");
CreateRange(5, 9, 2, 1, "Объект учета");
CreateRange(5, 10, 2, 1, "Номер ТП");
CreateRange(5, 11, 2, 1, "Статус счётчика");
CreateRange(5, 12, 2, 1, "Присоединение");
CreateRange(5, 13, 2, 1, "Тип");
CreateRange(5, 14, 2, 1, "Сетевой адрес");
CreateRange(5, 15, 2, 1, "Заводской номер");
CreateRange(5, 16, 2, 1, "Тариф");
CreateRange(6, 17, 1, 1, String.Format("предыдущее\r\n({0})", this.exportInfo.StartDate.ToString(this.exportInfo.StartDateFormat)));
CreateRange(6, 18, 1, 1, String.Format("следующее\r\n({0})", this.exportInfo.EndDate.ToString(this.exportInfo.EndDateFormat)));
CreateRange(6, 19, 1, 1, "разность");
ChangeRangeStyle(4, 1, 4, 19, tableHeaderStyle);
ChangeRangeStyle(5, 2, 5, 16, tableHeaderStyle);
ChangeRangeStyle(5, 17, 5, 19, tableHeaderStyle);
ChangeRangeStyle(6, 17, 6, 19, tableHeaderStyle);
}
internal void Import(XmlReader reader)
{
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
switch (xa.LocalName)
{
case "FontName":
{
this.Name = xa.Value;
break;
}
case "Size":
{
int i;
if (xa.Value.ParseToInt(out i))
{
this.Size = i;
}
break;
}
case "Color":
{
this.Color = XmlStyle.ExcelFormatToColor(xa.Value);
break;
}
case "Bold":
{
this.Bold = xa.Value == "1" ? true : false;
break;
}
case "Italic":
{
this.Italic = xa.Value == "1" ? true : false;
break;
}
case "Underline":
{
this.Underline = xa.Value == "Single" ? true : false;
break;
}
case "Strikeout":
{
this.Strikeout = xa.Value == "1" ? true : false;
break;
}
}
}
}
private void ImportCell(XmlReader reader, Row row)
{
bool isEmpty = reader.IsEmptyElement;
int cellIndex = row.Cells.Count;
int mergeDown = 0;
int mergeAcross = 0;
XmlStyle style = null;
string formula = string.Empty;
string reference = string.Empty;
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
if (xa.LocalName == "Index" && xa.HasValue)
{
xa.Value.ParseToInt(out cellIndex);
cellIndex--;
}
if (xa.LocalName == "StyleID" && xa.HasValue)
{
style = this.ParentBook.GetStyleByID(xa.Value);
}
if (xa.LocalName == "HRef" && xa.HasValue)
{
reference = xa.Value;
}
if (xa.LocalName == "Formula" && xa.HasValue)
{
formula = xa.Value;
}
if (xa.LocalName == "MergeAcross" && xa.HasValue)
{
xa.Value.ParseToInt(out mergeAcross);
}
if (xa.LocalName == "MergeDown" && xa.HasValue)
{
xa.Value.ParseToInt(out mergeDown);
}
}
Cell cell = this.Cells(cellIndex, row.RowIndex);
if (style != null)
{
cell.Style = style;
}
if (!reference.IsNullOrEmpty())
{
cell.HRef = reference;
}
if (!formula.IsNullOrEmpty())
{
FormulaParser.Parse(cell, formula);
return;
}
if (isEmpty)
{
return;
}
if (mergeDown > 0 || mergeAcross > 0)
{
cell.MergeStart = true;
Range range = new Range(cell, this.Cells(cellIndex + mergeAcross, row.RowIndex + mergeDown));
this.MergedCells.Add(range);
cell.ColumnSpan = range.ColumnCount;
cell.RowSpan = range.RowCount;
}
while (reader.Read() && !(reader.Name == "Cell" && reader.NodeType == XmlNodeType.EndElement))
{
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name == "Data")
{
ImportCellData(reader, cell);
}
else if (reader.Name == "Comment")
{
ImportCellComment(reader, cell);
}
}
}
}
private void ImportRow(XmlReader reader)
{
bool isEmpty = reader.IsEmptyElement;
int rowIndex = this.Rows.Count;
double height = -1;
XmlStyle style = null;
bool hidden = false;
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
if (xa.LocalName == "Height" && xa.HasValue)
{
xa.Value.ParseToInt(out height);
}
if (xa.LocalName == "Index" && xa.HasValue)
{
xa.Value.ParseToInt(out rowIndex);
rowIndex--;
}
if (xa.LocalName == "StyleID" && xa.HasValue)
{
style = this.ParentBook.GetStyleByID(xa.Value);
}
if (xa.LocalName == "Hidden" && xa.HasValue)
{
hidden = xa.Value == "1";
}
}
Row row = this.GetRowByIndex(rowIndex);
row.Hidden = hidden;
if (height != -1)
{
row.Height = height;
}
if (style != null)
{
row.Style = style;
}
if (isEmpty)
{
return;
}
while (reader.Read() && !(reader.Name == "Row" && reader.NodeType == XmlNodeType.EndElement))
{
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name == "Cell")
{
this.ImportCell(reader, row);
}
}
}
}
public override void CreateBody()
{
// создание стилей ячеек
XmlStyle textStyle = new XmlStyle();
textStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Left, Vertical = VerticalAlignment.Center, WrapText = true
};
textStyle.Border.LineStyle = Borderline.Continuous;
textStyle.Border.Sides = BorderSides.All;
textStyle.Border.Weight = 1;
textStyle.Font.Size = 13;
textStyle.Font.Name = "Calibri";
XmlStyle numbersStyle = new XmlStyle(textStyle);
numbersStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Center, Vertical = VerticalAlignment.Center, WrapText = true
};
numbersStyle.Font.Name = "Century Gothic";
numbersStyle.Font.Size = 14;
XmlStyle valuesStyle = new XmlStyle(numbersStyle);
valuesStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Right, Vertical = VerticalAlignment.Center, WrapText = false
};
valuesStyle.DisplayFormat = DisplayFormatType.Custom;
valuesStyle.CustomFormatString = "# ##0";
int rowCount = 0;
foreach (var item in this.collectors)
{
rowCount += item.CountersCount;
}
const int startRow = 6;
int rowIndex = startRow;
int collectorIndex = 0;
foreach (Collector collector in this.collectors)
{
int columnIndex = 1;
int collectorRowSpan = collector.Objects.Sum(o => o.Counters.Sum(c => c.TarifsCount == 1 ? 1 : 3));
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collectorIndex + 1, numbersStyle);
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collector.IsAnswered == true ? "OK" : "не отвечает", textStyle);
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collector.IsUSPD == true ? "УСПД" : string.Empty, textStyle);
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collector.NetworkAddress, numbersStyle);
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collector.PhoneNumber, numbersStyle);
int oldObjectColumnIndex = columnIndex;
for (int objectIndex = 0; objectIndex < collector.Objects.Count; objectIndex++)
{
AccountingObject aobject = collector.Objects[objectIndex];
int countersCount = aobject.CountersCount;
int objectRowSpan = aobject.Counters.Sum(c => c.TarifsCount == 1 ? 1 : 3);
columnIndex = oldObjectColumnIndex;
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.City, textStyle);
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.Contract, textStyle);
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.Subscriber, textStyle);
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.Name, textStyle);
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.Tp, textStyle);
bool isCounterMultiTariff = false;
int oldCounterColumnIndex = columnIndex;
for (int counterIndex = 0; counterIndex < countersCount; counterIndex++)
{
Counter counter = aobject.Counters[counterIndex];
isCounterMultiTariff = aobject.Counters[counterIndex].TarifsCount != 1;
int counterRowSpan = isCounterMultiTariff ? 3 : 1;
columnIndex = oldCounterColumnIndex;
CreateRange(rowIndex + counterIndex, columnIndex++, counterRowSpan, 1, counter.IsAnswered == true ? "OK" : "не отвечает", textStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, counterRowSpan, 1, counter.AccountingPoint, textStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, counterRowSpan, 1, counter.CounterType, textStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, counterRowSpan, 1, counter.CounterNetworkAdress, numbersStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, counterRowSpan, 1, counter.SerialNumber, numbersStyle);
}
rowIndex += countersCount;
}
collectorIndex++;
}
}
public override void CreateHeader()
{
CreateRange(1, 1, 1, 1, String.Format("Экспортировано: {0}", DateTime.Now));
XmlStyle titleStyle = new XmlStyle();
titleStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Center, Vertical = VerticalAlignment.Center, WrapText = true
};
titleStyle.Font.Size = 14;
string paramName = string.Empty;
switch (this.exportInfo.Param)
{
case ProfileType.Current:
paramName = "параметр: текущие показания";
break;
case ProfileType.Days:
paramName = "параметр: показания на начало суток";
break;
case ProfileType.Months:
paramName = "параметр: показания на начало месяца";
break;
}
CreateRange(2, 1, 1, 10,
String.Format("Отчет по результатам опроса объектов, оборудованных СДСП, в {0}", this.exportInfo.ElementName),
titleStyle);
CreateRange(3, 1, 1, 10, paramName, titleStyle);
XmlStyle tableHeaderStyle = new XmlStyle();
tableHeaderStyle.Font.Bold = true;
tableHeaderStyle.Font.Size = 12;
tableHeaderStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Center, Vertical = VerticalAlignment.Center, WrapText = true
};
tableHeaderStyle.Border.LineStyle = Borderline.Continuous;
tableHeaderStyle.Border.Sides = BorderSides.All;
tableHeaderStyle.Border.Weight = 1;
tableHeaderStyle.Interior.Color = System.Drawing.Color.Azure;
CreateRange(4, 2, 3, 1, "№ договора");
CreateRange(4, 3, 3, 1, "Расчётная точка");
CreateRange(4, 4, 1, 7, "Счётчик");
CreateRange(5, 8, 1, 3, "Показания");
CreateRange(4, 1, 3, 1, "№ п/п");
CreateRange(5, 4, 2, 1, "Присоединение");
CreateRange(5, 5, 2, 1, "Тип");
CreateRange(5, 6, 2, 1, "Заводской номер");
CreateRange(5, 7, 2, 1, "Тариф");
CreateRange(6, 8, 1, 1, String.Format("предыдущее\r\n({0})", this.exportInfo.StartDate.ToString(this.exportInfo.StartDateFormat)));
CreateRange(6, 9, 1, 1, String.Format("следующее\r\n({0})", this.exportInfo.EndDate.ToString(this.exportInfo.EndDateFormat)));
CreateRange(6, 10, 1, 1, "разность");
ChangeRangeStyle(4, 1, 6, 10, tableHeaderStyle);
XmlStyle externalBordersStyle = new XmlStyle();
externalBordersStyle.Border.LineStyle = Borderline.Continuous;
externalBordersStyle.Border.Sides = BorderSides.All;
externalBordersStyle.Border.Weight = 1;
ChangeRangeStyle(4, 1, 6, 10, externalBordersStyle);
}
internal void CreateRange(int row, int column, int rowSpan, int colSpan, object value, XmlStyle style)
{
sheet[column - 1, row - 1].Value = value;
Range r = new Range(sheet[column - 1, row - 1], sheet[column - 1 + colSpan - 1, row - 1 + rowSpan - 1]);
if (rowSpan > 1 || colSpan > 1)
{
r.Merge();
}
r.Style = style;
}
internal void ChangeRangeStyle(int row_start, int column_start, int row_end, int column_end, XmlStyle style)
{
new Range(sheet[column_start - 1, row_start - 1], sheet[column_end - 1, row_end - 1]).Style = style;
}
internal void CreateCell(int row, int column, object value, XmlStyle style)
{
sheet[column - 1, row - 1].Value = value;
sheet[column - 1, row - 1].Style = style;
}
public override void CreateBody()
{
// создание стилей ячеек
XmlStyle objectStyle = new XmlStyle();
objectStyle.Border.LineStyle = Borderline.Continuous;
objectStyle.Border.Sides = BorderSides.Bottom;
objectStyle.Border.Weight = 2;
XmlStyle textStyle = new XmlStyle();
textStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Left, Vertical = VerticalAlignment.Center, WrapText = true
};
textStyle.Border.LineStyle = Borderline.Continuous;
textStyle.Border.Sides = BorderSides.All;
textStyle.Border.Weight = 1;
textStyle.Font.Size = 13;
textStyle.Font.Name = "Calibri";
XmlStyle numbersStyle = new XmlStyle(textStyle);
numbersStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Center, Vertical = VerticalAlignment.Center, WrapText = true
};
numbersStyle.Font.Name = "Century Gothic";
numbersStyle.Font.Size = 14;
XmlStyle valuesStyle = new XmlStyle(numbersStyle);
valuesStyle.Alignment = new AlignmentOptions()
{
Horizontal = HorizontalAlignment.Right, Vertical = VerticalAlignment.Center, WrapText = false
};
valuesStyle.DisplayFormat = DisplayFormatType.Custom;
valuesStyle.CustomFormatString = "# ##0";
int rowCount = 0;
foreach (var item in this.collectors)
{
rowCount += item.CountersCount;
}
const int startRow = 7;
int rowIndex = startRow;
int collectorIndex = 0;
foreach (Collector collector in this.collectors)
{
int columnIndex = 1;
int collectorRowSpan = collector.Objects.Sum(o => o.Counters.Sum(c => c.TarifsCount == 1 ? 1 : 3));
CreateRange(rowIndex, columnIndex++, collectorRowSpan, 1, collectorIndex + 1, numbersStyle);
ChangeRangeStyle(rowIndex, columnIndex - 1, collectorRowSpan, _columnsCount, objectStyle);
int oldObjectColumnIndex = columnIndex;
for (int objectIndex = 0; objectIndex < collector.Objects.Count; objectIndex++)
{
AccountingObject aobject = collector.Objects[objectIndex];
int countersCount = aobject.CountersCount;
int objectRowSpan = aobject.Counters.Sum(c => c.TarifsCount == 1 ? 1 : 3);
columnIndex = oldObjectColumnIndex;
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, aobject.Contract, textStyle);
CreateRange(rowIndex, columnIndex++, objectRowSpan, 1, String.Format("{0} / {1}", aobject.Subscriber, aobject.Name), textStyle);
bool isCounterMultiTariff = false;
int oldCounterColumnIndex = columnIndex;
for (int counterIndex = 0; counterIndex < countersCount; counterIndex++)
{
Counter counter = aobject.Counters[counterIndex];
isCounterMultiTariff = aobject.Counters[counterIndex].TarifsCount != 1;
int counterRowSpan = isCounterMultiTariff ? 3 : 1;
columnIndex = oldCounterColumnIndex;
CreateRange(rowIndex + counterIndex, columnIndex++, counterRowSpan, 1, counter.AccountingPoint, textStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, counterRowSpan, 1, counter.CounterType, textStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, counterRowSpan, 1, counter.SerialNumber, numbersStyle);
// тариф
if (isCounterMultiTariff)
// счётчик многотарифный
{
// TƩ
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, "TƩ", textStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.PreviousIndications.Tarriff0), valuesStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.NextIndications.Tarriff0), valuesStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.Difference), valuesStyle);
rowIndex++;
// T1
columnIndex = oldCounterColumnIndex + 3;
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, "T1", textStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.PreviousIndications.Tarriff1), valuesStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.NextIndications.Tarriff1), valuesStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.DifferenceT1), valuesStyle);
rowIndex++;
// T2
columnIndex = oldCounterColumnIndex + 3;
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, "T2", textStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.PreviousIndications.Tarriff2), valuesStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.NextIndications.Tarriff2), valuesStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.DifferenceT2), valuesStyle);
}
// однотарифный
else
{
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, "TƩ", textStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.PreviousIndication), valuesStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.NextIndication), valuesStyle);
CreateRange(rowIndex + counterIndex, columnIndex++, 1, 1, GetIndication(counter.Difference), valuesStyle);
}
}
rowIndex += countersCount;
}
collectorIndex++;
}
}
private void ImportTable(XmlReader reader)
{
if (reader.IsEmptyElement)
{
return;
}
int column = 0;
while (reader.Read() && !(reader.Name == "Table" && reader.NodeType == XmlNodeType.EndElement))
{
if (reader.NodeType == XmlNodeType.Element)
{
switch (reader.Name)
{
case "Column":
{
double width = 0;
bool hidden = false;
int span = 1;
XmlStyle style = null;
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
if (xa.LocalName == "Width" && xa.HasValue)
{
double d;
if (xa.Value.ParseToInt(out d))
{
width = d;
}
}
if (xa.LocalName == "Hidden" && xa.HasValue)
{
hidden = xa.Value == "1";
}
if (xa.LocalName == "Index" && xa.HasValue)
{
xa.Value.ParseToInt(out column);
}
if (xa.LocalName == "Span" && xa.HasValue)
{
xa.Value.ParseToInt(out span);
}
if (xa.LocalName == "StyleID" && xa.HasValue)
{
style = this.ParentBook.GetStyleByID(xa.Value);
}
}
for (int i = 1; i <= span; i++)
{
this.Columns(column).Width = width;
this.Columns(column).Hidden = hidden;
if (style != null)
{
this.Columns(column).Style = style;
}
column++;
}
break;
}
case "Row":
{
this.ImportRow(reader);
break;
}
}
}
}
}
