public static void GenChart(IChartShape ct, object[] x, object[] y, string name, ExcelChartType type)
{
var s = ct.Series.Add(name, type);
s.EnteredDirectlyValues = y;
s.EnteredDirectlyCategoryLabels = x;
}
/// <summary>
/// Creates the Chart in the Excel document
/// </summary>
private void ChartCreation()
{
string resourcePath = "SampleBrowser.Samples.XlsIO.Template.ChartData.xlsx";
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
MemoryStream stream = new MemoryStream();
//Creates a new instance for ExcelEngine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Instantiate the Excel application object
IApplication application = excelEngine.Excel;
//Assigns default application version as Excel 2013
application.DefaultVersion = ExcelVersion.Excel2013;
//Open an existing workbook
IWorkbook workbook = application.Workbooks.Open(fileStream);
//Access the first worksheet
IWorksheet sheet = workbook.Worksheets[0];
//Create a Chart
IChartShape chart = sheet.Charts.Add();
//Set Chart Type as Column Clustered
chart.ChartType = ExcelChartType.Column_Clustered;
//Set data range of the chart
chart.DataRange = sheet["A4:E14"];
//Set the Chart Title
chart.ChartTitle = sheet["A3"].Text;
chart.HasLegend = false;
//Positioning chart in a worksheet
chart.TopRow = 15;
chart.LeftColumn = 1;
chart.RightColumn = 6;
chart.BottomRow = 27;
//Set the version of the workbook.
workbook.Version = ExcelVersion.Excel2013;
// Saving the workbook in xlsx format
workbook.SaveAs(stream);
}
if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Charts.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Charts.xlsx", "application/msexcel", stream);
}
}
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);
}
/// <summary>
/// To load Embedded Chart
/// </summary>
/// <param name="chartShape"></param>
/// <returns></returns>
public static Chart CreateChart(this IChartShape chartShape)
{
Chart chart = new Chart();
ChartArea chartArea = new ChartArea();
IChart iChart = chartShape as IChart;
CreateChartSeries(chartArea, iChart);
chart.Areas.Add(chartArea);
return(chart);
}
private void btnCreate_Click(object sender, RoutedEventArgs e)
{
#region Workbook Initialize
//Get the path of the input file
string inputPath = GetFullTemplatePath("TreemapTemplate.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 Treemap Chart Settings
chart.ChartType = ExcelChartType.TreeMap;
chart.DataRange = sheet["A1:F13"];
chart.ChartTitle = "Daily Food Sales";
foreach (IChartSerie serie in chart.Series)
{
serie.SerieFormat.TreeMapLabelOption = ExcelTreeMapLabelOption.Banner;
}
#endregion
chart.Legend.Position = ExcelLegendPosition.Top;
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 = 22;
chartShape.LeftColumn = 8;
chartShape.RightColumn = 15;
}
#region Workbook Save and Close
string outFileName = "Treemap_Chart.xlsx";
workbook.SaveAs(outFileName);
workbook.Close();
#endregion
OpenOutput(outFileName);
}
private void btnCreate_Click(object sender, RoutedEventArgs e)
{
#region Workbook Initialize
//Get the path of the input file
string inputPath = GetFullTemplatePath("FunnelChartTemplate.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 Funnel Chart Settings
chart.ChartType = ExcelChartType.Funnel;
chart.DataRange = sheet["A2:B8"];
chart.ChartTitle = "Sales Pipeline";
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8.5;
chart.Series[0].SerieFormat.CommonSerieOptions.GapWidth = 100;
#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 = 2;
chartShape.BottomRow = 19;
chartShape.LeftColumn = 4;
chartShape.RightColumn = 11;
}
#region Workbook Save and Close
string outFileName = "Funnel_Chart.xlsx";
workbook.SaveAs(outFileName);
workbook.Close();
#endregion
OpenOutput(outFileName);
}
public void AddChart(string range, string title, int top, int bottom, int left, int right)
{
IChartShape chart = m_sheet.Charts.Add();
chart.DataRange = m_sheet[range];
chart.ChartTitle = title;
chart.HasLegend = false;
chart.TopRow = top;
chart.LeftColumn = left;
chart.RightColumn = right;
chart.BottomRow = bottom;
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
string resourcePath = "";
#if COMMONSB
resourcePath = "SampleBrowser.Samples.XlsIO.Samples.Template.ChartData.xlsx";
#else
resourcePath = "SampleBrowser.XlsIO.Samples.Template.ChartData.xlsx";
#endif
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = application.Workbooks.Open(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
#region Generate Chart
IChartShape chart = sheet.Charts.Add();
chart.DataRange = sheet["A16:E26"];
chart.ChartTitle = sheet["A15"].Text;
chart.HasLegend = false;
chart.TopRow = 3;
chart.LeftColumn = 1;
chart.RightColumn = 6;
chart.BottomRow = 15;
#endregion
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Charts.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Charts.xlsx", "application/msexcel", stream);
}
}
protected override Chart CreateUIElement(GraphicStyleInfo cellInfo)
{
if (cellInfo.CellValue != null && cellInfo.CellValue is IChartShape)
{
IChartShape chartShape = cellInfo.CellValue as IChartShape;
return(chartShape.CreateChart());
}
else if (cellInfo.CellValue != null && cellInfo.CellValue is IChart)
{
IChart chartShape = cellInfo.CellValue as IChart;
return(chartShape.CreateChart());
}
return(base.CreateUIElement(cellInfo));
}
private void btnCreate_Click(object sender, RoutedEventArgs e)
{
#region Workbook Initialize
//Get the path of the input file
string inputPath = GetFullTemplatePath("SunburstTemplate.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 Sunburst Chart Settings
chart.ChartType = ExcelChartType.SunBurst;
chart.DataRange = sheet["A1:D29"];
chart.ChartTitle = "Breakdown of Bookstore Revenue";
#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 = 23;
chartShape.LeftColumn = 6;
chartShape.RightColumn = 15;
}
#region Workbook Save and Close
string outFileName = "Sunburst_Chart.xlsx";
workbook.SaveAs(outFileName);
workbook.Close();
#endregion
OpenOutput(outFileName);
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
string resourcePath = "SampleBrowser.Samples.XlsIO.Template.ChartData.xlsx";
Assembly assembly = Assembly.GetExecutingAssembly();
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = application.Workbooks.Open(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
#region Generate Chart
IChartShape chart = sheet.Charts.Add();
chart.DataRange = sheet["A16:E26"];
chart.ChartTitle = sheet["A15"].Text;
chart.HasLegend = false;
chart.TopRow = 3;
chart.LeftColumn = 1;
chart.RightColumn = 6;
chart.BottomRow = 15;
#endregion
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (stream != null)
{
SaveiOS iOSSave = new SaveiOS();
iOSSave.Save("Charts.xlsx", "application/msexcel", stream);
}
}
//
// GET: /Bar/
public ActionResult Treemap(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(@"TreemapTemplate.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("TreemapTemplate.xlsx"));
IWorksheet sheet = workbook.Worksheets[0];
IChart chart = null;
if (Saveoption == "sheet")
{
chart = workbook.Charts.Add();
}
else
{
chart = workbook.Worksheets[0].Charts.Add();
}
#region Treemap Chart Settings
chart.ChartType = ExcelChartType.TreeMap;
chart.DataRange = sheet["A1:F13"];
chart.ChartTitle = "Daily Food Sales";
foreach (IChartSerie serie in chart.Series)
{
serie.SerieFormat.TreeMapLabelOption = ExcelTreeMapLabelOption.Banner;
}
#endregion
chart.Legend.Position = ExcelLegendPosition.Top;
if (Saveoption == "sheet")
{
chart.Activate();
}
else
{
workbook.Worksheets[0].Activate();
IChartShape chartShape = chart as IChartShape;
chartShape.TopRow = 1;
chartShape.BottomRow = 22;
chartShape.LeftColumn = 8;
chartShape.RightColumn = 15;
}
try
{
return(excelEngine.SaveAsActionResult(workbook, "Treemap_Chart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
//
// 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
}
}
/// <summary>
/// Creates spreadsheet
/// </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())
{
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
//Create a workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sales Report";
sheet.IsGridLinesVisible = false;
IWorksheet sheet2 = workbook.Worksheets.Create("Data");
sheet.EnableSheetCalculations();
sheet.Range[1, 1, 1, 7].Merge();
IRange range = sheet.Range["A1"];
range.RowHeight = 22.5;
range.Text = "Yearly Sales";
range.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
range.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
range.CellStyle.Font.Size = 14;
range.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(155, 194, 230);
sheet.Range["A1"].ColumnWidth = 3.44;
sheet.Range["B1"].ColumnWidth = 11.0;
sheet.Range["C1"].ColumnWidth = 11.0;
sheet.Range["D1"].ColumnWidth = 0.92;
sheet.Range["E1"].ColumnWidth = 11.0;
sheet.Range["F1"].ColumnWidth = 11.0;
sheet.Range["G1"].ColumnWidth = 3.44;
sheet.Range[1, 1, 1, 7].Merge();
sheet.Range["A13"].RowHeight = 12;
sheet.Range["A14"].RowHeight = 21;
sheet.Range["A15"].RowHeight = 15;
sheet.Range["A16"].RowHeight = 3;
sheet.Range["A17"].RowHeight = 21;
sheet.Range["A18"].RowHeight = 15;
sheet.Range["A19"].RowHeight = 12;
IRange range5 = sheet.Range["B14:C14"];
IRange range6 = sheet.Range["B15:C15"];
IRange range7 = sheet.Range["B17:C17"];
IRange range8 = sheet.Range["B18:C18"];
IRange range9 = sheet.Range["E14:F14"];
IRange range10 = sheet.Range["E15:F15"];
IRange range11 = sheet.Range["E17:F17"];
IRange range12 = sheet.Range["E18:F18"];
range5.Text = "$ 4.51 M";
range9.Formula = "=Data!D14";
range7.Formula = "=Data!C19";
range11.Formula = "=Data!E14";
range5.Merge();
range6.Merge();
range7.Merge();
range8.Merge();
range9.Merge();
range10.Merge();
range11.Merge();
range12.Merge();
List <IStyle> styles = CreateStyles(workbook);
range5.CellStyle = styles[0];
range9.CellStyle = styles[1];
range7.CellStyle = styles[2];
range11.CellStyle = styles[3];
range6.CellStyle = styles[4];
range6.Text = "Sales Amount";
range10.CellStyle = styles[5];
range10.Text = "Average Unit Price";
range8.CellStyle = styles[6];
range8.Text = "Gross Profit Margin";
range12.CellStyle = styles[7];
range12.Text = "Customer Count";
sheet2.Range["B1"].ColumnWidth = 23;
sheet2.Range["C1"].ColumnWidth = 23;
sheet2.Range["D1"].ColumnWidth = 10;
sheet2.Range["E1"].ColumnWidth = 10;
sheet2.Range["A1"].Text = "Months";
sheet2.Range["B1"].Text = "Internet Sales Amount";
sheet2.Range["C1"].Text = "Reseller Sales Amount";
sheet2.Range["D1"].Text = "Unit Price";
sheet2.Range["E1"].Text = "Customers";
sheet2.Range["A2"].Text = "Jan";
sheet2.Range["A3"].Text = "Feb";
sheet2.Range["A4"].Text = "Mar";
sheet2.Range["A5"].Text = "Apr";
sheet2.Range["A6"].Text = "May";
sheet2.Range["A7"].Text = "June";
sheet2.Range["A8"].Text = "Jul";
sheet2.Range["A9"].Text = "Aug";
sheet2.Range["A10"].Text = "Sep";
sheet2.Range["A11"].Text = "Oct";
sheet2.Range["A12"].Text = "Nov";
sheet2.Range["A13"].Text = "Dec";
sheet2.Range["A14"].Text = "Total";
sheet2.Range["B2"].Number = 226170;
sheet2.Range["B3"].Number = 212259;
sheet2.Range["B4"].Number = 181079;
sheet2.Range["B5"].Number = 188809;
sheet2.Range["B6"].Number = 198195;
sheet2.Range["B7"].Number = 235524;
sheet2.Range["B8"].Number = 185786;
sheet2.Range["B9"].Number = 196745;
sheet2.Range["B10"].Number = 164897;
sheet2.Range["B11"].Number = 175673;
sheet2.Range["B12"].Number = 212896;
sheet2.Range["B13"].Number = 325634;
sheet2.Range["B14"].Formula = "=SUM(B2:B13)";
sheet2.Range["C2"].Number = 170234;
sheet2.Range["C3"].Number = 189456;
sheet2.Range["C4"].Number = 168795;
sheet2.Range["C5"].Number = 143567;
sheet2.Range["C6"].Number = 163567;
sheet2.Range["C7"].Number = 163546;
sheet2.Range["C8"].Number = 143787;
sheet2.Range["C9"].Number = 149898;
sheet2.Range["C10"].Number = 153784;
sheet2.Range["C11"].Number = 164289;
sheet2.Range["C12"].Number = 172453;
sheet2.Range["C13"].Number = 223430;
sheet2.Range["C14"].Formula = "=SUM(C2:C13)";
sheet2.Range["D2"].Number = 202;
sheet2.Range["D3"].Number = 204;
sheet2.Range["D4"].Number = 191;
sheet2.Range["D5"].Number = 223;
sheet2.Range["D6"].Number = 203;
sheet2.Range["D7"].Number = 185;
sheet2.Range["D8"].Number = 198;
sheet2.Range["D9"].Number = 196;
sheet2.Range["D10"].Number = 220;
sheet2.Range["D11"].Number = 218;
sheet2.Range["D12"].Number = 299;
sheet2.Range["D13"].Number = 185;
sheet2.Range["D14"].Formula = "=AVERAGE(D2:D13)";
sheet2.Range["E2"].Number = 1861;
sheet2.Range["E3"].Number = 1522;
sheet2.Range["E4"].Number = 1410;
sheet2.Range["E5"].Number = 1488;
sheet2.Range["E6"].Number = 1781;
sheet2.Range["E7"].Number = 2155;
sheet2.Range["E8"].Number = 1657;
sheet2.Range["E9"].Number = 1767;
sheet2.Range["E10"].Number = 1448;
sheet2.Range["E11"].Number = 1556;
sheet2.Range["E12"].Number = 1928;
sheet2.Range["E13"].Number = 2956;
sheet2.Range["E14"].Formula = "=SUM(E2:E13)";
sheet2.Range["B17"].Text = "2018 Sales";
sheet2.Range["B18"].Text = "2018 Sales";
sheet2.Range["B19"].Text = "Gain %";
sheet2.Range["C17"].Number = 3845634;
sheet2.Range["C18"].Formula = "=B14+C14";
sheet2.Range["C19"].Formula = "=(C18-C17)/10000000";
sheet2.Range["C19"].NumberFormat = "0.00%";
sheet2.Range["C17:C18"].NumberFormat = "_($* #,##0.00";
sheet2.Range["B2:D13"].NumberFormat = "_($* #,##0.00";
sheet2.Range["A1:E1"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(198, 224, 180);
sheet2.Range["A1:E1"].CellStyle.Font.Bold = true;
sheet2.Range["A14:E14"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(198, 224, 180);
sheet2.Range["A14:E14"].CellStyle.Font.Bold = true;
sheet.Range["G30"].Text = ".";
#region Chart Creation
IChartShape chart1 = sheet.Charts.Add();
chart1.ChartType = ExcelChartType.Column_Stacked;
chart1.DataRange = sheet2.Range["A1:B13"];
chart1.IsSeriesInRows = false;
chart1.Legend !.Position = ExcelLegendPosition.Bottom;
chart1.PrimaryValueAxis.NumberFormat = "$#,###";
chart1.PrimaryValueAxis.HasMajorGridLines = false;
chart1.TopRow = 2;
chart1.BottomRow = 13;
chart1.LeftColumn = 1;
chart1.RightColumn = 8;
chart1.ChartArea.Border.LinePattern = ExcelChartLinePattern.None;
chart1.PrimaryCategoryAxis.TextRotationAngle = -45;
IChartShape chart2 = sheet.Charts.Add();
chart2.ChartType = ExcelChartType.Line;
chart2.DataRange = sheet2.Range["A1:C13"];
chart2.IsSeriesInRows = false;
chart2.ChartTitleArea.Bold = true;
chart2.ChartTitleArea.Size = 11;
chart2.ChartTitleArea.Color = ExcelKnownColors.Grey_80_percent;
chart2.ChartTitleArea.Text = "Internet Sales vs Reseller Sales";
chart2.Legend !.Position = ExcelLegendPosition.Bottom;
chart2.Legend !.TextArea.Size = 9;
chart2.Legend.TextArea.Color = ExcelKnownColors.Grey_80_percent;
chart2.TopRow = 20;
chart2.BottomRow = 32;
chart2.LeftColumn = 1;
chart2.RightColumn = 8;
chart2.PrimaryValueAxis.NumberFormat = "$#,###";
chart2.PrimaryValueAxis.HasMajorGridLines = false;
chart2.ChartArea.Border.LinePattern = ExcelChartLinePattern.None;
#endregion
string OutputFilename = "YearlySale.xlsx";
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
Save(stream, OutputFilename);
stream.Dispose();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
}
}
//
// GET: /Bar/
public ActionResult FunnelChart(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(@"FunnelChartTemplate.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("FunnelChartTemplate.xlsx"));
IWorksheet sheet = workbook.Worksheets[0];
IChart chart = null;
if (Saveoption == "sheet")
{
chart = workbook.Charts.Add();
}
else
{
chart = workbook.Worksheets[0].Charts.Add();
}
#region Funnel Chart Settings
chart.ChartType = ExcelChartType.Funnel;
chart.DataRange = sheet["A2:B8"];
chart.ChartTitle = "Sales Pipeline";
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8.5;
chart.Series[0].SerieFormat.CommonSerieOptions.GapWidth = 100;
#endregion
chart.Legend.Position = ExcelLegendPosition.Right;
if (Saveoption == "sheet")
{
chart.Activate();
}
else
{
workbook.Worksheets[0].Activate();
IChartShape chartShape = chart as IChartShape;
chartShape.TopRow = 2;
chartShape.BottomRow = 19;
chartShape.LeftColumn = 4;
chartShape.RightColumn = 11;
}
try
{
return(excelEngine.SaveAsActionResult(workbook, "Funnel_Chart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
//
// GET: /Bar/
public ActionResult Sunburst(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(@"SunburstTemplate.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("SunburstTemplate.xlsx"));
IWorksheet sheet = workbook.Worksheets[0];
IChart chart = null;
if (Saveoption == "sheet")
{
chart = workbook.Charts.Add();
}
else
{
chart = workbook.Worksheets[0].Charts.Add();
}
#region Sunburst Chart Settings
chart.ChartType = ExcelChartType.SunBurst;
chart.DataRange = sheet["A1:D29"];
chart.ChartTitle = "Breakdown of Bookstore Revenue";
#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 = 23;
chartShape.LeftColumn = 6;
chartShape.RightColumn = 15;
}
try
{
return(excelEngine.SaveAsActionResult(workbook, "Sunburst_Chart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
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)
{
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: /Default/
public ActionResult Default(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;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 12 worksheets
IWorkbook workbook = application.Workbooks.Open(DefaultResolveApplicationDataPath(@"BudgetPlanner.xls"));
IWorksheet sheet = workbook.Worksheets[1];
sheet.FirstVisibleRow = 3;
IFont font = workbook.CreateFont();
font.Bold = true;
#region TextBox
ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(5, 2, 40, 140);
textbox.Text = "Quick Budget";
textbox.RichText.SetFont(0, 11, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(204, 204, 255);
textbox = sheet.TextBoxes.AddTextBox(7, 2, 25, 140);
textbox.Text = "Income";
textbox.RichText.SetFont(0, 5, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
textbox = sheet.TextBoxes.AddTextBox(16, 2, 25, 140);
textbox.Text = "Spending";
textbox.RichText.SetFont(0, 7, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
#endregion
#region Write Text and Numbers
sheet.Range["O6"].Text = "Weekly";
sheet.Range["E7"].Text = "Frequency";
sheet.Range["F7"].Text = "Amount";
sheet.Range["G7"].Text = "Monthly";
sheet.Range["H7"].Text = "Yearly";
sheet.Range["B8"].Text = "Total Income";
sheet.Range["C9"].Text = "Salary/Wages";
sheet.Range["C10"].Text = "Salary/Wages(Spouse)";
sheet.Range["C11"].Text = "Other";
sheet.Range["C12"].Text = "Other";
sheet.Range["C13"].Text = "Other";
sheet.Range["B17"].Text = "Transportation";
sheet.Range["F25"].Number = 3000;
sheet.Range["F9"].Number = 55000;
sheet.Range["F10"].Number = 35000;
sheet.Range["C18"].Text = "Auto Loan/Lease";
sheet.Range["C19"].Text = "Insurance";
sheet.Range["C20"].Text = "Gas ";
sheet.Range["C21"].Text = "Maintenance ";
sheet.Range["C22"].Text = "Registration/Inspection";
sheet.Range["C23"].Text = "Bill's train pass";
sheet.Range["C24"].Text = "Jane's bus pass";
sheet.Range["C25"].Text = "Other";
sheet.Range["E16"].Text = "Total";
sheet.Range["N6"].Text = "Chart";
sheet.Range["B27"].Text = "Home";
sheet.Range["F28"].Number = 20000;
sheet.Range["F29"].Number = 5000;
sheet.Range["F33"].Number = 5000;
sheet.Range["C28"].Text = "EMI";
sheet.Range["C29"].Text = "Rent";
sheet.Range["C30"].Text = "Maintanence";
sheet.Range["C31"].Text = "Insurance";
sheet.Range["C32"].Text = "Furniture";
sheet.Range["C33"].Text = "Household Supplies";
sheet.Range["C34"].Text = "Groceries";
sheet.Range["C35"].Text = "Real Estate Tax";
sheet.Range["C36"].Text = "Other";
sheet.Range["B39"].Text = "Utilities";
sheet.Range["F41"].Number = 1000;
sheet.Range["F42"].Number = 250;
sheet.Range["F43"].Number = 150;
sheet.Range["F45"].Number = 175;
sheet.Range["C40"].Text = "Phone - Home";
sheet.Range["C41"].Text = "Phone - Cell";
sheet.Range["C42"].Text = "Cable";
sheet.Range["C43"].Text = "Gas";
sheet.Range["C44"].Text = "Water";
sheet.Range["C45"].Text = "Electricity";
sheet.Range["C46"].Text = "Internet";
sheet.Range["C47"].Text = "Other";
sheet.Range["B50"].Text = "Health";
sheet.Range["F53"].Number = 500;
sheet.Range["C51"].Text = "Dental";
sheet.Range["C52"].Text = "Medical";
sheet.Range["C53"].Text = "Medication";
sheet.Range["C54"].Text = "Vision/contacts";
sheet.Range["C55"].Text = "Life Insurance";
sheet.Range["C56"].Text = "Electricity";
sheet.Range["C57"].Text = "Other";
#endregion
#region Cell styles
IStyle tableStyle = workbook.Styles.Add("TableStyle");
tableStyle.BeginUpdate();
tableStyle.Color = Color.White;
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableStyle.EndUpdate();
sheet.Range["E7:H7"].CellStyle.Font.Bold = true;
sheet.Range["B17"].CellStyle.Font.Bold = true;
sheet.Range["B27"].CellStyle.Font.Bold = true;
sheet.Range["B39"].CellStyle.Font.Bold = true;
sheet.Range["B50"].CellStyle.Font.Bold = true;
sheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single;
sheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C9:C13"].CellStyle = tableStyle;
sheet.Range["E9:F13"].CellStyle = tableStyle;
sheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["B17:C17"].CellStyle.Color = Color.White;
sheet.Range["C18:C25"].CellStyle = tableStyle;
sheet.Range["O6"].CellStyle = tableStyle;
sheet.Range["E18:F25"].CellStyle = tableStyle;
sheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C28:C36"].CellStyle = tableStyle;
sheet.Range["B27:C27"].CellStyle.Color = Color.White;
sheet.Range["E28:F36"].CellStyle = tableStyle;
sheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C40:C47"].CellStyle = tableStyle;
sheet.Range["B39:C39"].CellStyle.Color = Color.White;
sheet.Range["E40:F47"].CellStyle = tableStyle;
sheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C51:C57"].CellStyle = tableStyle;
sheet.Range["B50:C50"].CellStyle.Color = Color.White;
sheet.Range["E51:F57"].CellStyle = tableStyle;
#endregion
#region Data Validation
IDataValidation validation = sheet.Range["E9:E13"].DataValidation;
sheet.Range["E9:E13"].Text = "Monthly";
validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation1 = sheet.Range["E18:E25"].DataValidation;
sheet.Range["E18:E25"].Text = "Monthly";
validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation2 = sheet.Range["O6"].DataValidation;
validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" };
IDataValidation validation3 = sheet.Range["E28:E37"].DataValidation;
sheet.Range["E28:E36"].Text = "Monthly";
validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation4 = sheet.Range["E40:E47"].DataValidation;
sheet.Range["E40:E47"].Text = "Monthly";
validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation5 = sheet.Range["E51:E57"].DataValidation;
sheet.Range["E51:E57"].Text = "Monthly";
validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
#endregion
#region Formulas
sheet.Range["G8"].Formula = "SUM(G9:G13)";
sheet.Range["H8"].Formula = "SUM(H9:H13)";
sheet.Range["G17"].Formula = "SUM(G18:G25)";
sheet.Range["H17"].Formula = "SUM(H18:H25)";
sheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71";
sheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71";
for (int i = 9; i <= 13; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
for (int i = 18; i <= 25; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G27"].Formula = "SUM(G28:G36)";
sheet.Range["H27"].Formula = "SUM(H28:H37)";
for (int i = 28; i <= 36; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G39"].Formula = "SUM(G40:G47)";
sheet.Range["H39"].Formula = "SUM(H40:H47)";
for (int i = 40; i <= 47; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G50"].Formula = "SUM(G51:G57)";
sheet.Range["H50"].Formula = "SUM(H51:H57)";
for (int i = 51; i <= 57; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
#endregion
#region SummaryChart
//Clustered Column Chart
IChartShape chart = sheet.Charts.Add();
//Set Chart Type
chart.ChartType = ExcelChartType.Bar_Clustered;
//Set DataRange.
chart.Series.Add("Expense");
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"];
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Income");
chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"];
chart.Series[1].DataPoints[0].DataLabels.IsValue = true;
chart.Series[1].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Balance");
chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"];
chart.Series[2].DataPoints[0].DataLabels.IsValue = true;
chart.Series[2].DataPoints[0].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
chart.PrimaryCategoryAxis.Visible = false;
//Format Chart Area
IChartFrameFormat chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
IChartFrameFormat chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 7;
chart.BottomRow = 22;
chart.LeftColumn = 9;
chart.RightColumn = 16;
chart.ChartTitle = "Budget Summary";
chart.ChartTitleArea.Bold = true;
#endregion
#region SpendingChart
chart = sheet.Charts.Add();
chart.ChartTitle = "Spending Summary";
chart.ChartTitleArea.Bold = true;
//Set Chart Type
chart.ChartType = ExcelChartType.Pie_3D;
//Set DataRange.
chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"];
chart.IsSeriesInRows = false;
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"];
chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"];
chart.Series[0].Name = "Spending summary";
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series[0].DataPoints[1].DataLabels.IsValue = true;
chart.Series[0].DataPoints[1].DataLabels.Size = 7f;
chart.Series[0].DataPoints[2].DataLabels.IsValue = true;
chart.Series[0].DataPoints[2].DataLabels.Size = 7f;
chart.Series[0].DataPoints[3].DataLabels.IsValue = true;
chart.Series[0].DataPoints[3].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
//Format Chart Area
chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223);
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 25;
chart.BottomRow = 42;
chart.LeftColumn = 9;
chart.RightColumn = 16;
#endregion
#region Sheet View
workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden;
workbook.Worksheets[0].Activate();
workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue;
workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue;
workbook.Worksheets[1].IsRowColumnHeadersVisible = false;
sheet.InsertColumn(9);
#endregion
try
{
//Saving the workbook to disk.
if (SaveOption == "Xls")
{
//Save as .xls format
return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
//Save as .xlsx format
else
{
workbook.Version = ExcelVersion.Excel2016;
return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
StorageFile storageFile;
string fileName = "Sunburst_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("Sunburst_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(Sunburst).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.SunburstTemplate.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 Sunburst Chart Settings
chart.ChartType = ExcelChartType.SunBurst;
chart.DataRange = sheet["A1:D29"];
chart.ChartTitle = "Breakdown of Bookstore Revenue";
#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 = 23;
chartShape.LeftColumn = 6;
chartShape.RightColumn = 15;
}
#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
}
/// <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>
/// Creates spreadsheet
/// </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, EventArgs 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())
{
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet1 = workbook.Worksheets[0];
sheet1.Name = "Budget";
sheet1.IsGridLinesVisible = false;
sheet1.EnableSheetCalculations();
sheet1.Range[1, 1].ColumnWidth = 19.86;
sheet1.Range[1, 2].ColumnWidth = 14.38;
sheet1.Range[1, 3].ColumnWidth = 12.98;
sheet1.Range[1, 4].ColumnWidth = 12.08;
sheet1.Range[1, 5].ColumnWidth = 8.82;
sheet1.Range["A1:A18"].RowHeight = 20.2;
//Adding cell style.
IStyle style1 = workbook.Styles.Add("style1");
style1.Color = System.Drawing.Color.FromArgb(217, 225, 242);
style1.HorizontalAlignment = ExcelHAlign.HAlignLeft;
style1.VerticalAlignment = ExcelVAlign.VAlignCenter;
style1.Font.Bold = true;
IStyle style2 = workbook.Styles.Add("style2");
style2.Color = System.Drawing.Color.FromArgb(142, 169, 219);
style2.VerticalAlignment = ExcelVAlign.VAlignCenter;
style2.NumberFormat = "[Red]($#,###)";
style2.Font.Bold = true;
sheet1.Range["A10"].CellStyle = style1;
sheet1.Range["B10:D10"].CellStyle.Color = System.Drawing.Color.FromArgb(217, 225, 242);
sheet1.Range["B10:D10"].HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet1.Range["B10:D10"].VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["B10:D10"].CellStyle.Font.Bold = true;
sheet1.Range["A11:A17"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["A11:D17"].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
sheet1.Range["A11:D17"].Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Grey_25_percent;
sheet1.Range["D18"].CellStyle = style2;
sheet1.Range["D18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["A18:C18"].CellStyle.Color = System.Drawing.Color.FromArgb(142, 169, 219);
sheet1.Range["A18:C18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet1.Range["A18:C18"].CellStyle.Font.Bold = true;
sheet1.Range["A18:C18"].NumberFormat = "$#,###";
sheet1.Range[10, 1].Text = "Category";
sheet1.Range[10, 2].Text = "Expected cost";
sheet1.Range[10, 3].Text = "Actual Cost";
sheet1.Range[10, 4].Text = "Difference";
sheet1.Range[11, 1].Text = "Venue";
sheet1.Range[12, 1].Text = "Seating & Decor";
sheet1.Range[13, 1].Text = "Technical team";
sheet1.Range[14, 1].Text = "Performers";
sheet1.Range[15, 1].Text = "Performer\'s transport";
sheet1.Range[16, 1].Text = "Performer\'s stay";
sheet1.Range[17, 1].Text = "Marketing";
sheet1.Range[18, 1].Text = "Total";
sheet1.Range["B11:D17"].NumberFormat = "$#,###";
sheet1.Range["D11"].NumberFormat = "[Red]($#,###)";
sheet1.Range["D12"].NumberFormat = "[Red]($#,###)";
sheet1.Range["D14"].NumberFormat = "[Red]($#,###)";
sheet1.Range["B11"].Number = 16250;
sheet1.Range["B12"].Number = 1600;
sheet1.Range["B13"].Number = 1000;
sheet1.Range["B14"].Number = 12400;
sheet1.Range["B15"].Number = 3000;
sheet1.Range["B16"].Number = 4500;
sheet1.Range["B17"].Number = 3000;
sheet1.Range["B18"].Formula = "=SUM(B11:B17)";
sheet1.Range["C11"].Number = 17500;
sheet1.Range["C12"].Number = 1828;
sheet1.Range["C13"].Number = 800;
sheet1.Range["C14"].Number = 14000;
sheet1.Range["C15"].Number = 2600;
sheet1.Range["C16"].Number = 4464;
sheet1.Range["C17"].Number = 2700;
sheet1.Range["C18"].Formula = "=SUM(C11:C17)";
sheet1.Range["D11"].Formula = "=IF(C11>B11,C11-B11,B11-C11)";
sheet1.Range["D12"].Formula = "=IF(C12>B12,C12-B12,B12-C12)";
sheet1.Range["D13"].Formula = "=IF(C13>B13,C13-B13,B13-C13)";
sheet1.Range["D14"].Formula = "=IF(C14>B14,C14-B14,B14-C14)";
sheet1.Range["D15"].Formula = "=IF(C15>B15,C15-B15,B15-C15)";
sheet1.Range["D16"].Formula = "=IF(C16>B16,C16-B16,B16-C16)";
sheet1.Range["D17"].Formula = "=IF(C17>B17,C17-B17,B17-C17)";
sheet1.Range["D18"].Formula = "=IF(C18>B18,C18-B18,B18-C18)";
IChartShape chart = sheet1.Charts.Add();
chart.ChartType = ExcelChartType.Pie;
chart.DataRange = sheet1.Range["A11:B17"];
chart.IsSeriesInRows = false;
chart.ChartTitle = "Event Expenses";
chart.ChartTitleArea.Bold = true;
chart.ChartTitleArea.Size = 16;
chart.TopRow = 1;
chart.BottomRow = 10;
chart.LeftColumn = 1;
chart.RightColumn = 5;
chart.ChartArea.Border.LinePattern = ExcelChartLinePattern.None;
string OutputFileName = "ExpensesReport.xlsx";
workbook.SaveAs(OutputFileName);
excelEngine.Dispose();
#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)
{
try
{
//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(OutputFileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(OutputFileName);
#endif
//Exit
this.Close();
}
catch (Win32Exception)
{
MessageBox.Show("MS Excel is not installed in this system");
}
}
else
{
// Exit
this.Close();
}
#endregion
}
}
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 modernButton3_Click(object sender, EventArgs e)
{
Log("%TEST%");
var ew = new ExcelWindow("Test#3");
ew.spreadsheet1.Open("Data\\Test\\c5.xlsx");
ew.ShowDialog();
//ExcelOperator2 ex=new ExcelOperator2(ew.spreadsheet1);
var s = ew.spreadsheet1.ActiveSheet;
var l = s.ExportDataTable(s.UsedRange, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.ComputedFormulaValues);
//MessageBox.Show($"{l.Rows[0][0].ToString()}");
//ScoreTable st=new ScoreTable(new Dictionary<string, ScoreModel>(),new InputDataIndicator() );
List <ScoreModel> li = new List <ScoreModel>();
for (int j = 0; j < l.Rows.Count; j++)
{
ScoreModel sm = new ScoreModel();
for (int i = 0; i < l.Columns.Count; i++)
{
switch (i)
{
case 0:
sm.Name = l.Rows[j][i].ToString();
break;
case 1:
sm.Id = Int32.Parse(l.Rows[j][i].ToString());
break;
case 2:
sm.SumRank = Int32.Parse(l.Rows[j][i].ToString());
break;
case 3:
sm.SumGradeRank = Int32.Parse(l.Rows[j][i].ToString());
break;
case 4:
sm.Zh = Single.Parse(l.Rows[j][i].ToString());
break;
case 5:
sm.ZhRank = Int32.Parse(l.Rows[j][i].ToString());
break;
case 6:
sm.ZhGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 7:
sm.M = float.Parse(l.Rows[j][i].ToString());
break;
case 8:
sm.MRank = int.Parse(l.Rows[j][i].ToString());
break;
case 9:
sm.MGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 10:
sm.En = float.Parse(l.Rows[j][i].ToString());
break;
case 11:
sm.EnRank = int.Parse(l.Rows[j][i].ToString());
break;
case 12:
sm.EnGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 13:
sm.P = float.Parse(l.Rows[j][i].ToString());
break;
case 14:
sm.PRank = int.Parse(l.Rows[j][i].ToString());
break;
case 15:
sm.PGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 16:
sm.C = float.Parse(l.Rows[j][i].ToString());
break;
case 17:
sm.CRank = int.Parse(l.Rows[j][i].ToString());
break;
case 18:
sm.CGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 19:
sm.Po = float.Parse(l.Rows[j][i].ToString());
break;
case 20:
sm.PoRank = Int32.Parse(l.Rows[j][i].ToString());
break;
case 21:
sm.PoGradeRank = Int32.Parse(l.Rows[j][i].ToString());
break;
case 22:
sm.H = float.Parse(l.Rows[j][i].ToString());
break;
case 23:
sm.HRank = int.Parse(l.Rows[j][i].ToString());
break;
case 24:
sm.HGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 25:
sm.G = float.Parse(l.Rows[j][i].ToString());
break;
case 26:
sm.GRank = int.Parse(l.Rows[j][i].ToString());
break;
case 27:
sm.GGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
case 28:
sm.B = float.Parse(l.Rows[j][i].ToString());
break;
case 29:
sm.BRank = int.Parse(l.Rows[j][i].ToString());
break;
case 30:
sm.BGradeRank = int.Parse(l.Rows[j][i].ToString());
break;
default: throw new NotImplementedException();
break;
}
}
li.Add(sm);
//MessageBox.Show($"%C# {name}%=>%JSON%:\r\n{sm.ToString()}");
}
IList <(NfSubjects, float)> aveList = Analyzer.Average(li);
//BindingList<(NfSubjects, float)> aveBindingList = Analyzer.Average(li);//todo;
IList <(NfSubjects, float)> sumList = Analyzer.Sum(li);
IList <(NfSubjects, float[])> modeList = Analyzer.Mode(li);
IList <(NfSubjects, double)> midList = Analyzer.Mid(li);
Log("Average:\r\n" + string.Join(",", aveList));
Log("Sum:\r\n" + string.Join(",", sumList));
Log("Mode:");
foreach (var mode in modeList)
{
Log($"{mode.Item1.ToString()}:{string.Join(",",mode.Item2)}");
}
//Log("Mode:\r\n" + string.Join(",",modeList));
Log("Mid:\r\n" + string.Join(",", midList));
//MessageBox.Show(aveList[0].Item2.ToString());
DataVisualization dv = new DataVisualization();
dv.lDChartDataSourceBindingSource.DataSource = new LDChartDataSource(aveList);
#region Data Convert:
ChartSeries cs = new ChartSeries("%Title%");
ChartDataBindModel cdbm = new ChartDataBindModel(aveList);
ChartDataBindAxisLabelModel cdbalm = new ChartDataBindAxisLabelModel(aveList);
cdbm.XName = "Item1";
cdbalm.LabelName = "Item1";
cdbm.YNames = new String[] { "Item2" };
cs.SeriesModel = cdbm;
dv.chartControl1.Series.Add(cs);
dv.chartControl1.PrimaryXAxis.LabelsImpl = cdbalm;
dv.chartControl2.Series.Add(cs);
dv.chartControl2.PrimaryXAxis.LabelsImpl = cdbalm;
dv.chartControl2.PrimaryYAxis.Range = new MinMaxInfo(0, 150, 100);
#endregion
dv.Show();
//#region PPT Output
//IPresentation ip = Presentation.Create();
//IList<double> avelist_ppt=new List<double>();
//foreach (var ii in aveList)
//{
// avelist_ppt.Add(ii.Item2);
//}
//ISlide sl = ip.Slides.Add(SlideLayoutType.Blank);
//IPresentationChart ct = sl.Shapes.AddChart(avelist_ppt, 5, 5, 100, 100);
//Spire.Presentation.Presentation ppt =new Spire.Presentation.Presentation(File.Open($"{new Random().Next()}.pptx",FileMode.Create,FileAccess.ReadWrite),FileFormat.Pptx2010);
//var cts = ppt.Slides[0].Shapes.AppendChart(ChartType.Column3D,new RectangleF(5,5,200,200));
//foreach (var ave in avelist_ppt)
//{
// //cts.Series.Append(ave);
// cts.Series[0].Values.Add(ave);
//}
//ip.Save($"{new Random().Next()}.pptx");
//#endregion
#region ExcelChart
ExcelEngine ee = new ExcelEngine();
IApplication xls = ee.Excel;
xls.DefaultVersion = Syncfusion.XlsIO.ExcelVersion.Excel2016;
IWorkbook wb = xls.Workbooks.Open("Data\\Test\\c5.xlsx", ExcelOpenType.Automatic);
IWorksheet ws = wb.Worksheets.Create("Chart");
IChartShape chart = ws.Charts.Add();
/////////////////////////////////////////////////////
//IChartSerie serie = chart.Series.Add(Syncfusion.XlsIO.ExcelChartType.Column_Clustered);
//chart.ChartType = Syncfusion.XlsIO.ExcelChartType.Column_Clustered;
IList <string> xave = new List <string>();
IList <object> yave = new List <object>();
int allavei = Int32.MaxValue, allmidi = Int32.MaxValue;
for (var index = 0; index < aveList.Count; index++)
{
if (aveList[index].Item1 == NfSubjects.All)
{
allavei = index;
}
var ave = aveList[index];
xave.Add(ave.Item1.Name());
yave.Add(ave.Item2);
}
//serie.EnteredDirectlyValues = yave.ToArray();
//serie.Name = "平均分";
//serie.EnteredDirectlyCategoryLabels = xave.ToArray();
ChartGen.GenChart(chart, xave.ToArray(), yave.ToArray(), "平均分", ExcelChartType.Column_Clustered);
////////////////////////////////////////////////
//IChartSerie seriemid = chart.Series.Add(Syncfusion.XlsIO.ExcelChartType.Column_Clustered);
//chart.ChartType = Syncfusion.XlsIO.ExcelChartType.Column_Clustered;
IList <string> xmid = new List <string>();
IList <object> ymid = new List <object>();
for (var index = 0; index < midList.Count; index++)
{
if (midList[index].Item1 == NfSubjects.All)
{
allmidi = index;
}
var mid = midList[index];
xmid.Add(mid.Item1.Name());
ymid.Add(mid.Item2);
}
ChartGen.GenChart(chart, xmid.ToArray(), ymid.ToArray(), "中位分", ExcelChartType.Column_Clustered);
//{{{{{{{{{{{{{{
xave.RemoveAt(allavei);
yave.RemoveAt(allavei);
xmid.RemoveAt(allmidi);
ymid.RemoveAt(allmidi);
IChartShape leida = ws.Shapes.AddChart();
ChartGen.GenChart(leida, xave.ToArray(), yave.ToArray(), "平均分", ExcelChartType.Radar);
ChartGen.GenChart(leida, xmid.ToArray(), ymid.ToArray(), "中位分", ExcelChartType.Radar);
leida.Name = "学科成绩分布1";
leida.ChartTitle = "学科成绩分布1";
//}}}}}}}}}}}}}}
//{{{{{{{{{{{{{{
var xyave = Analyzer.ReArrangeData(aveList);
var xymid = Analyzer.ReArrangeData(midList);
IChartShape leida2 = ws.Shapes.AddChart();
ChartGen.GenChart(leida2, xyave.Item1, xyave.Item2, "平均分", ExcelChartType.Radar);
ChartGen.GenChart(leida2, xymid.Item1, xymid.Item2, "中位分", ExcelChartType.Radar);
leida2.Name = "学科成绩分布2";
leida2.ChartTitle = "学科成绩分布2";
//}}}}}}}}}}}}}}
var ordered = li.OrderBy(pp => pp.Sum);
ChartGen.GenChart(ws.Shapes.AddChart(), ordered.Select(sm => (object)(sm.Name)).ToArray(), ordered.Select(sm => (object)(sm.Sum ?? 0)).ToArray(), "总分", ExcelChartType.Column_3D);
//seriemid.EnteredDirectlyValues = ymid.ToArray();
//seriemid.Name = "中位分";
//seriemid.EnteredDirectlyCategoryLabels = xmid.ToArray();
/////////////////////////////////////////////////
xls.Save("output.xlsx");
wb.Close();
ee.Dispose();
#endregion
}
/// <summary>
/// Creates Spreadsheet with Styles and Formatting
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreate_Click(object sender, 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.
IApplication application = excelEngine.Excel;
//Get the path of the input file
string inputPath = GetFullTemplatePath("BudgetPlanner.xls");
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 12 worksheets
IWorkbook workbook = application.Workbooks.Open(inputPath);
IWorksheet worksheet = workbook.Worksheets[1];
worksheet.FirstVisibleRow = 3;
//Set the Default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
workbook.Version = ExcelVersion.Excel97to2003;
fileName = "BudgetPlannerOutput.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcek2007.Checked)
{
workbook.Version = ExcelVersion.Excel2007;
fileName = "BudgetPlannerOutput.xlsx";
}
//Set the Default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
workbook.Version = ExcelVersion.Excel2010;
fileName = "BudgetPlannerOutput.xlsx";
}
//Set the Default version as Excel 2013
else if (this.rdbExcel2013.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
fileName = "BudgetPlannerOutput.xlsx";
}
IFont font = workbook.CreateFont();
font.Bold = true;
#endregion
#region TextBox
ITextBoxShape textbox = worksheet.TextBoxes.AddTextBox(5, 2, 40, 140);
textbox.Text = "Quick Budget";
textbox.RichText.SetFont(0, 11, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(204, 204, 255);
textbox = worksheet.TextBoxes.AddTextBox(7, 2, 25, 140);
textbox.Text = "Income";
textbox.RichText.SetFont(0, 5, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
textbox = worksheet.TextBoxes.AddTextBox(16, 2, 25, 140);
textbox.Text = "Spending";
textbox.RichText.SetFont(0, 7, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
#endregion
#region Write Text and Numbers
worksheet.Range["O6"].Text = "Weekly";
worksheet.Range["E7"].Text = "Frequency";
worksheet.Range["F7"].Text = "Amount";
worksheet.Range["G7"].Text = "Monthly";
worksheet.Range["H7"].Text = "Yearly";
worksheet.Range["B8"].Text = "Total Income";
worksheet.Range["C9"].Text = "Salary/Wages";
worksheet.Range["C10"].Text = "Salary/Wages(Spouse)";
worksheet.Range["C11"].Text = "Other";
worksheet.Range["C12"].Text = "Other";
worksheet.Range["C13"].Text = "Other";
worksheet.Range["B17"].Text = "Transportation";
worksheet.Range["F25"].Number = 3000;
worksheet.Range["F9"].Number = 55000;
worksheet.Range["F10"].Number = 35000;
worksheet.Range["C18"].Text = "Auto Loan/Lease";
worksheet.Range["C19"].Text = "Insurance";
worksheet.Range["C20"].Text = "Gas ";
worksheet.Range["C21"].Text = "Maintenance ";
worksheet.Range["C22"].Text = "Registration/Inspection";
worksheet.Range["C23"].Text = "Bill's train pass";
worksheet.Range["C24"].Text = "Jane's bus pass";
worksheet.Range["C25"].Text = "Other";
worksheet.Range["E16"].Text = "Total";
worksheet.Range["N6"].Text = "Chart";
worksheet.Range["B27"].Text = "Home";
worksheet.Range["F28"].Number = 20000;
worksheet.Range["F29"].Number = 5000;
worksheet.Range["F33"].Number = 5000;
worksheet.Range["C28"].Text = "EMI";
worksheet.Range["C29"].Text = "Rent";
worksheet.Range["C30"].Text = "Maintanence";
worksheet.Range["C31"].Text = "Insurance";
worksheet.Range["C32"].Text = "Furniture";
worksheet.Range["C33"].Text = "Household Supplies";
worksheet.Range["C34"].Text = "Groceries";
worksheet.Range["C35"].Text = "Real Estate Tax";
worksheet.Range["C36"].Text = "Other";
worksheet.Range["B39"].Text = "Utilities";
worksheet.Range["F41"].Number = 1000;
worksheet.Range["F42"].Number = 250;
worksheet.Range["F43"].Number = 150;
worksheet.Range["F45"].Number = 175;
worksheet.Range["C40"].Text = "Phone - Home";
worksheet.Range["C41"].Text = "Phone - Cell";
worksheet.Range["C42"].Text = "Cable";
worksheet.Range["C43"].Text = "Gas";
worksheet.Range["C44"].Text = "Water";
worksheet.Range["C45"].Text = "Electricity";
worksheet.Range["C46"].Text = "Internet";
worksheet.Range["C47"].Text = "Other";
worksheet.Range["B50"].Text = "Health";
worksheet.Range["F53"].Number = 500;
worksheet.Range["C51"].Text = "Dental";
worksheet.Range["C52"].Text = "Medical";
worksheet.Range["C53"].Text = "Medication";
worksheet.Range["C54"].Text = "Vision/contacts";
worksheet.Range["C55"].Text = "Life Insurance";
worksheet.Range["C56"].Text = "Electricity";
worksheet.Range["C57"].Text = "Other";
#endregion
#region Cell styles
IStyle tableStyle = workbook.Styles.Add("TableStyle");
tableStyle.BeginUpdate();
tableStyle.Color = Color.White;
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableStyle.EndUpdate();
worksheet.Range["E7:H7"].CellStyle.Font.Bold = true;
worksheet.Range["B17"].CellStyle.Font.Bold = true;
worksheet.Range["B27"].CellStyle.Font.Bold = true;
worksheet.Range["B39"].CellStyle.Font.Bold = true;
worksheet.Range["B50"].CellStyle.Font.Bold = true;
worksheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single;
worksheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C9:C13"].CellStyle = tableStyle;
worksheet.Range["E9:F13"].CellStyle = tableStyle;
worksheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["B17:C17"].CellStyle.Color = Color.White;
worksheet.Range["C18:C25"].CellStyle = tableStyle;
worksheet.Range["O6"].CellStyle = tableStyle;
worksheet.Range["E18:F25"].CellStyle = tableStyle;
worksheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C28:C36"].CellStyle = tableStyle;
worksheet.Range["B27:C27"].CellStyle.Color = Color.White;
worksheet.Range["E28:F36"].CellStyle = tableStyle;
worksheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C40:C47"].CellStyle = tableStyle;
worksheet.Range["B39:C39"].CellStyle.Color = Color.White;
worksheet.Range["E40:F47"].CellStyle = tableStyle;
worksheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C51:C57"].CellStyle = tableStyle;
worksheet.Range["B50:C50"].CellStyle.Color = Color.White;
worksheet.Range["E51:F57"].CellStyle = tableStyle;
#endregion
#region Data Validation
IDataValidation validation = worksheet.Range["E9:E13"].DataValidation;
worksheet.Range["E9:E13"].Text = "Monthly";
validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation1 = worksheet.Range["E18:E25"].DataValidation;
worksheet.Range["E18:E25"].Text = "Monthly";
validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation2 = worksheet.Range["O6"].DataValidation;
validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" };
IDataValidation validation3 = worksheet.Range["E28:E37"].DataValidation;
worksheet.Range["E28:E36"].Text = "Monthly";
validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation4 = worksheet.Range["E40:E47"].DataValidation;
worksheet.Range["E40:E47"].Text = "Monthly";
validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation5 = worksheet.Range["E51:E57"].DataValidation;
worksheet.Range["E51:E57"].Text = "Monthly";
validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
#endregion
#region Formulas
worksheet.Range["G8"].Formula = "SUM(G9:G13)";
worksheet.Range["H8"].Formula = "SUM(H9:H13)";
worksheet.Range["G17"].Formula = "SUM(G18:G25)";
worksheet.Range["H17"].Formula = "SUM(H18:H25)";
worksheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71";
worksheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71";
for (int i = 9; i <= 13; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
for (int i = 18; i <= 25; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
worksheet.Range["G27"].Formula = "SUM(G28:G36)";
worksheet.Range["H27"].Formula = "SUM(H28:H37)";
for (int i = 28; i <= 36; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
worksheet.Range["G39"].Formula = "SUM(G40:G47)";
worksheet.Range["H39"].Formula = "SUM(H40:H47)";
for (int i = 40; i <= 47; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
worksheet.Range["G50"].Formula = "SUM(G51:G57)";
worksheet.Range["H50"].Formula = "SUM(H51:H57)";
for (int i = 51; i <= 57; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
#endregion
#region SummaryChart
//Clustered Column Chart
IChartShape chart = worksheet.Charts.Add();
//Set Chart Type
chart.ChartType = ExcelChartType.Bar_Clustered;
//Set DataRange.
chart.Series.Add("Expense");
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"];
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Income");
chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"];
chart.Series[1].DataPoints[0].DataLabels.IsValue = true;
chart.Series[1].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Balance");
chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"];
chart.Series[2].DataPoints[0].DataLabels.IsValue = true;
chart.Series[2].DataPoints[0].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
chart.PrimaryCategoryAxis.Visible = false;
//Format Chart Area
IChartFrameFormat chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
IChartFrameFormat chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 7;
chart.BottomRow = 22;
chart.LeftColumn = 9;
chart.RightColumn = 16;
chart.ChartTitle = "Budget Summary";
chart.ChartTitleArea.Bold = true;
#endregion
#region SpendingChart
chart = worksheet.Charts.Add();
chart.ChartTitle = "Spending Summary";
chart.ChartTitleArea.Bold = true;
//Set Chart Type
chart.ChartType = ExcelChartType.Pie_3D;
//Set DataRange.
chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"];
chart.IsSeriesInRows = false;
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"];
chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"];
chart.Series[0].Name = "Spending summary";
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series[0].DataPoints[1].DataLabels.IsValue = true;
chart.Series[0].DataPoints[1].DataLabels.Size = 7f;
chart.Series[0].DataPoints[2].DataLabels.IsValue = true;
chart.Series[0].DataPoints[2].DataLabels.Size = 7f;
chart.Series[0].DataPoints[3].DataLabels.IsValue = true;
chart.Series[0].DataPoints[3].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
//Format Chart Area
chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223);
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 25;
chart.BottomRow = 42;
chart.LeftColumn = 9;
chart.RightColumn = 16;
#endregion
#region Sheet View
workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden;
workbook.Worksheets[0].Activate();
workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue;
workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue;
workbook.Worksheets[1].IsRowColumnHeadersVisible = false;
worksheet.InsertColumn(9);
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
workbook.Close();
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>
/// 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);
}
}
}
// сохраняем в Excel
private void SaveExcel_m(string PathToSave_file)
{
try
{
FileInfo fi = new FileInfo(PathToSave_file);
ExcelEngine excelEngine = new ExcelEngine();
IApplication _excel = excelEngine.Excel;
// Тип файла
_excel.DefaultVersion = ExcelVersion.Excel97to2003; // .xls
if (fi.Extension == ".xlsx")
{
_excel.DefaultVersion = ExcelVersion.Excel2010; // .xlsx
}
// Создаем книгу
IWorkbook wBook = _excel.Workbooks.Create(1);
// Создаем лист
IWorksheet wSheet = wBook.Worksheets[0];
IStyle style = wBook.Styles.Add("FillColor");
style.ColorIndex = ExcelKnownColors.Grey_25_percent;
// заполняем заголовок (1-я строка)
for (int i = 0; i < dataGridView_m.Columns.Count; i++)
{
wSheet.Range[1, i + 1].Text = dataGridView_m.Columns[i].HeaderText;
wSheet.Range[1, i + 1].CellStyle = style;
wSheet.Range[1, i + 1].BorderAround(ExcelLineStyle.Thin, Color.Black);
}
IStyle style2 = wBook.Styles.Add("FillColor2");
style2.ColorIndex = ExcelKnownColors.Light_yellow;
// Заполняем данными (начинаем заполянть со 2-й строки)
for (int i = 0; i < dataGridView_m.Rows.Count; i++)
{
for (int j = 0; j < dataGridView_m.Columns.Count; j++)
{
if (j == 0)
{
wSheet.Range[i + 2, j + 1].Value = dataGridView_m.Rows[i].Cells[j].ToolTipText
+ (!string.IsNullOrEmpty(dataGridView_m.Rows[i].Cells[j].Value.ToString()) ? dataGridView_m.Rows[i].Cells[j].Value.ToString() : "");
}
else
{
wSheet.Range[i + 2, j + 1].Value = dataGridView_m.Rows[i].Cells[j].Value.ToString();
}
wSheet.Range[i + 2, j + 1].BorderAround(ExcelLineStyle.Thin, Color.Black);
}
}
// выравниваем колонки
for (int i = 0; i < dataGridView_m.Columns.Count; i++)
{
wSheet.AutofitColumn(i + 1);
}
// создаем график
IChartShape chart = wSheet.Charts.Add();
chart.DataRange = wSheet.Range[1, 1, 2, dataGridView_m.Columns.Count];
chart.ChartType = ExcelChartType.Column_Clustered;
chart.IsSeriesInRows = true;
chart.TopRow = dataGridView_m.Rows.Count + 3;
chart.LeftColumn = 1;
chart.RightColumn = 20;
chart.BottomRow = dataGridView_m.Rows.Count + 30;
chart.ChartTitle = "";
// Сохраняем файл Excel
_excel.Save(PathToSave_file);
filename_load = PathToSave_file;
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString(), "Ошибка сохранения файла Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (filename_load != "")
{
mk_saveExcel_ToolStripMenuItem.Enabled = true;
}
}
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
}
//
// 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());
}