|
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?
|
|
|
|
|
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.
|
|
|
|
|
Yes of course, forgot that one  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?
|
|
|
|
|
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.
|
|
|
|
|
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;
}
|
|
|
|
|
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]
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.
|
|
|
|
|
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?
|
|
|
|
|
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. 
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.
|
|
|
|
|
|
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.
|
|
|
|
|
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]);
}
}
|
|
|
|
|
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!
Did you know:
That by counting the rings on a tree trunk, you can tell how many other trees it has slept with.
|
|
|
|
|
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))
{
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.
|
|
|
|
|
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:
private Rectangle MakeRect(Point p1, Point p2)
{
return MakeRect(p1.X, p2.X, p1.Y, p2.Y);
}
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.
|
|
|
|
|
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;
}
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
|
|
|
|
|
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.
|
|
|
|
|
The little article[^] I provided a link to earlier still holds all that information.
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).
|
|
|
|
|
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.
|
|
|
|
|
the essence is in the 5-step theory; the zooming image is an example of it.
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).
|
|
|
|
|
I have a block of code to respond to text entry in textboxes; this exact code is working in one program, but not another. I've just noticed that in the new program, if I click on the first textbox, the cursor correctly moves there, but I can't type anything. If I then tab to the next textbox it works properly. If I tab through the elements on the page until I return to the first textbox, it then accepts characters as I type them. I think there's a problem in the tab index settings for the page elements, but I can't figure out why.
For all of the items which don't interact with the user I've set the tab index to 0. Those that accept user input are sequentially numbered from 1 to N. I expect that clicking on a textbox should give that item focus and allow it to operate correctly, but that is not what I'm seeing.
Can someone shed some light on this for me?
[EDIT]
Thanks for the helpful info! Oddly, I never found the reason for this peculiar behavior, so I shut it down and walked away for a day. When I fired it up this morning, all was working as it should. I should try this strategy at work sometime! 
[/EDIT]
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
modified on Tuesday, June 1, 2010 11:15 AM
|
|
|
|
|
I do not have a solution to your problem but I would like to point out a couple of things that, from your question, it seems that you might have misunderstood.
Controls that are capable of interaction with the user have a TabStop property in addition to TabIndex and if you want to disallow tabbing into them, then setting their TabStop property to false is the way to go since as far as TabIndex is concerned; C# uses zero based indexing. Therefore setting TabIndex to zero for any control would mean that it would be first in the tab order.
For controls with no user interaction (Labels for example) it does not matter what you do with TabIndex since it is ignored and they do not have a TabStop property, at least not visibly (it is hidden and set to false I believe).
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.
|
|
|
|
|
Thanks, Henry - that clears up a lot of misconceptions! I'll go fix it and see what happens...
[EDIT]
By the way, labels and group boxes don't have the Tab Stop property - I just checked. Still, I learned a bunch from your answer. Two of my radiobuttons had Tab Stop set to false; I don't know why, since I didn't set them that way, but I changed them to true. The problematic textboxes all have that property set to true, so the trouble must lie elsewhere. It's 2:30AM, so this is probably a good time to shut down VS2008 and see if things behave better tomorrow. That does sometimes happen; things that didn't work suddenly start working after a shutdown and restart. I have no idea why...
[/EDIT]
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
modified on Monday, May 31, 2010 5:26 AM
|
|
|
|
|
Henry Minute wrote: For controls with no user interaction (Labels for example) it does not matter what you do with TabIndex since it is ignored
Not quite: If there is a shortcut key associated with the label, then it will activate the next control in the tab order. E.g. label1.TabIndex = 5, textBox1.TabIndex = 6, with label1.Text = "&Hello". ALT+H will move the input to textBox1.
Did you know:
That by counting the rings on a tree trunk, you can tell how many other trees it has slept with.
|
|
|
|
|
True. I had forgotten that since I rarely use that functionality.
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.
|
|
|
|
|
Interesting. So early and I already learned my new thing of the day. Thanks.
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).
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
