LinuxQuestions.org
Visit the LQ Articles and Editorials section
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 02-28-2006, 08:31 AM   #1
mrobertson
Member
 
Registered: May 2005
Posts: 275

Rep: Reputation: 30
Problem Sending a dataset to excel in c#


HEre is the Sub that I am using:

Code:
private void btnExcel_Click(object sender, System.EventArgs e)
		{
			Excel.ApplicationClass excel = new ApplicationClass();

			excel.Application.Workbooks.Add(true);
			//System.Data.DataTable table = new System.Data.DataTable("Reported Coil Data");
			//System.Data.DataTable table = System.Data.DataTable("Reported Coil Data");
			int ColumnIndex=0; 
			//DataRow Customer in myData.Tables [ "Customers" ].Rows 
			//foreach(DataColumn col in table.Columns)
			foreach(DataColumn col in DataSet1.Tables["Level_3"].Columns)
			{   
				ColumnIndex++;
				excel.Cells[1,ColumnIndex]=col.ColumnName;
			} 
			int rowIndex=0; 
			//foreach(DataRow row in table.Rows) 
			foreach(DataRow row in DataSet1.Tables["Level_3"].Rows)
			{         
				rowIndex++;       
				ColumnIndex=0;         
				foreach(DataColumn col in DataSet1.Tables["Level_3"].Columns)         
				{  
					ColumnIndex++;                
					//excel.Cells[rowIndex+1,ColumnIndex]= row.Cells[col.ColumnName].Text;         
				}
			} 
			excel.Visible = true; 
			Worksheet worksheet = (Worksheet)excel.ActiveSheet; 
			worksheet.Activate();

		}
On the bolded line I am getting an error stating "object not set to reference object. I am populating my dataset (i.e. da.Fill(DataSet1, "Level_3") in a separate sub. Could that be the problem and if so what do I need to change? Does anyone know a fix for this?
 
Old 03-01-2006, 11:36 AM   #2
tekkieman
Member
 
Registered: Dec 2004
Location: Northern CA
Distribution: Mepis 6.5
Posts: 123

Rep: Reputation: 15
If Dataset1 was defined in the other sub, it is out of scope. It either needs to be passed, or defined globally.
 
Old 03-01-2006, 12:37 PM   #3
mrobertson
Member
 
Registered: May 2005
Posts: 275

Original Poster
Rep: Reputation: 30
How would I pass this dataset into this sub function or would it be easier to define it globally?
If so how do you define it globally?
 
Old 03-01-2006, 04:31 PM   #4
tekkieman
Member
 
Registered: Dec 2004
Location: Northern CA
Distribution: Mepis 6.5
Posts: 123

Rep: Reputation: 15
To define it globally, declare it after declaring the class:

public class foo
{
private DataSet dataSet1 = null;
...
...


To pass it, you would need to create a new class derived from EventArgs, a public event, and a public delegate. In other words, unless this is a professional development project, or you really want to learn about delegates, just declare it globally.

By the way, how did you make out with your ambiguous reference and whatever the other problem was?...Invalid type being passed as I recall.

Last edited by tekkieman; 03-01-2006 at 04:33 PM.
 
Old 03-03-2006, 07:37 AM   #5
mrobertson
Member
 
Registered: May 2005
Posts: 275

Original Poster
Rep: Reputation: 30
I am still getting the following error even after I defined the dataset globally:

Object reference not set to an instance of an object.

Do you have any other suggestions or know what might be wrong here.

The ambiguous error that I was having has been solved. I needed to defined objects with the complete definition rater than just their name. For instance, DataTable Table would be defined as System.Data.DataTable Table. Hope to here back from you soon.
 
Old 03-03-2006, 09:04 AM   #6
tekkieman
Member
 
Registered: Dec 2004
Location: Northern CA
Distribution: Mepis 6.5
Posts: 123

Rep: Reputation: 15
Can you post the section(s) of code where you declare, initialize and populate Dataset1?
 
Old 03-03-2006, 12:27 PM   #7
mrobertson
Member
 
Registered: May 2005
Posts: 275

Original Poster
Rep: Reputation: 30
I actually got the function working and I am good to go for right now. Thank you for all of your help so far. I was alos wondering if you how to print a datagrid. For my application, I want to have the option to send to excel or just print the datagrid from the form. Any suggestions or pointers on how to do this...?
 
Old 03-03-2006, 02:37 PM   #8
tekkieman
Member
 
Registered: Dec 2004
Location: Northern CA
Distribution: Mepis 6.5
Posts: 123

Rep: Reputation: 15
Code:
private void printGrid_Click(System.Object sender, System.EventArgs e)
{
   printDocument1.Print();
}
private void printDocument1_PrintPage(System.Object sender, System.Drawing.Printing.PrintPageEventArgs e)
{
   PaintEventArgs myPaintArgs = new PaintEventArgs(e.Graphics, new Rectangle(new Point(0, 0), this.Size));
   this.InvokePaint(dataGrid1, myPaintArgs);
}
Taken from : MSDN
 
Old 03-06-2006, 07:59 AM   #9
mrobertson
Member
 
Registered: May 2005
Posts: 275

Original Poster
Rep: Reputation: 30
I tried the code that you sent me in the following format:
Code:
private void printDocument1_PrintPage(System.Object sender, System.Drawing.Printing.PrintPageEventArgs e)
		{
			PaintEventArgs myPaintArgs = new PaintEventArgs(e.Graphics, new System.Drawing.Rectangle(new System.Drawing.Point(0, 0), this.Size));
			this.InvokePaint(dgQueryResults, myPaintArgs);
		}

		private void btnPrint_Click(object sender, System.EventArgs e)
		{
			printDocument1.Print();
		}
As you can see I used a button called print for the printDocument1.Print();. When I clicked the button, a message box cam up saying printing page 1 of 1 and then the document never ended up printing out. Is there something else that I need to add or change to what I have done?
 
Old 03-06-2006, 08:01 AM   #10
mrobertson
Member
 
Registered: May 2005
Posts: 275

Original Poster
Rep: Reputation: 30
It is printing a blank piece of paper.
 
Old 03-06-2006, 08:09 AM   #11
mrobertson
Member
 
Registered: May 2005
Posts: 275

Original Poster
Rep: Reputation: 30
Ok, I have got it printing now but, It will not fit the entire grid. It prints the "screen shot" size of the grid (i.e. what the user would see without scrolling over to see the rest of the columns. I have approximately 40 columns in my grid and need to have them all printed and viewable. Is this a sizing issue and if so how would I change it?
 
Old 03-06-2006, 12:05 PM   #12
tekkieman
Member
 
Registered: Dec 2004
Location: Northern CA
Distribution: Mepis 6.5
Posts: 123

Rep: Reputation: 15
This is not something I've ever needed to do. The best I can do is a Google search and find something similar to what you want to do.


Here is a project that appears to do what you are looking for.
 
Old 03-06-2006, 12:09 PM   #13
mrobertson
Member
 
Registered: May 2005
Posts: 275

Original Poster
Rep: Reputation: 30
I have been looking at that exact article for the past day or two and cant really get it to work. If you have any other suggestions, I would greatly appreciate it. Let me know.
 
Old 03-06-2006, 04:28 PM   #14
tekkieman
Member
 
Registered: Dec 2004
Location: Northern CA
Distribution: Mepis 6.5
Posts: 123

Rep: Reputation: 15
Quote:
Originally Posted by mrobertson
I have been looking at that exact article for the past day or two and cant really get it to work.
My first suggestion would be that looking at code won't make it work!

Is there something that doesn't make sense? Does it fail to compile? The last code sample you posted doesn't look like you've implemented anything from the project I linked to, so if you have some code specific to that, I can take a look. Also, the link that says "Google search" in my last post contains links to many (346,000) atricles on doing what you want. Did any of those look any better?

Last edited by tekkieman; 03-06-2006 at 04:29 PM.
 
Old 03-07-2006, 07:26 AM   #15
mrobertson
Member
 
Registered: May 2005
Posts: 275

Original Poster
Rep: Reputation: 30
Here is the code that I have implemented into my program from the link that you have sent me:
Code:
private void btnPrint_Click(object sender, System.EventArgs e)
		{
			//printDocument1.Print();
			SetupGridPrinter();
			printPreviewDialog1.Show();

		}

		private void printDocument1_PrintPage_1(object sender, System.Drawing.Printing.PrintPageEventArgs e)
		{
			//PaintEventArgs myPaintArgs = new PaintEventArgs(e.Graphics, new System.Drawing.Rectangle(new System.Drawing.Point(0, 0), this.Size));
			//this.InvokePaint(dgQueryResults, myPaintArgs);	


			Graphics g = e.Graphics; 
			// Draw  a label title for the grid
			DrawTopLabel(g);

			// draw the datagrid using the DrawDataGrid method passing the Graphics surface
			bool more = dataGridPrinter1.DrawDataGrid(g);

			// if there are more pages, set the flag to cause the form to trigger another print page event
			if (more == true)
			{
				e.HasMorePages = true;
				dataGridPrinter1.PageNumber++;
			}


		}
		void SetupGridPrinter()
		{
			dataGridPrinter1 = new DataGridPrinter(dgQueryResults, printDocument1, DataSet1.Tables[0]);
		}

		void DrawTopLabel(Graphics g)
		{
			int TopMargin = printDocument1.DefaultPageSettings.Margins.Top;

			//g.FillRectangle(new SolidBrush(label1.BackColor), label1.Location.X, label1.Location.Y + TopMargin, label1.Size.Width, label1.Size.Height);
			//g.DrawString(label1.Text, label1.Font, new SolidBrush(label1.ForeColor), label1.Location.X + 50, label1.Location.Y + TopMargin, new StringFormat());
		}

		private void printPreviewDialog1_Load(object sender, System.EventArgs e)
		{
			printPreviewDialog1.Bounds = ClientRectangle;

		}
I downloaded the source code for that project and found that you needed to add a separtate class which I did as follows:
Code:
public class DataGridPrinter
	{
		private PrintDocument ThePrintDocument;
		private DataTable TheTable;
		private DataGrid  TheDataGrid;

		public int RowCount = 0;  // current count of rows;
		private const int kVerticalCellLeeway = 10;
		public int PageNumber = 1;
		public ArrayList Lines = new ArrayList();

		int PageWidth;
		int PageHeight;
		int TopMargin;
		int BottomMargin;

		public DataGridPrinter(DataGrid aGrid, PrintDocument aPrintDocument, DataTable aTable)
		{
			//
			// TODO: Add constructor logic here
			//
			TheDataGrid = aGrid;
			ThePrintDocument = aPrintDocument;
			TheTable = aTable;

			PageWidth = ThePrintDocument.DefaultPageSettings.PaperSize.Width;
			PageHeight = ThePrintDocument.DefaultPageSettings.PaperSize.Height;
			TopMargin = ThePrintDocument.DefaultPageSettings.Margins.Top;
			BottomMargin = ThePrintDocument.DefaultPageSettings.Margins.Bottom;

		}

		public void DrawHeader(Graphics g)
		{
			SolidBrush ForeBrush = new SolidBrush(TheDataGrid.HeaderForeColor);
			SolidBrush BackBrush = new SolidBrush(TheDataGrid.HeaderBackColor);
			Pen TheLinePen = new Pen(TheDataGrid.GridLineColor, 1);
			StringFormat cellformat = new StringFormat();
			cellformat.Trimming = StringTrimming.EllipsisCharacter;
			cellformat.FormatFlags = StringFormatFlags.NoWrap | StringFormatFlags.LineLimit;



			int columnwidth = PageWidth/TheTable.Columns.Count;

			int initialRowCount = RowCount;

			// draw the table header
			float startxposition = TheDataGrid.Location.X;
			RectangleF nextcellbounds = new RectangleF(0,0, 0, 0);

			RectangleF HeaderBounds  = new RectangleF(0, 0, 0, 0);

			HeaderBounds.X = TheDataGrid.Location.X;
			HeaderBounds.Y = TheDataGrid.Location.Y + TopMargin + (RowCount - initialRowCount) * (TheDataGrid.Font.SizeInPoints  + kVerticalCellLeeway);
			HeaderBounds.Height = TheDataGrid.Font.SizeInPoints + kVerticalCellLeeway;
			HeaderBounds.Width = PageWidth;

			g.FillRectangle(BackBrush, HeaderBounds);

			for (int k = 0; k < TheTable.Columns.Count; k++)
			{
				string nextcolumn = TheTable.Columns[k].ToString();
				RectangleF cellbounds = new RectangleF(startxposition, TheDataGrid.Location.Y + TopMargin + (RowCount - initialRowCount) * (TheDataGrid.Font.SizeInPoints  + kVerticalCellLeeway),
					columnwidth, 
					TheDataGrid.HeaderFont.SizeInPoints + kVerticalCellLeeway);
				nextcellbounds = cellbounds;

				if (startxposition + columnwidth <= PageWidth)
				{
					g.DrawString(nextcolumn, TheDataGrid.HeaderFont, ForeBrush, cellbounds, cellformat);
				}

				startxposition = startxposition + columnwidth;

			}
	
			if (TheDataGrid.GridLineStyle != DataGridLineStyle.None)
				g.DrawLine(TheLinePen, TheDataGrid.Location.X, nextcellbounds.Bottom, PageWidth, nextcellbounds.Bottom);
		}

		public bool DrawRows(Graphics g)
		{
			int lastRowBottom = TopMargin;

			try
			{
				SolidBrush ForeBrush = new SolidBrush(TheDataGrid.ForeColor);
				SolidBrush BackBrush = new SolidBrush(TheDataGrid.BackColor);
				SolidBrush AlternatingBackBrush = new SolidBrush(TheDataGrid.AlternatingBackColor);
				Pen TheLinePen = new Pen(TheDataGrid.GridLineColor, 1);
				StringFormat cellformat = new StringFormat();
				cellformat.Trimming = StringTrimming.EllipsisCharacter;
				cellformat.FormatFlags = StringFormatFlags.NoWrap | StringFormatFlags.LineLimit;
				int columnwidth = PageWidth/TheTable.Columns.Count;

				int initialRowCount = RowCount;

				RectangleF RowBounds  = new RectangleF(0, 0, 0, 0);

				// draw vertical lines




				// draw the rows of the table
				for (int i = initialRowCount; i < TheTable.Rows.Count; i++)
				{
					DataRow dr = TheTable.Rows[i];
					int startxposition = TheDataGrid.Location.X;

					RowBounds.X = TheDataGrid.Location.X;
					RowBounds.Y = TheDataGrid.Location.Y + TopMargin + ((RowCount - initialRowCount)+1) * (TheDataGrid.Font.SizeInPoints  + kVerticalCellLeeway);
					RowBounds.Height = TheDataGrid.Font.SizeInPoints + kVerticalCellLeeway;
					RowBounds.Width = PageWidth;
					Lines.Add(RowBounds.Bottom);

					if (i%2 == 0)
					{
						g.FillRectangle(BackBrush, RowBounds);
					}
					else
					{
						g.FillRectangle(AlternatingBackBrush, RowBounds);
					}


					for (int j = 0; j < TheTable.Columns.Count; j++)
					{
						RectangleF cellbounds = new RectangleF(startxposition, 
							TheDataGrid.Location.Y + TopMargin + ((RowCount - initialRowCount) + 1) * (TheDataGrid.Font.SizeInPoints  + kVerticalCellLeeway),
							columnwidth, 
							TheDataGrid.Font.SizeInPoints + kVerticalCellLeeway);
									

						if (startxposition + columnwidth <= PageWidth)
						{
							g.DrawString(dr[j].ToString(), TheDataGrid.Font, ForeBrush, cellbounds, cellformat);
							lastRowBottom = (int)cellbounds.Bottom;
						}

						startxposition = startxposition + columnwidth;
					}

					RowCount++;

					if (RowCount * (TheDataGrid.Font.SizeInPoints  + kVerticalCellLeeway) > (PageHeight * PageNumber) - (BottomMargin+TopMargin))
					{
						DrawHorizontalLines(g, Lines);
						DrawVerticalGridLines(g, TheLinePen, columnwidth, lastRowBottom);
						return true;
					}


				}

				DrawHorizontalLines(g, Lines);
				DrawVerticalGridLines(g, TheLinePen, columnwidth, lastRowBottom);
				return false;

			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message.ToString());
				return false;
			}

		}

		void DrawHorizontalLines(Graphics g, ArrayList lines)
		{
			Pen TheLinePen = new Pen(TheDataGrid.GridLineColor, 1);

			if (TheDataGrid.GridLineStyle == DataGridLineStyle.None)
				return;

			for (int i = 0;  i < lines.Count; i++)
			{
				g.DrawLine(TheLinePen, TheDataGrid.Location.X, (float)lines[i], PageWidth, (float)lines[i]);
			}
		}

		void DrawVerticalGridLines(Graphics g, Pen TheLinePen, int columnwidth, int bottom)
		{
			if (TheDataGrid.GridLineStyle == DataGridLineStyle.None)
				return;

			for (int k = 0; k < TheTable.Columns.Count; k++)
			{
				g.DrawLine(TheLinePen, TheDataGrid.Location.X + k*columnwidth, 
					TheDataGrid.Location.Y + TopMargin,
					TheDataGrid.Location.X + k*columnwidth,
					bottom);
			}
		}


		public bool DrawDataGrid(Graphics g)
		{

			try
			{
				DrawHeader(g);
				bool bContinue = DrawRows(g);
				return bContinue;
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message.ToString());
				return false;
			}

		}
	}
I am not currently getting any errors, it will compile and print a datagrid; however, the data grid has compressed, unviewable(very thin) columns and is off centered on the page. The original code set that you sent me printed a much better looking graph, just not all of the graph. I have looked all over google and cannot find anything that will print a full, formatted datagrid. hopefully this may trigger some ideas for you that you can share with me.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Printing a datagrid/dataset in c# mrobertson Programming 1 02-27-2006 03:43 PM
Displaying an oracle dataset on a datagrid using C# mrobertson Programming 1 01-11-2006 02:34 PM
mail sending problem !!! hitesh_linux Linux - Networking 6 09-22-2004 10:01 PM
problem when sending mail adm1329 Linux - Software 3 03-18-2004 09:59 AM
problem with sending mail DreamerNS Linux - Software 0 03-30-2003 11:30 AM


All times are GMT -5. The time now is 05:55 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration