11 Replies - 4436 Views - Last Post: 03 February 2013 - 12:34 AM Rate Topic: -----

#1 chanlichin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Export data grid to excel

Post icon  Posted 02 November 2009 - 02:25 AM

Quote

Hi, Can any one give me a suggestion to Export data grid to excel, i want the coding by using vb.net. Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Export data grid to excel

#2 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Export data grid to excel

Posted 02 November 2009 - 02:31 AM

Hi,

You can modify my code......

 Dim fs As New IO.StreamWriter(FileName, False)
		fs.WriteLine("<?xml version=""1.0""?>")
		fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
		fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
		fs.WriteLine("	<ss:Styles>")
		fs.WriteLine("		<ss:Style ss:ID=""1"">")
		fs.WriteLine("		   <ss:Font ss:Bold=""1""/>")
		fs.WriteLine("		</ss:Style>")
		fs.WriteLine("	</ss:Styles>")
		fs.WriteLine("	<ss:Worksheet ss:Name=""Sheet1"">")
		fs.WriteLine("		<ss:Table>")
		For x As Integer = 0 To dtgEntities.Columns.Count - 1
			'If dtgEntities.Rows(x).Visible = True Then
			fs.WriteLine("			<ss:Column ss:Width=""{0}""/>", dtgEntities.Columns.Item(x).Width)
			'End If
		Next
		fs.WriteLine("			<ss:Row ss:StyleID=""1"">")
		For i As Integer = 0 To dtgEntities.Columns.Count - 1
			'If dtgEntities.Rows(i).Visible = True Then
			fs.WriteLine("				<ss:Cell>")
			fs.WriteLine(String.Format("				   <ss:Data ss:Type=""String"">{0}</ss:Data>", dtgEntities.Columns.Item(i).HeaderText))
			fs.WriteLine("				</ss:Cell>")
			'End If
		Next
		fs.WriteLine("			</ss:Row>")
		For intRow As Integer = 0 To dtgEntities.RowCount - 1
			If dtgEntities.Rows(intRow).Visible = True Then
				fs.WriteLine(String.Format("			<ss:Row ss:Height =""{0}"">", dtgEntities.Rows(intRow).Height))
				For intCol As Integer = 0 To dtgEntities.Columns.Count - 1
					fs.WriteLine("				<ss:Cell>")
					fs.WriteLine(String.Format("				   <ss:Data ss:Type=""String"">{0}</ss:Data>", dtgEntities.Item(intCol, intRow).Value.ToString))
					fs.WriteLine("				</ss:Cell>")
				Next
				fs.WriteLine("			</ss:Row>")
			End If
		Next
		fs.WriteLine("		</ss:Table>")
		fs.WriteLine("	</ss:Worksheet>")
		fs.WriteLine("</ss:Workbook>")
		fs.Close()
		MsgBox("Export Complete!", MsgBoxStyle.OkOnly, "Export Complete")


Was This Post Helpful? 0
  • +
  • -

#3 chanlichin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: Export data grid to excel

Posted 02 November 2009 - 02:54 AM

View Postmotcom, on 2 Nov, 2009 - 01:31 AM, said:

Hi,

You can modify my code......

 Dim fs As New IO.StreamWriter(FileName, False)
		fs.WriteLine("<?xml version=""1.0""?>")
		fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
		fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
		fs.WriteLine("	<ss:Styles>")
		fs.WriteLine("		<ss:Style ss:ID=""1"">")
		fs.WriteLine("		   <ss:Font ss:Bold=""1""/>")
		fs.WriteLine("		</ss:Style>")
		fs.WriteLine("	</ss:Styles>")
		fs.WriteLine("	<ss:Worksheet ss:Name=""Sheet1"">")
		fs.WriteLine("		<ss:Table>")
		For x As Integer = 0 To dtgEntities.Columns.Count - 1
			'If dtgEntities.Rows(x).Visible = True Then
			fs.WriteLine("			<ss:Column ss:Width=""{0}""/>", dtgEntities.Columns.Item(x).Width)
			'End If
		Next
		fs.WriteLine("			<ss:Row ss:StyleID=""1"">")
		For i As Integer = 0 To dtgEntities.Columns.Count - 1
			'If dtgEntities.Rows(i).Visible = True Then
			fs.WriteLine("				<ss:Cell>")
			fs.WriteLine(String.Format("				   <ss:Data ss:Type=""String"">{0}</ss:Data>", dtgEntities.Columns.Item(i).HeaderText))
			fs.WriteLine("				</ss:Cell>")
			'End If
		Next
		fs.WriteLine("			</ss:Row>")
		For intRow As Integer = 0 To dtgEntities.RowCount - 1
			If dtgEntities.Rows(intRow).Visible = True Then
				fs.WriteLine(String.Format("			<ss:Row ss:Height =""{0}"">", dtgEntities.Rows(intRow).Height))
				For intCol As Integer = 0 To dtgEntities.Columns.Count - 1
					fs.WriteLine("				<ss:Cell>")
					fs.WriteLine(String.Format("				   <ss:Data ss:Type=""String"">{0}</ss:Data>", dtgEntities.Item(intCol, intRow).Value.ToString))
					fs.WriteLine("				</ss:Cell>")
				Next
				fs.WriteLine("			</ss:Row>")
			End If
		Next
		fs.WriteLine("		</ss:Table>")
		fs.WriteLine("	</ss:Worksheet>")
		fs.WriteLine("</ss:Workbook>")
		fs.Close()
		MsgBox("Export Complete!", MsgBoxStyle.OkOnly, "Export Complete")


[quote]Hi Motcom, May i know izzit i need to add references?Thanks.
Was This Post Helpful? 0
  • +
  • -

#4 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Export data grid to excel

Posted 02 November 2009 - 03:08 AM

Hi,

No no reference needed.....


Maybe "Imports System.IO"
Was This Post Helpful? 0
  • +
  • -

#5 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 357
  • View blog
  • Posts: 2,768
  • Joined: 18-September 06

Re: Export data grid to excel

Posted 02 November 2009 - 09:45 AM

Or you could automate it. Take a look at my Office Automation tutorial and see if it helps :)
Was This Post Helpful? 0
  • +
  • -

#6 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Export data grid to excel

Posted 02 November 2009 - 10:46 PM

Just to add,

In my case the code is a bit old... but you do not need to have excel installed on the PC....
Was This Post Helpful? 0
  • +
  • -

#7 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 84
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: Export data grid to excel

Posted 03 November 2009 - 01:03 PM

View Postchanlichin, on 2 Nov, 2009 - 01:25 AM, said:

Quote

Hi, Can any one give me a suggestion to Export data grid to excel, i want the coding by using vb.net. Thanks.

Take a look at this example:

http://www.java-samp...tutorialid=1054

~'J'~
Was This Post Helpful? 0
  • +
  • -

#8 chanlichin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: Export data grid to excel

Posted 05 November 2009 - 07:14 AM

View Postfixo, on 3 Nov, 2009 - 12:03 PM, said:

View Postchanlichin, on 2 Nov, 2009 - 01:25 AM, said:

Quote

Hi, Can any one give me a suggestion to Export data grid to excel, i want the coding by using vb.net. Thanks.

Take a look at this example:

http://www.java-samp...tutorialid=1054

~'J'~

Quote

Hi, Fixo...thanks for suggest the web page for me. I can export the data grid to the excel but the title of each column can not display. Example from that web site: the title of product_id, Product_price and Product_name can not display. The details of each column can display so i just wonder what is happening.Hope you can help me,thanks.

Was This Post Helpful? 0
  • +
  • -

#9 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 84
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: Export data grid to excel

Posted 05 November 2009 - 02:41 PM

Quote

Hi, Fixo...thanks for suggest the web page for me. I can export the data grid to the excel but the title of each column can not display. Example from that web site: the title of product_id, Product_price and Product_name can not display. The details of each column can display so i just wonder what is happening.Hope you can help me,thanks.

Hi, chanlichin
Here is code snip from working project
(where dgvData is DataGridView)
Hope this will helps
					int i = 0;
					int j = 0;
int c =0;
					//int c = xlWorkSheet.get_Range("A65536", "A65536").get_End(Excel.XlDirection.xlUp).Row + 3;
					for (i = 0; i < dgvData.ColumnCount; i++)
					{

						xlRange = (Excel.Range)xlWorkSheet.Cells[c + 1, i + 1];
						xlRange.Value2 = dgvData.Columns[i].Name;
						xlRange.Font.Bold = true;
						xlRange.Font.Name = "Times New Roman";
						xlRange.Font.Size = 11;
						xlRange.Font.ColorIndex = 5;
					}

					for (i = 0; i < dgvData.RowCount; i++)
					{
						DataGridViewRow row = dgvData.Rows[i];
						if (!row.IsNewRow)
						{
							for (j = 0; j < dgvData.ColumnCount; j++)
							{
								DataGridViewCell cell = dgvData[j, i];
								xlRange = (Excel.Range)xlWorkSheet.Cells[i + c + 2, j + 1];
								xlRange.Value2 = cell.Value;

							}
						}
					}


~'J'~
Was This Post Helpful? 0
  • +
  • -

#10 chanlichin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: Export data grid to excel

Posted 05 November 2009 - 06:19 PM

View Postfixo, on 5 Nov, 2009 - 01:41 PM, said:

Quote

Hi, Fixo...thanks for suggest the web page for me. I can export the data grid to the excel but the title of each column can not display. Example from that web site: the title of product_id, Product_price and Product_name can not display. The details of each column can display so i just wonder what is happening.Hope you can help me,thanks.

Hi, chanlichin
Here is code snip from working project
(where dgvData is DataGridView)
Hope this will helps
					int i = 0;
					int j = 0;
int c =0;
					//int c = xlWorkSheet.get_Range("A65536", "A65536").get_End(Excel.XlDirection.xlUp).Row + 3;
					for (i = 0; i < dgvData.ColumnCount; i++)
					{

						xlRange = (Excel.Range)xlWorkSheet.Cells[c + 1, i + 1];
						xlRange.Value2 = dgvData.Columns[i].Name;
						xlRange.Font.Bold = true;
						xlRange.Font.Name = "Times New Roman";
						xlRange.Font.Size = 11;
						xlRange.Font.ColorIndex = 5;
					}

					for (i = 0; i < dgvData.RowCount; i++)
					{
						DataGridViewRow row = dgvData.Rows[i];
						if (!row.IsNewRow)
						{
							for (j = 0; j < dgvData.ColumnCount; j++)
							{
								DataGridViewCell cell = dgvData[j, i];
								xlRange = (Excel.Range)xlWorkSheet.Cells[i + c + 2, j + 1];
								xlRange.Value2 = cell.Value;

							}
						}
					}


~'J'~

Quote

Fixo, may i know the coding u provide is vb.net?cause i fail to convert in vb.net. Many error come out.

Was This Post Helpful? 0
  • +
  • -

#11 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 357
  • View blog
  • Posts: 2,768
  • Joined: 18-September 06

Re: Export data grid to excel

Posted 06 November 2009 - 03:24 AM

That's not VB.NET.

Also Chan, and Motcom you should see this too...

Quote

Dream.In.Code has a policy by which we prefer to see a good faith effort on your part before providing source code for homework assignments. Please post the code you have written in an effort to resolve the problem, and our members would be happy to provide some guidance. Be sure to include a description of any errors you are encountering as well.


Basically means you should not post a question expecting to be provided with code, and Motcom should not have just said 'here you go' and given you some.

Go and read the tutorial I posted. That should give you enough information to be able to figure this out for yourself.
Was This Post Helpful? 0
  • +
  • -

#12 softwareskill  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 02-February 13

Re: Export data grid to excel

Posted 03 February 2013 - 12:34 AM

Try this. Export Gridview to excel in c# as well as vb http://www.freedotne...el-spreadsheet/
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1