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