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("ＴＢ＿"))
{
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データに斜線はＯＫ）
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);
}
