Re: Help ploting Excel graph in C# - C# Discussion Boards

30-May-10 23:17

I have the current data in an excel sheet:

<br />
---A---B---C--<br />
1| 0 | 5 | 1 |<br />
2| 2 | 4 | 2 |<br />
3| 4 | 3 | 3 |<br />
4| 6 | 2 | 4 |<br />
5| 8 | 1 | 5 |<br />
<br />

The values for column A and B are the Y-axis values for two lines in the graph. The C column is the X-axis values which both lines shares.

I would like to create a graph containing two lines based on the data above. I don't understand how to do that. The guides on internet are to general and don't deal with this case.

I have come with this method to plot the graph in the active worksheet.
The "workSheet" object is the active work sheet in my C# application.

public void TestAddChart(XlChartType type, System.Drawing.Point location, Size size)
{
      ChartObjects chartObjects = (ChartObjects)workSheet.ChartObjects(Type.Missing);
      ChartObject chartObject = chartObjects.Add(location.X, location.Y, size.Width, size.Height);
      Chart chart = chartObject.Chart;

      Range line1Yvalues = workSheet.get_Range("A1", "A5");
      Range line2Yvalues = workSheet.get_Range("B1", "B5");
      Range commonXvalues = workSheet.get_Range("C1", "C5");

      chart.SetSourceData(line1Yvalues, XlRowCol.xlRows);
      chart.SetSourceData(line2Yvalues, XlRowCol.xlRows);
      chart.SetSourceData(commonXvalues, XlRowCol.xlRows);
}

I call the method with this code:

classInstance.TestAddChart(XlChartType.xlLine, new Point(100, 100), new Size(300, 300));

When I shut down my application and look in the excel file, I find the result of the graph to be something else. There is instead a bar chart containing five series.

What shall I change to make this work?

OriginalGriff30-May-10 23:29

30-May-10 23:29

Perhaps the place to start would be using the "type" parameter somewhere in your method?

Did you know:
That by counting the rings on a tree trunk, you can tell how many other trees it has slept with.

Mc_Topaz30-May-10 23:51

30-May-10 23:51

Yes of course, forgot that one D'Oh! | :doh:

Here is the correed version of the method:

public void TestAddChart(XlChartType type, System.Drawing.Point location, Size size)
{
    ChartObjects chartObjects = (ChartObjects)workSheet.ChartObjects(Type.Missing);
    ChartObject chartObject = chartObjects.Add(location.X, location.Y, size.Width, size.Height);
    Chart chart = chartObject.Chart;

    Range line1Yvalues = workSheet.get_Range("A1", "A5");
    Range line2Yvalues = workSheet.get_Range("B1", "B5");
    Range commonXvalues = workSheet.get_Range("C1", "C5");

    chart.SetSourceData(line1Yvalues, XlRowCol.xlRows);
    chart.SetSourceData(line2Yvalues, XlRowCol.xlRows);
    chart.SetSourceData(commonXvalues, XlRowCol.xlRows);
    chart.ChartType = type;
}

But the result is still not what I wanted. Here is the new result:

1) No lines in the graph is visible
2) There are five series, it should only be two
3) The Y-axis value is from 0 to 6, it should at least be 0 to 8.
4) The x-axis value is just 1, it should be 1 to 5.

I guess Excel cannot figure out that Line1 and Line2 should share same X-axis values. Or I lack those instructions.

What to do now?

Henry Minute31-May-10 0:37

Henry Minute

31-May-10 0:37

I have never used c# to access/modify Excel and so do not have a definitive answer to your problem.

However, in this part of your code

chart.SetSourceData(line1Yvalues, XlRowCol.xlRows);
chart.SetSourceData(line2Yvalues, XlRowCol.xlRows);
chart.SetSourceData(commonXvalues, XlRowCol.xlRows);
chart.ChartType = type;

I notice that all three data sources are set to XlRowCol.xlRows, which seems odd to me. Shouldn't, at least, one of them be set to XlRowCol.xlColumns?

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
Why do programmers often confuse Halloween and Christmas?
Because 31 Oct = 25 Dec.

Mc_Topaz31-May-10 1:10

31-May-10 1:10

Oh yes, you maybe right. I thought the XlRowCol specified how excel should read the data in the sheet. Guess I was wrong.
Anyway I changed commonXvalues's XlRowCol-value from "XlRowCol.xlRows" to "XlRowCol.xlColumns" in the SetSourceData() method call.

The final result is almost what I want. Here is the major issue:

Only one line is displayed. It seems that the line's data is taken from the C column only. That's not what I wanted.

It seems hard to plot two line with this configuration:
* Line 1 Y-axis data from line1Yvalues range and X-axis data from commonXvalues range.
* Line 2 Y-axis data from line2Yvalues range and X-axis data from commonXvalues range.

Here is yet again the corrected version of the method:

public void TestAddChart(XlChartType type, System.Drawing.Point location, Size size)
{
     ChartObjects chartObjects = (ChartObjects)workSheet.ChartObjects(Type.Missing);
     ChartObject chartObject = chartObjects.Add(location.X, location.Y, size.Width, size.Height);
     Chart chart = chartObject.Chart;

     Range line1Yvalues = workSheet.get_Range("A1", "A5");
     Range line2Yvalues = workSheet.get_Range("B1", "B5");
     Range commonXvalues = workSheet.get_Range("C1", "C5");

     chart.SetSourceData(line1Yvalues, XlRowCol.xlRows);
     chart.SetSourceData(line2Yvalues, XlRowCol.xlRows);
     chart.SetSourceData(commonXvalues, XlRowCol.xlColumns);
     chart.ChartType = type;
}

Henry Minute31-May-10 1:23

Henry Minute

31-May-10 1:23

I stress again that I have never done this myself, so anything I say is pure guesswork.

If the line you get is definitely from column 'C' and that is the only one set to xlColumns then if it was me I would experiment by changing each of the SetSourceData lines....

first line xlColumns other two xlRows, first two lines xlColumns..... etc. until you have a firm grasp of how doing so affects the output.

[Edit]
It has just occurred to me that your third SetSourceData line should not be there at all. After all commonXvalues contains values that should be displayed on the X axis, not data points. Maybe there is another method of chart to set these values chart.XLegend or something.
[/Edit]

Mc_Topaz31-May-10 2:29

31-May-10 2:29

I have been trying some test, switching the XlRowCol value for both line1Yvalues and line2Yvalues in the SetSourceData() and removing one of SetSourceData() method call, this is my conclusion:

1) The XlRowCol should be set to xlColumns (don't ask me why)
2) If I only have one SetSourceData() method call, the graph turns out to be just what I want for one line. But, I have not specified what the line's X-axis data is taken from. That is automatically filled in by Excel and I don't want that.

This mean that this solution don't work for multiple lines. Is there any other solution?

darw rectangle modification.... [modified]

Henry Minute31-May-10 2:51

Henry Minute

31-May-10 2:51

Take a look at Simple Column Chart Generator[^] to see if it gives any clues. I realize that it is a Column Chart but I suspect that changing it to a Line Chart will be the least of your problems.

BTW: I found the link by Googling on chart.setdatasource, so if the link above is not useful give google a go. Smile | :)

Nivas8230-May-10 22:42

30-May-10 22:42

Hi,

I have drawn the rectangle using picture box paint(refer : http://www.codeproject.com/Messages/3481256/text-alignment-problem-on-the-image.aspx[^]now I like to edit the drawn rectange (Like to increase or decrease the length of the drawn rectangle). how cab i do this.

modified on Monday, May 31, 2010 4:52 AM

OriginalGriff30-May-10 23:00

30-May-10 23:00

Firstly, your link takes you here - to the first page in C# questions, rather than to your earlier message. Out of politeness, fix that (use Edit on your original post).

Secondly, the link takes you to a whole mess of code, and several answers from Luc - over a week ago. As a result, the code you are talking about is unlikely to be the same as that you posted. Post an abstract of your code, showing only the painting-a-rectangle bit, formatted with <pre>...</pre> tags, and we will see if we can work out what your problem is!

Did you know:
That by counting the rings on a tree trunk, you can tell how many other trees it has slept with.

Nivas8231-May-10 18:53

31-May-10 18:53

ok. I will post my rectangle code here...

In my previous post I have solved my problem by making picture box size or bitmap size to image size (Image I load).

now i like to draw multiple rectangle on bitmap. how can i draw. I tried using array in rectangle. but i get error as "Object reference not set to an instance of an object." (Null reference Exception was unhandled).

private void pictureBox1_MouseDown(object sender, MouseEventArgs e)
        {
            if (mybitmap == null)
            {
                mybitmap = new Bitmap(sz.Width, sz.Height);
                
            }
            rect[count] = new Rectangle(e.X, e.Y, 0, 0);
            this.Invalidate();


if (stayToolStripMenuItem.Checked == true)
           {
               switch (e.Button)
               {
                   case MouseButtons.Left:
                       {
                           rect[count] = new Rectangle(rect[count].Left, rect[count].Top, e.X - rect[count].Left, e.Y - rect[count].Top);
                           pictureBox1.Invalidate();
                            break;
                       }

                       count++;
               }
           }

private void pictureBox1_Paint(object sender, PaintEventArgs e)
        {
if (mybitmap == null)
                {
                    return;
                }


                using (g = Graphics.FromImage(mybitmap))
                {

                    using (Pen pen = new Pen(Color.Red, 2))
                    {
                        g.Clear(Color.Transparent);


                        e.Graphics.DrawRectangle(pen, rect[count]);
                        label1.Top = rect[count].Top; label1.Left = rect[count].Left; label1.Width = rect[count].Width;
                        label1.Height = rect[count].Height;


if (label1.TextAlign == ContentAlignment.TopLeft)
                        {
                            e.Graphics.DrawString(label1.Text, label1.Font, new SolidBrush(label1.ForeColor), rect[count]);
                            g.DrawString(label1.Text, label1.Font, new SolidBrush(label1.ForeColor), rect[count]);
                            g.DrawRectangle(pen, rect[count]);
                        
                    }
}

OriginalGriff31-May-10 22:13

31-May-10 22:13

Ok, loads of things. Lets start with the simple stuff. Ignoring your drawing for the moment, your rectangle array has probably got problems. I can't see where you have defined it, or count, but I see nowhere where you change the value of count, so you would be over-writing the same rectangle. Unless you know in advance how many times teh use is going to click on your picture box, don't use an array - they are a fixed size. Use a List<Rectangle> instead:

List<Rectangle> rectangles = new List<Rectangle>();
private void pictureBox1_MouseDown(object sender, MouseEventArgs e)
   {
   rectangles.Add(new Rectangle(0, 0, e.X, e.Y));
   }

This adds a new rectangle to your list every time the user clicks the picture box. Note that I have changed the parameters for the rectangle to give it a width and height, which yours lacked!
Changing to a List will almost certainly get rid of your "Object reference not set..." error.

The next bit involves your drawing: You draw in the PictureBox.Paint event, but this may be a problem all on it's own. Your bitmap is persistent: changes you make to it are not lost. The paint event is there because the screen is not persistent - whenever it is invalidated, it must be re-drawn. When you draw on your bitmap in the Paint event, you are drawing again, and again, and again on the same bitmap that you drew on last time! Am I also right in thinking that your PictureBox control also displays the bitmap? If so, why are you handlign Paint yourself? If not, why are you using a PictureBox?

Instead, draw onto your bitmap when you add the rectangle to the list. If you need to handle the Paint event, either draw each rectangle onto the graphics context provided (e.Graphics) using a foreach loop to process each Rectangle in the list, or draw the bitmap to the graphics context provided.

Well done on putting using blocks round your Graphics and Pen items though! Thumbs Up | :thumbsup:

Did you know:
That by counting the rings on a tree trunk, you can tell how many other trees it has slept with.

Nivas821-Jun-10 22:17

1-Jun-10 22:17

Have change the code little bit. Instead of mouse move event used Mouse click event.

On using mouse move event it calls every time when mouse move on the picture box. so list rectangle count gets increased. for this i used mouse click event.

When list rectangle added in Mouse down event, it's only get the values for height and width of the rectangle (if use (0,0,e.X,e.Y))and also rectangle always starts from top left corner (not able to start the rectangle point where user likes) and it's gets only X and Y values (if use(e.X, e.Y,0,0))

to over come this I used the list rectangle in the mouse click event, so I get all values.

List<Rectangle> rectangles = new List<Rectangle>();
private void pictureBox1_MouseDown(object sender, MouseEventArgs e)
        {
            if (stayToolStripMenuItem.Checked == true)
            {

                if (mybitmap == null)
                {
                    mybitmap = new Bitmap(sz.Width, sz.Height);

                }
                rect = new Rectangle(e.X, e.Y, 0, 0);
                this.Invalidate();

            }
             
        }

       
        private void pictureBox1_Paint(object sender, PaintEventArgs e)
        {
            if (stayToolStripMenuItem.Checked == true)
            {
                button1.Visible = true;
                button2.Visible = true;

                if (mybitmap == null)
                {
                    return;
                }

                
                    using (g = Graphics.FromImage(mybitmap))
                    {

                        using (Pen pen = new Pen(Color.Red, 2))
                        {
                            //g.Clear(Color.Transparent);


                            e.Graphics.DrawRectangle(pen, rect);
                            label1.Top = rect.Top; label1.Left = rect.Left; label1.Width = rect.Width;
                            label1.Height = rect.Height;


                            if (label1.TextAlign == ContentAlignment.TopLeft)
                            {
                                e.Graphics.DrawString(label1.Text, label1.Font, new SolidBrush(label1.ForeColor), rect);
                                g.DrawString(label1.Text, label1.Font, new SolidBrush(label1.ForeColor), rect);
                                g.DrawRectangle(pen, rect);

                            }

                        }

                    }

private void pictureBox1_MouseClick(object sender, MouseEventArgs e)
        {
            
                if (e.Button == MouseButtons.Left)
                
                {
                    rect = new Rectangle(rect.Left, rect.Top, e.X - rect.Left, e.Y - rect.Top);
                    rectangles.Add(rect);
                    pictureBox1.Invalidate();
                    f = 0;

                }

           
        }

thanks Griff for advising to use list rectangles.

What ever rectangle I draw get saved in the list rectangles and get saved when i saved the work (could see all the drawn rectangles when i opened the saved file).

Now the problem is, when I draw a new rectangle the previous one get disappeared (on run time. However this added in list rectangle).

How to display all the rectangles drawn on run time, so user can know how many rectangles drawn and where.

OriginalGriff1-Jun-10 22:59

Re: darw rectangle modification.... [modified]

1-Jun-10 22:59

Well done - it looks better already!

I still wouldn't draw onto your bitmap in the Paint event - don't forget the bitmap is persistent - all changes you make are kept (unlike painting on the provided graphics context in the Paint event). For the moment, just comment it out and ignore it.

You are handling the new rectangle creation a little oddly - MouseDown and MouseClick are not the events I would pick! The normal way to do it is:
MouseDown: Save the current location - you do this - but in a Point rather than a rectangle. Set a Bool to true to show you are creating a new rectangle.
MouseUp: If creating new rectangle, make new rectangle using saved point, and cancel creating mode. Add the new rectangle to the list.
You can also add MouseMove: If creating, save the current position as a new, temporary rectangle. Invalidate.
Then in Paint: if creating, paint an "elastic band" for the temporary rectangle.

I have given some sample code for this below (it is not compiled, so I can't say if it works!)

private void StoreLayoutPanel_MouseDown(object sender, MouseEventArgs e)
    {
    mouseDown = true;
    mouseDownAt = new Point(e.X, e.Y);
    Invalidate();
    }

private void StoreLayoutPanel_MouseUp(object sender, MouseEventArgs e)
    {
    if (mouseDown)
       {
       Rectangle rect = MakeRect(mouseDownAt.X, mouseDownAt.Y, e.X, e.Y);
       rectangles.Add(rect);
       Invalidate();
       }
    mouseDown = false;
    }

private void StoreLayoutPanel_MouseMove(object sender, MouseEventArgs e)
    {
    if (mouseDown)
        {
        mouseRect = MakeRect(mouseDownAt.X, e.X, mouseDownAt.Y, e.Y);
        Invalidate();
        }
    }

private void StoreLayoutPanel_Paint(object sender, PaintEventArgs e)
    {
    Pen pen = new Pen(Color.Black);
    foreach (Rectangle r in rectangles)
        {
        e.Graphics.DrawRectangle(pen, r);
        }
    if (mouseDown)
        {
        Pen p = new Pen(Color.Gray);
        p.DashStyle = System.Drawing.Drawing2D.DashStyle.Dash;
        e.Graphics.DrawRectangle(p, mouseRect);
        }
    }

Here are a couple of methods you may find usefull:

/// <summary>
/// Make a rectangle from two points
/// The rectangle will use the smallest X and the smallest Y as the TLHC, and
/// the largest X and the largest Y as the BRHC
/// </summary>
/// <param name="p1"></param>
/// <param name="p2"></param>
/// <returns></returns>
private Rectangle MakeRect(Point p1, Point p2)
    {
    return MakeRect(p1.X, p2.X, p1.Y, p2.Y);
    }
/// <summary>
/// Make a rectangle from two points
/// The rectangle will use the smallest X and the smallest Y as the TLHC, and
/// the largest X and the largest Y as the BRHC
/// </summary>
/// <param name="X1"></param>
/// <param name="X2"></param>
/// <param name="Y1"></param>
/// <param name="Y2"></param>
/// <returns></returns>
private Rectangle MakeRect(int X1, int X2, int Y1, int Y2)
    {
    int XL = Math.Min(X1, X2);
    int XR = Math.Max(X1, X2);
    int YL = Math.Min(Y1, Y2);
    int YR = Math.Max(Y1, Y2);
    return new Rectangle(XL, YL, XR - XL, YR - YL);
    }
#endregion

Did you know:
That by counting the rings on a tree trunk, you can tell how many other trees it has slept with.

Nivas822-Jun-10 19:18

2-Jun-10 19:18

Thanks Giff. Ur ideas fulfill my requirements. I have added foreach statement in my paint and its works.

Final and full code

        Image image;
        Bitmap mybitmap;
        Rectangle rect;
        List<Rectangle> rectangles = new List<Rectangle>();
        Graphics g;
private void openToolStripMenuItem_Click(object sender, EventArgs e)
        {
            
            OpenFD.FileName = "";
            OpenFD.Title = "open image";
            OpenFD.InitialDirectory = "C";
            OpenFD.Filter = "JPEG|*.jpg|Bmp|*.bmp|All Files|*.*.*";
            if (OpenFD.ShowDialog() == DialogResult.OK)
            {
string file = "";
                file = OpenFD.FileName;
                 image = Image.FromFile(file);
                 pictureBox1.Image = image;
                 sz = image.Size;
                 pictureBox1.Size = sz;
                 pictureBox1.SizeMode = PictureBoxSizeMode.Zoom;//this not needed if we give original image size to picturebox
                
            }

private void pictureBox1_MouseDown(object sender, MouseEventArgs e)
        {
            if (stayToolStripMenuItem.Checked == true)
            {

                if (mybitmap == null)
                {
                    mybitmap = new Bitmap(sz.Width, sz.Height);

                }
                rect = new Rectangle(e.X, e.Y, 0, 0);
                this.Invalidate();

            }
             
        }
private void pictureBox1_MouseClick(object sender, MouseEventArgs e)
        {
            
                if (e.Button == MouseButtons.Left)
                
                {
                    rect = new Rectangle(rect.Left, rect.Top, e.X - rect.Left, e.Y - rect.Top);
                    rectangles.Add(rect);
                    pictureBox1.Invalidate();
                   
                }
                
            }
        }


private void pictureBox1_Paint(object sender, PaintEventArgs e)
        {
            
              
                if (mybitmap == null)
                {
                    return;
                }

                
                    using (g = Graphics.FromImage(mybitmap))
                    {
                        Pen pen = new Pen(Color.Black);
                        foreach (Rectangle r in rectangles)
                        {
                            e.Graphics.DrawRectangle(pen, r);
                            label1.Top = rect.Top; label1.Left = rect.Left; label1.Width = rect.Width;
                            label1.Height = rect.Height;
                            e.Graphics.DrawString(label1.Text, label1.Font, new SolidBrush(label1.ForeColor), rect);
                            g.DrawString(label1.Text, label1.Font, new SolidBrush(label1.ForeColor), rect);
                            g.DrawRectangle(pen, r);
                            
                        }
                        
            }
            

                 
        }


private void saveToolStripMenuItem_Click(object sender, EventArgs e)
        {
                        
            SaveFileDialog SaveFD1 = new SaveFileDialog();
            SaveFD1.FileName = "";
            SaveFD1.InitialDirectory = "C";
            SaveFD1.Title = "save file Name";
            SaveFD1.Filter = "JPG|*.jpg|Bmp|*.bmp";


            if (mybitmap != null)
            {

                if (SaveFD1.ShowDialog() == DialogResult.OK)
                {
                    if (SaveFD1.FileName == "")
                        return;
                    else
                    {

                        System.IO.Stream filename = (System.IO.FileStream)SaveFD1.OpenFile();
                            using (g = Graphics.FromImage(pictureBox1.Image))
                            {
                                foreach (Rectangle r1 in rectangles)
                        {
                            g.DrawImage(mybitmap,0, 0);
                        }

                            }
      
                         pictureBox1.Image.Save(filename, ImageFormat.Jpeg);
                           

                        }
}
}
}

private void textBox1_TextChanged(object sender, EventArgs e)
        {
            label1.Text = textBox1.Text;
         
        }

private void button1_Click(object sender, EventArgs e)
        {
            fontDialog1.ShowDialog();
            label1.Font = fontDialog1.Font;
            Invalidate();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            colorDialog1.ShowDialog();
            label1.ForeColor = colorDialog1.Color;
            Invalidate();
        }

This code will draw rectangles with the text given in the textbox at the position where the user likes.

Thanks once again Giff......and i worked with code given by u and it also working fine and for time being i need to stick with my code. Don't mistake me.

modified on Thursday, June 3, 2010 3:03 AM

Nivas827-Jun-10 21:39

7-Jun-10 21:39

About bitmap "persistent", I have added the (g.draw)after added the rectangle in the foreach loop (removed (g.draw) from Paint event). Thanks for ur suggestion on this regards.

Luc Pattyn31-May-10 1:57

Luc Pattyn

31-May-10 1:57

The little article[^] I provided a link to earlier still holds all that information.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

Nivas8231-May-10 19:02

31-May-10 19:02

Hi Luc,

have saw ur animation and it look like it is increasing the size of the Image that loaded (Zoom image functions). but here I need to edit rectangle on run time. please guide me.