CODEWITHSUNDEEP
c#colorsinterop
Aaveg Barole
Aaveg Barole

Reputation: 13

Please suggest how to set userdefined colors to bar

How to set the user-defined color for series, not able to figure it out, Please help

private void button1_Click(object sender, EventArgs e) {

    Microsoft.Office.Interop.Excel.Application excelWrite = new Microsoft.Office.Interop.Excel.Application();
    // Create empty workbook
    excelWrite.Workbooks.Add();
    // Create Worksheet from active sheet
    Microsoft.Office.Interop.Excel._Worksheet workSheet = excelWrite.ActiveSheet;
    workSheet.Name = "Result";

    workSheet.Cells[1, "A"] = "Color";
    workSheet.Cells[2, "A"] = "R";
    workSheet.Cells[3, "A"] = "G";
    workSheet.Cells[4, "A"] = "B";

    workSheet.Cells[1, "B"] = "Brown";
    workSheet.Cells[1, "C"] = "Pink";
    workSheet.Cells[1, "D"] = "Silver";


    workSheet.Cells[2, "B"] = 40;
    workSheet.Cells[3, "B"] = 26;
    workSheet.Cells[4, "B"] = 13;

    workSheet.Cells[2, "C"] = 100;
    workSheet.Cells[3, "C"] = 75;
    workSheet.Cells[4, "C"] = 80;

    workSheet.Cells[2, "D"] = 75;
    workSheet.Cells[3, "D"] = 75;
    workSheet.Cells[4, "D"] = 75;

    Microsoft.Office.Interop.Excel.Range xlRange = workSheet.UsedRange;


    int rowCount = xlRange.Rows.Count;
    int colCount = xlRange.Columns.Count;

    Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)workSheet.ChartObjects(Type.Missing);

    Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(150, 150, 800, 350);
    Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;


    chartPage.SetSourceData(xlRange, Type.Missing);
    chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlBarStacked100;
    chartPage.AutoScaling = true;

    Microsoft.Office.Interop.Excel.ChartClass cc = myChart.


    excelWrite.DisplayAlerts = false;
    workSheet.SaveAs(@"C:\Users\apex\Desktop\" + "Result_ForChart_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");

    ClearAllEndAll(excelWrite, workSheet);
    Defeat();

}

Is it possible to do with interop, if not Please suggest other nuget/lib to be used

Upvotes: 1

Views: 61

Answers (1)

mr.coffee
mr.coffee

Reputation: 1028

Have you tried EPPlus? It's an OOXML wrapper that allows you to create Excel spreadsheets with ease. It is available via NuGet or on github (https://github.com/JanKallman/EPPlus).

Edit - Here is a code sample:

using (FileStream stream = new FileStream("test.xlsx", FileMode.Create))
using (ExcelPackage package = new ExcelPackage())
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("My Data");
    Color bgcolor = ColorTranslator.FromHtml("#00FFFF");
    worksheet.Cells["A1:B1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
    worksheet.Cells["A1:B1"].Style.Fill.BackgroundColor.SetColor(bgcolor);
    worksheet.Cells["A1"].Value = "Hello";
    worksheet.Cells["B1"].Value = "World!";
    package.SaveAs(stream);
}

Upvotes: 0

Related Questions