private static void CreateDataLabels(ChartSeries UIChartSeries, IChartSerie XlsIOChartSerie) { UIChartSeries.AdornmentsInfo.SegmentLabelFontFamily = new System.Windows.Media.FontFamily(XlsIOChartSerie.DataPoints.DefaultDataPoint.DataLabels.FontName); UIChartSeries.AdornmentsInfo.SegmentLabelFontSize = (int)XlsIOChartSerie.DataPoints[0].DataLabels.Size; UIChartSeries.AdornmentsInfo.SegmentLabelFontWeight = XlsIOChartSerie.DataPoints[0].DataLabels.Bold ? FontWeights.Bold : FontWeights.Normal; UIChartSeries.AdornmentsInfo.IsLabelRotate = true; UIChartSeries.AdornmentsInfo.SegmentLabelRotation = XlsIOChartSerie.DataPoints.DefaultDataPoint.DataLabels.TextRotationAngle; if ((XlsIOChartSerie.DataPoints.DefaultDataPoint.DataLabels.Position & (ExcelDataLabelPosition.Outside | ExcelDataLabelPosition.OutsideBase | ExcelDataLabelPosition.Automatic)) == 0) { UIChartSeries.AdornmentsInfo.SegmentIsOut = true; UIChartSeries.AdornmentsInfo.AdornmentsPosition = AdornmentsPosition.Top; } if (XlsIOChartSerie.DataPoints.DefaultDataPoint.DataLabels.IsPercentage) { UIChartSeries.ShowDataLabels = true; UIChartSeries.AdornmentsInfo.SegmentLabelContent = LabelContent.Percentage; UIChartSeries.AdornmentsInfo.SegmentLabelFormat = "0"; } if (XlsIOChartSerie.DataPoints.DefaultDataPoint.DataLabels.IsValue) { UIChartSeries.ShowDataLabels = true; UIChartSeries.AdornmentsInfo.SegmentLabelContent = LabelContent.YValue; } }
private void btnCreate_Click(object sender, RoutedEventArgs e) { #region Workbook Initialize //Get the path of the input file string inputPath = GetFullTemplatePath("BoxAndWhiskerTemplate.xlsx"); IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath, ExcelOpenType.Automatic); #endregion IWorksheet sheet = workbook.Worksheets[0]; IChart chart = null; if (this.rdbChartSheet.IsChecked != null && this.rdbChartSheet.IsChecked.Value) { chart = workbook.Charts.Add(); } else { chart = workbook.Worksheets[0].Charts.Add(); } #region Box and Whisker Chart Settings chart.ChartType = ExcelChartType.BoxAndWhisker; chart.DataRange = sheet["B1:C181"]; IChartSerie series = chart.Series[0]; series.SerieFormat.ShowInnerPoints = false; series.SerieFormat.ShowOutlierPoints = true; series.SerieFormat.ShowMeanMarkers = true; series.SerieFormat.ShowMeanLine = false; series.SerieFormat.QuartileCalculationType = ExcelQuartileCalculation.ExclusiveMedian; series.SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.Grey_25_percent; chart.ChartTitle = "Box & Whisker Plot for Price Distribution of Books by Genre"; #endregion chart.Legend.Position = ExcelLegendPosition.Right; if (this.rdbChartSheet.IsChecked != null && this.rdbChartSheet.IsChecked.Value) { chart.Activate(); } else { workbook.Worksheets[0].Activate(); IChartShape chartShape = chart as IChartShape; chartShape.TopRow = 1; chartShape.BottomRow = 20; chartShape.LeftColumn = 6; chartShape.RightColumn = 13; } #region Workbook Save and Close string outFileName = "BoxAndWhisker_Chart.xlsx"; workbook.SaveAs(outFileName); workbook.Close(); #endregion OpenOutput(outFileName); }
public Chart(XElement element) : base(element) { foreach (var xmlSerie in element.Elements("serie")) { var serie = new ChartSerie(xmlSerie); if (serie.Name == "windAverage") average = serie; else if (serie.Name == "windMax") max = serie; } }
public void ExportWithChart() { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; IWorkbook workbook = excelEngine.Excel.Workbooks.Add(); workbook.Version = ExcelVersion.Excel97to2003; IWorksheet workSheet = workbook.Worksheets[0]; IChart chartShape = workSheet.Charts.Add(); IChartSerie series1 = chartShape.Series.Add(); series1.SerieType = ExcelChartType.Column_Clustered; chartShape.ChartTitle = "Column_Clustered"; series1.Values = workSheet.Range["B1:B5"]; series1.CategoryLabels = workSheet.Range["A1:A5"]; Random r = new Random(); for (int i = 1; i <= 4; i++) { workSheet.Range[i, 1].Number = i; workSheet.Range[i, 2].Number = r.Next(4000, 6000); } for (int i = 1; i <= 4; i++) { workSheet.Range[i + 5, 1].Number = i; workSheet.Range[i + 5, 2].Number = r.Next(4000, 6000); } gridControl.CurrentCell.EndEdit(); IRange excelRange = workSheet.Range[21, 5]; GridRangeInfoList rangeList = gridControl.Model.SelectedRanges; if (rangeList.Count > 0) { GridRangeInfo range = rangeList[0]; gridControl.Model.ExportToExcel(range, workSheet, excelRange); try { workbook.SaveAs("Sample.xls"); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo("Sample.xls"); info.UseShellExecute = true; System.Diagnostics.Process.Start(info); //System.Diagnostics.Process.Start("Sample.xls"); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } } else { MessageBox.Show("Select the range first"); } }
public Chart(XElement element) : base(element) { foreach (var xmlSerie in element.Elements("serie")) { var serie = new ChartSerie(xmlSerie); if (serie.Name == "windAverage") { average = serie; } else if (serie.Name == "windMax") { max = serie; } } }
// // GET: /Bar/ public ActionResult BoxAndWhisker(string button, string Saveoption) { if (button == null) { return(View()); } if (button == "Input Template") { //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"BoxAndWhiskerTemplate.xlsx")); return(excelEngine.SaveAsActionResult(workbook, "InputTemplate.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } else { //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //Open workbook with Data IWorkbook workbook = excelEngine.Excel.Workbooks.Open(ResolveApplicationDataPath("BoxAndWhiskerTemplate.xlsx")); IWorksheet sheet = workbook.Worksheets[0]; IChart chart = null; if (Saveoption == "sheet") { chart = workbook.Charts.Add(); } else { chart = workbook.Worksheets[0].Charts.Add(); } #region Box and Whisker Chart Settings chart.ChartType = ExcelChartType.BoxAndWhisker; chart.DataRange = sheet["B1:C181"]; IChartSerie series = chart.Series[0]; series.SerieFormat.ShowInnerPoints = false; series.SerieFormat.ShowOutlierPoints = true; series.SerieFormat.ShowMeanMarkers = true; series.SerieFormat.ShowMeanLine = false; series.SerieFormat.QuartileCalculationType = ExcelQuartileCalculation.ExclusiveMedian; series.SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.Grey_25_percent; chart.ChartTitle = "Box & Whisker Plot for Price Distribution of Books by Genre"; #endregion chart.Legend.Position = ExcelLegendPosition.Right; if (Saveoption == "sheet") { chart.Activate(); } else { workbook.Worksheets[0].Activate(); IChartShape chartShape = chart as IChartShape; chartShape.TopRow = 1; chartShape.BottomRow = 20; chartShape.LeftColumn = 6; chartShape.RightColumn = 13; } try { return(excelEngine.SaveAsActionResult(workbook, "BoxAndWhisker_Chart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } catch (Exception) { } workbook.Close(); excelEngine.Dispose(); } return(View()); }
// // GET: /CallCenterDashboard/ public ActionResult CallCenterDashboard(string button) { if (button == null) { return(View()); } else if (button == "Input Template") { //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; application.EnablePartialTrustCode = true; IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"CallCenterTemplate.xlsx")); return(excelEngine.SaveAsActionResult(workbook, "Template.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } else { #region Workbook Initialize //Initialize the spreadsheet creation engine ExcelEngine excelEngine = new ExcelEngine(); //Initialize the Excel application object IApplication application = excelEngine.Excel; //Set the default application version application.DefaultVersion = ExcelVersion.Excel2016; //Enable the incremental formula application.EnableIncrementalFormula = true; //Load the existing Excel document into IWorkbook IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"CallCenterTemplate.xlsx")); #endregion #region Calculation Sheet //Access the Calculation sheet IWorksheet calculation = workbook.Worksheets["Calculation"]; //Formula calculation is enabled for the sheet calculation.EnableSheetCalculations(); calculation.Range["A1"].Text = "Week"; calculation.Range["B1"].Text = "Sorting Control"; calculation.Range["D2"].NumberFormat = "dd-mmm-yyyy"; calculation.Range["D2"].Formula = "=DATE(2016,1,4)+7*(A2-1)"; calculation.Range["E2"].Formula = "=\"Week # \"&A2"; #region Call data in the week calculation.Range["A4"].Text = "Total Calls"; calculation.Range["B4"].Formula = "=COUNTIF(Data[Column1],TRUE)"; calculation.Range["A5"].Text = "Calls Answered"; calculation.Range["B5"].Formula = "=SUMPRODUCT((Data[Answered (Y/N)]=\"Y\")*(Data[Column1]=TRUE))"; calculation.Range["A6"].Text = "Avg Speed of Answer"; calculation.Range["B6"].Formula = "=SUMPRODUCT((Data[Speed of Answer]),--(Data[Column1]=TRUE))/B4"; calculation.Range["A7"].Text = "Abandon Rate"; calculation.Range["B7"].Formula = "=SUMPRODUCT((Data[Answered (Y/N)]=\"N\")*(Data[Column1]=TRUE))/B4"; calculation.Range["A8"].Text = "Avg Call/Min"; calculation.Range["B8"].Formula = "=B4/(7*9*60)"; calculation.Range["A9"].Text = "Satisfaction Overall"; calculation.Range["B9"].Formula = "=SUMPRODUCT((Data[Satisfaction rating]),--(Data[Column1]=TRUE))/B5"; calculation.Range["A10"].Text = "Calls of Less than 180 Seconds"; calculation.Range["B10"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Answered (Y/N)]=\"Y\")*(Data[AvgTalkDuration]<TIME(0,3,0)))"; calculation.Range["A11"].Text = "% Calls of Less than 180 Seconds"; calculation.Range["B11"].Formula = "=B10/B5"; calculation.Range["A12"].Text = "Satisfaction less than equal to 3"; calculation.Range["B12"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Date]<D2+6)*(Data[Satisfaction rating]<=3))"; #endregion #region Call data of each agent calculation.Range["A15"].Text = "Agent Name"; calculation.Range["B15"].Text = "Total Calls"; calculation.Range["C15"].Text = "Calls Answered"; calculation.Range["D15"].Text = "Avg Speed of Answer"; calculation.Range["E15"].Text = "Call Resolution %"; calculation.Range["F15"].Text = "Call Resolved"; calculation.Range["H15"].Text = "For Sorting"; calculation.Range["M15"].Text = "Total Calls"; calculation.Range["N15"].Text = "Calls Answered"; calculation.Range["O15"].Text = "Avg Speed of Answer"; calculation.Range["P15"].Text = "Call Resolution (%)"; calculation.Range["A16"].Text = "Diane"; calculation.Range["A17"].Text = "Becky"; calculation.Range["A18"].Text = "Stewart"; calculation.Range["A19"].Text = "Greg"; calculation.Range["A20"].Text = "Jim"; calculation.Range["A21"].Text = "Joe"; calculation.Range["A22"].Text = "Martha"; calculation.Range["A23"].Text = "Dan"; calculation.Range["B16:B23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16))"; calculation.Range["C16:C23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16)*(Data[Answered (Y/N)]=\"Y\"))"; calculation.Range["D16:D23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16),(Data[Speed of Answer]))/C16"; calculation.Range["E16:E23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16)*(Data[Resolved]=\"Y\"))/B16"; calculation.Range["F16:F23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16)*(Data[Resolved]=\"Y\"))"; calculation.Range["H16:H23"].Formula = "=INDEX($B$16:$E$23,ROWS($G$16:G16),$B$2)"; calculation.Range["I16:I23"].Formula = "=H16+ROWS($H$16:H16)/1000000"; calculation.Range["J16:J23"].Formula = "=IF($B$2=3,SMALL($I$16:$I$23,ROWS($I$16:I16)),LARGE($I$16:$I$23,ROWS($I$16:I16)))"; calculation.Range["K16:K23"].Formula = "=MATCH(J16,$I$16:$I$23,0)"; calculation.Range["L16:L23"].Formula = "=INDEX($A$16:$A$23,K16)"; calculation.Range["M16:M23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:L16))"; calculation.Range["N16:N23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:M16))"; calculation.Range["O16:O23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:N16))"; calculation.Range["P16:P23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:O16))"; #endregion #region Overall Satisfaction chart calculation.Range["A25"].Text = "Satisfaction Chart"; calculation.Range["A26"].Number = 50; calculation.Range["A27"].Number = 20; calculation.Range["A28"].Number = 30; calculation.Range["A29"].Number = 100; calculation.Range["B26"].Formula = "=B9*20-2"; calculation.Range["B27"].Number = 2; calculation.Range["B28"].Formula = "=200-B26"; #endregion #region call data of each agent per day calculation.Range["A32"].Text = "Agent Name"; calculation.Range["B32"].Text = "Mon"; calculation.Range["C32"].Text = "Tue"; calculation.Range["D32"].Text = "Wed"; calculation.Range["E32"].Text = "Thu"; calculation.Range["F32"].Text = "Fri"; calculation.Range["G32"].Text = "Sat"; calculation.Range["H32"].Text = "Sun"; calculation.Range["A33:A40"].Formula = "=L16"; calculation.Range["B33:B40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:A33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["C33:C40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:B33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["D33:D40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:C33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["E33:E40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:D33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["F33:F40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:E33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["G33:G40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:F33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["H33:H40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:G33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; #endregion #region Satisfaction score for each agent calculation.Range["A43"].Text = "Agent Name"; calculation.Range["B43"].Text = "Satisfaction Score"; calculation.Range["C43"].Text = "Target"; calculation.Range["A44:A51"].Formula = "=A33"; calculation.Range["B44:B51"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=$A44),(Data[Satisfaction rating]))/N16"; calculation.Range["C44:C51"].Number = 3.5; calculation.Range["D44:D51"].Formula = "=IF(B44>C44,$A$52&\" \"&A44,A44)"; calculation.Range["E44:E51"].Formula = "=B44"; #endregion #region call data for each product calculation.Range["B54"].Text = "Total Cell"; calculation.Range["C54"].Text = "Call Answered"; calculation.Range["D54"].Text = "Abandoned Calls %"; calculation.Range["E54"].Text = "SLA Limit"; calculation.Range["F54"].Text = "SLA Breached"; calculation.Range["A55"].Text = "Washing Machine"; calculation.Range["A56"].Text = "Toaster"; calculation.Range["A57"].Text = "Fridge"; calculation.Range["A58"].Text = "Air Conditioner"; calculation.Range["A59"].Text = "Television"; calculation.Range["B55:B59"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Department]=A55))"; calculation.Range["C55:C59"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Department]=A55)*(Data[Answered (Y/N)]=\"Y\"))"; calculation.Range["D55:D59"].Formula = "=(B55-C55)/B55"; calculation.Range["E55:E59"].NumberFormat = "0%"; calculation.Range["E55:E59"].Value = "20%"; calculation.Range["F55:F59"].Formula = "=IF(D55>E55,D55,NA())"; #endregion //Formula calculation is disabled for the sheet calculation.DisableSheetCalculations(); #endregion #region Dashboard Sheet //Create Dashboard sheet IWorksheet dashboard = workbook.Worksheets.Create("Dashboard"); dashboard.Range["A1"].ColumnWidth = 0.5; dashboard.Range["A1"].RowHeight = 30; dashboard.Range["I1"].ColumnWidth = 0.5; #region Marcos for selecting the week and sort option //Cell Style for B1 to R1 dashboard.Range["B1:R1"].CellStyle.Color = Color.FromArgb(48, 13, 225); //Cell Style and text in B6 - Click to Sort dashboard.Range["B6:B7"].Merge(); dashboard.Range["B6"].Text = "Click to Sort"; dashboard.Range["B6"].CellStyle.Font.RGBColor = Color.FromArgb(48, 13, 225); dashboard.Range["B6"].CellStyle.Font.Italic = true; dashboard.Range["B6"].HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["B6"].VerticalAlignment = ExcelVAlign.VAlignCenter; //Macros for Scroll Bar and Option Buttons IVbaProject project = workbook.VbaProject; IVbaModules vbaModules = project.Modules; IVbaModule scrollBar = vbaModules.Add("scrollBar", VbaModuleType.StdModule); scrollBar.Code = "Sub Auto_Open()" + "\n Dim Worksheet_Name As String" + "\n Worksheet_Name = \"Dashboard\"" + "\n ThisWorkbook.Worksheets(Worksheet_Name).Select" + "\n ThisWorkbook.Worksheets(Worksheet_Name).ScrollBars.Add(12, 3.5, 96.5, 20).Select" + "\n With Selection" + "\n .Value = 0" + "\n .Min = 1" + "\n .Max = 4" + "\n .SmallChange = 1" + "\n .LargeChange = 10" + "\n .LinkedCell = \"=Calculation!A2\"" + "\n .Display3DShading = True" + "\n End With" + "\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(98.5, 97, 63, 17.5).Select" + "\n Selection.Characters.Text = \"\"" + "\n With Selection" + "\n .Value = xlOn" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" + "\n End With" + "\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(166, 97, 63, 17.5).Select" + "\n Selection.Characters.Text = \"\"" + "\n With Selection" + "\n .Value = xlOff" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" + "\n End With" + "\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(270, 97, 63, 17.5).Select" + "\n Selection.Characters.Text = \"\"" + "\n With Selection" + "\n .Value = xlOff" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" + "\n End With" + "\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(380, 97, 63, 17.5).Select" + "\n Selection.Characters.Text = \"\"" + "\n With Selection" + "\n .Value = xlOff" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" + "\n End With" + "\n End Sub"; #endregion #region Week display //Week Selected dashboard.Range["D1"].Formula = "=Calculation!E2"; dashboard.Range["D1"].CellStyle.Font.Bold = true; dashboard.Range["D1"].CellStyle.Font.Italic = true; dashboard.Range["D1"].CellStyle.Font.Size = 16; dashboard.Range["D1"].CellStyle.Font.RGBColor = Color.FromArgb(225, 225, 225); dashboard.Range["D1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["D1"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; #endregion #region Call data overview for the week dashboard.Range["B2:D3"].Merge(); dashboard.Range["B4:D5"].Merge(); dashboard.Range["E2:G3"].Merge(); dashboard.Range["E4:G5"].Merge(); dashboard.Range["H2:M3"].Merge(); dashboard.Range["H4:M5"].Merge(); dashboard.Range["N2:R3"].Merge(); dashboard.Range["N4:R5"].Merge(); dashboard.Range["B2:N4"].CellStyle.Font.FontName = "Verdana"; dashboard.Range["B2:N4"].CellStyle.Font.Bold = true; dashboard.Range["B2:N2"].CellStyle.Font.RGBColor = Color.FromArgb(0, 0, 0); dashboard.Range["B4:N4"].CellStyle.Font.RGBColor = Color.FromArgb(48, 13, 225); dashboard.Range["B2:N2"].CellStyle.Font.Size = 12; dashboard.Range["B4:N4"].CellStyle.Font.Size = 18; dashboard.Range["B2:N4"].HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["B2:N4"].VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["B2:N4"].CellStyle.Color = Color.FromArgb(217, 217, 217); dashboard.Range["B2"].Text = "Total Calls"; dashboard.Range["E2"].Text = "Avg. Answer Speed (in sec)"; //Create a rich text string IRichTextString richText = dashboard.Range["E2"].RichText; //Create fonts for rich text formatting IFont font1 = workbook.CreateFont(); font1.FontName = "Verdana"; font1.RGBColor = Color.FromArgb(0, 0, 0); font1.Bold = true; font1.Size = 12; richText.SetFont(0, 17, font1); IFont font2 = workbook.CreateFont(); font2.FontName = "Verdana"; font2.RGBColor = Color.FromArgb(0, 0, 0); font2.Bold = false; font2.Size = 10; richText.SetFont(18, 25, font2); dashboard.Range["H2"].Text = "Abandon rate"; dashboard.Range["N2"].Text = "Avg Calls/Minute"; dashboard.Range["B4"].Formula = "=Calculation!B4"; dashboard.Range["E4"].NumberFormat = "0.0"; dashboard.Range["E4"].Formula = "=Calculation!B6"; dashboard.Range["H4"].NumberFormat = "0.0%"; dashboard.Range["H4"].Formula = "=Calculation!B7"; dashboard.Range["N4"].NumberFormat = "0.000"; dashboard.Range["N4"].Formula = "=Calculation!B8"; dashboard.Range["B2:D5"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["E2:G5"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["H2:M5"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["N2:R5"].BorderAround(ExcelLineStyle.Thin, Color.Black); #endregion #region Table to display call data of each agent dashboard.Range["B8"].Text = "Agent Name"; dashboard.Range["C8"].Text = "Total Calls"; dashboard.Range["D8"].Text = "Calls Answered"; dashboard.Range["E8"].Text = "Avg. Speed of Answer"; dashboard.Range["F8"].Text = "Call Resolution (%)"; dashboard.Range["H8"].Text = "CR Trend"; dashboard.Range["B9:B16"].Formula = "=Calculation!L16"; dashboard.Range["C9:C16"].Formula = "=Calculation!M16"; dashboard.Range["D9:D16"].Formula = "=Calculation!N16"; dashboard.Range["E9:E16"].NumberFormat = "0.0"; dashboard.Range["E9:E16"].Formula = "=Calculation!O16"; dashboard.Range["F9:F16"].NumberFormat = "0.0%"; dashboard.Range["F9:F16"].Formula = "=Calculation!P16"; dashboard.Range["G9:G16"].Formula = "=F9"; #region Conditional formats //Create icon sets for the data in the specified range IConditionalFormats conditionalFormats = dashboard.Range["G9:G16"].ConditionalFormats; IConditionalFormat conditionalFormat = conditionalFormats.AddCondition(); conditionalFormat.FormatType = ExcelCFType.IconSet; IIconSet iconSet = conditionalFormat.IconSet; iconSet.IconSet = ExcelIconSetType.ThreeSigns; //Apply three signs icon and hide the data in the specified range IIconConditionValue iconValue2 = iconSet.IconCriteria[1] as IIconConditionValue; iconValue2.IconSet = ExcelIconSetType.ThreeSigns; iconValue2.Index = 1; iconValue2.Type = ConditionValueType.Number; iconValue2.Value = "0.7"; iconValue2.Operator = ConditionalFormatOperator.GreaterThan; IIconConditionValue iconValue3 = iconSet.IconCriteria[2] as IIconConditionValue; iconValue3.IconSet = ExcelIconSetType.ThreeSigns; iconValue3.Index = 2; iconValue3.Type = ConditionValueType.Number; iconValue3.Value = "0.8"; iconValue3.Operator = ConditionalFormatOperator.GreaterThanorEqualTo; iconSet.ShowIconOnly = true; #endregion //Auto-fit columns dashboard.Range["B8:G16"].AutofitColumns(); dashboard.Range["H8"].ColumnWidth = 11; #region Sparklines //Add sparkline groups ISparklineGroup sparklineGroup = dashboard.SparklineGroups.Add(); sparklineGroup.SparklineType = SparklineType.Line; sparklineGroup.MarkersColor = Color.FromArgb(51, 102, 153); sparklineGroup.LowPointColor = Color.FromArgb(192, 0, 0); sparklineGroup.ShowHighPoint = false; sparklineGroup.ShowFirstPoint = false; sparklineGroup.ShowLastPoint = false; sparklineGroup.ShowMarkers = false; //Add sparklines ISparklines sparklines = sparklineGroup.Add(); IRange dataRange = calculation.Range["B33:H40"]; IRange referenceRange = dashboard.Range["H9:H16"]; sparklines.Add(dataRange, referenceRange); #endregion //Apply range formatting using cellstyle properties dashboard.Range["B8:E16"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["B8:E16"].BorderInside(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F8:G8"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F9:G9"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F10:G10"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F11:G11"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F12:G12"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F13:G13"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F14:G14"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F15:G15"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F16:G16"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["H8:H16"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["B8:H8"].CellStyle.Font.Bold = true; dashboard.Range["B8:H8"].CellStyle.Font.Italic = true; dashboard.Range["B8:H8"].CellStyle.Color = Color.FromArgb(217, 217, 217); #endregion #region Column Chart - Call Abandon Rate By Department //Column Chart - Chart Title dashboard.Range["J8:R8"].Merge(); dashboard.Range["J8"].CellStyle.Font.Italic = true; dashboard.Range["J8"].CellStyle.Font.Size = 12; dashboard.Range["J8"].CellStyle.Font.RGBColor = Color.FromArgb(127, 127, 127); dashboard.Range["J8"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["J8"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["J8"].Text = "Call Abandon Rate - By Department"; //Column Chart - Call Abandon Rate - By Department IChartShape columnChart = dashboard.Charts.Add(); IChartSerie columnserieOne = columnChart.Series.Add(); columnserieOne.Values = calculation.Range["D55:D59"]; columnserieOne.CategoryLabels = calculation.Range["A55:A59"]; columnserieOne.SerieFormat.CommonSerieOptions.GapWidth = 150; columnserieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; columnChart.PrimaryValueAxis.MajorUnit = 0.1; columnChart.PrimaryValueAxis.NumberFormat = "0.0%"; columnChart.PrimaryValueAxis.HasMajorGridLines = false; columnChart.HasLegend = false; columnChart.ChartArea.Border.LineColor = Color.White; columnChart.Left = 1; columnChart.TopRow = 9; columnChart.LeftColumn = 10; columnChart.BottomRow = 17; columnChart.RightColumn = 19; (columnChart as IChart).Height = (columnChart as IChart).Height - 10; (columnChart as IChart).Width = (columnChart as IChart).Width - 10; dashboard.Range["J8:R16"].BorderAround(ExcelLineStyle.Thin, Color.Black); #endregion #region SLA Limits dashboard.Range["J18:R18"].Merge(); dashboard.Range["J21:O23"].Merge(); dashboard.Range["J26:O28"].Merge(); dashboard.Range["P21:R23"].Merge(); dashboard.Range["P26:R28"].Merge(); dashboard.Range["J18"].Text = "SLA LIMITS"; dashboard.Range["J18"].CellStyle.Font.Bold = true; dashboard.Range["J18"].CellStyle.Font.Size = 16; dashboard.Range["J18"].CellStyle.Font.FontName = "Calibri (body)"; dashboard.Range["J18"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["J18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["J18"].CellStyle.Font.Color = ExcelKnownColors.Black; dashboard.Range["J18"].CellStyle.Color = Color.FromArgb(217, 217, 217); dashboard.Range["J18:R18"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["J21"].Text = "Calls answered in less than 180 Seconds:"; dashboard.Range["J21"].CellStyle.Font.Bold = true; dashboard.Range["J21"].CellStyle.Font.Size = 14; dashboard.Range["J21"].CellStyle.Font.FontName = "Verdana"; dashboard.Range["J21"].CellStyle.WrapText = true; dashboard.Range["J21"].CellStyle.Font.Color = ExcelKnownColors.Black; dashboard.Range["J21"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["P21"].Formula = "=Calculation!B11"; dashboard.Range["P21"].CellStyle.Font.Bold = true; dashboard.Range["P21"].CellStyle.Font.Size = 28; dashboard.Range["P21"].CellStyle.Font.FontName = "Verdana"; dashboard.Range["P21"].NumberFormat = "0.0%"; dashboard.Range["P21"].CellStyle.Font.RGBColor = Color.FromArgb(84, 130, 53); dashboard.Range["P21"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["P21"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft; dashboard.Range["J26"].Text = "Calls with satisfaction score less than 3:"; dashboard.Range["J26"].CellStyle.Font.Bold = true; dashboard.Range["J26"].CellStyle.Font.Size = 14; dashboard.Range["J26"].CellStyle.Font.FontName = "Verdana"; dashboard.Range["J26"].CellStyle.WrapText = true; dashboard.Range["J26"].CellStyle.Font.Color = ExcelKnownColors.Black; dashboard.Range["J26"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["P26"].Formula = "=Calculation!B12"; dashboard.Range["P26"].CellStyle.Font.Bold = true; dashboard.Range["P26"].CellStyle.Font.Size = 28; dashboard.Range["P26"].CellStyle.Font.FontName = "Verdana"; dashboard.Range["P26"].CellStyle.Font.RGBColor = Color.FromArgb(192, 0, 0); dashboard.Range["P26"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["P26"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft; dashboard.Range["J19:R30"].BorderAround(ExcelLineStyle.Thin, Color.Black); #endregion #region Bar Chart - Satisfaction Score By Agent //Bar Chart - Chart Title dashboard.Range["E18:H18"].Merge(); dashboard.Range["E18"].CellStyle.Font.Italic = true; dashboard.Range["E18"].CellStyle.Font.Underline = ExcelUnderline.Single; dashboard.Range["E18"].CellStyle.Font.Size = 12; dashboard.Range["E18"].CellStyle.Font.RGBColor = Color.FromArgb(127, 127, 127); dashboard.Range["E18"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["E18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["E18"].Text = "Satisfaction Score - By Agent"; //Bar Chart - Satisfaction Score - By Agent IChartShape barChart = dashboard.Charts.Add(); IChartSerie barSerieOne = barChart.Series.Add(); barSerieOne.SerieType = ExcelChartType.Bar_Clustered; barSerieOne.CategoryLabels = calculation.Range["D44:D51"]; barSerieOne.Values = calculation.Range["E44:E51"]; barChart.PrimaryValueAxis.MinimumValue = 0; barChart.PrimaryValueAxis.MaximumValue = 5; barChart.PrimaryValueAxis.HasMajorGridLines = false; barChart.HasLegend = false; barChart.ChartArea.Border.LineColor = Color.White; barChart.Top = 1; barChart.TopRow = 20; barChart.LeftColumn = 5; barChart.BottomRow = 31; barChart.RightColumn = 9; (barChart as IChart).Height = (barChart as IChart).Height - 10; (barChart as IChart).Width = (barChart as IChart).Width - 10; #endregion #region Doughnut and Pie Chart - Overall Satisfaction Score //Doughnut Chart - Chart Title dashboard.Range["B18:D18"].Merge(); dashboard.Range["B18"].CellStyle.Font.Italic = true; dashboard.Range["B18"].CellStyle.Font.Underline = ExcelUnderline.Single; dashboard.Range["B18"].CellStyle.Font.Size = 12; dashboard.Range["B18"].CellStyle.Font.RGBColor = Color.FromArgb(127, 127, 127); dashboard.Range["B18"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["B18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["B18"].Text = "Overall Satisfaction Score"; //Doughnut Chart - Overall Satisfaction Score IChartShape doughnutChart = dashboard.Charts.Add(); IChartSerie doughnutSerie = doughnutChart.Series.Add(); doughnutChart.ChartType = ExcelChartType.Doughnut; doughnutSerie.Values = calculation.Range["A26:A29"]; doughnutSerie.SerieFormat.CommonSerieOptions.FirstSliceAngle = 270; doughnutSerie.DataPoints[0].DataFormat.Fill.ForeColor = Color.FromArgb(255, 0, 0); doughnutSerie.DataPoints[1].DataFormat.Fill.ForeColor = Color.FromArgb(255, 192, 0); doughnutSerie.DataPoints[2].DataFormat.Fill.ForeColor = Color.FromArgb(0, 176, 80); doughnutSerie.DataPoints[3].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.White; doughnutSerie.DataPoints[3].DataFormat.Fill.Transparency = 1.0; doughnutChart.HasLegend = false; doughnutChart.ChartArea.Border.LineColor = Color.White; doughnutChart.ChartArea.Fill.Transparency = 1.0; doughnutChart.PlotArea.Fill.Transparency = 1.0; doughnutChart.Left = 1; doughnutChart.TopRow = 20; doughnutChart.LeftColumn = 2; doughnutChart.BottomRow = 32; doughnutChart.RightColumn = 5; //Pie Chart - Overall Satisfaction Score IChartShape pieChart = dashboard.Charts.Add(); IChartSerie pieSerieOne = pieChart.Series.Add(); pieSerieOne.SerieType = ExcelChartType.Pie; pieSerieOne.Values = calculation.Range["B26:B28"]; pieSerieOne.SerieFormat.CommonSerieOptions.FirstSliceAngle = 270; pieSerieOne.DataPoints[0].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.White; pieSerieOne.DataPoints[0].DataFormat.Fill.Transparency = 1.0; pieSerieOne.DataPoints[1].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.Black; pieSerieOne.DataPoints[1].DataFormat.LineProperties.LineColor = Color.White; pieSerieOne.DataPoints[1].DataFormat.LineProperties.LineWeight = ExcelChartLineWeight.Narrow; pieSerieOne.DataPoints[2].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.White; pieSerieOne.DataPoints[2].DataFormat.Fill.Transparency = 1.0; pieChart.HasLegend = false; pieChart.ChartArea.Border.LineColor = Color.White; pieChart.ChartArea.Fill.Transparency = 1.0; pieChart.PlotArea.Fill.Transparency = 1.0; pieChart.Top = 1; pieChart.Left = 1; pieChart.TopRow = 20; pieChart.LeftColumn = 2; pieChart.BottomRow = 33; pieChart.RightColumn = 5; dashboard.Range["B30:C30"].Merge(); dashboard.Range["B30"].Text = "Satisafction Score:"; dashboard.Range["B30"].HorizontalAlignment = ExcelHAlign.HAlignRight; dashboard.Range["B30"].VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["D30"].NumberFormat = "0.00"; dashboard.Range["D30"].Formula = "=Calculation!B9"; dashboard.Range["D30"].CellStyle.Font.Size = 18; dashboard.Range["D30"].CellStyle.Font.Bold = true; dashboard.Range["D30"].CellStyle.Font.RGBColor = Color.FromArgb(0, 112, 192); dashboard.Range["D30"].HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["B18:H30"].BorderAround(ExcelLineStyle.Thin, Color.Black); #endregion //Disable the gridlines dashboard.IsGridLinesVisible = false; //Set the dashboard sheet as active sheet dashboard.Activate(); #endregion #region Save the Workbook //Save the workbook to disk workbook.Version = ExcelVersion.Excel2016; return(excelEngine.SaveAsActionResult(workbook, "CallCenterDashboard.xlsm", ExcelSaveType.SaveAsMacro, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); #endregion } }
// // GET: /Bar/ public ActionResult EmbeddedChart(string button, string SaveOption) { string basePath = _hostingEnvironment.WebRootPath; if (button == null) { return(View()); } //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //Open workbook with Data FileStream inputStream = new FileStream(basePath + @"/XlsIO/EmbeddedChart.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream); if (SaveOption == "Xls") { workbook.Version = ExcelVersion.Excel97to2003; } else { workbook.Version = ExcelVersion.Excel2016; } //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; sheet.Name = "Sample"; // Adding a New chart to the Existing Worksheet IChartShape chart = workbook.Worksheets[0].Charts.Add(); chart.DataRange = sheet.Range["A3:C15"]; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; chart.PrimaryCategoryAxis.Title = "Month"; IChartSerie serieOne = chart.Series[0]; //set the Chart Type chart.ChartType = ExcelChartType.Column_Clustered_3D; //set the Backwall fill option chart.BackWall.Fill.FillType = ExcelFillType.Gradient; //set the Texture Type chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor; chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down; chart.BackWall.Fill.ForeColor = Color.WhiteSmoke; chart.BackWall.Fill.BackColor = Color.LightBlue; //set the Border Linecolor chart.BackWall.Border.LineColor = Color.Wheat; //set the Picture Type chart.BackWall.PictureUnit = ExcelChartPictureType.stretch; //set the Backwall thickness chart.BackWall.Thickness = 10; //set the sidewall fill option chart.SideWall.Fill.FillType = ExcelFillType.SolidColor; //set the sidewall foreground and backcolor chart.SideWall.Fill.BackColor = Color.White; chart.SideWall.Fill.ForeColor = Color.White; //set the side wall Border color chart.SideWall.Border.LineColor = Color.Beige; //set floor fill option chart.Floor.Fill.FillType = ExcelFillType.Pattern; //set the floor pattern Type chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot; //Set the floor fore and Back ground color chart.Floor.Fill.ForeColor = Color.Blue; chart.Floor.Fill.BackColor = Color.White; //set the floor thickness chart.Floor.Thickness = 3; IChartSerie serieTwo = chart.Series[1]; //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; //Embedded Chart Position chart.TopRow = 2; chart.BottomRow = 30; chart.LeftColumn = 5; chart.RightColumn = 18; serieTwo.Name = "Temperature,deg.F"; // Legend setting chart.Legend.Position = ExcelLegendPosition.Right; chart.Legend.IsVerticalLegend = false; try { string ContentType = null; string fileName = null; if (SaveOption == "Xls") { workbook.Version = ExcelVersion.Excel97to2003; ContentType = "Application/vnd.ms-excel"; fileName = "EmbeddedChart.xls"; } else { workbook.Version = ExcelVersion.Excel2013; ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; fileName = "EmbeddedChart.xlsx"; } MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; return(File(ms, ContentType, fileName)); } catch (Exception) { } workbook.Close(); excelEngine.Dispose(); return(View()); }
// // GET: /ChartWorksheet/ public ActionResult ChartWorksheet(string Saveoption) { if (Saveoption == null) { return(View()); } //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; // Default version is set as Excel 2007 if (Saveoption == "Xls") { application.DefaultVersion = ExcelVersion.Excel97to2003; } else { application.DefaultVersion = ExcelVersion.Excel2016; } //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //The new workbook will have 1 worksheet. IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; // Entering the Datas for the chart sheet.Range["A1"].Text = "Crescent City, CA"; sheet.Range["A1:D1"].Merge(); sheet.Range["A1"].CellStyle.Font.Bold = true; sheet.Range["B3"].Text = "Precipitation,in."; sheet.Range["C3"].Text = "Temperature,deg.F"; sheet.Range["A4"].Text = "Jan"; sheet.Range["A5"].Text = "Feb"; sheet.Range["A6"].Text = "March"; sheet.Range["A7"].Text = "Apr"; sheet.Range["A8"].Text = "May"; sheet.Range["A9"].Text = "June"; sheet.Range["A10"].Text = "July"; sheet.Range["A11"].Text = "Aug"; sheet.Range["A12"].Text = "Sept"; sheet.Range["A13"].Text = "Oct"; sheet.Range["A14"].Text = "Nov"; sheet.Range["A15"].Text = "Dec"; sheet.Range["B4"].Number = 10.9; sheet.Range["B5"].Number = 8.9; sheet.Range["B6"].Number = 8.6; sheet.Range["B7"].Number = 4.8; sheet.Range["B8"].Number = 3.2; sheet.Range["B9"].Number = 1.4; sheet.Range["B10"].Number = 0.6; sheet.Range["B11"].Number = 0.7; sheet.Range["B12"].Number = 1.7; sheet.Range["B13"].Number = 5.4; sheet.Range["B14"].Number = 9.0; sheet.Range["B15"].Number = 10.4; sheet.Range["C4"].Number = 47.5; sheet.Range["C5"].Number = 48.7; sheet.Range["C6"].Number = 48.9; sheet.Range["C7"].Number = 50.2; sheet.Range["C8"].Number = 53.1; sheet.Range["C9"].Number = 56.3; sheet.Range["C10"].Number = 58.1; sheet.Range["C11"].Number = 59.0; sheet.Range["C12"].Number = 58.5; sheet.Range["C13"].Number = 55.4; sheet.Range["C14"].Number = 51.1; sheet.Range["C15"].Number = 47.8; sheet.UsedRange.AutofitColumns(); // Adding a New chart to the Existing Worksheet IChart chart = workbook.Charts.Add(); chart.DataRange = sheet.Range["A3:C15"]; chart.Name = "CrescentCity,CA"; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; // Format serie IChartSerie serieOne = chart.Series[0]; serieOne.Name = "Precipitation,in."; serieOne.SerieFormat.Fill.FillType = ExcelFillType.Gradient; serieOne.SerieFormat.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); serieOne.SerieFormat.Fill.GradientColorType = ExcelGradientColor.TwoColor; serieOne.SerieFormat.Fill.ForeColor = Color.Plum; //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieOne.DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside; //Format the second serie IChartSerie serieTwo = chart.Series[1]; serieTwo.SerieType = ExcelChartType.Line_Markers; serieTwo.Name = "Temperature,deg.F"; //Format marker serieTwo.SerieFormat.MarkerStyle = ExcelChartMarkerType.Diamond; serieTwo.SerieFormat.MarkerSize = 8; serieTwo.SerieFormat.MarkerBackgroundColor = Color.DarkGreen; serieTwo.SerieFormat.MarkerForegroundColor = Color.DarkGreen; serieTwo.SerieFormat.LineProperties.LineColor = Color.DarkGreen; //Use Secondary Axis serieTwo.UsePrimaryAxis = false; //Display secondary axis for the series. chart.SecondaryCategoryAxis.IsMaxCross = true; chart.SecondaryValueAxis.IsMaxCross = true; //Set the title chart.SecondaryValueAxis.Title = "Temperature,deg.F"; chart.SecondaryValueAxis.TitleArea.TextRotationAngle = 90; //Hide the secondary category axis chart.SecondaryCategoryAxis.Border.LineColor = Color.Transparent; chart.SecondaryCategoryAxis.MajorTickMark = ExcelTickMark.TickMark_None; chart.SecondaryCategoryAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_None; chart.Legend.Position = ExcelLegendPosition.Bottom; chart.Legend.IsVerticalLegend = false; sheet.Move(1); chart.Activate(); try { string ContentType = null; string fileName = null; if (Saveoption == "Xls") { workbook.Version = ExcelVersion.Excel97to2003; ContentType = "Application/vnd.ms-excel"; fileName = "Chart.xls"; } else { workbook.Version = ExcelVersion.Excel2013; ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; fileName = "Chart.xlsx"; } MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; return(File(ms, ContentType, fileName)); } catch (Exception) { } // Close the workbook workbook.Close(); excelEngine.Dispose(); return(View()); }
//Export and saves the chart in Excel Sheet #region XLsIO private void buttonXLsIO_Click(object sender, EventArgs e) { try { //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Instantiate the excel application object. IApplication application = excelEngine.Excel; //Set the default version as Excel 2007; application.DefaultVersion = ExcelVersion.Excel2007; exportFileName = fileName + ".xlsx"; //A new workbook with a worksheet is created. IWorkbook chartBook = application.Workbooks.Create(1); IWorksheet sheet = chartBook.Worksheets[0]; //Fill the worksheet by chart data. for (int i = 1; i <= 4; i++) { sheet.Range[i, 1].Number = this.chartControl1.Series[0].Points[i - 1].X; sheet.Range[i, 2].Number = this.chartControl1.Series[0].Points[i - 1].YValues[0]; } for (int i = 1; i <= 4; i++) { sheet.Range[i + 5, 1].Number = this.chartControl1.Series[1].Points[i - 1].X; sheet.Range[i + 5, 2].Number = this.chartControl1.Series[1].Points[i - 1].YValues[0]; } //Create a chart worksheet. IChart chart = chartBook.Charts.Add("Essential Chart"); //Specifies the title of the Chart. chart.ChartTitle = "Essential Chart"; //Initializes a new series instance and adds it to the series collection of the chart. IChartSerie series1 = chart.Series.Add(); //Specify the chart type of the series. series1.SerieType = ExcelChartType.Column_Clustered; //Specify the name of the series. This will be displayed as the text of the legend. series1.Name = "Sample Series"; //Specify the value ranges for the series. series1.Values = sheet.Range["B1:B5"]; //Specify the Category labels for the series. series1.CategoryLabels = sheet.Range["A1:A5"]; IChartSerie series2 = chart.Series.Add(); //Specify the chart type of the series. series2.SerieType = ExcelChartType.Column_Clustered; //Specify the name of the series. This will be displayed as the text of the legend. series2.Name = "Sample Series"; //Specify the value ranges for the series. series2.Values = sheet.Range["B6:B10"]; //Specify the Category labels for the series. series2.CategoryLabels = sheet.Range["A6:A10"]; //Makes the chart as active sheet. chart.Activate(); //Save the Chart book. chartBook.SaveAs(exportFileName); chartBook.Close(); ExcelUtils.Close(); OpenFile("XLsIO", exportFileName); System.Diagnostics.Process.Start(exportFileName); } catch (Exception ex) { this.toolStripStatusLabel1.Text = "Chart Export failed."; Console.WriteLine(ex.ToString()); } }
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e) { #region Setting output location StorageFile storageFile; if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))) { FileSavePicker savePicker = new FileSavePicker(); savePicker.SuggestedStartLocation = PickerLocationId.Desktop; savePicker.SuggestedFileName = "ChartSample"; if (rdBtn2003.IsChecked.Value) { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xls" }); } else { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xlsx", }); } storageFile = await savePicker.PickSaveFileAsync(); } else { StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder; if (rdBtn2003.IsChecked.Value) { storageFile = await local.CreateFileAsync("ChartSample.xls", CreationCollisionOption.ReplaceExisting); } else { storageFile = await local.CreateFileAsync("ChartSample.xlsx", CreationCollisionOption.ReplaceExisting); } } if (storageFile == null) { return; } #endregion #region Initializing workbook //Instantiate excel Engine ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; if (rdBtn2003.IsChecked.Value) { application.DefaultVersion = ExcelVersion.Excel97to2003; } else { application.DefaultVersion = ExcelVersion.Excel2013; } IWorkbook workbook; if (application.DefaultVersion != ExcelVersion.Excel97to2003) { Assembly assembly = typeof(Chart).GetTypeInfo().Assembly; string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.Sparkline.xlsx"; Stream fileStream = assembly.GetManifestResourceStream(resourcePath); workbook = await application.Workbooks.OpenAsync(fileStream); } else { workbook = application.Workbooks.Create(1); } IWorksheet sheet = workbook.Worksheets[0]; #endregion //#if WINDOWS_APP if (application.DefaultVersion != ExcelVersion.Excel97to2003) { #region Sparklines #region WholeSale Report //A new Sparkline group is added to the sheet sparklinegroups ISparklineGroup sparklineGroup = sheet.SparklineGroups.Add(); //Set the Sparkline group type as line sparklineGroup.SparklineType = SparklineType.Line; //Set to display the empty cell as line sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Line; //Sparkline group style properties sparklineGroup.ShowFirstPoint = true; sparklineGroup.FirstPointColor = Color.FromArgb(Colors.Green.A, Colors.Green.R, Colors.Green.G, Colors.Green.B); sparklineGroup.ShowLastPoint = true; sparklineGroup.LastPointColor = Color.FromArgb(Colors.Orange.A, Colors.Orange.R, Colors.Orange.G, Colors.Orange.B); sparklineGroup.ShowHighPoint = true; sparklineGroup.HighPointColor = Color.FromArgb(Colors.Blue.A, Colors.Blue.R, Colors.Blue.G, Colors.Blue.B); sparklineGroup.ShowLowPoint = true; sparklineGroup.LowPointColor = Color.FromArgb(Colors.Purple.A, Colors.Purple.R, Colors.Purple.G, Colors.Purple.B); sparklineGroup.ShowMarkers = true; sparklineGroup.MarkersColor = Color.FromArgb(Colors.Black.A, Colors.Black.R, Colors.Black.G, Colors.Black.B); sparklineGroup.ShowNegativePoint = true; sparklineGroup.NegativePointColor = Color.FromArgb(Colors.Red.A, Colors.Red.R, Colors.Red.G, Colors.Red.B); //set the line weight sparklineGroup.LineWeight = 0.3; //The sparklines are added to the sparklinegroup. ISparklines sparklines = sparklineGroup.Add(); //Set the Sparkline Datarange . IRange dataRange = sheet.Range["D6:G17"]; //Set the Sparkline Reference range. IRange referenceRange = sheet.Range["H6:H17"]; //Create a sparkline with the datarange and reference range. sparklines.Add(dataRange, referenceRange); #endregion #region Retail Trade //A new Sparkline group is added to the sheet sparklinegroups sparklineGroup = sheet.SparklineGroups.Add(); //Set the Sparkline group type as column sparklineGroup.SparklineType = SparklineType.Column; //Set to display the empty cell as zero sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Zero; //Sparkline group style properties sparklineGroup.ShowHighPoint = true; sparklineGroup.HighPointColor = Color.FromArgb(Colors.Green.A, Colors.Green.R, Colors.Green.G, Colors.Green.B); sparklineGroup.ShowLowPoint = true; sparklineGroup.LowPointColor = Color.FromArgb(Colors.Red.A, Colors.Red.R, Colors.Red.G, Colors.Red.B); sparklineGroup.ShowNegativePoint = true; sparklineGroup.NegativePointColor = Color.FromArgb(Colors.Black.A, Colors.Black.R, Colors.Black.G, Colors.Black.B); //The sparklines are added to the sparklinegroup. sparklines = sparklineGroup.Add(); //Set the Sparkline Datarange . dataRange = sheet.Range["D21:G32"]; //Set the Sparkline Reference range. referenceRange = sheet.Range["H21:H32"]; //Create a sparkline with the datarange and reference range. sparklines.Add(dataRange, referenceRange); #endregion #region Manufacturing Trade //A new Sparkline group is added to the sheet sparklinegroups sparklineGroup = sheet.SparklineGroups.Add(); //Set the Sparkline group type as win/loss sparklineGroup.SparklineType = SparklineType.ColumnStacked100; sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Zero; sparklineGroup.DisplayAxis = true; sparklineGroup.AxisColor = Color.FromArgb(Colors.Black.A, Colors.Black.R, Colors.Black.G, Colors.Black.B); sparklineGroup.ShowFirstPoint = true; sparklineGroup.FirstPointColor = Color.FromArgb(Colors.Green.A, Colors.Green.R, Colors.Green.G, Colors.Green.B); sparklineGroup.ShowLastPoint = true; sparklineGroup.LastPointColor = Color.FromArgb(Colors.Orange.A, Colors.Orange.R, Colors.Orange.G, Colors.Orange.B); sparklineGroup.ShowNegativePoint = true; sparklineGroup.NegativePointColor = Color.FromArgb(Colors.Red.A, Colors.Red.R, Colors.Red.G, Colors.Red.B); sparklines = sparklineGroup.Add(); dataRange = sheet.Range["D36:G46"]; referenceRange = sheet.Range["H36:H46"]; sparklines.Add(dataRange, referenceRange); #endregion #endregion } //#endif #region Creating chart datasource IWorksheet chartSheet; if (application.DefaultVersion != ExcelVersion.Excel97to2003) { chartSheet = workbook.Worksheets.Create("Chart Data"); } else { chartSheet = workbook.Worksheets[0]; } // Entering the Datas for the chart chartSheet.Range["A1"].Text = "Crescent City, CA"; chartSheet.Range["A1:D1"].Merge(); chartSheet.Range["A1"].CellStyle.Font.Bold = true; chartSheet.Range["B3"].Text = "Precipitation,in."; chartSheet.Range["C3"].Text = "Temperature,deg.F"; chartSheet.Range["A4"].Text = "Jan"; chartSheet.Range["A5"].Text = "Feb"; chartSheet.Range["A6"].Text = "March"; chartSheet.Range["A7"].Text = "Apr"; chartSheet.Range["A8"].Text = "May"; chartSheet.Range["A9"].Text = "June"; chartSheet.Range["A10"].Text = "July"; chartSheet.Range["A11"].Text = "Aug"; chartSheet.Range["A12"].Text = "Sept"; chartSheet.Range["A13"].Text = "Oct"; chartSheet.Range["A14"].Text = "Nov"; chartSheet.Range["A15"].Text = "Dec"; chartSheet.Range["B4"].Number = 10.9; chartSheet.Range["B5"].Number = 8.9; chartSheet.Range["B6"].Number = 8.6; chartSheet.Range["B7"].Number = 4.8; chartSheet.Range["B8"].Number = 3.2; chartSheet.Range["B9"].Number = 1.4; chartSheet.Range["B10"].Number = 0.6; chartSheet.Range["B11"].Number = 0.7; chartSheet.Range["B12"].Number = 1.7; chartSheet.Range["B13"].Number = 5.4; chartSheet.Range["B14"].Number = 9.0; chartSheet.Range["B15"].Number = 10.4; chartSheet.Range["C4"].Number = 4.5; chartSheet.Range["C5"].Number = 2.7; chartSheet.Range["C6"].Number = 9.9; chartSheet.Range["C7"].Number = 4.2; chartSheet.Range["C8"].Number = 6.1; chartSheet.Range["C9"].Number = 5.3; chartSheet.Range["C10"].Number = 3.1; chartSheet.Range["C11"].Number = 7; chartSheet.Range["C12"].Number = 4.5; chartSheet.Range["C13"].Number = 8.4; chartSheet.Range["C14"].Number = 3.1; chartSheet.Range["C15"].Number = 8.8; chartSheet.UsedRange.AutofitColumns(); #endregion //#if WINDOWS_APP #region Column Chart #region Creating ChartSheet with Wall implementation // Adding a New chart to the Existing Worksheet IChart chart = workbook.Charts.Add("Column Chart"); chart.DataRange = chartSheet.Range["A3:C15"]; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; chart.PrimaryCategoryAxis.Title = "Month"; IChartSerie serieOne = chart.Series[0]; //set the Chart Type chart.ChartType = ExcelChartType.Column_Clustered_3D; //set the Backwall fill option chart.BackWall.Fill.FillType = ExcelFillType.Gradient; //set the Texture Type chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor; chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down; chart.BackWall.Fill.ForeColor = Colors.White; chart.BackWall.Fill.BackColor = Colors.Blue; //set the Border Linecolor chart.BackWall.Border.LineColor = Colors.Brown; //set the Picture Type chart.BackWall.PictureUnit = ExcelChartPictureType.stretch; //set the Backwall thickness chart.BackWall.Thickness = 10; //set the sidewall fill option chart.SideWall.Fill.FillType = ExcelFillType.SolidColor; //set the sidewall foreground and backcolor chart.SideWall.Fill.BackColor = Colors.White; chart.SideWall.Fill.ForeColor = Colors.White; //set the side wall Border color chart.SideWall.Border.LineColor = Colors.Red; //set floor fill option chart.Floor.Fill.FillType = ExcelFillType.Pattern; //set the floor pattern Type chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot; //Set the floor fore and Back ground color chart.Floor.Fill.ForeColor = Colors.Blue; chart.Floor.Fill.BackColor = Colors.White; //set the floor thickness chart.Floor.Thickness = 3; //Set rotation angles chart.Rotation = 20; chart.Elevation = 15; IChartSerie serieTwo = chart.Series[1]; //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieTwo.Name = "Temperature,deg.F"; // Legend setting chart.Legend.Position = ExcelLegendPosition.Right; chart.Legend.IsVerticalLegend = false; //Move the worksheet chartSheet.Move(1); #endregion #endregion //#endif #region Pie Chart IWorksheet embeddedChartSheet = workbook.Worksheets.Create("Pie Chart"); IChartShape embeddedChart = embeddedChartSheet.Charts.Add(); embeddedChartSheet.Activate(); embeddedChart.ChartTitle = "Precipitation in Months"; embeddedChart.IsSeriesInRows = false; embeddedChart.ChartType = ExcelChartType.Pie; embeddedChart.DataRange = chartSheet["A4:B15"]; embeddedChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true; embeddedChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.ShowLeaderLines = true; #endregion #if WINDOWS_PHONE_APP workbook.Worksheets[0].Remove(); embeddedChartSheet.Activate(); #endif #region Saving workbook and disposing objects await workbook.SaveAsAsync(storageFile); workbook.Close(); excelEngine.Dispose(); #endregion #region Save acknowledgement and launching of ouput file MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully."); UICommand yesCmd = new UICommand("Yes"); msgDialog.Commands.Add(yesCmd); UICommand noCmd = new UICommand("No"); msgDialog.Commands.Add(noCmd); IUICommand cmd = await msgDialog.ShowAsync(); if (cmd == yesCmd) { // Launch the saved file bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile); } #endregion }
private void btnCreate_Click(object sender, System.EventArgs e) { #region Workbook Initialize //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. //Set the default version as Excel97to2003 IApplication application = excelEngine.Excel; if (this.rdbExcel97.Checked) { application.DefaultVersion = ExcelVersion.Excel97to2003; fileName = "ChartWorksheet.xls"; } //Set the default version as Excel 2007; else if (this.rdbExcel2007.Checked) { application.DefaultVersion = ExcelVersion.Excel2007; fileName = "ChartWorksheet.xlsx"; } //Set the default version as Excel 2010; else if (this.rdbExcel2010.Checked) { application.DefaultVersion = ExcelVersion.Excel2010; fileName = "ChartWorksheet.xlsx"; } //Set the default version as Excel 2010; else if (this.rdbExcel2013.Checked) { application.DefaultVersion = ExcelVersion.Excel2013; fileName = "ChartWorksheet.xlsx"; } //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 1 worksheet. IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region Insert Data into Cells of Worksheet // Entering the Datas for the chart worksheet.Range["A1"].Text = "Crescent City, CA"; worksheet.Range["A1:D1"].Merge(); worksheet.Range["A1"].CellStyle.Font.Bold = true; worksheet.Range["B3"].Text = "Precipitation,in."; worksheet.Range["C3"].Text = "Temperature,deg.F"; worksheet.Range["A4"].Text = "Jan"; worksheet.Range["A5"].Text = "Feb"; worksheet.Range["A6"].Text = "March"; worksheet.Range["A7"].Text = "Apr"; worksheet.Range["A8"].Text = "May"; worksheet.Range["A9"].Text = "June"; worksheet.Range["A10"].Text = "July"; worksheet.Range["A11"].Text = "Aug"; worksheet.Range["A12"].Text = "Sept"; worksheet.Range["A13"].Text = "Oct"; worksheet.Range["A14"].Text = "Nov"; worksheet.Range["A15"].Text = "Dec"; worksheet.Range["B4"].Number = 10.9; worksheet.Range["B5"].Number = 8.9; worksheet.Range["B6"].Number = 8.6; worksheet.Range["B7"].Number = 4.8; worksheet.Range["B8"].Number = 3.2; worksheet.Range["B9"].Number = 1.4; worksheet.Range["B10"].Number = 0.6; worksheet.Range["B11"].Number = 0.7; worksheet.Range["B12"].Number = 1.7; worksheet.Range["B13"].Number = 5.4; worksheet.Range["B14"].Number = 9.0; worksheet.Range["B15"].Number = 10.4; worksheet.Range["C4"].Number = 47.5; worksheet.Range["C5"].Number = 48.7; worksheet.Range["C6"].Number = 48.9; worksheet.Range["C7"].Number = 50.2; worksheet.Range["C8"].Number = 53.1; worksheet.Range["C9"].Number = 56.3; worksheet.Range["C10"].Number = 58.1; worksheet.Range["C11"].Number = 59.0; worksheet.Range["C12"].Number = 58.5; worksheet.Range["C13"].Number = 55.4; worksheet.Range["C14"].Number = 51.1; worksheet.Range["C15"].Number = 47.8; worksheet.UsedRange.AutofitColumns(); #endregion #region Add New chart to Workbook // Adding a New chart to the Existing Worksheet IChart chart = workbook.Charts.Add(); chart.DataRange = worksheet.Range["A3:C15"]; chart.Name = "CrescentCity,CA"; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; #region Format Series // Format serie IChartSerie serieOne = chart.Series[0]; serieOne.Name = "Precipitation,in."; serieOne.SerieFormat.Fill.FillType = ExcelFillType.Gradient; serieOne.SerieFormat.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); serieOne.SerieFormat.Fill.GradientColorType = ExcelGradientColor.TwoColor; serieOne.SerieFormat.Fill.ForeColor = Color.Plum; //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieOne.DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside; //Format the second serie IChartSerie serieTwo = chart.Series[1]; serieTwo.SerieType = ExcelChartType.Line_Markers; serieTwo.Name = "Temperature,deg.F"; //Format marker serieTwo.SerieFormat.MarkerStyle = ExcelChartMarkerType.Diamond; serieTwo.SerieFormat.MarkerSize = 8; serieTwo.SerieFormat.MarkerBackgroundColor = Color.DarkGreen; serieTwo.SerieFormat.MarkerForegroundColor = Color.DarkGreen; serieTwo.SerieFormat.LineProperties.LineColor = Color.DarkGreen; //Use Secondary Axis serieTwo.UsePrimaryAxis = false; #endregion #region Set the Secondary Axis for Second Serie. //Display secondary axis for the series. chart.SecondaryCategoryAxis.IsMaxCross = true; chart.SecondaryValueAxis.IsMaxCross = true; //Set the title chart.SecondaryValueAxis.Title = "Temperature,deg.F"; chart.SecondaryValueAxis.TitleArea.TextRotationAngle = 90; //Hide the secondary category axis chart.SecondaryCategoryAxis.Border.LineColor = Color.Transparent; chart.SecondaryCategoryAxis.MajorTickMark = ExcelTickMark.TickMark_None; chart.SecondaryCategoryAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_None; #endregion #region Legend setting chart.Legend.Position = ExcelLegendPosition.Bottom; chart.Legend.IsVerticalLegend = false; #endregion #region Excel2013 Filter Enabled if (rdbExcel2013.Checked) { chart.Series[1].IsFiltered = true; chart.Categories[0].IsFiltered = true; chart.Categories[1].IsFiltered = true; } #endregion //Move the worksheet worksheet.Move(1); //Activate the chart chart.Activate(); #endregion #region Save Workbook //Saving the workbook to disk. workbook.SaveAs(fileName); #endregion #region Workbook Close and Dispose //Close the workbook. workbook.Close(); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); #endregion #region View the Workbook //Message box confirmation to view the created spreadsheet. if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] #if NETCORE System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName) { UseShellExecute = true }; process.Start(); #else Process.Start(fileName); #endif //Exit this.Close(); } else { // Exit this.Close(); } #endregion }
/// <summary> /// Creates spreadsheet with chart /// </summary> /// <param name="sender">Contains a reference to the control/object that raised the event</param> /// <param name="e">Contains the event data</param> private void btnCreate_Click(object sender, RoutedEventArgs e) { //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. using (ExcelEngine excelEngine = new ExcelEngine()) { #region Workbook Initialize //Step 2 : Instantiate the excel application object. IWorkbook workbook = null; IApplication application = excelEngine.Excel; Stream input = new MemoryStream(); if (this.rdbtnXLS.IsChecked.Value) { application.DefaultVersion = ExcelVersion.Excel97to2003; string inputPath = "syncfusion.xlsiodemos.winui.Assets.XlsIO.EmbeddedChart.xls"; Assembly assembly = typeof(ColumnChart).GetTypeInfo().Assembly; input = assembly.GetManifestResourceStream(inputPath); workbook = application.Workbooks.Open(input); filename = "ColumnChart.xls"; } else if (this.rdbtnXLSX.IsChecked.Value) { string inputPath = "syncfusion.xlsiodemos.winui.Assets.XlsIO.EmbeddedChart.xlsx"; Assembly assembly = typeof(ColumnChart).GetTypeInfo().Assembly; input = assembly.GetManifestResourceStream(inputPath); workbook = application.Workbooks.Open(input); workbook.Version = ExcelVersion.Xlsx; filename = "ColumnChart.xlsx"; } //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion // Adding a New chart to the Existing Worksheet IChartShape chart = workbook.Worksheets[0].Charts.Add(); chart.DataRange = worksheet.Range["A3:C15"]; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; chart.PrimaryCategoryAxis.Title = "Month"; #region Format Series IChartSerie serieOne = chart.Series[0]; chart.ChartType = ExcelChartType.Column_Clustered_3D; //set the Backwall fill option chart.BackWall.Fill.FillType = ExcelFillType.Gradient; //set the Texture Type chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor; chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down; chart.BackWall.Fill.ForeColor = Syncfusion.Drawing.Color.WhiteSmoke; chart.BackWall.Fill.BackColor = Syncfusion.Drawing.Color.LightBlue; //set the Border Linecolor chart.BackWall.Border.LineColor = Syncfusion.Drawing.Color.Wheat; //set the Picture Type chart.BackWall.PictureUnit = ExcelChartPictureType.stretch; //set the Backwall thickness chart.BackWall.Thickness = 10; //set the sidewall fill option chart.SideWall.Fill.FillType = ExcelFillType.SolidColor; //set the sidewall foreground and backcolor chart.SideWall.Fill.BackColor = Syncfusion.Drawing.Color.White; chart.SideWall.Fill.ForeColor = Syncfusion.Drawing.Color.White; //set the side wall Border color chart.SideWall.Border.LineColor = Syncfusion.Drawing.Color.Beige; //set floor fill option chart.Floor.Fill.FillType = ExcelFillType.Pattern; //set the floor pattern Type chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot; //Set the floor fore and Back ground color chart.Floor.Fill.ForeColor = Syncfusion.Drawing.Color.Blue; chart.Floor.Fill.BackColor = Syncfusion.Drawing.Color.White; //set the floor thickness chart.Floor.Thickness = 3; IChartSerie serieTwo = chart.Series[1]; //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; //ColumnChart Chart Position chart.TopRow = 2; chart.BottomRow = 30; chart.LeftColumn = 5; chart.RightColumn = 18; serieTwo.Name = "Temperature,deg.F"; #endregion #region Legend setting chart.Legend.Position = ExcelLegendPosition.Right; chart.Legend.IsVerticalLegend = false; #endregion if (this.rdbtnXLSX.IsChecked.Value) { chart.Series[0].IsFiltered = true; chart.Categories[0].IsFiltered = true; chart.Categories[1].IsFiltered = true; } if (this.rdbtnXLS.IsChecked.Value) { //Saving the workbook to disk. MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); Save(stream, filename, true, false); stream.Dispose(); } else if (this.rdbtnXLSX.IsChecked.Value) { //Saving the workbook to disk. MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); Save(stream, filename, false, true); stream.Dispose(); } input.Dispose(); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; } }
/// <summary> /// Create the Excel document with embedded chart /// </summary> /// <returns>Return the created excel document as stream</returns> public MemoryStream EmbeddedChartXlsIO(string version) { //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine using (ExcelEngine excelEngine = new ExcelEngine()) { //Step 2 : Instantiate the excel application object IApplication application = excelEngine.Excel; //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //Open workbook with Data FileStream inputStream = new FileStream(ResolveApplicationPath("embedded-chart.xlsx"), FileMode.Open, FileAccess.Read); IWorkbook workbook = application.Workbooks.Open(inputStream); //Set the default version if (version == "XLS") { workbook.Version = ExcelVersion.Excel97to2003; } else { workbook.Version = ExcelVersion.Excel2016; } //The first worksheet object in the worksheets collection is accessed IWorksheet sheet = workbook.Worksheets[0]; sheet.Name = "Sample"; // Adding a New chart to the Existing Worksheet IChartShape chart = workbook.Worksheets[0].Charts.Add(); chart.DataRange = sheet.Range["A3:C15"]; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; chart.PrimaryCategoryAxis.Title = "Month"; IChartSerie serieOne = chart.Series[0]; //set the Chart Type chart.ChartType = ExcelChartType.Column_Clustered_3D; //set the Backwall fill option chart.BackWall.Fill.FillType = ExcelFillType.Gradient; //set the Texture Type chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor; chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down; chart.BackWall.Fill.ForeColor = Color.WhiteSmoke; chart.BackWall.Fill.BackColor = Color.LightBlue; //set the Border Linecolor chart.BackWall.Border.LineColor = Color.Wheat; //set the Picture Type chart.BackWall.PictureUnit = ExcelChartPictureType.stretch; //set the Backwall thickness chart.BackWall.Thickness = 10; //set the sidewall fill option chart.SideWall.Fill.FillType = ExcelFillType.SolidColor; //set the sidewall foreground and backcolor chart.SideWall.Fill.BackColor = Color.White; chart.SideWall.Fill.ForeColor = Color.White; //set the side wall Border color chart.SideWall.Border.LineColor = Color.Beige; //set floor fill option chart.Floor.Fill.FillType = ExcelFillType.Pattern; //set the floor pattern Type chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot; //Set the floor fore and Back ground color chart.Floor.Fill.ForeColor = Color.Blue; chart.Floor.Fill.BackColor = Color.White; //set the floor thickness chart.Floor.Thickness = 3; IChartSerie serieTwo = chart.Series[1]; //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; //Embedded Chart Position chart.TopRow = 2; chart.BottomRow = 30; chart.LeftColumn = 5; chart.RightColumn = 18; serieTwo.Name = "Temperature,deg.F"; // Legend setting chart.Legend.Position = ExcelLegendPosition.Right; chart.Legend.IsVerticalLegend = false; //Save the document as a stream and retrun the stream using (MemoryStream stream = new MemoryStream()) { //Save the created Excel document to MemoryStream workbook.SaveAs(stream); return(stream); } } }
private static void CreateChartSeries(ChartArea chartArea, IChart iChart) { ChartAxis commonaxis = new ChartAxis() { Orientation = Orientation.Vertical, OpposedPosition = true }; chartArea.Axes.Add(commonaxis); for (int count = 0; count < iChart.Series.Count; count++) { IChartSerie XlsIOChartSerie = iChart.Series[count]; ChartSeries UIChartSeries = new ChartSeries() { ShowEmptyPoints = false }; SetSerieType(UIChartSeries, XlsIOChartSerie); DataTable ct = new DataTable(); DataTable vt = new DataTable(); if (XlsIOChartSerie.CategoryLabels != null) { ct = XlsIOChartSerie.CategoryLabels.Worksheet.ExportDataTable(XlsIOChartSerie.CategoryLabels, ExcelExportDataTableOptions.ComputedFormulaValues); vt = XlsIOChartSerie.Values.Worksheet.ExportDataTable(XlsIOChartSerie.Values, ExcelExportDataTableOptions.ComputedFormulaValues); } else if (XlsIOChartSerie.NameRange != null) { ct = XlsIOChartSerie.Values.Worksheet.ExportDataTable(XlsIOChartSerie.NameRange, ExcelExportDataTableOptions.ComputedFormulaValues); vt = XlsIOChartSerie.Values.Worksheet.ExportDataTable(XlsIOChartSerie.Values, ExcelExportDataTableOptions.ComputedFormulaValues); } else if (iChart.DataRange != null) { ct = XlsIOChartSerie.Values.Worksheet.ExportDataTable(iChart.DataRange, ExcelExportDataTableOptions.ComputedFormulaValues); vt = XlsIOChartSerie.Values.Worksheet.ExportDataTable(XlsIOChartSerie.Values, ExcelExportDataTableOptions.ComputedFormulaValues); } List <ChartDataPoint> data = ConvertDataTableToDataPoints(ct, vt, iChart.IsSeriesInRows); UIChartSeries.DataSource = data; UIChartSeries.BindingPathX = "X"; UIChartSeries.BindingPathsY = new List <string> { "Y" }; UIChartSeries.Label = XlsIOChartSerie.Name; UIChartSeries.Resolution = 5; UIChartSeries.UseOptimization = true; UIChartSeries.ShowSmartLabels = false; #region Pie Chart if (UIChartSeries.Type == ChartTypes.Pie) { System.Drawing.Color serieborder = new System.Drawing.Color(); System.Drawing.Color seriecolor = new System.Drawing.Color(); UIChartSeries.ColorEach = true; UIChartSeries.Palette = ChartColorPalette.Custom; Brush[] brushes = new Brush[iChart.Workbook.Palette.Length]; if (iChart.ChartType == ExcelChartType.Pie_3D) { int i = 0; foreach (Syncfusion.XlsIO.IChartDataPoint dataPoint in XlsIOChartSerie.DataPoints) { if (dataPoint.DataFormat.HasInterior && !dataPoint.DataFormat.Interior.UseAutomaticFormat) { seriecolor = dataPoint.DataFormat.Interior.ForegroundColor; brushes[i] = new SolidColorBrush(Color.FromRgb(seriecolor.R, seriecolor.G, seriecolor.B)); i++; UIChartSeries.CustomPalette = brushes; } } } else { serieborder = XlsIOChartSerie.SerieFormat.LineProperties.LineColor; if (XlsIOChartSerie.SerieFormat.AreaProperties.UseAutomaticFormat == false) { seriecolor = XlsIOChartSerie.SerieFormat.Fill.ForeColor; } UIChartSeries.Interior = new SolidColorBrush(Color.FromRgb(seriecolor.R, seriecolor.G, seriecolor.B)); } if (XlsIOChartSerie.SerieFormat.HasLineProperties) { UIChartSeries.StrokeThickness = GetLineWidth(XlsIOChartSerie.SerieFormat.LineProperties.LineWeight); } else { UIChartSeries.StrokeThickness = 0; } } #endregion #region Line and Scatter Chart else if (UIChartSeries.Type == ChartTypes.Line || UIChartSeries.Type == ChartTypes.Scatter || UIChartSeries.Type == ChartTypes.FastScatter) { if (UIChartSeries.Type == ChartTypes.Scatter || UIChartSeries.Type == ChartTypes.FastScatter) { UIChartSeries.BindingPathX = "XDouble"; } if (XlsIOChartSerie.SerieFormat.IsMarkerSupported) { if (!XlsIOChartSerie.SerieFormat.IsAutoMarker) { System.Drawing.Color markercolor = XlsIOChartSerie.SerieFormat.MarkerBackgroundColor; UIChartSeries.Interior = new SolidColorBrush(Color.FromRgb(markercolor.R, markercolor.G, markercolor.B)); UIChartSeries.Stroke = new SolidColorBrush(Color.FromRgb(markercolor.R, markercolor.G, markercolor.B)); } } if (!XlsIOChartSerie.SerieFormat.LineProperties.IsAutoLineColor) { System.Drawing.Color lineColor = XlsIOChartSerie.SerieFormat.LineProperties.LineColor; UIChartSeries.Stroke = new SolidColorBrush(Color.FromRgb(lineColor.R, lineColor.G, lineColor.B)); } UIChartSeries.ColorEach = false; UIChartSeries.LegendIcon = ChartLegendIcon.HorizontalLine; UIChartSeries.StrokeThickness = GetLineWidth(XlsIOChartSerie.SerieFormat.LineProperties.LineWeight) + 1; } #endregion #region Other Charts else { UIChartSeries.ColorEach = false; System.Drawing.Color fcolor; if (XlsIOChartSerie.SerieFormat.AreaProperties != null) { if (XlsIOChartSerie.SerieFormat.AreaProperties.UseAutomaticFormat == false) { fcolor = XlsIOChartSerie.SerieFormat.Fill.ForeColor; Color fclr = Color.FromRgb(fcolor.R, fcolor.G, fcolor.B); UIChartSeries.Interior = new SolidColorBrush(fclr); UIChartSeries.ColorEach = false; } } } #endregion CreateDataLabels(UIChartSeries, XlsIOChartSerie); CreateChartSecondaryAxis(UIChartSeries, XlsIOChartSerie, chartArea, iChart); chartArea.Series.Add(UIChartSeries); } System.Drawing.Color areaColor = iChart.ChartArea.Fill.ForeColor; chartArea.Background = new SolidColorBrush(Color.FromRgb(areaColor.R, areaColor.G, areaColor.B)); if (!iChart.PlotArea.Interior.UseAutomaticFormat) { System.Drawing.Color plotColor = iChart.PlotArea.Fill.ForeColor; chartArea.GridBackground = new SolidColorBrush(Color.FromRgb(plotColor.R, plotColor.G, plotColor.B)); } CreateChartTitle(chartArea, iChart); CreateChartLegend(chartArea, iChart); CreateChartPrimaryAxis(chartArea, iChart); }
private static void CreateChartSecondaryAxis(ChartSeries UIChartSeries, IChartSerie XlsIOChartSerie, ChartArea chartArea, IChart iChart) { ///Secondary Axis Implementation if (!XlsIOChartSerie.UsePrimaryAxis) { SecondaryAxisflag = true; ///Secondary Horizontal Axis Implementation #region Secondary Horizontal Axis if (iChart.SecondaryCategoryAxis.Visible) { UIChartSeries.XAxis = chartArea.Axes[2]; UIChartSeries.XAxis.Header = iChart.SecondaryCategoryAxis.Title; UIChartSeries.XAxis.LabelFontFamily = new FontFamily(iChart.SecondaryCategoryAxis.Font.FontName); UIChartSeries.XAxis.LabelFontSize = iChart.SecondaryCategoryAxis.Font.Size; UIChartSeries.XAxis.LabelRotateAngle = iChart.SecondaryCategoryAxis.TextRotationAngle; if (!iChart.SecondaryCategoryAxis.HasMajorGridLines && !iChart.SecondaryCategoryAxis.HasMinorGridLines) { ChartArea.SetShowGridLines(UIChartSeries.XAxis, false); } if (!iChart.SecondaryCategoryAxis.AutoTickLabelSpacing) { UIChartSeries.XAxis.IsAutoSetRange = false; if (iChart.SecondaryCategoryAxis.CategoryLabels != null && iChart.SecondaryCategoryAxis.CategoryLabels.HasDateTime) { UIChartSeries.XAxis.ValueType = ChartValueType.DateTime; UIChartSeries.XAxis.DateTimeInterval = TimeSpan.FromDays(iChart.SecondaryCategoryAxis.TickLabelSpacing); string format = iChart.SecondaryCategoryAxis.NumberFormat; format = format.Replace("m", "M"); UIChartSeries.XAxis.LabelDateTimeFormat = format; UIChartSeries.XAxis.IsAutoSetRange = true; } else if (iChart.SecondaryCategoryAxis.CategoryLabels != null && iChart.SecondaryCategoryAxis.CategoryLabels.HasNumber) { UIChartSeries.XAxis.ValueType = ChartValueType.Double; UIChartSeries.XAxis.Interval = iChart.SecondaryCategoryAxis.TickLabelSpacing; UIChartSeries.XAxis.Range = new DoubleRange(iChart.SecondaryCategoryAxis.MinimumValue, iChart.SecondaryCategoryAxis.MaximumValue); } else { UIChartSeries.XAxis.Interval = iChart.SecondaryCategoryAxis.TickLabelSpacing; } } else { try { if (iChart.SecondaryCategoryAxis.CategoryLabels != null && iChart.SecondaryCategoryAxis.CategoryLabels.HasNumber) { chartArea.PrimaryAxis.ValueType = ChartValueType.Double; foreach (var ser in chartArea.Series) { ser.Data.ChartXValueType = ChartValueType.Double; } UIChartSeries.XAxis.Interval = iChart.PrimaryCategoryAxis.TickLabelSpacing; } } catch { UIChartSeries.XAxis.IsAutoSetRange = true; } } } #endregion ///Secondary Vertical axis Implementation #region Secondary Vertical axis if (iChart.SecondaryValueAxis.Visible) { UIChartSeries.YAxis = chartArea.Axes[2]; if (!iChart.SecondaryValueAxis.HasMajorGridLines && !iChart.SecondaryValueAxis.HasMinorGridLines) { ChartArea.SetShowGridLines(UIChartSeries.YAxis, false); } try { UIChartSeries.YAxis.Header = iChart.SecondaryValueAxis.Title; } catch { } UIChartSeries.YAxis.LabelFontFamily = new FontFamily(iChart.SecondaryValueAxis.Font.FontName); UIChartSeries.YAxis.LabelFontSize = iChart.SecondaryValueAxis.Font.Size; UIChartSeries.YAxis.LabelFontWeight = iChart.PrimaryValueAxis.Font.Bold ? FontWeights.Bold : FontWeights.Normal; UIChartSeries.YAxis.LabelRotateAngle = iChart.SecondaryValueAxis.TextRotationAngle; if (iChart.SecondaryValueAxis.MinimumValue == 0 && iChart.SecondaryValueAxis.MaximumValue == 0) { ComputeRange(chartArea, iChart, "SecondaryValueAxis"); } else { UIChartSeries.YAxis.IsAutoSetRange = false; DoubleRange doubleRange = new DoubleRange(iChart.SecondaryValueAxis.MinimumValue, iChart.SecondaryValueAxis.MaximumValue); UIChartSeries.YAxis.Range = doubleRange; UIChartSeries.YAxis.Interval = iChart.SecondaryValueAxis.MajorUnit; } } else { UIChartSeries.YAxis.AxisVisibility = Visibility.Hidden; } #endregion } else if (chartArea.Axes.Count > 2 && !SecondaryAxisflag) { chartArea.Axes.RemoveAt(2); } }
private void Button_Click(object sender, RoutedEventArgs e) { #region Workbook Initialize //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IWorkbook workbook = null; //Set the default version as Excel97to2003 IApplication application = excelEngine.Excel; if (this.rdButtonxls.IsChecked.Value) { application.DefaultVersion = ExcelVersion.Excel97to2003; workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\EmbeddedChart.xls"); fileName = "ChartWorksheet.xls"; } //Set the default version as Excel 2007; else if (this.rdButtonxlsx.IsChecked.Value) { workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\EmbeddedChart.xlsx"); workbook.Version = ExcelVersion.Excel2007; fileName = "ChartWorksheet.xlsx"; } //Set the default version as Excel 2010; else if (this.rdButtonexcel2010.IsChecked.Value) { workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\EmbeddedChart.xlsx"); workbook.Version = ExcelVersion.Excel2010; fileName = "ChartWorksheet.xlsx"; } //Set the default version as Excel 2010; else if (this.rdButtonexcel2013.IsChecked.Value) { workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\EmbeddedChart.xlsx"); workbook.Version = ExcelVersion.Excel2013; fileName = "ChartWorksheet.xlsx"; } //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion // Adding a New chart to the Existing Worksheet IChartShape chart = workbook.Worksheets[0].Charts.Add(); chart.DataRange = worksheet.Range["A3:C15"]; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; chart.PrimaryCategoryAxis.Title = "Month"; #region Format Series IChartSerie serieOne = chart.Series[0]; chart.ChartType = ExcelChartType.Column_Clustered_3D; //set the Backwall fill option chart.BackWall.Fill.FillType = ExcelFillType.Gradient; //set the Texture Type chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor; chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down; chart.BackWall.Fill.ForeColor = System.Drawing.Color.WhiteSmoke; chart.BackWall.Fill.BackColor = System.Drawing.Color.LightBlue; //set the Border Linecolor chart.BackWall.Border.LineColor = System.Drawing.Color.Wheat; //set the Picture Type chart.BackWall.PictureUnit = ExcelChartPictureType.stretch; //set the Backwall thickness chart.BackWall.Thickness = 10; //set the sidewall fill option chart.SideWall.Fill.FillType = ExcelFillType.SolidColor; //set the sidewall foreground and backcolor chart.SideWall.Fill.BackColor = System.Drawing.Color.White; chart.SideWall.Fill.ForeColor = System.Drawing.Color.White; //set the side wall Border color chart.SideWall.Border.LineColor = System.Drawing.Color.Beige; //set floor fill option chart.Floor.Fill.FillType = ExcelFillType.Pattern; //set the floor pattern Type chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot; //Set the floor fore and Back ground color chart.Floor.Fill.ForeColor = System.Drawing.Color.Blue; chart.Floor.Fill.BackColor = System.Drawing.Color.White; //set the floor thickness chart.Floor.Thickness = 3; IChartSerie serieTwo = chart.Series[1]; //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; //Embedded Chart Position chart.TopRow = 2; chart.BottomRow = 30; chart.LeftColumn = 5; chart.RightColumn = 18; serieTwo.Name = "Temperature,deg.F"; #endregion #region Legend setting chart.Legend.Position = ExcelLegendPosition.Right; chart.Legend.IsVerticalLegend = false; #endregion if (this.rdButtonexcel2013.IsChecked.Value) { chart.Series[0].IsFiltered = true; chart.Categories[0].IsFiltered = true; chart.Categories[1].IsFiltered = true; } //chart.Scale(50, 50); try { workbook.SaveAs(fileName); //Close the workbook. workbook.Close(); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); //Message box confirmation to view the created spreadsheet. if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] System.Diagnostics.Process.Start(fileName); //Exit this.Close(); } else { // Exit this.Close(); } } catch { MessageBox.Show("Sorry, Excel can't open two workbooks with the same name at the same time.\nPlease close the workbook and try again.", "File is already open", MessageBoxButton.OK); } }
// // GET: /Bar/ public ActionResult EmbeddedChart(string button, string SaveOption) { if (button == null) { return(View()); } //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel] //Open workbook with Data IWorkbook workbook = excelEngine.Excel.Workbooks.Open(ResolveApplicationDataPath("EmbeddedChart.xlsx")); if (SaveOption == "Xls") { workbook.Version = ExcelVersion.Excel97to2003; } else { workbook.Version = ExcelVersion.Excel2016; } //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; sheet.Name = "Sample"; // Adding a New chart to the Existing Worksheet IChartShape chart = workbook.Worksheets[0].Charts.Add(); chart.DataRange = sheet.Range["A3:C15"]; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; chart.PrimaryCategoryAxis.Title = "Month"; IChartSerie serieOne = chart.Series[0]; //set the Chart Type chart.ChartType = ExcelChartType.Column_Clustered_3D; //set the Backwall fill option chart.BackWall.Fill.FillType = ExcelFillType.Gradient; //set the Texture Type chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor; chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down; chart.BackWall.Fill.ForeColor = Color.WhiteSmoke; chart.BackWall.Fill.BackColor = Color.LightBlue; //set the Border Linecolor chart.BackWall.Border.LineColor = Color.Wheat; //set the Picture Type chart.BackWall.PictureUnit = ExcelChartPictureType.stretch; //set the Backwall thickness chart.BackWall.Thickness = 10; //set the sidewall fill option chart.SideWall.Fill.FillType = ExcelFillType.SolidColor; //set the sidewall foreground and backcolor chart.SideWall.Fill.BackColor = Color.White; chart.SideWall.Fill.ForeColor = Color.White; //set the side wall Border color chart.SideWall.Border.LineColor = Color.Beige; //set floor fill option chart.Floor.Fill.FillType = ExcelFillType.Pattern; //set the floor pattern Type chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot; //Set the floor fore and Back ground color chart.Floor.Fill.ForeColor = Color.Blue; chart.Floor.Fill.BackColor = Color.White; //set the floor thickness chart.Floor.Thickness = 3; IChartSerie serieTwo = chart.Series[1]; //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; //Embedded Chart Position chart.TopRow = 2; chart.BottomRow = 30; chart.LeftColumn = 5; chart.RightColumn = 18; serieTwo.Name = "Temperature,deg.F"; // Legend setting chart.Legend.Position = ExcelLegendPosition.Right; chart.Legend.IsVerticalLegend = false; try { //Saving the workbook to disk. if (SaveOption == "Xls") { return(excelEngine.SaveAsActionResult(workbook, "EmbeddedChart.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } else { return(excelEngine.SaveAsActionResult(workbook, "EmbeddedChart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } } catch (Exception) { } workbook.Close(); excelEngine.Dispose(); return(View()); }
private void btnCreate_Click(object sender, System.EventArgs e) { #region Workbook Initialize //Instantiate the spreadsheet creation engine.e ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; IWorkbook workbook = null; if (this.rdbExcel97.Checked) { application.DefaultVersion = ExcelVersion.Excel97to2003; workbook = application.Workbooks.Open(GetTemplatePath("EmbeddedChart.xls")); fileName = "ChartWorksheet.xls"; } //Set the Workbook version as Excel 2007; else if (this.rdbExcel2007.Checked) { workbook = application.Workbooks.Open(GetTemplatePath("EmbeddedChart.xlsx")); workbook.Version = ExcelVersion.Excel2007; fileName = "ChartWorksheet.xlsx"; } //Set the Workbook version as Excel 2010; else if (this.rdbExcel2010.Checked) { workbook = application.Workbooks.Open(GetTemplatePath("EmbeddedChart.xlsx")); workbook.Version = ExcelVersion.Excel2010; fileName = "ChartWorksheet.xlsx"; } else if (this.rdbExcel2013.Checked) { workbook = application.Workbooks.Open(GetTemplatePath("EmbeddedChart.xlsx")); workbook.Version = ExcelVersion.Excel2013; fileName = "ChartWorksheet1.xlsx"; } //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 1 worksheet. //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion // Adding a New chart to the Existing Worksheet IChartShape chart = workbook.Worksheets[0].Charts.Add(); chart.DataRange = worksheet.Range["A3:C15"]; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; chart.PrimaryCategoryAxis.Title = "Month"; #region Format Series IChartSerie serieOne = chart.Series[0]; chart.ChartType = ExcelChartType.Column_Clustered_3D; //set the Backwall fill option chart.BackWall.Fill.FillType = ExcelFillType.Gradient; //set the Texture Type chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor; chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down; chart.BackWall.Fill.ForeColor = System.Drawing.Color.WhiteSmoke; chart.BackWall.Fill.BackColor = System.Drawing.Color.LightBlue; //set the Border Linecolor chart.BackWall.Border.LineColor = System.Drawing.Color.Wheat; //set the Picture Type chart.BackWall.PictureUnit = ExcelChartPictureType.stretch; //set the Backwall thickness chart.BackWall.Thickness = 10; //set the sidewall fill option chart.SideWall.Fill.FillType = ExcelFillType.SolidColor; //set the sidewall foreground and backcolor chart.SideWall.Fill.BackColor = System.Drawing.Color.White; chart.SideWall.Fill.ForeColor = System.Drawing.Color.White; //set the side wall Border color chart.SideWall.Border.LineColor = System.Drawing.Color.Beige; //set floor fill option chart.Floor.Fill.FillType = ExcelFillType.Pattern; //set the floor pattern Type chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot; //Set the floor fore and Back ground color chart.Floor.Fill.ForeColor = System.Drawing.Color.Blue; chart.Floor.Fill.BackColor = System.Drawing.Color.White; //set the floor thickness chart.Floor.Thickness = 3; IChartSerie serieTwo = chart.Series[1]; //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; //Embedded Chart Position chart.TopRow = 2; chart.BottomRow = 30; chart.LeftColumn = 5; chart.RightColumn = 18; serieTwo.Name = "Temperature,deg.F"; #endregion #region Legend setting chart.Legend.Position = ExcelLegendPosition.Right; chart.Legend.IsVerticalLegend = false; #endregion //chart.Scale(50, 50); if (this.rdbExcel2013.Checked) { chart.Series[0].IsFiltered = true; chart.Categories[0].IsFiltered = true; chart.Categories[1].IsFiltered = true; } workbook.SaveAs(fileName); #region Workbook Close and Dispose //Close the workbook. workbook.Close(); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); #endregion #region View the Workbook //Message box confirmation to view the created spreadsheet. if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] #if NETCORE System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName) { UseShellExecute = true }; process.Start(); #else Process.Start(fileName); #endif //Exit this.Close(); } else { // Exit this.Close(); } #endregion }
private static void SetSerieType(ChartSeries UIChartSeries, IChartSerie XlsIOChartSerie) { switch (XlsIOChartSerie.SerieType) { case ExcelChartType.Column_Clustered_3D: UIChartSeries.Type = ChartTypes.Column; break; case ExcelChartType.Column_Clustered: UIChartSeries.Type = ChartTypes.Column; break; case ExcelChartType.Column_Stacked: UIChartSeries.Type = ChartTypes.StackingColumn; break; case ExcelChartType.Column_Stacked_100: UIChartSeries.Type = ChartTypes.StackingColumn100; break; case ExcelChartType.Line: UIChartSeries.Type = ChartTypes.Line; break; case ExcelChartType.Line_Markers: UIChartSeries.Type = ChartTypes.Line; UIChartSeries.EnableEffects = true; break; case ExcelChartType.Pie: UIChartSeries.Type = ChartTypes.Pie; break; case ExcelChartType.Pie_Exploded: UIChartSeries.Type = ChartTypes.Pie; ChartPieType.SetExplodedAll(UIChartSeries, true); break; case ExcelChartType.Bar_Clustered: UIChartSeries.Type = ChartTypes.Bar; break; case ExcelChartType.Bar_Stacked: UIChartSeries.Type = ChartTypes.StackingBar; break; case ExcelChartType.Bar_Stacked_100: UIChartSeries.Type = ChartTypes.StackingBar100; break; case ExcelChartType.Area: UIChartSeries.Type = ChartTypes.Area; break; case ExcelChartType.Area_Stacked: UIChartSeries.Type = ChartTypes.StackingArea; break; case ExcelChartType.Scatter_Line: case ExcelChartType.Scatter_Markers: case ExcelChartType.Scatter_Line_Markers: case ExcelChartType.Scatter_SmoothedLine: case ExcelChartType.Scatter_SmoothedLine_Markers: UIChartSeries.Type = ChartTypes.Scatter; break; case ExcelChartType.Stock_HighLowClose: UIChartSeries.Type = ChartTypes.HiLo; //4 break; case ExcelChartType.Stock_OpenHighLowClose: UIChartSeries.Type = ChartTypes.HiLoOpenClose; //4 break; case ExcelChartType.Stock_VolumeHighLowClose: UIChartSeries.Type = ChartTypes.Candle; //4 break; case ExcelChartType.Doughnut: UIChartSeries.Type = ChartTypes.Doughnut; break; case ExcelChartType.Doughnut_Exploded: UIChartSeries.Type = ChartTypes.Doughnut; ChartPieType.SetExplodedAll(UIChartSeries, true); break; case ExcelChartType.Bubble: UIChartSeries.Type = ChartTypes.Bubble; //2 break; case ExcelChartType.Radar: UIChartSeries.Type = ChartTypes.Radar; break; case ExcelChartType.Pie_3D: UIChartSeries.Type = ChartTypes.Pie; break; default: return; } }
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e) { //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; // Default version is set as Excel 2007 if (this.rdbExcel2013.IsChecked.Value) { application.DefaultVersion = ExcelVersion.Excel2013; } else { application.DefaultVersion = ExcelVersion.Excel97to2003; } //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 1 worksheet. IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; // Entering the Datas for the chart sheet.Range["A1"].Text = "Crescent City, CA"; sheet.Range["A1:D1"].Merge(); sheet.Range["A1"].CellStyle.Font.Bold = true; sheet.Range["B3"].Text = "Precipitation,in."; sheet.Range["C3"].Text = "Temperature,deg.F"; sheet.Range["A4"].Text = "Jan"; sheet.Range["A5"].Text = "Feb"; sheet.Range["A6"].Text = "March"; sheet.Range["A7"].Text = "Apr"; sheet.Range["A8"].Text = "May"; sheet.Range["A9"].Text = "June"; sheet.Range["A10"].Text = "July"; sheet.Range["A11"].Text = "Aug"; sheet.Range["A12"].Text = "Sept"; sheet.Range["A13"].Text = "Oct"; sheet.Range["A14"].Text = "Nov"; sheet.Range["A15"].Text = "Dec"; sheet.Range["B4"].Number = 10.9; sheet.Range["B5"].Number = 8.9; sheet.Range["B6"].Number = 8.6; sheet.Range["B7"].Number = 4.8; sheet.Range["B8"].Number = 3.2; sheet.Range["B9"].Number = 1.4; sheet.Range["B10"].Number = 0.6; sheet.Range["B11"].Number = 0.7; sheet.Range["B12"].Number = 1.7; sheet.Range["B13"].Number = 5.4; sheet.Range["B14"].Number = 9.0; sheet.Range["B15"].Number = 10.4; sheet.Range["C4"].Number = 47.5; sheet.Range["C5"].Number = 48.7; sheet.Range["C6"].Number = 48.9; sheet.Range["C7"].Number = 50.2; sheet.Range["C8"].Number = 53.1; sheet.Range["C9"].Number = 56.3; sheet.Range["C10"].Number = 58.1; sheet.Range["C11"].Number = 59.0; sheet.Range["C12"].Number = 58.5; sheet.Range["C13"].Number = 55.4; sheet.Range["C14"].Number = 51.1; sheet.Range["C15"].Number = 47.8; sheet.UsedRange.AutofitColumns(); // Adding a New chart to the Existing Worksheet IChart chart = workbook.Charts.Add(); chart.DataRange = sheet.Range["A3:C15"]; chart.Name = "CrescentCity,CA"; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; // Format serie IChartSerie serieOne = chart.Series[0]; serieOne.Name = "Precipitation,in."; serieOne.SerieFormat.Fill.FillType = ExcelFillType.Gradient; serieOne.SerieFormat.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2); serieOne.SerieFormat.Fill.GradientColorType = ExcelGradientColor.TwoColor; serieOne.SerieFormat.Fill.ForeColor = Color.FromArgb(255, 221, 160, 221); //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieOne.DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside; //Format the second serie IChartSerie serieTwo = chart.Series[1]; serieTwo.SerieType = ExcelChartType.Line_Markers; serieTwo.Name = "Temperature,deg.F"; //Format marker serieTwo.SerieFormat.MarkerStyle = ExcelChartMarkerType.Diamond; serieTwo.SerieFormat.MarkerSize = 8; serieTwo.SerieFormat.MarkerBackgroundColor = Color.FromArgb(255, 0, 100, 0); serieTwo.SerieFormat.MarkerForegroundColor = Color.FromArgb(255, 0, 100, 0); serieTwo.SerieFormat.LineProperties.LineColor = Color.FromArgb(255, 0, 100, 0); //Use Secondary Axis serieTwo.UsePrimaryAxis = false; //Display secondary axis for the series. chart.SecondaryCategoryAxis.IsMaxCross = true; chart.SecondaryValueAxis.IsMaxCross = true; //Set the title chart.SecondaryValueAxis.Title = "Temperature,deg.F"; chart.SecondaryValueAxis.TitleArea.TextRotationAngle = 90; //Hide the secondary category axis chart.SecondaryCategoryAxis.Border.LineColor = Color.FromArgb(0, 0, 0, 0); chart.SecondaryCategoryAxis.MajorTickMark = ExcelTickMark.TickMark_None; chart.SecondaryCategoryAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_None; chart.Legend.Position = ExcelLegendPosition.Bottom; chart.Legend.IsVerticalLegend = false; //Excel2013 Filter option if (rdbExcel2013.IsChecked.Value) { chart.Series[1].IsFiltered = true; chart.Categories[0].IsFiltered = true; chart.Categories[1].IsFiltered = true; } sheet.Move(1); chart.Activate(); #region Save the Workbook StorageFile storageFile; if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))) { FileSavePicker savePicker = new FileSavePicker(); savePicker.SuggestedStartLocation = PickerLocationId.Desktop; savePicker.SuggestedFileName = "ChartWorksheet"; if (workbook.Version == ExcelVersion.Excel97to2003) { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xls" }); } else { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xlsx", }); } storageFile = await savePicker.PickSaveFileAsync(); } else { StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder; if (workbook.Version == ExcelVersion.Excel97to2003) { storageFile = await local.CreateFileAsync("ChartWorksheet.xls", CreationCollisionOption.ReplaceExisting); } else { storageFile = await local.CreateFileAsync("ChartWorksheet.xlsx", CreationCollisionOption.ReplaceExisting); } } if (storageFile != null) { //Saving the workbook await workbook.SaveAsAsync(storageFile); workbook.Close(); excelEngine.Dispose(); MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully."); UICommand yesCmd = new UICommand("Yes"); msgDialog.Commands.Add(yesCmd); UICommand noCmd = new UICommand("No"); msgDialog.Commands.Add(noCmd); IUICommand cmd = await msgDialog.ShowAsync(); if (cmd == yesCmd) { // Launch the saved file bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile); } } else { workbook.Close(); excelEngine.Dispose(); } #endregion }
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e) { StorageFile storageFile; string fileName = "BoxAndWhisker_Chart.xlsx"; if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))) { FileSavePicker savePicker = new FileSavePicker(); savePicker.SuggestedStartLocation = PickerLocationId.Desktop; savePicker.SuggestedFileName = fileName; savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xlsx", }); storageFile = await savePicker.PickSaveFileAsync(); } else { StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder; storageFile = await local.CreateFileAsync("BoxAndWhisker_Chart.xlsx", CreationCollisionOption.ReplaceExisting); } if (storageFile == null) { return; } #region Initializing Workbook //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the Excel application object. IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; Assembly assembly = typeof(BoxAndWhisker).GetTypeInfo().Assembly; string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.BoxAndWhiskerTemplate.xlsx"; Stream fileStream = assembly.GetManifestResourceStream(resourcePath); IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream); //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = workbook.Worksheets[0]; #endregion #region Chart Creation IChart chart = null; if (this.rdBtnSheet.IsChecked != null && this.rdBtnSheet.IsChecked.Value) { chart = workbook.Charts.Add(); } else { chart = workbook.Worksheets[0].Charts.Add(); } #region Box and Whisker Chart Settings chart.ChartType = ExcelChartType.BoxAndWhisker; chart.DataRange = sheet["B1:C181"]; IChartSerie series = chart.Series[0]; series.SerieFormat.ShowInnerPoints = false; series.SerieFormat.ShowOutlierPoints = true; series.SerieFormat.ShowMeanMarkers = true; series.SerieFormat.ShowMeanLine = false; series.SerieFormat.QuartileCalculationType = ExcelQuartileCalculation.ExclusiveMedian; series.SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.Grey_25_percent; chart.ChartTitle = "Box & Whisker Plot for Price Distribution of Books by Genre"; #endregion chart.Legend.Position = ExcelLegendPosition.Right; if (this.rdBtnSheet.IsChecked != null && this.rdBtnSheet.IsChecked.Value) { chart.Activate(); } else { workbook.Worksheets[0].Activate(); IChartShape chartShape = chart as IChartShape; chartShape.TopRow = 1; chartShape.BottomRow = 20; chartShape.LeftColumn = 6; chartShape.RightColumn = 13; } #endregion #region Saving the workbook await workbook.SaveAsAsync(storageFile); workbook.Close(); excelEngine.Dispose(); #endregion #region Launching the saved workbook MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully."); UICommand yesCmd = new UICommand("Yes"); msgDialog.Commands.Add(yesCmd); UICommand noCmd = new UICommand("No"); msgDialog.Commands.Add(noCmd); IUICommand cmd = await msgDialog.ShowAsync(); if (cmd == yesCmd) { // Launch the saved file bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile); } #endregion }