0 Replies - 6611 Views - Last Post: 16 December 2007 - 12:35 PM Rate Topic: -----

#1 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

vb .net to Excel formatting

Post icon  Posted 16 December 2007 - 12:35 PM

Hi all...

I have done 3 days worth of researching and finally came across PsychoCoder's code which was awesome, but I am wanting to do a couple things differently, eg. Either A, keep an existing Excel spreadsheets formatting and populate it, or B, create a new spreadsheet and format the column header cells appropriately... Here's what I got so far...

	Private Sub exportItemButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exportItemButton.Click
		materialId = exportItemTextBox.Text
		Dim InventoryBindingSource As New BindingSource()
		Dim dt As New DataTable("inventory")
		dt.Locale = System.Globalization.CultureInfo.InvariantCulture
		InventoryBindingSource.DataSource = dt
		Dim daMyName As New SqlDataAdapter
		Dim dsMyName As New DataSet
		Dim mySelectQuery As String = "Select materialId, ceq, version, itemDescription, serial, visual, lighton, software, racal, passfail, comments From MainTable Where materialId = '" & materialId & "';"
		Dim myConnection As New SqlConnection(ConnString)
		Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
		myConnection.Open()
		daMyName.SelectCommand = myCommand
		daMyName.Fill(dsMyName)
		daMyName.Fill(dt)
		Dim test As String = String.Empty
		CreateExcelFile(test, dt)
		myConnection.Close()
	End Sub

	Public Shared Function CreateExcelFile(ByVal fileName As String, ByVal dt As DataTable) As Boolean
		Dim excelExport As New Microsoft.Office.Interop.Excel.Application()
		Dim excelBook As Microsoft.Office.Interop.Excel.Workbook
		Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets
		Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet
		Dim excelCells As Microsoft.Office.Interop.Excel.Range
		Dim location As Integer = System.Windows.Forms.Application.ExecutablePath.LastIndexOf("\")
		Dim exPath As String = System.Windows.Forms.Application.ExecutablePath
		Dim filePath As String
		Try
			filePath = "C:\TestExcel2.xls" 'exPath.Substring(0, (location + 1)) + "tmpFiles\" & fileName
			'If Not Directory.Exists(exPath.Substring(0, (location + 1)) + "tmpFiles\") Then
			'	Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\")
			'End If
			excelExport.Visible = False : excelExport.DisplayAlerts = False
			excelBook = excelExport.Workbooks.Add
			excelSheets = excelBook.Worksheets
			excelSheet = CType(excelSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
			excelSheet.Name = "Results - " & Date.Now.Day & Date.Now.ToString("MMM") & Date.Now.ToString("yy")
			excelCells = excelSheet.Cells

			PopulateSheet(dt, excelCells)

			excelSheet.SaveAs(filePath)
			excelBook.Close()
			excelExport.Quit()

			ReleaseComObject(excelCells) : ReleaseComObject(excelSheet)

			ReleaseComObject(excelSheets)
			ReleaseComObject(excelBook) : ReleaseComObject(excelExport)

			excelExport = Nothing : excelBook = Nothing : excelSheets = Nothing
			excelSheet = Nothing : excelCells = Nothing

			System.GC.Collect()
			Return True
		Catch ex As Exception
			MessageBox.Show("Error exporting data")
			Return False
		End Try
	End Function

	Private Shared Sub PopulateSheet(ByVal dt As System.Data.DataTable, ByVal oCells As Microsoft.Office.Interop.Excel.Range)
		Dim dRow As DataRow
		Dim dataArray() As Object
		Dim count As Integer
		Dim column_count As Integer
		'Output Column Headers
		'For column_count = 0 To dt.Columns.Count - 1
		'	oCells(2, column_count + 1) = dt.Columns(column_count).ToString
		'Next

		'Output Data
		For count = 0 To dt.Rows.Count - 1
			dRow = dt.Rows.Item(count)
			dataArray = dRow.ItemArray
			For column_count = 0 To UBound(dataArray)
				oCells(count + 3, column_count + 1) = dataArray(column_count).ToString
			Next
		Next
	End Sub


Any help or guidance would be greatly appreciated...

Is This A Good Question/Topic? 0
  • +

Page 1 of 1