Click here to Skip to main content
15,560,045 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I am creating a bar chart with two columns and an x-axis that I want to set from a column of dates to the x-axis labels. I can not see a way to do it, although, in Office 2010, I was able to.

What I have tried:

Tried to set series3 to it.
Checked in format chart axis/chart options.
Checked in format chart axis/text options.
Posted
Updated 16-Jul-22 9:35am
Comments
Graeme_Grant 13-Jul-22 20:12pm    
What is you code?
PaulaJoannAllen 13-Jul-22 21:11pm    
I am just creating the sheet, and the code will follow. I need to get the bar chart set up so I can use it.
Graeme_Grant 14-Jul-22 0:54am    
I'm learning C# Interop Excel as I answer these questions... It is interesting how it works...

I've expanded the ExcelHelper class from my last answer[^] to help with this answer:

C#
public class ExcelHelper
{
    public ExcelHelper(string path)
    {
        _path = path;
        _workbook = _excel.Workbooks.Open(path);

        _excel.DisplayAlerts = false;
    }

    private readonly string _path;

    private readonly _Application _excel = new _Excel.Application();

    private readonly Workbook _workbook;

    public IEnumerable<Worksheet> Worksheets()
    {
        foreach (Worksheet worksheet
                           in _workbook.Worksheets
                                       .Cast<Worksheet>())
            yield return worksheet;
    }

    public IEnumerable<Chart> GetCharts(Worksheet worksheet)
    {
        foreach (var chartObject
                     in worksheet.ChartObjects())
            yield return chartObject.Chart;
    }

    public Worksheet Worksheet(string name)
    {
        return (Worksheet)_workbook.Worksheets[name];
    }

    public ChartObject ChartObject(Worksheet worksheet, string ChartName)
    {
        return (ChartObject)worksheet.ChartObjects(ChartName);
    }

    public void SaveChanges()
    {
        _workbook.SaveAs(Filename: _path,
                         AccessMode: XlSaveAsAccessMode.xlNoChange);
    }

    public void Close(bool Save = false)
    {
        if (save)
            SaveChanges();

       // no longer required
        _workbook.Close();
    }
}

Here I have added methods Worksheet, ChartObject, and SaveChanges to help simplify common tasks.

Now to work with the chart object to set the data source:

C#
string resourcePath = Path.Combine(Environment.CurrentDirectory, "Workbooks");
string path = Path.Combine(resourcePath, "Book1.xlsx");

ExcelHelper excelHelper = new ExcelHelper(path);

Worksheet worksheet = excelHelper.Worksheet("MySheet");
ChartObject chartObject = excelHelper.ChartObject(worksheet, "MyChart");
Chart myChart = chartObject.Chart;

// Data is already on the same worksheet as the chart
_Excel.Range ChartSourceRange = worksheet.Range["B22", "E25"];

// Set the Chart data
myChart.SetSourceData(ChartSourceRange);

// Set the Chart Type
myChart.ChartType = XlChartType.xlColumnClustered;

// lets save an image of the Chart to file
//   so we can check what it looks like
myChart.Export(Path.Combine(resourcePath, "MyChart.bmp"), "BMP");

// We're done, so save changes
excelHelper.SaveChanges();

// Close the workbook
excelHelper.Close();

Tested and works. Enjoy...

PS: I should point out that you are working with COM objects, so you will need to release them. I would use an IDisposable interface on the ExcelHelper, but I will leave that up to you. You can read more here: Working with COM in .Net requires to release com objects - Stack Overflow[^]
 
Share this answer
 
v3
I made a new excel file with the chart in it, and I was now able to set the names on the x-axis.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900