/// <summary>
/// TODO: переписать реализацию
/// </summary>
public static void SetDynamicCellValue(IXLCell cell, object value)
{
switch (value)
{
case string stringValue:
cell.SetValue(stringValue);
cell.SetDataType(XLDataType.Text);
break;
case int intValue:
cell.SetValue(intValue);
cell.SetDataType(XLDataType.Number);
break;
case long longValue:
cell.SetValue(longValue);
cell.SetDataType(XLDataType.Number);
break;
case double doubleValue:
cell.SetValue(doubleValue);
cell.SetDataType(XLDataType.Number);
break;
default:
throw new Exception($"Неизвестный тип: {value?.GetType().Name}");
}
}
public void Validation_1()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Data Validation Issue");
IXLCell cell = ws.Cell("E1");
cell.SetValue("Value 1");
cell = cell.CellBelow();
cell.SetValue("Value 2");
cell = cell.CellBelow();
cell.SetValue("Value 3");
cell = cell.CellBelow();
cell.SetValue("Value 4");
cell = cell.CellBelow();
ws.Cell("A1").SetValue("Cell below has Validation Only.");
cell = ws.Cell("A2");
cell.DataValidation.List(ws.Range("$E$1:$E$4"));
ws.Cell("B1").SetValue("Cell below has Validation with a title.");
cell = ws.Cell("B2");
cell.DataValidation.List(ws.Range("$E$1:$E$4"));
cell.DataValidation.InputTitle = "Title for B2";
Assert.AreEqual(cell.DataValidation.AllowedValues, XLAllowedValues.List);
Assert.AreEqual(cell.DataValidation.Value, "'Data Validation Issue'!$E$1:$E$4");
Assert.AreEqual(cell.DataValidation.InputTitle, "Title for B2");
ws.Cell("C1").SetValue("Cell below has Validation with a message.");
cell = ws.Cell("C2");
cell.DataValidation.List(ws.Range("$E$1:$E$4"));
cell.DataValidation.InputMessage = "Message for C2";
Assert.AreEqual(cell.DataValidation.AllowedValues, XLAllowedValues.List);
Assert.AreEqual(cell.DataValidation.Value, "'Data Validation Issue'!$E$1:$E$4");
Assert.AreEqual(cell.DataValidation.InputMessage, "Message for C2");
ws.Cell("D1").SetValue("Cell below has Validation with title and message.");
cell = ws.Cell("D2");
cell.DataValidation.List(ws.Range("$E$1:$E$4"));
cell.DataValidation.InputTitle = "Title for D2";
cell.DataValidation.InputMessage = "Message for D2";
Assert.AreEqual(cell.DataValidation.AllowedValues, XLAllowedValues.List);
Assert.AreEqual(cell.DataValidation.Value, "'Data Validation Issue'!$E$1:$E$4");
Assert.AreEqual(cell.DataValidation.InputTitle, "Title for D2");
Assert.AreEqual(cell.DataValidation.InputMessage, "Message for D2");
}
public void HasRichTextTest1()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
cell.RichText.AddText("123");
Assert.AreEqual(true, cell.HasRichText);
cell.DataType = XLCellValues.Text;
Assert.AreEqual(true, cell.HasRichText);
cell.DataType = XLCellValues.Number;
Assert.AreEqual(false, cell.HasRichText);
cell.RichText.AddText("123");
Assert.AreEqual(true, cell.HasRichText);
cell.Value = 123;
Assert.AreEqual(false, cell.HasRichText);
cell.RichText.AddText("123");
Assert.AreEqual(true, cell.HasRichText);
cell.SetValue("123");
Assert.AreEqual(false, cell.HasRichText);
}
public static IXLCell Value <T>(this IXLCell cell, T value)
{
cell.ShareString = false;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.SetValue(value);
return(cell);
}
public void EditCellValue(string fileLocation, string sheetName, string newCellValue, int row, int column)
{
XLWorkbook Workbook = new XLWorkbook(fileLocation);
IXLWorksheet Worksheet = Workbook.Worksheet(sheetName);
IXLCell CellForNewData = Worksheet.Cell(row, column);
CellForNewData.Clear();
CellForNewData.SetValue(newCellValue);
Workbook.SaveAs(fileLocation);
}
private void AddTableHeaderTextAndStyles(IXLCell cell, string text)
{
cell.SetValue(text);
cell.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
cell.Style.Border.OutsideBorderColor = XLColor.Black;
cell.Style.Font.Bold = true;
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
cell.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 102, 255);
}
public static void SetValue(this IXLCell cell, Type dataType, object value)
{
if (dataType.Equals(typeof(string)))
{
cell.SetValue((string)value);
}
else if (dataType.Equals(typeof(int)))
{
cell.SetValue((int)value);
}
else if (dataType.Equals(typeof(uint)))
{
cell.SetValue((uint)value);
}
else if (dataType.Equals(typeof(DateTime)))
{
cell.SetValue((DateTime)value);
}
else if (dataType.Equals(typeof(short)))
{
cell.SetValue((short)value);
}
else if (dataType.Equals(typeof(ushort)))
{
cell.SetValue((ushort)value);
}
else if (dataType.Equals(typeof(float)))
{
cell.SetValue((float)value);
}
else if (dataType.Equals(typeof(double)))
{
cell.SetValue((double)value);
}
else if (dataType.Equals(typeof(long)))
{
cell.SetValue((long)value);
}
else if (dataType.Equals(typeof(ulong)))
{
cell.SetValue((ulong)value);
}
}
private static void DrawHeaderCell(IXLCell cell, object value)
{
cell.Style.Font.Bold = true;
cell.Style.Font.FontSize = 9;
cell.Style.Font.FontName = "Arial";
cell.Style.Fill.BackgroundColor = XLColor.Gainsboro;
cell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
cell.SetValue(value);
}
public void Double_NaN_is_a_string()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell("A1");
var doubleList = new List <Double> {
0.0 / 0.0
};
cell.Value = 5;
cell.Value = doubleList;
Assert.AreEqual(XLDataType.Text, cell.DataType);
Assert.AreEqual(CultureInfo.CurrentCulture.NumberFormat.NaNSymbol, cell.Value);
cell.Value = 5;
Assert.Throws <ArgumentException>(() => cell.SetValue(doubleList));
}
private void AddTableCellTextAndStyles(IXLCell cell, bool text)
{
cell.SetValue(text);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
cell.Style.Border.LeftBorderColor = XLColor.Black;
cell.Style.Border.RightBorder = XLBorderStyleValues.Thin;
cell.Style.Border.RightBorderColor = XLColor.Black;
cell.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
cell.Style.Border.BottomBorderColor = XLColor.Black;
}
public void ValueSetToNull()
{
string expected = String.Empty;
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
cell.Value = new DateTime(2000, 1, 2);
cell.Value = null;
Assert.AreEqual(expected, cell.GetString());
Assert.AreEqual(expected, cell.Value);
cell.Value = new DateTime(2000, 1, 2);
cell.SetValue(null as string);
Assert.AreEqual(expected, cell.GetString());
Assert.AreEqual(expected, cell.Value);
}
public void Double_Infinity_is_a_string()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell("A1");
var doubleList = new List <Double> {
1.0 / 0.0
};
cell.Value = 5;
cell.Value = doubleList;
Assert.AreEqual(XLDataType.Text, cell.DataType);
Assert.AreEqual(CultureInfo.CurrentCulture.NumberFormat.PositiveInfinitySymbol, cell.Value);
cell.Value = 5;
cell.SetValue(doubleList);
Assert.AreEqual(XLDataType.Text, cell.DataType);
Assert.AreEqual(CultureInfo.CurrentCulture.NumberFormat.PositiveInfinitySymbol, cell.Value);
}
private void SetFormat <T>(IXLCell cell, T value)
{
cell.Style
.Border
.TopBorder = XLBorderStyleValues.Thin;
cell.Style
.Border
.BottomBorder = XLBorderStyleValues.Thin;
cell.Style
.Border
.LeftBorder = XLBorderStyleValues.Thin;
cell.Style
.Border
.RightBorder = XLBorderStyleValues.Thin;
cell.Style
.Font
.FontName = "Calibri";
cell.SetValue(value);
}
public static IXLCell SetHtmlValue(this IXLCell target, string html)
{
var reader = new SgmlReader();
reader.DocType = "HTML";
reader.WhitespaceHandling = WhitespaceHandling.None;
reader.InputStream = new StringReader(html);
var xmlRoot = new XmlDocument();
xmlRoot.Load(reader);
target.SetValue(xmlRoot.InnerText);
var rootText = xmlRoot.InnerXml;
var spanIndex = 0;
foreach (var item in xmlRoot.GetElementsByTagName("span").Cast <XmlElement>())
{
var itemText = item.OuterXml;
var rawStart = rootText.IndexOf(itemText, spanIndex);
var rawLength = itemText.Length;
var trimStart = Regex.Replace(rootText.Substring(0, rawStart), "<.+?>", "").Length;
var trimLength = item.InnerText.Length;
var richText = target.RichText.Substring(trimStart, trimLength);
richText.SetCssStyle(item);
spanIndex = rawStart + rawLength;
}
var strongIndex = 0;
foreach (var item in xmlRoot.GetElementsByTagName("strong").Cast <XmlElement>())
{
var itemText = item.OuterXml;
var rawStart = rootText.IndexOf(itemText, strongIndex);
var rawLength = itemText.Length;
var trimStart = Regex.Replace(rootText.Substring(0, rawStart), "<.+?>", "").Length;
var trimLength = item.InnerText.Length;
var richText = target.RichText.Substring(trimStart, trimLength);
richText.SetBold();
richText.SetCssStyle(item);
strongIndex = rawStart + rawLength;
}
return(target);
}
public void AddTextTest2()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
Int32 number = 123;
cell.SetValue(number).Style
.Font.SetBold()
.Font.SetFontColor(XLColor.Red);
string text = number.ToString();
Assert.AreEqual(cell.RichText.ToString(), text);
Assert.AreEqual(cell.RichText.First().Bold, true);
Assert.AreEqual(cell.RichText.First().FontColor, XLColor.Red);
Assert.AreEqual(1, cell.RichText.Count);
cell.RichText.AddText("World");
Assert.AreEqual(cell.RichText.First().Text, text, "Item in collection is not the same as the one returned");
}
/// <summary>
/// Adds event cells to the given row
/// </summary>
private static IXLCell CreateCells(IXLCell cell, EventResult eventItem)
{
var action = eventItem.Object;
var project = eventItem.Project;
var isAction = action != null;
var isProject = project != null;
cell.SetValue(isAction ? action.Id : default(int?)).CellRight()
.SetValue(isAction ? action.Name : null).CellRight()
.SetValue(isProject ? project.Id : default(int?)).CellRight()
.SetValue(isProject ? project.Name : null).CellRight()
.SetValue(eventItem.CustomUri).CellRight()
.SetValue("").CellRight()
.SetDataState(eventItem.ContactState).CellRight()
.SetIpAddress(eventItem.ClientId).CellRight()
.SetDateTime(eventItem.StartDate).CellRight()
.SetDateTime(eventItem.FinishDate).CellRight()
.SetHyperlink(eventItem.ReferrerUrl).CellRight();
return(cell);
}
private void HandleCellValue(IEnumerable <Variable> parameters, IXLCell cell, string cellValue)
{
var matches = PARAM_REGEX.Matches(cellValue);
if (matches.Count > 0)
{
foreach (Match match in matches)
{
var parameter = parameters.FirstOrDefault(x => match.Value.Contains(x.Name, System.StringComparison.InvariantCultureIgnoreCase));
switch (parameter.Value.Type)
{
case JTokenType.Date:
cellValue = cellValue.Replace(match.Value, DateTime.Parse(parameter.Value.ToString()).ToShortDateString());
break;
default:
cellValue = cellValue.Replace(match.Value, parameter.Value.ToString());
break;
}
}
cell.SetValue(cellValue);
}
}
public void CreateCell(IXLCell cell, object value, string type)
{
if (value == null)
{
return;
}
try
{
switch (string.IsNullOrWhiteSpace(type) ? Type : type)
{
case "integer":
cell.SetValue <int>(Convert.ToInt32(value));
break;
case "decimal":
case "percent":
cell.SetValue <decimal>(Convert.ToDecimal(value));
break;
case "datetime":
case "date":
case "time":
cell.SetValue <DateTime>(value is long?FromUnixTimestamp(value) : (DateTime)value);
break;
case "datetime+utc":
case "date+utc":
case "time+utc":
cell.SetValue <DateTime>(((value is long?FromUnixTimestamp(value) : (DateTime)value)).ToUniversalTime());
break;
case "boolean":
cell.SetValue <bool>(Convert.ToBoolean(value));
break;
default:
cell.SetValue <string>(value.ToString());
break;
}
}
catch { }
}
// 输出一行实体/订购/期刊/评注信息
// parameters:
// dropdown_list 下拉列表内容数组。每个元素为这样的格式：value1,value2,value3
// 也可以选择在此函数以外设置 cell range 的 valuelist。
public static void OutputItemLine(
string strRecPath,
string strXml,
int nBiblioIndex,
IXLWorksheet sheet,
int nStartColIndex, // 从 0 开始计数
List <string> col_list,
List <string> dropdown_list,
int nRowIndex, // 从 0 开始计数。
XLColor backColor,
out IXLCell copyNumberCell)
{
copyNumberCell = null;
//string strState = "";
//List<IXLCell> cells = new List<IXLCell>();
XmlDocument dom = new XmlDocument();
dom.LoadXml(strXml);
int i = 0;
foreach (string col in col_list)
{
List <string> value_list = null;
if (dropdown_list != null)
{
value_list = StringUtil.SplitList(dropdown_list[i]);
}
string strValue = "";
if (col == "recpath" || col.EndsWith("_recpath"))
{
strValue = strRecPath;
}
else
{
strValue = FindItemContent(dom, col);
}
//if (col == "state" || col.EndsWith("_state"))
// strState = strValue;
{
IXLCell cell = sheet.Cell(nRowIndex + 1, nStartColIndex + i + 1);
//cells.Add(cell);
if (col == "copyNumber" || col == "copyItems")
{
Int32 value = 0;
if (string.IsNullOrEmpty(strValue) == false)
{
if (Int32.TryParse(strValue, out value) == false)
{
throw new Exception("列 '" + col + "' 的值 '" + strValue + "' 应该为纯数字");
}
}
cell.SetValue <Int32>(value);
}
else
{
cell.SetValue(strValue);
}
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//if (backColor != XLColor.NoColor)
// cell.Style.Fill.BackgroundColor = backColor;
if (i == 0)
{
cell.Style.Border.SetLeftBorderColor(XLColor.Black);
cell.Style.Border.SetLeftBorder(XLBorderStyleValues.Medium);
}
if (i == col_list.Count - 1)
{
cell.Style.Border.SetRightBorderColor(XLColor.Black);
cell.Style.Border.SetRightBorder(XLBorderStyleValues.Medium);
}
if (value_list != null && value_list.Count > 0)
{
//Pass a string in this format: "Option1,Option2,Option3"
// var options = new List<string> { "Option1", "Option2", "Option3" };
var validOptions = $"\"{String.Join(",", value_list)}\"";
cell.DataValidation.List(validOptions, true);
}
if (col == "copy")
{
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
}
if ((col == "recpath" || col.EndsWith("_recpath")) ||
(col == "state" || col.EndsWith("_state")))
{
cell.Style.Font.FontColor = XLColor.LightGray;
}
else
{
cell.Style.Protection.SetLocked(false);
}
if (col == "copyNumber")
{
copyNumberCell = cell;
}
}
i++;
}
}
public void SetValue(IXLCell cell, T dataObject)
{
switch (_funcType)
{
case FuncType.String:
var stringValue = _stringValueFunc(dataObject);
if (stringValue != null)
{
cell.SetValue(stringValue);
}
break;
case FuncType.Int:
cell.SetValue(_intValueFunc(dataObject));
break;
case FuncType.NullableInt:
var intValue = _nullableIntValueFunc(dataObject);
if (intValue.HasValue)
{
cell.SetValue(intValue);
}
break;
case FuncType.DateTime:
cell.SetValue(_dateTimeValueFunc(dataObject));
break;
case FuncType.NullableDateTime:
var dateTimeValue = _nullableDateTimeValueFunc(dataObject);
if (dateTimeValue.HasValue)
{
cell.SetValue(dateTimeValue);
}
break;
case FuncType.Decimal:
cell.SetValue(_decimalValueFunc(dataObject));
break;
case FuncType.NullableDecimal:
var decimalValue = _nullableDecimalValueFunc(dataObject);
if (decimalValue.HasValue)
{
cell.SetValue(decimalValue);
}
break;
case FuncType.Double:
cell.SetValue(_doubleValueFunc(dataObject));
break;
case FuncType.NullableDouble:
var doubleValue = _nullableDoubleValueFunc(dataObject);
if (doubleValue.HasValue)
{
cell.SetValue(doubleValue);
}
break;
case FuncType.Bool:
cell.SetValue(_boolValueFunc(dataObject));
break;
case FuncType.NullableBool:
var boolValue = _nullableBoolValueFunc(dataObject);
if (boolValue.HasValue)
{
cell.SetValue(boolValue);
}
break;
case FuncType.Short:
cell.SetValue(_shortValueFunc(dataObject));
break;
case FuncType.NullableShort:
var shortValue = _nullableShortValueFunc(dataObject);
if (shortValue.HasValue)
{
cell.SetValue(shortValue);
}
break;
case FuncType.Byte:
cell.SetValue(_byteValueFunc(dataObject));
break;
case FuncType.NullableByte:
var byteValue = _nullableByteValueFunc(dataObject);
if (byteValue.HasValue)
{
cell.SetValue(byteValue);
}
break;
default:
throw new ArgumentOutOfRangeException();
}
}
private void WriteCell(IXLCell cell, T item, PropertyInfo property)
{
cell.SetValue(property.GetValue(item, null)?.ToString() ?? "");
}
public async Task <bool> ExportToExcelFileAsync(DataToExport dataToExport, string filePath)
{
bool success = false;
await Task.Run(() =>
{
try
{
using var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet();
string canalType = dataToExport.CanalCharacteristics.canalType;
IList <Parameter> canalGeometryParameters = dataToExport.CanalCharacteristics.Item2;
ws.Cell("A2").SetValue("Тип канала");
ws.Cell("A3").SetValue(canalType);
for (int i = 0; i < canalGeometryParameters.Count; i++)
{
ws.Cell(2, i + 4).SetValue(canalGeometryParameters[i].Name);
ws.Cell(3, i + 4).SetValue(canalGeometryParameters[i].Value + $" {canalGeometryParameters[i].MeasureUnit}");
}
string materialType = dataToExport.MaterialCharacteristics.materialType;
IList <Parameter> materialPropertyParameters = dataToExport.MaterialCharacteristics.Item2;
ws.Cell("A5").SetValue("Тип материала");
ws.Cell("A6").SetValue(materialType);
for (int i = 0; i < materialPropertyParameters.Count; i++)
{
ws.Cell(5, i + 4).SetValue(materialPropertyParameters[i].Name);
ws.Cell(6, i + 4).SetValue(materialPropertyParameters[i].Value + $" {materialPropertyParameters[i].MeasureUnit}");
}
IXLColumn lastMergedColumn = ws.LastColumnUsed();
ws.Range(ws.Cell(1, 1), ws.LastColumnUsed().Cell(1)).Merge().SetValue("Входные параметры");
IXLCell xLCell = ws.LastColumnUsed().ColumnRight().ColumnRight().Cell(2);
IList <Parameter> variableParameters = dataToExport.VariableParameters;
for (int i = 0; i < variableParameters.Count; i++)
{
xLCell.SetValue(variableParameters[i].Name);
xLCell = xLCell.CellBelow().SetValue(variableParameters[i].Value + $" {variableParameters[i].MeasureUnit}");
xLCell = xLCell.CellRight().CellAbove();
}
ws.Range(lastMergedColumn.ColumnRight().ColumnRight().Cell(1),
ws.LastColumnUsed().Cell(1)).Merge().SetValue("Варьируемые параметры");
lastMergedColumn = ws.LastColumnUsed();
xLCell = ws.LastColumnUsed().ColumnRight().ColumnRight().Cell(2);
IList <Parameter> empiricalParametersOfMathModel = dataToExport.EmpiricalParametersOfMathModel;
for (int i = 0; i < empiricalParametersOfMathModel.Count; i++)
{
xLCell.SetValue(empiricalParametersOfMathModel[i].Name);
xLCell = xLCell.CellBelow().SetValue(empiricalParametersOfMathModel[i].Value + $" {empiricalParametersOfMathModel[i].MeasureUnit}");
xLCell = xLCell.CellRight().CellAbove();
}
ws.Range(lastMergedColumn.ColumnRight().ColumnRight().Cell(1),
ws.LastColumnUsed().Cell(1)).Merge().SetValue("Эмпирические коэффициенты математической модели");
IDictionary <string, IList <Parameter> > discreteOutputParameters = dataToExport.DiscreteOutputParameters;
xLCell = xLCell.CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow()
.WorksheetRow().Cell(1);
foreach (KeyValuePair <string, IList <Parameter> > keyValuePair in discreteOutputParameters)
{
IXLCell firstCell = xLCell;
foreach (Parameter parameter in keyValuePair.Value)
{
xLCell.SetValue(parameter.Name);
xLCell = xLCell.CellBelow().SetValue($"{parameter.Value} {parameter.MeasureUnit}");
xLCell = xLCell.CellAbove().CellRight();
}
if (keyValuePair.Value.Count <= 0)
{
continue;
}
if (keyValuePair.Value.Count == 1)
{
xLCell = xLCell.CellRight();
continue;
}
ws.Range(firstCell.CellAbove(), ws.LastRowUsed().LastCellUsed().CellAbove().CellAbove()).
Merge().SetValue(keyValuePair.Key);
xLCell = xLCell.CellRight();
}
xLCell = xLCell.CellLeft().CellLeft().CellAbove().CellAbove();
ws.Range(xLCell, xLCell.WorksheetRow().Cell(1)).Merge().SetValue("Результаты");
xLCell = ws.LastRowUsed().Cell(1).CellBelow().CellBelow();
ws.Range(xLCell, xLCell.CellRight().CellRight().CellRight()).Merge().SetValue("Таблица результатов");
IList <(Parameter coordinate, Parameter temperature, Parameter viscosity)> resultsTable = dataToExport.ContiniousResults;
xLCell.CellBelow().SetValue($"{resultsTable[0].coordinate.Name}, {resultsTable[0].coordinate.MeasureUnit}")
.CellRight().SetValue($"{resultsTable[0].temperature.Name}, {resultsTable[0].temperature.MeasureUnit}")
.CellRight().SetValue($"{resultsTable[0].viscosity.Name}, {resultsTable[0].viscosity.MeasureUnit}");
xLCell = xLCell.CellBelow().CellBelow();
NumberFormatInfo nfi = new NumberFormatInfo
{
NumberDecimalSeparator = "."
};
foreach (var(coordinate, temperature, viscosity) in resultsTable)
{
xLCell.SetValue(((double)coordinate.Value)
.ToString($"F{dataToExport.CoordinatePrecision}", nfi))
.SetDataType(XLDataType.Number)
.CellRight().SetValue(((double)temperature.Value).ToString($"F2", nfi))
.SetDataType(XLDataType.Number)
.CellRight().SetValue(((double)viscosity.Value).ToString("F2", nfi))
.SetDataType(XLDataType.Number);
xLCell = xLCell.CellBelow();
}
//ws.RowsUsed().AdjustToContents();
//ws.ColumnsUsed().AdjustToContents();
//using var stream = new MemoryStream();
//dataToExport.TemperaturePlot.Save(stream, ImageFormat.Png);
//ws.AddPicture(stream).MoveTo(ws.Cell("H8"))
// .WithSize(dataToExport.TemperaturePlot.Width, dataToExport.TemperaturePlot.Height);
//using var stream1 = new MemoryStream();
//dataToExport.ViscosityPlot.Save(stream1, ImageFormat.Png);
//ws.AddPicture(stream1).MoveTo(ws.Cell("O8"))
// .WithSize(dataToExport.ViscosityPlot.Width, dataToExport.ViscosityPlot.Height);
wb.SaveAs(filePath);
success = true;
}
catch { success = false; }
});
return(success);
}
/// <summary>
/// Canvasシートに情報を書き込む
/// </summary>
/// <param name="canvas">Canvasシート</param>
private void setCanvas(IXLWorksheet canvas)
{
BitmapData data = null;
try
{
data = Image.LockBits(new Rectangle(
0, 0, Image.Width, Image.Height),
ImageLockMode.ReadOnly,
PixelFormat.Format32bppArgb);
int size = Image.Width * Image.Height;
byte[] buf = new byte[size * 4];
Marshal.Copy(data.Scan0, buf, 0, buf.Length);
for (int i = 0; i < Image.Height; i++)
{
for (int j = 0; j < Image.Width; j++)
{
Cancel.Token.ThrowIfCancellationRequested();
// プログレスバー更新
int progress = (j + Image.Width * i) * 100 / size + 1;
if (ProgressCount != progress)
{
ProgressCount = progress;
Progress.Report(progress);
}
IXLCell cell = canvas.Cell(i + 1, j + 1);
cell.Style.Border.SetOutsideBorder(XLBorderStyleValues.Dashed);
int idx = (j + Image.Width * i) * 4;
byte a = buf[idx + 3];
byte r = buf[idx + 2];
byte g = buf[idx + 1];
byte b = buf[idx];
// アルファ値が0のピクセルはスキップ
if (a == 0)
{
continue;
}
int colorIdx = getNearestColorIndex(r, g, b);
Bead bead = Palette[colorIdx];
bead.Count++;
cell.SetValue(bead.No)
.Style.Fill.SetBackgroundColor(bead.Color)
.Font.SetFontSize(10)
.Font.SetFontColor(bead.FontColor)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
}
}
}
finally
{
if (data != null)
{
Image.UnlockBits(data);
}
}
// 行列幅
canvas.Columns(1, Image.Width).Width = 3.57;
canvas.Rows(1, Image.Height).Height = 22.5;
}
/// <summary>
/// Excel fájlba exportálja a paraméterként megadott terméklistát WHosting szerinti összeállításban a munkamappába, vagy a paraméterként megadott mappába.
/// Az exportált fájl neve a beállításokban megadott fájlnévből és az aktuális dátumból képződik (YYYYMMDD).
/// A metódus kérdezés nélkül felülírja a létező listát.
/// </summary>
/// <param name="_termekek">A terméklista amelyet exportálni kívánunk.</param>
/// <param name="_elteromappa">Itt lehetséges megadni az eltérő mappát, ha nem a munkamappába kívánjuk exportálni a terméklistát.</param>
/// <returns></returns>
public static async Task FajlbaExportal(IList <Termek> _termekek, CancellationToken _canceltoken, string _elteromappa = null)
{
if (_termekek == null)
{
throw new ArgumentNullException(nameof(_termekek));
}
if (_canceltoken == null)
{
throw new ArgumentNullException(nameof(_canceltoken));
}
FolyamatValtozott?.Invoke(0, 0, string.Format("{0}/{1} termék exportálva ({2:P2})", 0, 0, 0));
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Products");
int ctr = 1, osszestermekszam = Globalisok.Munkaasztal.HRPTermekLista.Count;
// Oszlopok szélességének beállítása
int[] oszlopszelessegek = new int[] { 10, 30, 30, 12,
10, 12, 14, 13, 13, 15, 10,
8, 8, 12, 12, 8, 10,
6, 19, 19, 14,
6, 11, 8, 8, 8, 11,
6, 12,
32, 32, 20, 20, 32, 32, 32, 32,
15, 16, 16, 16, 32, 32,
10, 8, 8 };
foreach (int szelesseg in oszlopszelessegek)
{
ws.Column(ctr++).Width = szelesseg;
}
// Fejléc hozzáadása
string[] fejlec = new string[]
{
"product_id", "name(en-gb)", "name(hu-hu)", "categories",
"sku", "upc", "ean", "jan", "isbn", "mpn", "location",
"quantity", "model", "manufacturer", "image_name", "shipping", "price",
"points", "date_added", "date_modified", "date_available",
"weight", "weight_unit", "length", "width", "height", "length_unit",
"status", "tax_class_id",
"description(en-gb)", "description(hu-hu)", "meta_title(en-gb)", "meta_title(hu-hu)", "meta_description(en-gb)", "meta_description(hu-hu)", "meta_keywords(en-gb)", "meta_keywords(hu-hu)",
"stock_status_id", "store_ids", "layout", "related_ids", "tags(en-gb)", "tags(hu-hu)",
"sort_order", "subtract", "minimum"
};
ctr = 1;
foreach (string fln in fejlec)
{
IXLCell cell = ws.Cell(1, ctr++);
cell.SetValue(fln);
cell.Style.Font.Bold = true;
cell.Style.Fill.BackgroundColor = XLColor.FromArgb(189, 215, 238);
}
// Termékek hozzáadása
await Task.Run(async() =>
{
DateTime most = DateTime.Now;
ctr = 2;
foreach (Termek termek in _termekek)
{
if (_canceltoken.IsCancellationRequested)
{
break;
}
// Cellák tartalmainak feltöltése
ws.Cell(ctr, 1).SetValue(termek.product_id);
ws.Cell(ctr, 2).SetValue(termek.name_engb);
ws.Cell(ctr, 3).SetValue(termek.name_huhu);
//ws.Cell(ctr, 4).SetValue(termek.categories);
ws.Cell(ctr, 5).SetValue(termek.sku);
//ws.Cell(ctr, 6).SetValue(termek.upc);
ws.Cell(ctr, 7).SetValue(termek.ean);
//ws.Cell(ctr, 8).SetValue(termek.jan);
//ws.Cell(ctr, 9).SetValue(termek.isbn);
ws.Cell(ctr, 10).SetValue(termek.mpn);
//ws.Cell(ctr, 11).SetValue(termek.location);
ws.Cell(ctr, 12).SetValue(termek.quantity);
ws.Cell(ctr, 13).SetValue(termek.model);
ws.Cell(ctr, 14).SetValue(termek.manufacturer);
//ws.Cell(ctr, 15).SetValue(termek.image_name);
ws.Cell(ctr, 16).SetValue(termek.shipping);
ws.Cell(ctr, 17).SetValue(termek.price);
ws.Cell(ctr, 18).SetValue(termek.points);
ws.Cell(ctr, 19).SetValue(termek.date_added);
ws.Cell(ctr, 20).SetValue(termek.date_modified);
ws.Cell(ctr, 21).SetValue(termek.date_available);
ws.Cell(ctr, 22).SetValue(termek.weight);
ws.Cell(ctr, 23).SetValue(termek.weight_unit);
ws.Cell(ctr, 24).SetValue(termek.length);
ws.Cell(ctr, 25).SetValue(termek.width);
ws.Cell(ctr, 26).SetValue(termek.height);
ws.Cell(ctr, 27).SetValue(termek.length_unit);
ws.Cell(ctr, 28).SetValue(termek.shipping);
ws.Cell(ctr, 29).SetValue(termek.tax_class_id);
ws.Cell(ctr, 30).SetValue(termek.description_engb);
ws.Cell(ctr, 31).SetValue(termek.description_huhu);
ws.Cell(ctr, 32).SetValue(termek.meta_title_engb);
ws.Cell(ctr, 33).SetValue(termek.meta_title_huhu);
ws.Cell(ctr, 34).SetValue(termek.meta_description_engb);
ws.Cell(ctr, 35).SetValue(termek.meta_description_huhu);
ws.Cell(ctr, 36).SetValue(termek.meta_keywords_engb);
ws.Cell(ctr, 37).SetValue(termek.meta_keywords_huhu);
ws.Cell(ctr, 38).SetValue(termek.stock_status_id);
ws.Cell(ctr, 39).SetValue(termek.store_ids);
//ws.Cell(ctr, 40).SetValue(termek.layout);
//ws.Cell(ctr, 41).SetValue(termek.related_ids);
ws.Cell(ctr, 42).SetValue(termek.tags_engb);
ws.Cell(ctr, 43).SetValue(termek.tags_huhu);
ws.Cell(ctr, 44).SetValue(termek.sort_order);
ws.Cell(ctr, 45).SetValue(termek.subtract);
ws.Cell(ctr, 46).SetValue(termek.minimum);
// Horizontális igazítás
ws.Cell(ctr, 17).Style.Alignment.Horizontal =
ws.Cell(ctr, 22).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
// Cellaformátumok beállítása
ws.Cell(ctr, 17).DataType =
ws.Cell(ctr, 22).DataType = XLDataType.Number;
//ws.Cell(ctr, 17).Style.NumberFormat.Format = "# ##0,00";
//ws.Cell(ctr, 22).Style.NumberFormat.Format = "# ##0,00";
if ((ctr - 1) % 25 == 0)
{
FolyamatValtozott?.Invoke(ctr - 1, osszestermekszam, string.Format("{0}/{1} termék exportálva ({2:P2})", ctr - 1, osszestermekszam, (double)(ctr - 1) / (double)osszestermekszam));
}
++ctr;
}
// Fejléc sora és az első oszlop rögzítve legyen
ws.SheetView.FreezeRows(1);
ws.SheetView.FreezeColumns(1);
if (!_canceltoken.IsCancellationRequested)
{
FolyamatValtozott?.Invoke(1, 1, "Fájlba írás...");
StorageFolder folder = await StorageFolder.GetFolderFromPathAsync(_elteromappa ?? Globalisok.Mappa_Termeklista);
StorageFile file = await folder.CreateFileAsync(Fajlnev, CreationCollisionOption.ReplaceExisting);
using (Stream filestream = await file.OpenStreamForWriteAsync())
{
wb.SaveAs(filestream);
filestream.Close();
}
FolyamatValtozott?.Invoke(1, 1, "A termékek exportálása sikeresen végbement.");
}
});
}
/// <summary>
/// Excel fájlba exportálja a paraméterként megadott terméklistát HRP szerinti összeállításban a munkamappába, vagy a paraméterként megadott mappába.
/// Az exportált fájl neve a beállításokban megadott fájlnévből és az aktuális dátumból képződik (YYYYMMDD).
/// A metódus kérdezés nélkül felülírja a létező listát.
/// </summary>
/// <param name="_termekek">A terméklista amelyet exportálni kívánunk.</param>
/// <param name="_elteromappa">Itt lehetséges megadni az eltérő mappát, ha nem a munkamappába kívánjuk exportálni a terméklistát.</param>
/// <returns></returns>
public static async Task FajlbaExportal_HRP(IList <Model.Termek> _termekek, CancellationToken _canceltoken, string _elteromappa = null)
{
if (_termekek == null)
{
throw new ArgumentNullException(nameof(_termekek));
}
if (_canceltoken == null)
{
throw new ArgumentNullException(nameof(_canceltoken));
}
FolyamatValtozott?.Invoke(0, 0, string.Format("{0}/{1} termék exportálva ({2:P2})", 0, 0, 0));
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Termékek");
int ctr = 1, osszestermekszam = Globalisok.Munkaasztal.TermekLista.Count;
// Oszlopok szélességének beállítása
int[] oszlopszelessegek = new int[] { 17, 17, 70, 19, 14, 8, 14, 8, 14, 8, 14,
16, 11, 10, 8, 8,
13, 8, 8, 8, 8, 15,
8, 8, 8, 8 };
foreach (int szelesseg in oszlopszelessegek)
{
ws.Column(ctr++).Width = szelesseg;
}
// Fejléc hozzáadása
string[] fejlec = new string[]
{
"ID", "Cikkszám", "Megnevezés", "Leírás", "Gyártó", "Ktg#1 ID", "Kategória #1", "Ktg#2 ID", "Kategória #2", "Ktg#3 ID", "Kategória #3",
"Garancia mód", "Garancia idő", "Ár", "Új", "Kedvezmény",
"Készletinfó", "Van raktáron", "Készletszám", "Külső készletszám", "Értékesítés vége", "Vonalkód",
"Nettó súly", "Bruttó súly", "Szélesség", "Magasság", "Hossz"
};
ctr = 1;
foreach (string fln in fejlec)
{
IXLCell cell = ws.Cell(1, ctr++);
cell.SetValue(fln);
cell.Style.Font.Bold = true;
cell.Style.Fill.BackgroundColor = XLColor.FromArgb(189, 215, 238);
}
// Termékek hozzáadása
await Task.Run(async() =>
{
ctr = 2;
foreach (Termek termek in _termekek)
{
#if CSAKNOTEBOOK
if (!termek.ElsoKategoriaID.Equals("M001") || !termek.MasodikKategoriaID.Equals("N001"))
{
continue;
}
#endif
if (_canceltoken.IsCancellationRequested)
{
break;
}
// A sor betűinek színe szürke, ha nincs forgalmazva a termék
if (termek.ErtekesitesVege)
{
foreach (var cell in ws.Range(ctr, 1, ctr, 27).Cells())
{
cell.Style.Font.FontColor = XLColor.Gray;
}
}
// Cellák tartalmainak feltöltése
ws.Cell(ctr, 1).SetValue(termek.ID);
ws.Cell(ctr, 2).SetValue(termek.Cikkszam);
ws.Cell(ctr, 3).SetValue(termek.Megnevezes);
ws.Cell(ctr, 4).SetValue(termek.Leiras);
ws.Cell(ctr, 5).SetValue(termek.GyartoMegnevezes);
ws.Cell(ctr, 6).SetValue(termek.ElsoKategoriaID);
ws.Cell(ctr, 7).SetValue(termek.ElsoKategoriaMegnevezes);
ws.Cell(ctr, 8).SetValue(termek.MasodikKategoriaID);
ws.Cell(ctr, 9).SetValue(termek.MasodikKategoriaMegnevezes);
ws.Cell(ctr, 10).SetValue(termek.HarmadikKategoriaID);
ws.Cell(ctr, 11).SetValue(termek.HarmadikKategoriaMegnevezes);
ws.Cell(ctr, 12).SetValue(termek.GaranciaMod);
ws.Cell(ctr, 13).SetValue(termek.GaranciaIdo);
ws.Cell(ctr, 14).SetValue(termek.Ar);
ws.Cell(ctr, 15).SetValue(termek.Uj ? "igen" : "nem"); ws.Cell(ctr, 12).Style.Font.FontColor = termek.Uj ? XLColor.Green : XLColor.Red;
ws.Cell(ctr, 16).SetValue(termek.Kedvezmeny ? "igen" : "nem"); ws.Cell(ctr, 13).Style.Font.FontColor = termek.Kedvezmeny ? XLColor.Green : XLColor.Red;
ws.Cell(ctr, 17).SetValue(termek.KeszletInfo);
ws.Cell(ctr, 18).SetValue(termek.VanRaktaron ? "igen" : "nem"); ws.Cell(ctr, 15).Style.Font.FontColor = termek.VanRaktaron ? XLColor.Green : XLColor.Red;
ws.Cell(ctr, 19).SetValue(termek.KeszletSzam);
ws.Cell(ctr, 20).SetValue(termek.KulsoKeszletSzam);
ws.Cell(ctr, 21).SetValue(termek.ErtekesitesVege ? "igen" : "nem"); ws.Cell(ctr, 18).Style.Font.FontColor = termek.ErtekesitesVege ? XLColor.Green : XLColor.Red;
ws.Cell(ctr, 22).SetValue(termek.Vonalkod);
ws.Cell(ctr, 23).SetValue(termek.NettoSuly.ToString());
ws.Cell(ctr, 24).SetValue(termek.BruttoSuly.ToString());
ws.Cell(ctr, 25).SetValue(termek.Szelesseg.ToString());
ws.Cell(ctr, 26).SetValue(termek.Magassag.ToString());
ws.Cell(ctr, 27).SetValue(termek.Hossz.ToString());
// Horizontális igazítás
ws.Cell(ctr, 14).Style.Alignment.Horizontal =
ws.Cell(ctr, 19).Style.Alignment.Horizontal =
ws.Cell(ctr, 20).Style.Alignment.Horizontal =
ws.Cell(ctr, 23).Style.Alignment.Horizontal =
ws.Cell(ctr, 24).Style.Alignment.Horizontal =
ws.Cell(ctr, 25).Style.Alignment.Horizontal =
ws.Cell(ctr, 26).Style.Alignment.Horizontal =
ws.Cell(ctr, 27).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
// Cellaformátumok beállítása
ws.Cell(ctr, 14).DataType = XLDataType.Number;
if ((ctr - 1) % 25 == 0)
{
FolyamatValtozott?.Invoke(ctr - 1, osszestermekszam, string.Format("{0}/{1} termék exportálva ({2:P2})", ctr - 1, osszestermekszam, (double)(ctr - 1) / (double)osszestermekszam));
}
++ctr;
}
// Fejléc sora rögzítve legyen
ws.SheetView.FreezeRows(1);
if (!_canceltoken.IsCancellationRequested)
{
FolyamatValtozott?.Invoke(1, 1, "Fájlba írás...");
StorageFolder folder = await StorageFolder.GetFolderFromPathAsync(_elteromappa ?? Globalisok.Mappa_Termeklista);
StorageFile file = await folder.CreateFileAsync(FajlnevHRP, CreationCollisionOption.ReplaceExisting);
using (Stream filestream = await file.OpenStreamForWriteAsync())
{
wb.SaveAs(filestream);
filestream.Close();
}
FolyamatValtozott?.Invoke(1, 1, "A termékek exportálása sikeresen végbement.");
}
});
}
public FileResult GetPDF()
{
#region "GetSessionVariables"
string logon_user = (string)Session["logon_user"];
long teamId, repId;
int month, year;
teamId = (long)Session["IS_Team"];
repId = (long)Session["IS_Rep"];
month = (int)Session["IS_Month"];
year = (int)Session["IS_Year"];
OSC_Team oSC_Team = db.Teams.Find(teamId);
OSC_Representative oSC_Representative = db.Representatives.Find(repId);
#endregion "GetSessionVariables"
#region "WorkbookUsingClosedXML"
string templateFileName = Server.MapPath("~/Templates/Template.xlsx");
XLWorkbook workbook = new XLWorkbook(templateFileName);
string exportPath = Server.MapPath("~/Export");
string excelFileName = "IndividualScorecard_" + oSC_Representative.LastName + oSC_Representative.FirstName + "_" + month.ToString() + year.ToString() + "_" + logon_user + ".xlsx";
string pdfFileName = "IndividualScorecard_" + oSC_Representative.LastName + oSC_Representative.FirstName + "_" + month.ToString() + year.ToString() + "_" + logon_user + ".pdf";
string tempFileName = exportPath + "/" + excelFileName;
string outputFileName = exportPath + "/" + pdfFileName;
if (System.IO.File.Exists(tempFileName))
{
System.IO.File.Delete(tempFileName);
}
workbook.SaveAs(tempFileName);
IXLWorksheet worksheet = workbook.Worksheet("Scorecard");
IXLWorksheet worksheet2 = workbook.Worksheet("Workitems");
IXLWorksheet worksheet3 = workbook.Worksheet("NPT");
#region "Table"
List <IndividualScorecard> list = new List <IndividualScorecard>();
list = af.GetIndividualScorecardFull(teamId, repId, month, year);
int x = 2;
int y = 29;
IXLCell cell = worksheet.Cell(y, x);
cell.SetValue("Attendance");
x += 1;
cell = worksheet.Cell(29, x);
cell.SetValue("Overtime");
x += 1;
cell = worksheet.Cell(29, x);
cell.SetValue("NPT Hours");
x += 1;
cell = worksheet.Cell(29, x);
cell.SetValue("Processing Time");
x += 1;
cell = worksheet.Cell(29, x);
cell.SetValue("Total Transactions");
x += 1;
cell = worksheet.Cell(29, x);
cell.SetValue("Rate of Production");
x += 1;
cell = worksheet.Cell(29, x);
cell.SetValue("Processing Quality");
x += 1;
cell = worksheet.Cell(29, x);
cell.SetValue("Total Utilization");
x += 1;
cell = worksheet.Cell(29, x);
cell.SetValue("Efficiency");
for (int i = 1; i <= list.Count; i++)
{
y += 1;
x = 2;
cell = worksheet.Cell(y, x);
cell.SetValue(list[i - 1].individualActivities.Attendance_Days.ToString());
x += 1;
cell = worksheet.Cell(y, x);
cell.SetValue(list[i - 1].individualActivities.Overtime_Hours.ToString());
x += 1;
cell = worksheet.Cell(y, x);
cell.SetValue(list[i - 1].individualNonProcessing.NPTHours.ToString());
x += 1;
cell = worksheet.Cell(y, x);
cell.SetValue(list[i - 1].individualBIProd.ProcessingHours.ToString());
x += 1;
cell = worksheet.Cell(y, x);
cell.SetValue(list[i - 1].individualBIProd.Count.ToString());
x += 1;
cell = worksheet.Cell(y, x);
cell.SetValue(list[i - 1].ProductivityRating.ToString() + "%");
x += 1;
cell = worksheet.Cell(y, x);
cell.SetValue(list[i - 1].individualBIQual.QualityRating.ToString() + "%");
x += 1;
cell = worksheet.Cell(y, x);
cell.SetValue(list[i - 1].TotalUtilization.ToString() + "%");
x += 1;
cell = worksheet.Cell(y, x);
cell.SetValue(list[i - 1].Efficiency.ToString() + "%");
}
do
{
x += 1;
worksheet.Column(x).Clear();
}while (x <= 23);
#endregion "Table"
#region "Highlights"
IndividualScorecard ind = af.GetIndividualScorecard(teamId, repId, month, year);
cell = worksheet.Cell("A47");
cell.SetValue(ind.Highlights);
#endregion "Highlights"
#region "Worktypes"
List <IndividualWorkTypes> worktypes = af.GetIndividualWorkTypes(teamId, repId, month, year);
y = 1;
x = 1;
IXLCell cell2 = worksheet2.Cell(y, x);
for (int i = 1; i <= worktypes.Count; i++)
{
y += 1;
x = 1;
cell2 = worksheet2.Cell(y, x);
cell2.SetValue(worktypes[i - 1].WorkType.ToString());
x += 1;
cell2 = worksheet2.Cell(y, x);
cell2.SetValue(((int)worktypes[i - 1].Count));
}
do
{
y += 1;
worksheet2.Row(y).Clear();
}while(y <= 200);
#endregion "Worktypes"
#region "NPT"
List <PieList> npts = af.GetIndividualNPT(teamId, repId, month, year);
y = 1;
x = 1;
IXLCell cell3 = worksheet3.Cell(y, x);
for (int i = 1; i <= npts.Count; i++)
{
y += 1;
x = 1;
cell3 = worksheet3.Cell(y, x);
cell3.SetValue(npts[i - 1].Category.ToString());
x += 1;
cell3 = worksheet3.Cell(y, x);
cell3.SetValue((double)npts[i - 1].TimeSpent);
}
do
{
y += 1;
worksheet3.Row(y).Clear();
}while (y <= 200);
#endregion "NPT"
#region "SaveWorkBook""
workbook.SaveAs(tempFileName);
#endregion "SaveWorkBook"
#endregion "WorkbookUsingClosedXML"
#region "UsingSpireXLS"
sxl.Workbook swb = new sxl.Workbook();
swb.LoadFromFile(tempFileName);
sxl.Worksheet sws1 = swb.Worksheets[0];
sxl.Worksheet sws2 = swb.Worksheets[1];
sxl.Worksheet sws3 = swb.Worksheets[2];
sws1.TextBoxes[0].Text = oSC_Team.TeamName + " Monthly Scorecard";
sws1.TextBoxes[1].Text = oSC_Representative.FirstName + " " + oSC_Representative.LastName;
sws1.TextBoxes[2].Text = db.months.Where(m => m.Value == Convert.ToString((int)month)).First().Text + "-" + year.ToString();
sxl.Chart chart1 = sws1.Charts[0];
chart1.DataRange = sws2.Range[1, 1, sws2.Rows.Count(), 2];
sxl.Chart chart2 = sws1.Charts[1];
chart2.DataRange = sws3.Range[1, 1, sws3.Rows.Count(), 2];
swb.Save();
#endregion "UsingSpireXLS"
#region "WorkbookUsingInterop"
//remove this codes when 403 - 454 when closedXML can generate this to PDF
xl.Application app = new xl.Application();
xl.Workbook wb = app.Workbooks.Open(tempFileName);
#region "Cover"
xl.Sheets xs = wb.Worksheets;
xl.Worksheet ws = (xl.Worksheet)xs.get_Item("Scorecard");
xl.Worksheet ws2 = (xl.Worksheet)xs.get_Item("Workitems");
xl.Worksheet ws3 = (xl.Worksheet)xs.get_Item("NPT");
int txt_ctr = 1;
foreach (xl.Shape shp in ws.Shapes)
{
if (shp.Type == Microsoft.Office.Core.MsoShapeType.msoTextBox)
{
if (txt_ctr == 1)
{
shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = oSC_Team.TeamName + " Monthly Scorecard";
}
else if (txt_ctr == 2)
{
shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = oSC_Representative.FirstName + " " + oSC_Representative.LastName;
shp.ZOrder(MsoZOrderCmd.msoBringToFront);
}
else
{
shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = db.months.Where(m => m.Value == Convert.ToString((int)month)).First().Text + "-" + year.ToString();
shp.ZOrder(MsoZOrderCmd.msoBringToFront);
}
}
txt_ctr += 1;
}
int cht_ctr = 1;
foreach (xl.ChartObject cht in ws.ChartObjects())
{
if (cht_ctr == 1)
{
xl.Range last = ws2.Cells.SpecialCells(xl.XlCellType.xlCellTypeLastCell, Type.Missing);
xl.Range chartRange = ws2.get_Range("A1", last);
cht.Chart.SetSourceData(chartRange);
}
else
{
xl.Range last = ws3.Cells.SpecialCells(xl.XlCellType.xlCellTypeLastCell, Type.Missing);
xl.Range chartRange = ws3.get_Range("A1", last);
cht.Chart.SetSourceData(chartRange);
}
cht_ctr += 1;
}
#endregion "Cover"
wb.Save();
wb.ExportAsFixedFormat(xl.XlFixedFormatType.xlTypePDF, outputFileName, xl.XlFixedFormatQuality.xlQualityStandard);
wb.Close();
app.Quit();
#endregion "WorkbookUsingInterop"
#region "Return"
byte[] fileBytes = System.IO.File.ReadAllBytes(outputFileName);
string fileName = pdfFileName;
return(File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName));
#endregion "Return"
}
private IXLCell AddValues(int level, TrackedTimeNode row, IXLCell currentCell, string teamMember,
double?wiTotalDurationInMin, double?wiDirectDurationInMin, DateTime?recordDate)
{
var firstCell = currentCell;
currentCell.Value = row.WorkItemId;
currentCell = currentCell.CellRight().SetValue(row.Project);
currentCell = currentCell.CellRight().SetValue(row.WorkItemType);
currentCell = currentCell.CellRight().SetValue(level >= 2 ? row.ParentId : null);
currentCell = currentCell.CellRight().SetValue(level >= 3 ? row.ParentId : null);
currentCell = currentCell.CellRight().SetValue(level >= 4 ? row.ParentId : null);
currentCell = currentCell.CellRight().SetValue(level > 4 ? row.ParentId : null);
string indent = "";
if (level > 1)
{
indent = string.Concat(Enumerable.Repeat("║ ", Math.Max(0, level - 2))) + "╠ "; // for a "tree" visualization
}
currentCell = currentCell.CellRight().SetValue(indent + row.Title);
currentCell = currentCell.CellRight().SetValue(level == 1 ? wiTotalDurationInMin : null);
currentCell = currentCell.CellRight().SetValue(level == 2 ? wiTotalDurationInMin : null);
currentCell = currentCell.CellRight().SetValue(level == 3 ? wiTotalDurationInMin : null);
currentCell = currentCell.CellRight().SetValue(level >= 4 ? wiTotalDurationInMin : null);
currentCell = currentCell.CellRight().SetValue(wiDirectDurationInMin / 60d);
currentCell = currentCell.CellRight().SetValue(recordDate);
currentCell = currentCell.CellRight().SetValue(teamMember);
if (showReportsByMonth)
{
var currentMonth = FirstMonth;
while (currentMonth <= Lastmonth)
{
currentCell = currentCell.CellRight();
double?durationInMin = null;
if (wiDirectDurationInMin == null)
{
// global WorkItem (not a time tracked record)
durationInMin = row.GetTotalDurationWithChildrenInMinForMonth(currentMonth);
}
else if (recordDate.HasValue && recordDate.Value.Year == currentMonth.Year && recordDate.Value.Month == currentMonth.Month)
{
// time tracked record which is in the current month
durationInMin = wiDirectDurationInMin;
}
if (durationInMin != null && durationInMin.Value > 0)
{
currentCell.SetValue(durationInMin / 60d);
}
currentMonth = currentMonth.AddMonths(1);
}
}
var range = worksheet.Range(firstCell, currentCell);
if (level == 1 && wiDirectDurationInMin == null) // 1stl level WorkItem (not a time tracked record)
{
range.Style.Fill.BackgroundColor = XLColor.FromArgb(216, 228, 188);
}
else if (level > 1 && wiDirectDurationInMin == null) // next level WorkItem (not a time tracked record)
{
range.Style.Fill.BackgroundColor = XLColor.FromArgb(235, 241, 222);
}
else
{
// wiDirectDurationInMin != null (time tracked record)
range.Style.Fill.BackgroundColor = XLColor.White;
}
return(currentCell);
}