static void DataLabelsSeparator(IWorkbook workbook)
{
#region #DataLabelsSeparator
Worksheet worksheet = workbook.Worksheets["chartTask1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.Pie, worksheet["B2:C7"]);
chart.TopLeftCell = worksheet.Cells["E2"];
chart.BottomRightCell = worksheet.Cells["K15"];
// Display the category name and percentage.
DataLabelOptions dataLabels = chart.Views[0].DataLabels;
dataLabels.ShowCategoryName = true;
dataLabels.ShowPercent = true;
dataLabels.Separator = "\n";
// Set the chart style.
chart.Style = ChartStyle.ColorGradient;
// Hide the legend.
chart.Legend.Visible = false;
// Set the angle of the first pie-chart slice.
chart.Views[0].FirstSliceAngle = 100;
#endregion #DataLabelsSeparator
}
public static void CreatePieChart(IWorkbook wbook)
{
DevExpress.Spreadsheet.Worksheet worksheet = SetActiveWorksheet(wbook, "Range1");
// Create a chart and specify its location
Chart chart = worksheet.Charts.Add(ChartType.PieExploded, worksheet["B2:C7"]);
// Display the chart title
chart.Title.Visible = true;
chart.Title.SetReference(worksheet["B1"]);
chart.TopLeftCell = worksheet.Cells["E2"];
chart.BottomRightCell = worksheet.Cells["K15"];
// Set the chart style
chart.Style = ChartStyle.ColorGradient;
// Hide the legend
chart.Legend.Visible = false;
// Rotate the pie chart view
chart.Views[0].FirstSliceAngle = 100;
// Display data labels
DataLabelOptions dataLabels = chart.Views[0].DataLabels;
dataLabels.ShowCategoryName = true;
dataLabels.ShowPercent = true;
dataLabels.Separator = "\n";
}
static void PieChart(Workbook workbook)
{
#region #PieChart
Worksheet worksheet = workbook.Worksheets["chartTask1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a chart and specify its location
Chart chart = worksheet.Charts.Add(ChartType.PieExploded, worksheet["B2:C7"]);
chart.TopLeftCell = worksheet.Cells["E2"];
chart.BottomRightCell = worksheet.Cells["K15"];
// Set the chart style
chart.Style = ChartStyle.ColorGradient;
// Hide the legend
chart.Legend.Visible = false;
// Rotate the pie chart view
chart.Views[0].FirstSliceAngle = 100;
// Display data labels
DataLabelOptions dataLabels = chart.Views[0].DataLabels;
dataLabels.ShowCategoryName = true;
dataLabels.ShowPercent = true;
dataLabels.Separator = "\n";
#endregion #PieChart
}
static void DataLabelsSeparator(IWorkbook workbook)
{
#region #DataLabelsSeparator
Worksheet worksheet = workbook.Worksheets["chartTask1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
Chart chart = worksheet.Charts.Add(ChartType.Pie, worksheet["B2:C7"]);
chart.TopLeftCell = worksheet.Cells["E2"];
chart.BottomRightCell = worksheet.Cells["K15"];
DataLabelOptions dataLabels = chart.Views[0].DataLabels;
dataLabels.ShowCategoryName = true;
dataLabels.ShowPercent = true;
dataLabels.Separator = "\n";
chart.Style = ChartStyle.ColorGradient;
chart.Legend.Visible = false;
chart.Views[0].FirstSliceAngle = 100;
#endregion #DataLabelsSeparator
}
public static void CreateBubbleChart(IWorkbook wbook)
{
Worksheet worksheet = SetActiveWorksheet(wbook, "Range6");
// Create a chart and specify its location
Chart chart = worksheet.Charts.Add(ChartType.Bubble3D);
chart.TopLeftCell = worksheet.Cells["F2"];
chart.BottomRightCell = worksheet.Cells["L15"];
Series s1 = chart.Series.Add(worksheet["A3"], worksheet["C3:C7"], worksheet["D3:D7"]);
s1.BubbleSize = ChartData.FromRange(worksheet["E3:E7"]);
Series s2 = chart.Series.Add(worksheet["A9"], worksheet["C9:C13"], worksheet["D9:D13"]);
s2.BubbleSize = ChartData.FromRange(worksheet["E9:E13"]);
// Set the chart style
chart.Style = ChartStyle.ColorGradient;
// Set the bubble size 1.5x relative to the default setting.
chart.Views[0].BubbleScale = 150;
// Hide the legend
chart.Legend.Visible = false;
// Display data labels
DataLabelOptions dataLabels = chart.Views[0].DataLabels;
dataLabels.ShowBubbleSize = true;
// Set the minimum and maximum values for the chart value axis.
Axis axis = chart.PrimaryAxes[1];
axis.Scaling.AutoMax = false;
axis.Scaling.Max = 82;
axis.Scaling.AutoMin = false;
axis.Scaling.Min = 64;
}
/// <summary>
/// Method to create Excel based on options selected by the user.
/// </summary>
private void GenerateExcel()
{
openBtn.Enabled = false;
try
{
FileInfo file = new FileInfo(RESULT_FILE);
if (file.Exists && IsFileAlreadyOpen(file))
{
return;
}
string text = File.ReadAllText(SOURCE_JSON);
ExampleList list = JsonConvert.DeserializeObject <ExampleList>(text);
Workbook book = new Workbook();
Worksheet dataSheet = book.Worksheets[0];
dataSheet.Name = "DataSheet";
Cell cell = dataSheet.Cells["A1"];
cell.Value = "EmployeeId";
cell = dataSheet.Cells["B1"];
cell.Value = "Type";
cell = dataSheet.Cells["C1"];
cell.Value = "Price";
dataSheet.Import(list, 1, 0);
if (chartCB.Checked)
{
Worksheet chartsSheet = book.Worksheets.Add("Charts");
Chart chart = chartsSheet.Charts.Add(ChartType.Pie);
chart.TopLeftCell = chartsSheet.Cells["B2"];
chart.BottomRightCell = chartsSheet.Cells["P30"];
// Add chart series using worksheet ranges as the data sources.
Series series1 = chart.Series.Add(dataSheet["B1"], dataSheet["B2:B10"], dataSheet["C2:C10"]);
chart.Series.Add(dataSheet["A1"], dataSheet["A2:A10"], dataSheet["C2:C10"]);
// Display the category name and percentage.
DataLabelOptions dataLabels = chart.Views[0].DataLabels;
dataLabels.ShowCategoryName = true;
dataLabels.LabelPosition = DataLabelPosition.OutsideEnd;
dataLabels.ShowPercent = true;
}
if (tableCB.Checked)
{
Worksheet tableSheet = book.Worksheets.Add("Table");
cell = tableSheet.Cells["A1"];
cell.Value = "EmployeeId";
cell = tableSheet.Cells["B1"];
cell.Value = "Type";
cell = tableSheet.Cells["C1"];
cell.Value = "Price";
tableSheet.Import(list, 1, 0);
Table table = tableSheet.Tables.Add(tableSheet["A1:C" + (list.Count + 1)], true);
table.ShowTotals = true;
table.Style = book.TableStyles[BuiltInTableStyleId.TableStyleMedium27];
}
if (pivotCB.Checked)
{
Worksheet pivotSheet = book.Worksheets.Add("Pivot");
PivotTable table = pivotSheet.PivotTables.Add(dataSheet["A1:C10"], pivotSheet["A1"], "PivotTable");
table.RowFields.Add(table.Fields["EmployeeId"]);
table.ColumnFields.Add(table.Fields["Type"]);
table.DataFields.Add(table.Fields["Price"]);
table.Style = book.TableStyles[BuiltInPivotStyleId.PivotStyleDark6];
}
dataSheet.Visible = false;
dataSheet.VisibilityType = WorksheetVisibilityType.VeryHidden;
book.SaveDocument(RESULT_FILE, DocumentFormat.Xlsx);
MessageBox.Show("Excel created successfully.");
}
catch (Exception ex)
{
MessageBox.Show("Some error occurred, Please try again.");
}
openBtn.Enabled = true;
}
