I've expanded the
ExcelHelper
class from my
last answer[
^] to help with this answer:
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();
_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:
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;
_Excel.Range ChartSourceRange = worksheet.Range["B22", "E25"];
myChart.SetSourceData(ChartSourceRange);
myChart.ChartType = XlChartType.xlColumnClustered;
myChart.Export(Path.Combine(resourcePath, "MyChart.bmp"), "BMP");
excelHelper.SaveChanges();
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[
^]