set colors of bars

[Miranda Johnson]

I have the following code which works great. However, I would like to be able to set different colors for Male, Female and Declined columns. Is that possible to do?

// create the excel version of the EEOC Report

public ActionResult EEOC_Excel(DateTime StartDate, DateTime EndDate, int JobID, int LocationID)

{

// Instantiate the spreadsheet creation engine.

using (ExcelEngine excelEngine = new ExcelEngine())

{

//Get the data

Report rpt = new Report();

rpt.StartDate = StartDate;

rpt.EndDate = EndDate;

rpt.JobID = JobID;

rpt.LocationID = LocationID;

//Date for 1st spreadsheet with applicant info

IList EEOC = rpt.EEOCReportData().ToList();

//Data for use by individual spreadsheets

List JWA = rpt.JobsWithApplicants();

//Get number of jobs with applicants so we know how many worksheets are needed

int numJobs = JWA.Count();

// Set the default application version as Excel 2016.

excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;

// Create a workbook with all needed worksheets.

IWorkbook workbook = excelEngine.Excel.Workbooks.Create(numJobs + 1);

// Create the worksheet

IWorksheet sheet = workbook.Worksheets[0];

// Display the EEOC data for all jobs and applicants on 1st worksheet

sheet.ImportData(EEOC,1,1,true);

//Now loop through each position that had applicants and display the info on seperate workseets for each position

int x = 1;

foreach(var j in JWA)

{

rpt.JobID = j.JobID;

List data = rpt.EEOCNumbers();

// Create a worksheet for each job

IWorksheet sheet1 = workbook.Worksheets[x];

// Display this on the first row

sheet1.Range["A1"].Text = j.JobTitle;

sheet1.Range["A1"].CellStyle.Font.Bold = true;

sheet1.Range["A1"].CellStyle.Font.Size = 18;

//Put in header row for data

sheet1.Range["A2"].Text = "Race";

sheet1.Range["A2"].CellStyle.Font.Bold = true;

sheet1.Range["B2"].Text = "Male";

sheet1.Range["B2"].CellStyle.Font.Bold = true;

sheet1.Range["C2"].Text = "Female";

sheet1.Range["C2"].CellStyle.Font.Bold = true;

sheet1.Range["D2"].Text = "Declined";

sheet1.Range["D2"].CellStyle.Font.Bold = true;

//Add the data to the worksheet

sheet1.ImportData(data,3,1,false);

//Create the chart for each job

IChartShape chart = sheet1.Charts.Add();

chart.ChartType = ExcelChartType.Column_Clustered;

// Input the data from above list into the chart

chart.DataRange = sheet1.Range["A2:D10"];

// Set ranges of chart

chart.PrimaryValueAxis.MinimumValue = 0;

// Display

chart.PrimaryValueAxis.Title = "Number of Applicants";

chart.ChartTitle = "";

chart.IsSeriesInRows = false;

// Put legend at bottom

chart.Legend.Position = ExcelLegendPosition.Bottom;

// This is the placement and size of the chart

chart.TopRow = 15;

chart.BottomRow = 45;

chart.LeftColumn = 1;

chart.RightColumn = 20;

// Show values in chart

foreach (IChartSerie serie in chart.Series)

{

serie.DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Blue; //Color of font on column label values

serie.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;

}

x++;

}

workbook.SaveAs("EEOC-Report_"+ StartDate.ToString("MM-dd-yyyy")+"_" + EndDate.ToString("MM-dd-yyyy") + ".xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog);

workbook.Close();

excelEngine.Dispose();

}

return View();

}

[Abirami Varadharajan]

Hi Miranda, 

Thank you for contacting Syncfusion support. 

Yes, you can set different colors to the series. Please refer below code to do the same. 

Code Example: 

serie.SerieFormat.Fill.ForeColor = Color.Yellow; 

Please refer below documentation to know more about formatting chart. 

Documentation Link: https://help.syncfusion.com/file-formats/xlsio/working-with-charts#series-appearance  

 

Regards, 

Abirami. 

[Miranda Johnson]

I had read the example provided   but it did not answer my question.  

That would work if it were  ExcelChartType.Column or ExcelChartType.Bar   but I am using ExcelChartType.Column_Clustered   

I have 3 columns for each race, one for Male one for Female and one for Declined to provide.   I wanted to know if it is possible to set a different color on each of the 3 columns like in the image.   Your example changes all 3 columns to be the same color.  

[Abirami Varadharajan]

Hi Miranda,

 

You can apply different colors for each and every series in the chart. Please refer below code snippet to set colors of the series.

 

Code Example:

            chart.Series[1].SerieFormat.Fill.ForeColor = Color.Blue;

            chart.Series[2].SerieFormat.Fill.ForeColor = Color.DeepPink;

            chart.Series[3].SerieFormat.Fill.ForeColor = Color.Green;

 

 

We have also prepared sample as per your requirement and the sample can be downloaded from the following link.

 

Sample Link:http://www.syncfusion.com/downloads/support/directtrac/general/ze/XlsIOSample698630152.zip

 

Kindly refer and let us know that your requirement is fulfilled.

 

Regards,

Abirami.

[Miranda Johnson]

Thank You!!    That was just what I needed.  

[Abirami Varadharajan]

Hi Miranda, 

Thank you for updating us. 

We are glad that your requirement is fulfilled. Please let us know if you need any further assistance. 

Regards, 

Abirami.