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