c#asp.net.net.net-coredotnet-httpclient
How to create redar diagram in excel using Epplus in dotnet 6
I have created report in excel sheet which Is described in image1 and this image should give redar diagram which in in Image2 in different excel sheet like given in screenshot. there are two excel sheet in one excel as you can see in screenshot so in chart sheet there should be redar diagram and sheet named sheet there is excel report valud as you can see in screenshot
I have already done piechart and excel report as well and I get stocked while making redar diagram , example of that I had made piechart and excel report :
public static class TestRunTestCaseExportHelper {
public static byte[] TestRunTestCaseByTestRunIdToExcel(List<TestRunTestCaseExportModel> data1, List<TestRunTestCaseExportTestResultCountModel> datas, List<FunctionModuleModel> funcitonData, TestRunTestCaseCountPercentageModel testRunStatusCountPercentage)
{
try
{
byte[] result;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage())
{
int cellNumb = 1;
string[] totalColumns = {
"SN",
"Test Case",
"Test Plan",
"Status"
};
string[] totalColumn = {
"Test Plan",
"Passed",
"Failed",
"Pending",
"Blocked",
};
string[] totalColumn3 = {
"Function",
"Count"
};
string[] totalColumn4 = {
"Status",
"Percentage",
};
var worksheet = package.Workbook.Worksheets.Add("Chart");
var worksheets = package.Workbook.Worksheets.Add("sheet");
//worksheet.InsertRow(5, 2);
var rand = new Random();
var testRunStatusCountPercentages = testRunStatusCountPercentage;
var data = new List<KeyValuePair<string, int>>
{
new KeyValuePair<string, int>("Passed", testRunStatusCountPercentages.PassedPercentage),
new KeyValuePair<string, int>("Failed", testRunStatusCountPercentages.FailedPercentage),
new KeyValuePair<string, int>("Pending",85),
new KeyValuePair<string, int>("Blocked", 15),
};
for (var i = 0; i < totalColumn4.Length; i++)
{
worksheets.Cells[1, i + 16].Value = totalColumn4[i]; //populate header row
}
var m = 2;
//Fill the table
var startCell = worksheets.Cells[1, 16];
if (testRunStatusCountPercentage != null)
{
for (var i = 0; i < data.Count(); i++)
{
startCell.Offset(i + 1, 0).Value = data[i].Key;
startCell.Offset(i + 1, 1).Value = data[i].Value;
}
using (ExcelRange Rng = worksheets.Cells[1, 16, 5, data.Count + 13])
{
Rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
}
//Add the chart to the sheet
var pieChart = worksheet.Drawings.AddChart("Chart1", eChartType.Pie);
pieChart.SetPosition(data.Count + 1, 0, 0, 0);
pieChart.Title.Text = "Test Case Excution Status";
pieChart.Title.Font.Bold = true;
pieChart.Title.Font.Size = 12;
//Set the data range
var series = pieChart.Series.Add(worksheets.Cells[2, 17, data.Count + 1, 17], worksheets.Cells[2, 16, data.Count + 1, 16]);
var pieSeries = (ExcelPieChartSerie)series;
pieSeries.Explosion = 5;
//Format the labels
pieSeries.DataLabel.Font.Bold = true;
pieSeries.DataLabel.ShowValue = true;
pieSeries.DataLabel.ShowPercent = true;
pieSeries.DataLabel.ShowLeaderLines = true;
pieSeries.DataLabel.Separator = ";";
pieSeries.DataLabel.Position = eLabelPosition.BestFit;
//Format the legend
pieChart.Legend.Add();
pieChart.Legend.Border.Width = 0;
pieChart.Legend.Font.Size = 12;
pieChart.Legend.Font.Bold = true;
pieChart.Legend.Position = eLegendPosition.Right;
// add a new worksheet to the empty workbook
using (var cells = worksheets.Cells[1, 1, 1, totalColumns.Length]) //(1,1) => (1,10)
{
cells.Style.Font.Bold = true;
}
using (var cells = worksheets.Cells[1, 7, 1, totalColumn.Length + 7]) //(1,1) => (1,10)
{
cells.Style.Font.Bold = true;
}
using (var cells = worksheets.Cells[1, 13, 1, totalColumn3.Length + 13]) //(1,1) => (1,10)
{
cells.Style.Font.Bold = true;
}
using (var cells = worksheets.Cells[1, 16, 1, totalColumn4.Length + 16]) //(1,1) => (1,10)
{
cells.Style.Font.Bold = true;
}
var records = data1.ToList();
var list = datas.ToList();
var funValue = funcitonData.ToList();
int totalRows = data1.Count + 1; //data including header row
int totalRowsList = datas.Count + 2;
var totalRowsForFuntion = funcitonData.Count + 1;
for (var i = 0; i < totalColumns.Length; i++)
{
worksheets.Cells[1, i + 1].Value = totalColumns[i]; //populate header row
}
for (var i = 0; i < totalColumn.Length; i++)
{
worksheets.Cells[1, i + 7].Value = totalColumn[i]; //populate header row
}
for (var i = 0; i < totalColumn3.Length; i++)
{
worksheets.Cells[1, i + 13].Value = totalColumn3[i]; //populate header row
}
//Add values
var j = 2; //to start data from second row after the header row.
var k = 2;
var l = 2;
if (data1 != null)
{
foreach (var item in records)
{
worksheets.Cells["A" + j].Value = cellNumb;
worksheets.Cells["B" + j].Value = item.TestCaseName;
worksheets.Cells["C" + j].Value = item.TestPlanName;
worksheets.Cells["D" + j].Value = item.Status;
j++;
cellNumb++;
}
using (ExcelRange Rng = worksheets.Cells[1, 1, totalRows, totalColumns.Length])
{
Rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
}
if (datas != null)
{
foreach (var item in datas)
{
worksheets.Cells["G" + k].Value = item.TestPlanNameForCount;
worksheets.Cells["H" + k].Value = item.TotalPassedCount;
worksheets.Cells["I" + k].Value = item.TotalFailedCount;
worksheets.Cells["J" + k].Value = item.TotalPendingCount;
worksheets.Cells["K" + k].Value = item.TotalBlockCount;
k++;
}
worksheets.Cells["G" + k].Value = "Total";
worksheets.Cells["H" + k].Value = datas.Sum(x => x.TotalPassedCount);
worksheets.Cells["I" + k].Value = datas.Sum(x => x.TotalFailedCount);
worksheets.Cells["J" + k].Value = datas.Sum(x => x.TotalPendingCount);
worksheets.Cells["K" + k].Value = datas.Sum(x => x.TotalBlockCount);
using (ExcelRange Rng = worksheets.Cells[1, 7, totalRowsList, totalColumn.Length + 6])
{
Rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
}
if (funcitonData != null)
{
foreach (var item in funValue)
{
worksheets.Cells["M" + l].Value = item.FunctionName;
worksheets.Cells["N" + l].Value = item.TotalCountTestCaseByTestRunId;
l++;
}
using (ExcelRange Rng = worksheets.Cells[1, 13, totalRowsForFuntion, totalColumn3.Length + 12])
{
Rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
}
worksheets.Cells.AutoFitColumns();
result = package.GetAsByteArray();
return result;
}
}
catch (Exception ex)
{
throw new Exception($"Download failed : {ex.Message}");
}
}
}
Solution
var redarChart = worksheet.Drawings.AddRadarChart("RadarChart",
eRadarChartType.RadarMarkers);
redarChart.SetPosition(42, 0, 0, 0);
redarChart.SetSize(700, 300);
redarChart.Title.Text = "Function Map";
redarChart.Title.Font.Bold = true;
redarChart.Title.Font.Size = 12;
var serie = redarChart.Series.Add(worksheets.Cells[2, 14, funValue.Count + 1, 14], worksheets.Cells[2, 13, funValue.Count + 1, 13]);
serie.HeaderAddress = new ExcelAddress("'sheet'!N1");
redarChart.StyleManager.SetChartStyle(ePresetChartStyleMultiSeries.RadarChartStyle4);
redarChart.Fill.Color = System.Drawing.Color.Black;
redarChart.Legend.Position = eLegendPosition.TopRight;
//If you want to apply custom styling do that after setting the chart style so its not overwritten.
redarChart.Legend.Effect.SetPresetShadow(ePresetExcelShadowType.OuterTopLeft);
var radarSeries = (ExcelRadarChartSerie)serie;
radarSeries.Marker.Size = 5;
- Sort program not working, not sure why
- Fast & accurate atan/arctan approximation algorithm
- What's the difference between strtok_r and strtok_s in C?
- How memory address for pointer to arrays is same as an element in 2D array?
- Which is the best way to suppress "unused variable" warning
- How to use ellipsis in c's case statement?
- How can I exclude non-numeric keys? CS50 Caesar Pset2
- Fast ceiling of an integer division in C / C++
- Is there an invalid pthread_t id?
- How to Implement Universal Setter/Getter Functions for Interrupt-Driven Variables in Embedded C?
- How does SIMD (avx) processing work? for example, if I want 10 32 bit floats how do i fit in a 256 bit avx vector?
- FDCAN problems on STM32G4
- How does the call macro enable mutual recursion between functions f and g in this Hanoi Tower implementation?
- Running test on Rocket core CPU - global variable initialized to 0 is unsuccessful, output wrong value instead
- Interacting with C arrays without knowing the size
- Combination of two strings
- Avoiding strcpy overflow destination warning
- carriage return by fgets
- How to use special characters in C?
- Why does 1.0/100.0 == 0.1/10.0 give True?
- Is it correct to compare pointers in C?
- Force free() to return malloc memory back to OS
- How can I print to standard error in C with 'printf'?
- What is the standard behavior of fread in C on Windows?
- How is strtok removing lines it shouldn't have access to?
- Using array as smart point in C
- Assigning string to malloced 2d char array not working as intended
- How to refactor repetition inside a Makefile?
- Why does an empty preprocessor command still evaluate to something?
- How to implement variable sized array within C struct