private void ReadChild(ExcelRangeBase cell, ref int row, int level) { do { bool isValueTag = !string.IsNullOrEmpty(cell.Offset(row, 6).Text); int depth; int.TryParse(cell.Offset(row, 1).Text, out depth); if (level != 0 && depth <= level) { break; } string tagName = cell.Offset(row, 2 + depth).Text; row += 1; if (isValueTag) { Console.WriteLine("{2}<{0}>{1}</{0}>", tagName, "ValueTag", new string(' ', depth * 4)); } else { Console.WriteLine("{1}<{0}>", tagName, new string(' ', depth * 4)); ReadChild(cell, ref row, depth); Console.WriteLine("{1}</{0}>", tagName, new string(' ', depth * 4)); } } while (!string.IsNullOrEmpty(cell.Offset(row, 0).Text)); }
/// <summary> /// Apply a border to a given EPPlus Range object. /// </summary> /// <param name="this"></param> /// <param name="borderStyle"></param> /// <remarks> /// This implementation runs faster than <see cref="BorderAll" />, /// and it applies borders to subranges in such a way that the same /// all-border effect is achieved, but without actually bordering /// all individual cells. NOTE: It assumes a strictly rectangular /// range! /// </remarks> public static void BorderAllFaster( this ExcelRangeBase @this, ExcelBorderStyle borderStyle) { var fromRow = @this.Start.Row; var fromCol = @this.Start.Column; var toRow = @this.End.Row; var toCol = @this.End.Column; var numRows = toRow - fromRow + 1; var numCols = toCol - fromCol + 1; @this.Style.Border.BorderAround(borderStyle); for (var rowOffset = 1; rowOffset < numRows; rowOffset += 2) { var row = @this.Offset(rowOffset, 0, 1, numCols); row.Style.Border.BorderAround(borderStyle); } for (var colOffset = 1; colOffset < numCols; colOffset += 2) { var col = @this.Offset(0, colOffset, numRows, 1); col.Style.Border.BorderAround(borderStyle); } }
private static List <int> GetStylesForRange(ExcelRangeBase range, eShiftTypeInsert shift) { var list = new List <int>(); if (shift == eShiftTypeInsert.Down) { for (int i = 0; i < range.Columns; i++) { if (range._fromRow == 1) { list.Add(0); } else { list.Add(range.Offset(-1, i).StyleID); } } } else { for (int i = 0; i < range.Rows; i++) { if (range._fromCol == 1) { list.Add(0); } else { list.Add(range.Offset(i, -1).StyleID); } } } return(list); }
/// <summary> /// 指定したセルを基準とした定数情報を生成する。 /// </summary> /// <param name="cell"></param> /// <returns></returns> private static DefinedInfo CreateDefinedInfo(ExcelRangeBase cell) { var result = new DefinedInfo(cell.Offset(0, 0).ValueString() , cell.Offset(1, 0).ValueString() , cell.Offset(3, 0).ValueString() , cell.Offset(2, 0).ValueString()); return(result); }
private void FillFromExcelDocument(string fileName, XElement xDeclarationsHolder) { var etbUnit = new CurrencyUnit("ETB"); Instance.Units.Add(etbUnit); var quarter1Of2018 = new DurationPeriod(new DateTime(2018, 1, 1), new DateTime(2018, 3, 31)); var startOfQuarter1Of2018 = new InstantPeriod(new DateTime(2017, 12, 31)); var endOfQuarter1Of2018 = new InstantPeriod(new DateTime(2018, 3, 31)); Instance.Periods.Add(quarter1Of2018); Instance.Periods.Add(startOfQuarter1Of2018); Instance.Periods.Add(endOfQuarter1Of2018); var wsi = new Entity { Id = "wsi", IdentifierScheme = new Uri("http://www.sec.gov/CIK"), Identifier = "WSI", Name = "Walia Steel Industry PLC" }; Instance.Entities.Add(wsi); var package = new ExcelPackage(new FileInfo(fileName)); var worksheet = package.Workbook.Worksheets[1]; ExcelRangeBase conceptCell = worksheet.Cells["B2"]; ExcelRangeBase valueCell = worksheet.Cells["C2"]; ExcelRangeBase startDateCell = worksheet.Cells["D2"]; ExcelRangeBase dateOrEndDateCell = worksheet.Cells["E2"]; ExcelRangeBase explicitMembersCell = worksheet.Cells["F2"]; while (conceptCell.Value != null && valueCell.Value != null) { var concept = conceptCell.GetValue <string>(); var value = valueCell.Value; var period = startDateCell.Value == null || string.IsNullOrEmpty(startDateCell.Value.ToString()) ? new InstantPeriod(dateOrEndDateCell.GetValue <DateTime>()) : (Period) new DurationPeriod(startDateCell.GetValue <DateTime>(), dateOrEndDateCell.GetValue <DateTime>()); var conceptName = ToXName(concept, xDeclarationsHolder); var fact = new Fact(conceptName, wsi, period) { Unit = etbUnit, Value = value.ToString(), BalanceMethod = BalanceMethod.Algebraic }; AddExplicitMembers(fact, explicitMembersCell.Value, xDeclarationsHolder); Instance.Facts.Add(fact); conceptCell = conceptCell.Offset(1, 0); valueCell = valueCell.Offset(1, 0); startDateCell = startDateCell.Offset(1, 0); dateOrEndDateCell = dateOrEndDateCell.Offset(1, 0); explicitMembersCell = explicitMembersCell.Offset(1, 0); } }
/// <summary> /// Add a rich text string /// </summary> /// <param name="Text">The text to add</param> /// <returns></returns> public ExcelRichText Add(string Text) { ConvertRichtext(); XmlDocument doc; if (TopNode is XmlDocument) { doc = TopNode as XmlDocument; } else { doc = TopNode.OwnerDocument; } var node = doc.CreateElement("d", "r", ExcelPackage.schemaMain); TopNode.AppendChild(node); var rt = new ExcelRichText(NameSpaceManager, node, this); if (_list.Count > 0) { ExcelRichText prevItem = _list[_list.Count - 1]; rt.FontName = prevItem.FontName; rt.Size = prevItem.Size; if (prevItem.Color.IsEmpty) { rt.Color = Color.Black; } else { rt.Color = prevItem.Color; } rt.PreserveSpace = rt.PreserveSpace; rt.Bold = prevItem.Bold; rt.Italic = prevItem.Italic; rt.UnderLine = prevItem.UnderLine; } else if (_cells == null) { rt.FontName = "Calibri"; rt.Size = 11; } else { var style = _cells.Offset(0, 0).Style; rt.FontName = style.Font.Name; rt.Size = style.Font.Size; rt.Bold = style.Font.Bold; rt.Italic = style.Font.Italic; _cells.IsRichText = true; } rt.Text = Text; rt.PreserveSpace = true; if (_cells != null) { rt.SetCallback(UpdateCells); UpdateCells(); } _list.Add(rt); return(rt); }
/// <summary> /// Create a pivottable on the supplied range /// </summary> /// <param name="Range">The range address including header and total row</param> /// <param name="Source">The Source data range address</param> /// <param name="Name">The name of the pivottable. Must be unique </param> /// <returns>The pivottable object</returns> public ExcelPivotTable Add(ExcelAddressBase Range, ExcelRangeBase Source, string Name) { if (string.IsNullOrEmpty(Name)) { Name = GetNewTableName(); } if (Source.Rows < 2) { throw (new ArgumentException("The Range must contain at least 2 rows", "Source")); } if (Range.WorkSheetName != _ws.Name) { throw(new Exception("The Range must be in the current worksheet")); } else if (_ws.Workbook.ExistsTableName(Name)) { throw (new ArgumentException("Tablename is not unique")); } foreach (var t in _pivotTables) { if (t.Address.Collide(Range) != ExcelAddressBase.eAddressCollition.No) { throw (new ArgumentException(string.Format("Table range collides with table {0}", t.Name))); } } for (int i = 0; i < Source.Columns; i++) { if (Source.Offset(0, i, 1, 1).Value == null) { throw (new ArgumentException("First row of source range should contain the field headers and can't have blank cells.", "Source")); } } return(Add(new ExcelPivotTable(_ws, Range, Source, Name, _ws.Workbook._nextPivotTableID++))); }
private static void SetStylesForRange(ExcelRangeBase range, eShiftTypeInsert shift, List <int> list) { if (shift == eShiftTypeInsert.Down) { for (int i = 0; i < range.Columns; i++) { range.Offset(0, i, range.Rows, 1).StyleID = list[i]; } } else { for (int i = 0; i < range.Rows; i++) { range.Offset(i, 0, 1, range.Columns).StyleID = list[i]; } } }
List <ErrData> errorList = new List <ErrData>(); //エラーリスト //summary //データ変換ツールのデータからフルパスのtxtを出力する public string DataConvertToText(string filePath) { string txtPath = filePath + ".txt"; try { //Excelを開く cellsのvalue(size,size)で最後の行が分かる? ExcelPackage excel = new ExcelPackage(new FileInfo(filePath)); ExcelWorksheet sheet = excel.Workbook.Worksheets["変換設定"]; //出力ファイル int lastRow = sheet.Dimension.End.Row; int lastColumn = sheet.Dimension.End.Column; stream = new StreamWriter(txtPath, false, Encoding.GetEncoding("shift_jis")); stream.WriteLine("[HEAD]"); stream.WriteLine(DateTime.Now); stream.WriteLine(); for (int i = 0; i <= (lastRow); i++) { ExcelRangeBase rangeBase = sheet.Cells[1, 3].Offset(i, 0); if (null != rangeBase.Value && rangeBase.Value.ToString() == "変換ファイル名(フルパス)") { if (null != rangeBase.Offset(0, 1).Value&& rangeBase.Offset(0, 1).Value.ToString() != "") { stream.WriteLine(rangeBase.Offset(0, 1).Value.ToString()); } } } stream.Close(); } catch (Exception e) { StreamWriter error = new StreamWriter(filePath + ".log", false, Encoding.GetEncoding("shift_jis")); error.WriteLine("Error log"); error.WriteLine(e.Message); error.WriteLine(e.Source); error.Close(); } return(txtPath); }
public void Apply(ExcelRangeBase referenceRange) { var targetRange = referenceRange.Offset( this.RowOffset, this.ColumnOffset, this.RowCount, this.ColumnCount); this.Formatter(targetRange); }
private static void SetEmailAsHyperlink(ExcelRangeBase range) { for (int row = 1; row <= range.Rows; row++) { var cell = range.Offset(row, 2, 1, 1); if (cell.Value != null) { cell.Hyperlink = new Uri($"mailto:{cell.Value}"); } } }
public static void Table(ExcelRangeBase range) { var headings = range.Offset( 0, 0, 1, range.End.Column - range.Start.Column + 1); Heading(headings); AllBorders(range); }
/// <summary> /// 入力チェック属性情報を生成する。 /// </summary> /// <param name="cell"></param> /// <returns></returns> private static IEnumerable <CheckAttributeBase> CreateCheckAttributes(ExcelRangeBase cell) { var results = new List <CheckAttributeBase>(); string isCheckRequired = cell.Offset(0, 0).ValueString(); if (string.IsNullOrEmpty(isCheckRequired) == false) { // 未入力チェック実施 results.Add(new CheckRequiredAttribute()); } string typeName = cell.Offset(0, -4).ValueString(); string minimum = cell.Offset(0, 1).ValueString(); string maximum = cell.Offset(0, 2).ValueString(); if (string.IsNullOrEmpty(minimum) == false && string.IsNullOrEmpty(maximum) == false) { // 範囲入力チェック実施 results.Add(new CheckRangeAttribute(typeName, minimum, maximum)); } string minLength = cell.Offset(0, 3).ValueString(); string maxLength = cell.Offset(0, 4).ValueString(); if (string.IsNullOrEmpty(minLength) == false && string.IsNullOrEmpty(maxLength) == false) { // 文字数制限チェック実施 results.Add(new CheckStringLengthAttribute(int.Parse(minLength), int.Parse(maxLength))); } return(results); }
public static void SetHeaders(this ExcelRangeBase cell, params string[] headers) { foreach (string text in headers) { cell.Value = text; cell.Style.Font.Bold = true; cell.Style.Fill.PatternType = ExcelFillStyle.Solid; cell.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue); cell.Style.Font.Color.SetColor(Color.White); cell.AutoFilter = true; cell = cell.Offset(0, 1); } }
private static void PopulateWorksheet(Dictionary <string, object>[] rows, ExcelWorksheet worksheet, WorksheetBinding worksheetBinding) { foreach (var cellBinding in worksheetBinding.Cells) { ExcelRangeBase cell = worksheet.Cells[cellBinding.Name]; foreach (var row in rows) { var value = row[cellBinding.Source]; cell.Value = cellBinding.Format != null && value is IFormattable formattable?formattable.ToString(cellBinding.Format, null) : value.ToString(); cell = cell.Offset(1, 0); } } }
public ListInfoProvider() { using (var stream = Load.ScotlandsMountains.ListInfo) using (var package = new ExcelPackage()) { package.Load(stream); ExcelRangeBase cell = package.Workbook.Worksheets.Single().Cells["A2"]; while (cell.Value != null) { _listInfo.Add(new ListInfo { Code = cell.GetValue <string>(), Name = cell.Offset(0, 1).GetValue <string>(), Order = cell.Offset(0, 2).GetValue <int>(), Description = cell.Offset(0, 3).GetValue <string>(), Enabled = cell.Offset(0, 4).GetValue <bool>() }); cell = cell.Offset(1, 0); } } }
public void InsertAt(ExcelRangeBase range) { // Load data into cells first ... // --- There may be a slight performance hit with doing this before // formatting. range.LoadFromDataTable(this.Table, this.ShowHeaders); // ... then apply formatting. // --- This is second mainly so that table values themselves can be // modified or some kind of conditional formatting (not the kind // that's built-in with Excel) can be applied. var referenceRange = this.ShowHeaders ? range.Offset(1, 0) : range; foreach (var fmt in this.Formattings) { fmt.Apply(referenceRange); } }
/// <summary> /// 指定したセルを基準としたプロパティ情報を生成する。 /// </summary> /// <param name="cell"></param> /// <returns></returns> private static PropertyInfo CreatePropertyInfo(ExcelRangeBase cell) { string typeName = cell.Offset(0, 2).ValueString(); string defaultValue = string.Empty; string elementCount = cell.Offset(0, 3).ValueString(); int count; // 配列やコレクションの場合、メンバで初期化する必要がある。 // それ以外の場合は、初期化は実施しない。 // ここでは、デフォルト値の設定有無の判定と、型名の変換を行う。 if (int.TryParse(elementCount, out count) == true) { if (count > 1) { // 配列で定義されている。 defaultValue = string.Format("new {0}[{1}]", typeName, count); typeName = string.Format("{0}[]", typeName); } } else if (elementCount == "*") { // コレクションで定義されている。 typeName = string.Format("ObservableCollection<{0}>", typeName); defaultValue = string.Format("new {0}()", typeName); } var result = new PropertyInfo(cell.Offset(0, 0).ValueString() , typeName , cell.Offset(0, 4).ValueString() , cell.Offset(0, 1).ValueString() , cell.Offset(0, 5).ValueString()) { DefaultValue = defaultValue, }; result.CheckAttributes.AddRange(CreateCheckAttributes(cell.Offset(0, 6))); return(result); }
/// <summary> /// Insert a rich text string at the specified index. /// </summary> /// <param name="index">The zero-based index at which rich text should be inserted.</param> /// <param name="text">The text to insert.</param> /// <returns></returns> public ExcelRichText Insert(int index, string text) { if (string.IsNullOrEmpty(text)) { throw new ArgumentException("Text can't be empty", "text"); } ConvertRichtext(); XmlDocument doc; if (TopNode is XmlDocument) { doc = TopNode as XmlDocument; } else { doc = TopNode.OwnerDocument; } var node = doc.CreateElement("d", "r", ExcelPackage.schemaMain); if (index < _list.Count) { TopNode.InsertBefore(node, TopNode.ChildNodes[index]); } else { TopNode.AppendChild(node); } var rt = new ExcelRichText(NameSpaceManager, node, this); if (_list.Count > 0) { ExcelRichText prevItem = _list[index < _list.Count ? index : _list.Count - 1]; rt.FontName = prevItem.FontName; rt.Size = prevItem.Size; if (prevItem.Color.IsEmpty) { rt.Color = Color.Black; } else { rt.Color = prevItem.Color; } rt.PreserveSpace = rt.PreserveSpace; rt.Bold = prevItem.Bold; rt.Italic = prevItem.Italic; rt.UnderLine = prevItem.UnderLine; } else if (_cells == null) { rt.FontName = "Calibri"; rt.Size = 11; } else { var style = _cells.Offset(0, 0).Style; rt.FontName = style.Font.Name; rt.Size = style.Font.Size; rt.Bold = style.Font.Bold; rt.Italic = style.Font.Italic; _cells.IsRichText = true; } rt.Text = text; rt.PreserveSpace = true; if (_cells != null) { rt.SetCallback(UpdateCells); UpdateCells(); } _list.Insert(index, rt); return(rt); }
//summary //指定したファイルを開き簡易チェックを行う。 public void CheckSheet(string filePath) { //fileExists if (File.Exists(filePath) == false) { return; } //Excelを開く ExcelPackage excel = new ExcelPackage(new FileInfo(filePath)); //svnクライアント SvnClient client = new SvnClient(); //クライアントのファイル位置を設定 SvnPathTarget local = new SvnPathTarget(filePath); //ファイルのsvnプロパティを抜く SvnInfoEventArgs clientInfo; client.GetInfo(local, out clientInfo); foreach (ExcelWorksheet ws in excel.Workbook.Worksheets) { string s = Strings.StrConv(ws.Name, VbStrConv.Wide | VbStrConv.Uppercase); if (s.StartsWith("TB_")) { try { //◎の検索(なければリターン)、移動 //Findメソッドがないので、cellデータを取得してLinqによりアドレスを算出する var query = from cell in ws.Cells where cell.Value?.ToString() == "◎" select cell; //◎のリストが完成したのでそれぞれでデータチェック if (0 < query.Count()) { foreach (ExcelRangeBase range in query) { //振分けテーブル名の被りチェック( Dictionaryチェック) if (tbNmae.ContainsValue(range.Offset(0, 1).Text)) { ErrData d = new ErrData(); d.Auther = clientInfo.LastChangeAuthor; d.ErrCode = ErrorCode.eTableName; d.ErrName = range.Offset(0, 1).Text; errorList.Add(d); } else { tbNmae.Add(tbNmae.Count + 1, range.Offset(0, 1).Text); } //振分け合計値の確認+データ有りの斜線チェック(W119では0データに斜線はOK) int dataMax = int.Parse(range.Offset(3, 1).Text); //データ項目列(標準)まで移動 右移動で数値になるまで int dataEndColumn = 0; int dataEndRow = 0; ExcelRangeBase exStart; ExcelRangeBase exEnd; //データのスタート位置 for (int i = 1; ; i++) { if (int.TryParse(range.Offset(4, 1).Offset(0, i).Text, out int result) == false) { exStart = range.Offset(5, 1).Offset(0, i); break; } } //データの横終了位置 for (int i = 1; ; i++) { if (exStart.Offset(0, i).Text == "") { dataEndColumn = exStart.Offset(0, i - 1).Columns; break; } } //データの縦終了位置 for (int i = 1; ; i++) { if (exStart.Offset(i, 0).Text == "") { dataEndRow = exStart.Offset(i - 1, 0).Rows; break; } } exEnd = exStart.Offset(dataEndRow, dataEndColumn); ExcelRangeBase dataCheck = exStart; for (int i = 1; i <= dataEndRow; i++) { //横に足し算しながら進めていく int sum = 0; for (int x = 1; x <= dataEndColumn; x++) { if (int.TryParse(dataCheck.Offset(i, x).Value.ToString(), out int result)) { sum += result; } } //合計計算 if (sum != dataMax) { ErrData d = new ErrData(); d.Auther = clientInfo.LastChangeAuthor; d.ErrCode = ErrorCode.eTableName; d.ErrName = range.Offset(0, 1).Text; errorList.Add(d); } } //処理を完了した振分けシート名を出力(速度が見える) Console.WriteLine(range.Offset(0, 1).Text); } } }catch (Exception e) { Console.WriteLine(e.Message); } //振分け最大値の取得 //データ項目列へ移動 //データを縦になめていく //合計値、斜線チェック } } }
private Instance LoadInstanceFromExcelDocument(string fileName) { XNamespace bSharpNamespace = "http://banan-it.com/taxonomy/2018-07-05/bsharp"; XNamespace ifrsNamespace = "http://xbrl.ifrs.org/taxonomy/2017-03-09/ifrs-full"; var xDeclarationsHolder = new XElement("dummy", new XAttribute(XNamespace.Xmlns + "ifrs-full", ifrsNamespace), new XAttribute(XNamespace.Xmlns + "banan", bSharpNamespace)); var instance = new Instance(); var etbUnit = new CurrencyUnit("ETB"); instance.Units.Add(etbUnit); var quarter1Of2018 = new DurationPeriod(new DateTime(2018, 1, 1), new DateTime(2018, 3, 31)); var startOfQuarter1Of2018 = new InstantPeriod(new DateTime(2017, 12, 31)); var endOfQuarter1Of2018 = new InstantPeriod(new DateTime(2018, 3, 31)); instance.Periods.Add(quarter1Of2018); instance.Periods.Add(startOfQuarter1Of2018); instance.Periods.Add(endOfQuarter1Of2018); var wsi = new Entity { Id = "wsi", IdentifierScheme = new Uri("http://www.sec.gov/CIK"), Identifier = "WSI", Name = "Walia Steel Industry PLC" }; instance.Entities.Add(wsi); var package = new ExcelPackage(new FileInfo(fileName)); var worksheet = package.Workbook.Worksheets[1]; ExcelRangeBase conceptCell = worksheet.Cells["B2"]; ExcelRangeBase valueCell = worksheet.Cells["C2"]; ExcelRangeBase startDateCell = worksheet.Cells["D2"]; ExcelRangeBase dateOrEndDateCell = worksheet.Cells["E2"]; ExcelRangeBase explicitMembersCell = worksheet.Cells["F2"]; while (conceptCell.Value != null && valueCell.Value != null) { var concept = conceptCell.GetValue <string>(); var value = valueCell.Value; var period = startDateCell.Value == null || string.IsNullOrEmpty(startDateCell.Value.ToString()) ? new InstantPeriod(dateOrEndDateCell.GetValue <DateTime>()) : (Period) new DurationPeriod(startDateCell.GetValue <DateTime>(), dateOrEndDateCell.GetValue <DateTime>()); var conceptName = ToXName(concept, xDeclarationsHolder); var fact = new Fact(conceptName, wsi, period) { Unit = etbUnit, Value = value.ToString(), BalanceMethod = BalanceMethod.Algebraic }; AddExplicitMembers(fact, explicitMembersCell.Value, xDeclarationsHolder); instance.Facts.Add(fact); conceptCell = conceptCell.Offset(1, 0); valueCell = valueCell.Offset(1, 0); startDateCell = startDateCell.Offset(1, 0); dateOrEndDateCell = dateOrEndDateCell.Offset(1, 0); explicitMembersCell = explicitMembersCell.Offset(1, 0); } return(instance); }
public async Task <Stream> KpiXLSFileStreamResult(ParamsCalculateKpi data) { var stream = new MemoryStream(); ExcelPackage package; int startByRow = 4; int startByColumn = 2; List <PreciseDelivery> _kpis = _dataService.CalculateKpi(data); int countKpi = _kpis.Count; // PreciseDelivery delivery = _kpis.First(e => e.Detail.Count == _kpis.Max(p => p.Detail.Count)); using (package = new ExcelPackage(stream)) { ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("KPI"); using (var range = worksheet.Cells[startByRow, startByColumn, startByRow + 1, startByColumn + countKpi + 1]) { range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; range.Style.Border.Top.Style = ExcelBorderStyle.Thin; range.Style.Border.Left.Style = ExcelBorderStyle.Thin; range.Style.Border.Right.Style = ExcelBorderStyle.Thin; range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; range.Style.VerticalAlignment = ExcelVerticalAlignment.Center; range.Style.WrapText = true; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(238, 236, 225)); } /* =========================== Caption ========================================= */ worksheet.Cells[startByRow - 2, startByColumn + 1].Value = $"Клиент {data.Customer.Name}"; worksheet.Cells[startByRow - 1, startByColumn + 1].Value = $"Период с {data.RangeDate.Start} по {data.RangeDate.End}"; /* ------------------------------------------------------------------------------ */ worksheet.Cells[startByRow, startByColumn, startByRow + 1, startByColumn].Merge = true; worksheet.Cells[startByRow, startByColumn].Value = @"Месяц"; worksheet.Column(startByColumn).Width = 20; worksheet.Cells[startByRow, startByColumn + 1, startByRow + 1, startByColumn + 1].Merge = true; worksheet.Cells[startByRow, startByColumn + 1].Value = @"Показатель"; worksheet.Column(startByColumn + 1).Width = 20; worksheet.Cells[startByRow, startByColumn + 2, startByRow, startByColumn + countKpi + 1].Merge = true; worksheet.Cells[startByRow, startByColumn + 2, startByRow, startByColumn + countKpi + 1].Value = @"KPI"; Dictionary <int, string> dictionary = new Dictionary <int, string>(); for (int i = startByColumn + 2; i < startByColumn + countKpi + 2; i++) { worksheet.Cells[startByRow + 1, i].Value = _kpis[i - (startByColumn + 2)].Description; dictionary.Add(i, _kpis[i - (startByColumn + 2)].Description); worksheet.Column(i).Width = 25; } int beginKpiValue = startByRow + 2; int lastRow = beginKpiValue; Dictionary <String, List <Tuple <int, int> > > points; int paddingChart = 0; int countMonth = this.countMonthInKpi(_kpis); ExcelRangeBase startCell = worksheet.Cells[startByRow + countMonth * 4 + 3, startByColumn - 1]; const double EXCELDEFAULTROWHEIGHT = 20.0; const double EXCELDEFAULTROWWIDTH = 60.0; var chartcellheight = (int)Math.Ceiling(400 / EXCELDEFAULTROWHEIGHT); var chartcellwidth = (int)Math.Ceiling(500 / EXCELDEFAULTROWWIDTH); foreach (var kpi in _kpis) { points = new Dictionary <string, List <Tuple <int, int> > >(); int k = dictionary.FirstOrDefault(x => x.Value.Equals(kpi.Description)).Key; beginKpiValue = startByRow + 2; lastRow = beginKpiValue; List <Tuple <int, int> > _pointMonth = new List <Tuple <int, int> >(); List <Tuple <int, int> > _pointTarget = new List <Tuple <int, int> >(); List <Tuple <int, int> > _pointFact = new List <Tuple <int, int> >(); List <Tuple <int, int> > _pointDeviation = new List <Tuple <int, int> >(); List <Tuple <int, int> > _pointCountOrder = new List <Tuple <int, int> >(); foreach (var dev in kpi.Detail) { worksheet.Cells[beginKpiValue, startByColumn].Value = new DateTime(dev.Year, dev.Month, 1); worksheet.Cells[beginKpiValue, startByColumn].Style.Numberformat.Format = "MMMM"; _pointMonth.Add(new Tuple <int, int>(beginKpiValue, startByColumn)); worksheet.Cells[beginKpiValue, startByColumn, beginKpiValue + 3, startByColumn].Merge = true; worksheet.Cells[beginKpiValue, startByColumn + 1].Value = "Цель"; worksheet.Cells[beginKpiValue + 1, startByColumn + 1].Value = "Факт"; worksheet.Cells[beginKpiValue + 2, startByColumn + 1].Value = "Откл"; worksheet.Cells[beginKpiValue + 3, startByColumn + 1].Value = "Заказов"; worksheet.Cells[beginKpiValue, k].Style.Numberformat.Format = "0.00"; worksheet.Cells[beginKpiValue, k].Value = dev.Target; _pointTarget.Add(new Tuple <int, int>(beginKpiValue, k)); worksheet.Cells[beginKpiValue + 1, k].Style.Numberformat.Format = "0.00"; worksheet.Cells[beginKpiValue + 1, k].Value = dev.Fact; _pointFact.Add(new Tuple <int, int>(beginKpiValue + 1, k)); worksheet.Cells[beginKpiValue + 2, k].Style.Numberformat.Format = "0.00"; worksheet.Cells[beginKpiValue + 2, k].Value = dev.Deviation; _pointDeviation.Add(new Tuple <int, int>(beginKpiValue + 2, k)); worksheet.Cells[beginKpiValue + 3, k].Style.Numberformat.Format = "0"; worksheet.Cells[beginKpiValue + 3, k].Value = dev.CountOrder; _pointCountOrder.Add(new Tuple <int, int>(beginKpiValue + 3, k)); beginKpiValue += 4; lastRow += beginKpiValue; } points.Add("month", _pointMonth); points.Add("target", _pointTarget); points.Add("fact", _pointFact); points.Add("deviation", _pointDeviation); points.Add("countOrder", _pointCountOrder); CreateChart(worksheet, kpi.Description, points, startCell, paddingChart); if (k % 2 == 0) { chartcellwidth = (int)Math.Ceiling(500 / 160.0); startCell = startCell.Offset(chartcellheight * 0, chartcellwidth); } else { /*startCell = startCell.Offset(chartcellheight, 0); * chartcellwidth = (int)Math.Ceiling(500 / 160.0);*/ startCell = startCell.Offset(chartcellheight, -chartcellwidth); } paddingChart++; } /*int k = dictionary.FirstOrDefault(x => x.Value.Equals(delivery.Description)).Key; * int beginKpiValue = startByRow + 2; * * foreach (var dev in delivery.Detail) * { * * worksheet.Cells[beginKpiValue , startByColumn].Value = dev.Date; * worksheet.Cells[beginKpiValue , startByColumn].Style.Numberformat.Format = "MMMM"; * worksheet.Cells[beginKpiValue , startByColumn+1].Value = "Цель"; * worksheet.Cells[beginKpiValue+1, startByColumn+1].Value = "Факт"; * worksheet.Cells[beginKpiValue+2, startByColumn+1].Value = "Откл"; * worksheet.Cells[beginKpiValue+3 , startByColumn+1].Value = "Заказов"; * worksheet.Cells[beginKpiValue , k].Value = dev.Target; * worksheet.Cells[beginKpiValue+1, k].Value = dev.Fact; * worksheet.Cells[beginKpiValue+2, k].Value = dev.Deviation; * worksheet.Cells[beginKpiValue+3 , k].Value = dev.CountOrder; * * beginKpiValue += 4; * * * * }*/ using (var range = worksheet.Cells[startByRow + 2, startByColumn, beginKpiValue - 1, startByColumn + countKpi + 1]) { range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; range.Style.Border.Top.Style = ExcelBorderStyle.Thin; range.Style.Border.Left.Style = ExcelBorderStyle.Thin; range.Style.Border.Right.Style = ExcelBorderStyle.Thin; range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; range.Style.VerticalAlignment = ExcelVerticalAlignment.Center; range.Style.WrapText = true; } package.Save(); } return(stream); }