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);
}
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);
}
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);
}
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 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;
//Set the default version as Excel 2007;
if (rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
}
//Set the default version as Excel 2010;
else if (rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
}
else if (rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
//Get the path of the input file
string inputPath = GetFullTemplatePath("PivotCodeDate.xlsx");
IWorkbook workbook = application.Workbooks.Open(inputPath);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Add Pivot Table
//Access the worksheet to draw pivot table.
IWorksheet pivotSheet = workbook.Worksheets[1];
//Select the data to add in cache
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
//Insert the pivot table.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
IPivotField field = pivotSheet.PivotTables[0].Fields[5];
pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);
//Show expand/collapse button.
pivotTable.ShowDrillIndicators = true;
//Shows row grand.
pivotTable.RowGrand = true;
//Shows filter and field caption.
pivotTable.DisplayFieldCaptions = true;
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
#endregion
#region Add Pivot Chart
//Add the Pivot chart worksheet to the workbook.
IChart pivotChartSheet = workbook.Charts.Add();
pivotChartSheet.Name = "PivotChart";
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;
//Activate the pivot worksheet.
pivotChartSheet.Activate();
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs("PivotChart.xlsx");
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
try
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("PivotChart.xlsx");
//Exit
this.Close();
}
catch (Win32Exception)
{
MessageBox.Show("MS Excel is not installed in this system");
}
}
else
{
// Exit
this.Close();
}
#endregion
}
//
// GET: /PivotChart/
public ActionResult PivotChart(string button)
{
if (button == 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;
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath("PivotCodeDate.xlsx"));
workbook.Version = ExcelVersion.Excel2016;
// The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Access the sheet to draw pivot table.
IWorksheet pivotSheet = workbook.Worksheets[1];
//Select the data to add in cache
IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:H50"]);
//Insert the pivot table.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
IPivotField field = pivotSheet.PivotTables[0].Fields[5];
pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);
//Show expand/collapse button.
pivotTable.ShowDrillIndicators = true;
//Shows row grand.
pivotTable.RowGrand = true;
//Shows filter and field caption.
pivotTable.DisplayFieldCaptions = true;
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
//Add the Pivot chart sheet to the workbook.
IChart pivotChartSheet = workbook.Charts.Add();
pivotChartSheet.Name = "PivotChart";
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
//Activate the pivot sheet.
pivotChartSheet.Activate();
try
{
return(excelEngine.SaveAsActionResult(workbook, "PivotChart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
//
// 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());
}
private void Button_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the default version as Excel 2007;
if (this.rdButtonxlsx.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2007;
}
else if (this.rdButtonexcel2010.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2010;
}
else if (this.rdButtonexcel2013.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
#if NETCORE
IWorkbook workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\PivotCodeDate.xlsx");
#else
IWorkbook workbook = application.Workbooks.Open(@"..\..\..\..\..\..\Common\Data\XlsIO\PivotCodeDate.xlsx");
#endif
// The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Access the sheet to draw pivot table.
IWorksheet pivotSheet = workbook.Worksheets[1];
//Select the data to add in cache
IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:H50"]);
//Insert the pivot table.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
IPivotField field = pivotSheet.PivotTables[0].Fields[5];
pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);
//Show expand/collapse button.
pivotTable.ShowDrillIndicators = true;
//Shows row grand.
pivotTable.RowGrand = true;
//Shows filter and field caption.
pivotTable.DisplayFieldCaptions = true;
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
//Add the Pivot chart sheet to the workbook.
IChart pivotChartSheet = workbook.Charts.Add();
pivotChartSheet.Name = "PivotChart";
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
//Activate the pivot sheet.
pivotChartSheet.Activate();
try
{
//Saving the workbook to disk.
workbook.SaveAs("PivotChart.xlsx");
//Close the workbook.
workbook.Close();
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)
{
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("PivotChart.xlsx")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("PivotChart.xlsx");
#endif
//Exit
this.Close();
}
catch (Win32Exception ex)
{
MessageBox.Show("Excel 2007 is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
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);
}
}
private async void btnCreate_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 = "PivotChartCreateSample";
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("PivotChartCreateSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
#endregion
#region Initializing Workbook
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(PivotChart).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.PivotCodeData.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
IWorksheet dataSheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
#endregion
#region Creating Pivot chart
IPivotCache cache = workbook.PivotCaches.Add(dataSheet["A1:H50"]);
//Insert the pivot table.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
IPivotField field = pivotSheet.PivotTables[0].Fields[5];
pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);
//Show expand/collapse button.
pivotTable.ShowDrillIndicators = true;
//Shows row grand.
pivotTable.RowGrand = true;
//Shows filter and field caption.
pivotTable.DisplayFieldCaptions = true;
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
//Add the Pivot chart sheet to the workbook.
IChart pivotChartSheet = workbook.Charts.Add();
pivotChartSheet.Name = "PivotChart";
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
//Activate the pivot sheet.
pivotChartSheet.Activate();
#endregion
#region Saving workbook and disposing objects
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
#endregion
#region Save accknowledgement and Launching of output 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
}
//
// 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 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: /ChartWorksheet/
public ActionResult ChartWorksheet(string Saveoption)
{
if (Saveoption == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Default version is set as Excel 2007
if (Saveoption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 1 worksheet.
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
// Entering the Datas for the chart
sheet.Range["A1"].Text = "Crescent City, CA";
sheet.Range["A1:D1"].Merge();
sheet.Range["A1"].CellStyle.Font.Bold = true;
sheet.Range["B3"].Text = "Precipitation,in.";
sheet.Range["C3"].Text = "Temperature,deg.F";
sheet.Range["A4"].Text = "Jan";
sheet.Range["A5"].Text = "Feb";
sheet.Range["A6"].Text = "March";
sheet.Range["A7"].Text = "Apr";
sheet.Range["A8"].Text = "May";
sheet.Range["A9"].Text = "June";
sheet.Range["A10"].Text = "July";
sheet.Range["A11"].Text = "Aug";
sheet.Range["A12"].Text = "Sept";
sheet.Range["A13"].Text = "Oct";
sheet.Range["A14"].Text = "Nov";
sheet.Range["A15"].Text = "Dec";
sheet.Range["B4"].Number = 10.9;
sheet.Range["B5"].Number = 8.9;
sheet.Range["B6"].Number = 8.6;
sheet.Range["B7"].Number = 4.8;
sheet.Range["B8"].Number = 3.2;
sheet.Range["B9"].Number = 1.4;
sheet.Range["B10"].Number = 0.6;
sheet.Range["B11"].Number = 0.7;
sheet.Range["B12"].Number = 1.7;
sheet.Range["B13"].Number = 5.4;
sheet.Range["B14"].Number = 9.0;
sheet.Range["B15"].Number = 10.4;
sheet.Range["C4"].Number = 47.5;
sheet.Range["C5"].Number = 48.7;
sheet.Range["C6"].Number = 48.9;
sheet.Range["C7"].Number = 50.2;
sheet.Range["C8"].Number = 53.1;
sheet.Range["C9"].Number = 56.3;
sheet.Range["C10"].Number = 58.1;
sheet.Range["C11"].Number = 59.0;
sheet.Range["C12"].Number = 58.5;
sheet.Range["C13"].Number = 55.4;
sheet.Range["C14"].Number = 51.1;
sheet.Range["C15"].Number = 47.8;
sheet.UsedRange.AutofitColumns();
// Adding a New chart to the Existing Worksheet
IChart chart = workbook.Charts.Add();
chart.DataRange = sheet.Range["A3:C15"];
chart.Name = "CrescentCity,CA";
chart.ChartTitle = "Crescent City, CA";
chart.IsSeriesInRows = false;
chart.PrimaryValueAxis.Title = "Precipitation,in.";
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MaximumValue = 14.0;
chart.PrimaryValueAxis.NumberFormat = "0.0";
// Format serie
IChartSerie serieOne = chart.Series[0];
serieOne.Name = "Precipitation,in.";
serieOne.SerieFormat.Fill.FillType = ExcelFillType.Gradient;
serieOne.SerieFormat.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
serieOne.SerieFormat.Fill.GradientColorType = ExcelGradientColor.TwoColor;
serieOne.SerieFormat.Fill.ForeColor = Color.Plum;
//Show value as data labels
serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieOne.DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;
//Format the second serie
IChartSerie serieTwo = chart.Series[1];
serieTwo.SerieType = ExcelChartType.Line_Markers;
serieTwo.Name = "Temperature,deg.F";
//Format marker
serieTwo.SerieFormat.MarkerStyle = ExcelChartMarkerType.Diamond;
serieTwo.SerieFormat.MarkerSize = 8;
serieTwo.SerieFormat.MarkerBackgroundColor = Color.DarkGreen;
serieTwo.SerieFormat.MarkerForegroundColor = Color.DarkGreen;
serieTwo.SerieFormat.LineProperties.LineColor = Color.DarkGreen;
//Use Secondary Axis
serieTwo.UsePrimaryAxis = false;
//Display secondary axis for the series.
chart.SecondaryCategoryAxis.IsMaxCross = true;
chart.SecondaryValueAxis.IsMaxCross = true;
//Set the title
chart.SecondaryValueAxis.Title = "Temperature,deg.F";
chart.SecondaryValueAxis.TitleArea.TextRotationAngle = 90;
//Hide the secondary category axis
chart.SecondaryCategoryAxis.Border.LineColor = Color.Transparent;
chart.SecondaryCategoryAxis.MajorTickMark = ExcelTickMark.TickMark_None;
chart.SecondaryCategoryAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_None;
chart.Legend.Position = ExcelLegendPosition.Bottom;
chart.Legend.IsVerticalLegend = false;
sheet.Move(1);
chart.Activate();
try
{
string ContentType = null;
string fileName = null;
if (Saveoption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "Chart.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "Chart.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
// Close the workbook
workbook.Close();
excelEngine.Dispose();
return(View());
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
//Set the default version as Excel97to2003
IApplication application = excelEngine.Excel;
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "ChartWorksheet.xls";
}
//Set the default version as Excel 2007;
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "ChartWorksheet.xlsx";
}
//Set the default version as Excel 2010;
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "ChartWorksheet.xlsx";
}
//Set the default version as Excel 2010;
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "ChartWorksheet.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheet.
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Insert Data into Cells of Worksheet
// Entering the Datas for the chart
worksheet.Range["A1"].Text = "Crescent City, CA";
worksheet.Range["A1:D1"].Merge();
worksheet.Range["A1"].CellStyle.Font.Bold = true;
worksheet.Range["B3"].Text = "Precipitation,in.";
worksheet.Range["C3"].Text = "Temperature,deg.F";
worksheet.Range["A4"].Text = "Jan";
worksheet.Range["A5"].Text = "Feb";
worksheet.Range["A6"].Text = "March";
worksheet.Range["A7"].Text = "Apr";
worksheet.Range["A8"].Text = "May";
worksheet.Range["A9"].Text = "June";
worksheet.Range["A10"].Text = "July";
worksheet.Range["A11"].Text = "Aug";
worksheet.Range["A12"].Text = "Sept";
worksheet.Range["A13"].Text = "Oct";
worksheet.Range["A14"].Text = "Nov";
worksheet.Range["A15"].Text = "Dec";
worksheet.Range["B4"].Number = 10.9;
worksheet.Range["B5"].Number = 8.9;
worksheet.Range["B6"].Number = 8.6;
worksheet.Range["B7"].Number = 4.8;
worksheet.Range["B8"].Number = 3.2;
worksheet.Range["B9"].Number = 1.4;
worksheet.Range["B10"].Number = 0.6;
worksheet.Range["B11"].Number = 0.7;
worksheet.Range["B12"].Number = 1.7;
worksheet.Range["B13"].Number = 5.4;
worksheet.Range["B14"].Number = 9.0;
worksheet.Range["B15"].Number = 10.4;
worksheet.Range["C4"].Number = 47.5;
worksheet.Range["C5"].Number = 48.7;
worksheet.Range["C6"].Number = 48.9;
worksheet.Range["C7"].Number = 50.2;
worksheet.Range["C8"].Number = 53.1;
worksheet.Range["C9"].Number = 56.3;
worksheet.Range["C10"].Number = 58.1;
worksheet.Range["C11"].Number = 59.0;
worksheet.Range["C12"].Number = 58.5;
worksheet.Range["C13"].Number = 55.4;
worksheet.Range["C14"].Number = 51.1;
worksheet.Range["C15"].Number = 47.8;
worksheet.UsedRange.AutofitColumns();
#endregion
#region Add New chart to Workbook
// Adding a New chart to the Existing Worksheet
IChart chart = workbook.Charts.Add();
chart.DataRange = worksheet.Range["A3:C15"];
chart.Name = "CrescentCity,CA";
chart.ChartTitle = "Crescent City, CA";
chart.IsSeriesInRows = false;
chart.PrimaryValueAxis.Title = "Precipitation,in.";
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MaximumValue = 14.0;
chart.PrimaryValueAxis.NumberFormat = "0.0";
#region Format Series
// Format serie
IChartSerie serieOne = chart.Series[0];
serieOne.Name = "Precipitation,in.";
serieOne.SerieFormat.Fill.FillType = ExcelFillType.Gradient;
serieOne.SerieFormat.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
serieOne.SerieFormat.Fill.GradientColorType = ExcelGradientColor.TwoColor;
serieOne.SerieFormat.Fill.ForeColor = Color.Plum;
//Show value as data labels
serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieOne.DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;
//Format the second serie
IChartSerie serieTwo = chart.Series[1];
serieTwo.SerieType = ExcelChartType.Line_Markers;
serieTwo.Name = "Temperature,deg.F";
//Format marker
serieTwo.SerieFormat.MarkerStyle = ExcelChartMarkerType.Diamond;
serieTwo.SerieFormat.MarkerSize = 8;
serieTwo.SerieFormat.MarkerBackgroundColor = Color.DarkGreen;
serieTwo.SerieFormat.MarkerForegroundColor = Color.DarkGreen;
serieTwo.SerieFormat.LineProperties.LineColor = Color.DarkGreen;
//Use Secondary Axis
serieTwo.UsePrimaryAxis = false;
#endregion
#region Set the Secondary Axis for Second Serie.
//Display secondary axis for the series.
chart.SecondaryCategoryAxis.IsMaxCross = true;
chart.SecondaryValueAxis.IsMaxCross = true;
//Set the title
chart.SecondaryValueAxis.Title = "Temperature,deg.F";
chart.SecondaryValueAxis.TitleArea.TextRotationAngle = 90;
//Hide the secondary category axis
chart.SecondaryCategoryAxis.Border.LineColor = Color.Transparent;
chart.SecondaryCategoryAxis.MajorTickMark = ExcelTickMark.TickMark_None;
chart.SecondaryCategoryAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_None;
#endregion
#region Legend setting
chart.Legend.Position = ExcelLegendPosition.Bottom;
chart.Legend.IsVerticalLegend = false;
#endregion
#region Excel2013 Filter Enabled
if (rdbExcel2013.Checked)
{
chart.Series[1].IsFiltered = true;
chart.Categories[0].IsFiltered = true;
chart.Categories[1].IsFiltered = true;
}
#endregion
//Move the worksheet
worksheet.Move(1);
//Activate the chart
chart.Activate();
#endregion
#region Save Workbook
//Saving the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
//
// 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 async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Default version is set as Excel 2007
if (this.rdbExcel2013.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
else
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheet.
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
// Entering the Datas for the chart
sheet.Range["A1"].Text = "Crescent City, CA";
sheet.Range["A1:D1"].Merge();
sheet.Range["A1"].CellStyle.Font.Bold = true;
sheet.Range["B3"].Text = "Precipitation,in.";
sheet.Range["C3"].Text = "Temperature,deg.F";
sheet.Range["A4"].Text = "Jan";
sheet.Range["A5"].Text = "Feb";
sheet.Range["A6"].Text = "March";
sheet.Range["A7"].Text = "Apr";
sheet.Range["A8"].Text = "May";
sheet.Range["A9"].Text = "June";
sheet.Range["A10"].Text = "July";
sheet.Range["A11"].Text = "Aug";
sheet.Range["A12"].Text = "Sept";
sheet.Range["A13"].Text = "Oct";
sheet.Range["A14"].Text = "Nov";
sheet.Range["A15"].Text = "Dec";
sheet.Range["B4"].Number = 10.9;
sheet.Range["B5"].Number = 8.9;
sheet.Range["B6"].Number = 8.6;
sheet.Range["B7"].Number = 4.8;
sheet.Range["B8"].Number = 3.2;
sheet.Range["B9"].Number = 1.4;
sheet.Range["B10"].Number = 0.6;
sheet.Range["B11"].Number = 0.7;
sheet.Range["B12"].Number = 1.7;
sheet.Range["B13"].Number = 5.4;
sheet.Range["B14"].Number = 9.0;
sheet.Range["B15"].Number = 10.4;
sheet.Range["C4"].Number = 47.5;
sheet.Range["C5"].Number = 48.7;
sheet.Range["C6"].Number = 48.9;
sheet.Range["C7"].Number = 50.2;
sheet.Range["C8"].Number = 53.1;
sheet.Range["C9"].Number = 56.3;
sheet.Range["C10"].Number = 58.1;
sheet.Range["C11"].Number = 59.0;
sheet.Range["C12"].Number = 58.5;
sheet.Range["C13"].Number = 55.4;
sheet.Range["C14"].Number = 51.1;
sheet.Range["C15"].Number = 47.8;
sheet.UsedRange.AutofitColumns();
// Adding a New chart to the Existing Worksheet
IChart chart = workbook.Charts.Add();
chart.DataRange = sheet.Range["A3:C15"];
chart.Name = "CrescentCity,CA";
chart.ChartTitle = "Crescent City, CA";
chart.IsSeriesInRows = false;
chart.PrimaryValueAxis.Title = "Precipitation,in.";
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MaximumValue = 14.0;
chart.PrimaryValueAxis.NumberFormat = "0.0";
// Format serie
IChartSerie serieOne = chart.Series[0];
serieOne.Name = "Precipitation,in.";
serieOne.SerieFormat.Fill.FillType = ExcelFillType.Gradient;
serieOne.SerieFormat.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
serieOne.SerieFormat.Fill.GradientColorType = ExcelGradientColor.TwoColor;
serieOne.SerieFormat.Fill.ForeColor = Color.FromArgb(255, 221, 160, 221);
//Show value as data labels
serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieOne.DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;
//Format the second serie
IChartSerie serieTwo = chart.Series[1];
serieTwo.SerieType = ExcelChartType.Line_Markers;
serieTwo.Name = "Temperature,deg.F";
//Format marker
serieTwo.SerieFormat.MarkerStyle = ExcelChartMarkerType.Diamond;
serieTwo.SerieFormat.MarkerSize = 8;
serieTwo.SerieFormat.MarkerBackgroundColor = Color.FromArgb(255, 0, 100, 0);
serieTwo.SerieFormat.MarkerForegroundColor = Color.FromArgb(255, 0, 100, 0);
serieTwo.SerieFormat.LineProperties.LineColor = Color.FromArgb(255, 0, 100, 0);
//Use Secondary Axis
serieTwo.UsePrimaryAxis = false;
//Display secondary axis for the series.
chart.SecondaryCategoryAxis.IsMaxCross = true;
chart.SecondaryValueAxis.IsMaxCross = true;
//Set the title
chart.SecondaryValueAxis.Title = "Temperature,deg.F";
chart.SecondaryValueAxis.TitleArea.TextRotationAngle = 90;
//Hide the secondary category axis
chart.SecondaryCategoryAxis.Border.LineColor = Color.FromArgb(0, 0, 0, 0);
chart.SecondaryCategoryAxis.MajorTickMark = ExcelTickMark.TickMark_None;
chart.SecondaryCategoryAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_None;
chart.Legend.Position = ExcelLegendPosition.Bottom;
chart.Legend.IsVerticalLegend = false;
//Excel2013 Filter option
if (rdbExcel2013.IsChecked.Value)
{
chart.Series[1].IsFiltered = true;
chart.Categories[0].IsFiltered = true;
chart.Categories[1].IsFiltered = true;
}
sheet.Move(1);
chart.Activate();
#region Save the Workbook
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "ChartWorksheet";
if (workbook.Version == ExcelVersion.Excel97to2003)
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xls"
});
}
else
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
}
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
if (workbook.Version == ExcelVersion.Excel97to2003)
{
storageFile = await local.CreateFileAsync("ChartWorksheet.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("ChartWorksheet.xlsx", CreationCollisionOption.ReplaceExisting);
}
}
if (storageFile != null)
{
//Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
else
{
workbook.Close();
excelEngine.Dispose();
}
#endregion
}
//Export and saves the chart in Excel Sheet
#region XLsIO
private void buttonXLsIO_Click(object sender, EventArgs e)
{
try
{
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the default version as Excel 2007;
application.DefaultVersion = ExcelVersion.Excel2007;
exportFileName = fileName + ".xlsx";
//A new workbook with a worksheet is created.
IWorkbook chartBook = application.Workbooks.Create(1);
IWorksheet sheet = chartBook.Worksheets[0];
//Fill the worksheet by chart data.
for (int i = 1; i <= 4; i++)
{
sheet.Range[i, 1].Number = this.chartControl1.Series[0].Points[i - 1].X;
sheet.Range[i, 2].Number = this.chartControl1.Series[0].Points[i - 1].YValues[0];
}
for (int i = 1; i <= 4; i++)
{
sheet.Range[i + 5, 1].Number = this.chartControl1.Series[1].Points[i - 1].X;
sheet.Range[i + 5, 2].Number = this.chartControl1.Series[1].Points[i - 1].YValues[0];
}
//Create a chart worksheet.
IChart chart = chartBook.Charts.Add("Essential Chart");
//Specifies the title of the Chart.
chart.ChartTitle = "Essential Chart";
//Initializes a new series instance and adds it to the series collection of the chart.
IChartSerie series1 = chart.Series.Add();
//Specify the chart type of the series.
series1.SerieType = ExcelChartType.Column_Clustered;
//Specify the name of the series. This will be displayed as the text of the legend.
series1.Name = "Sample Series";
//Specify the value ranges for the series.
series1.Values = sheet.Range["B1:B5"];
//Specify the Category labels for the series.
series1.CategoryLabels = sheet.Range["A1:A5"];
IChartSerie series2 = chart.Series.Add();
//Specify the chart type of the series.
series2.SerieType = ExcelChartType.Column_Clustered;
//Specify the name of the series. This will be displayed as the text of the legend.
series2.Name = "Sample Series";
//Specify the value ranges for the series.
series2.Values = sheet.Range["B6:B10"];
//Specify the Category labels for the series.
series2.CategoryLabels = sheet.Range["A6:A10"];
//Makes the chart as active sheet.
chart.Activate();
//Save the Chart book.
chartBook.SaveAs(exportFileName);
chartBook.Close();
ExcelUtils.Close();
OpenFile("XLsIO", exportFileName);
System.Diagnostics.Process.Start(exportFileName);
}
catch (Exception ex)
{
this.toolStripStatusLabel1.Text = "Chart Export failed.";
Console.WriteLine(ex.ToString());
}
}
