Externally create an excel graph from a CSV file

Question

I have a bunch of data that I am saving out into .csv (Comma Seperated Value) format. In excel, I can easily create a bar graph of this by manually selecting the data and hitting Graph.

However, I want to do this externally with a click of a button.

Ideally, I hit a button in C#. This calls a method which generates my .csv file, and then calls an excel function that creates the excel sheet, with the graph created in it.

Not sure how to go about this, or if excel even supports something like this, much less from a csv file!

Thank you.

Answer 1

Further to my comment, here is an example. There is no need to use 3rd Party Libraries. Excel has all the things required to make a chart. Also opening csv is not like opening any workbook in Excel.

For a better understanding, let's create a sample.csv and place it in C: which looks like this

Create a new form in C#, place a button and set a reference to the Excel Object Library. Next paste this code in the click event of the button

TRIED AND TESTED

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

Namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        Public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            Microsoft.Office.Interop.Excel.Application xlexcel;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

            object misValue = System.Reflection.Missing.Value;
            xlexcel = new Excel.Application();

            var xlWorkBooks = xlexcel.Workbooks;

            xlexcel.Visible = true;


            xlWorkBooks.OpenText("C:\\Sample.csv", misValue, misValue, Excel.XlTextParsingType.xlDelimited,
                                 Excel.XlTextQualifier.xlTextQualifierNone, misValue, misValue,
                                 misValue, misValue, misValue, misValue, misValue, misValue, misValue,
                                 misValue, misValue, misValue, misValue);

            // Set Sheet 1 as the sheet you want to work with
            xlWorkSheet = (Excel.Worksheet)xlWorkBooks[1].Worksheets.get_Item(1);

            xlWorkSheet.Shapes.AddChart(misValue,misValue,misValue,misValue,misValue).Select();

            //~~> Make it a Line Chart
            xlexcel.ActiveChart.ApplyCustomType(Excel.XlChartType.xlLineMarkers);

            //~~> Set the data range
            xlexcel.ActiveChart.SetSourceData(xlWorkSheet.Range["$A$1:$B$6"]);

            //uncomment this if required
            //xlWorkBooks[1].Close(true, misValue, misValue);
            //xlexcel.Quit();

            //releaseObject(xlWorkSheet);
            //releaseObject(xlWorkBook);
            //releaseObject(xlexcel);
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

OUTPUT

Answer 2

I'd try with EPPlus library for creating your excel files directly.

It supports charts and has generally worked great for my projects in the past. The easiest way may be to prepare a "template" file in Excel with blank data (just normal a xlsx file) and insert desired charts and any other required elements. Then you can just open the template file with that library in C#, fill datasheet with data, and save it as another xlsx file with actual data.

Maybe some flag for "recalculation" of data needs to be set, which occurs when the file is opened. Don't know exactly for that library, but it was required for another one I used in the past for xls files.

(I suppose the you already have you data in your application, if not, check those answers for parsing CSV: CSV parser/reader for C#?, CSV parser/reader for C#?)

Answer 3

If you insist in doing it in Excel, you will need to have some kind of library to connect to it. You can either use Office Tools included in VS Pro and better, or use some Open Source library like NetOffice. Then you can write code like this:

Excel.Application app = new Excel.Application();
Excel.Workbook wb = app.Workbooks.Open(<path to csv>);
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
Excel.Chart chart = ((Excel.ChartObjects)ws.ChartObjects()).Add(0, 0, 100, 100).Chart; //the numbers are position and dimensions of the chart
chart.ChartWizard(); // here you have to format your chart, see link below
wb.SaveAs(<output path and format>);

Link to MSDN documentation.

But I think using something like Gnuplot is probably much more straightforward.