Format Excel Column

[ArizonaRedneck]

I need to transfer a dataset into excel for work. I can do that, but I don't know how to format the column through code. for example...

in excel, you would highlight the column,click format,cells, and under category I would choose text.

I need to do this because some of the fields in my dataset are numbers stored as text (zip code and others). When excel opens the dataset, it converts these columns to numbers and any leading zeros are cut off.

here is the sub I'm using.

 Friend Sub ExportToExcel(ByVal dstDataSet As DataSet, ByVal TableName As String)
		Dim excelApp As New Excel.Application()
		Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
		Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
		excelApp.Visible = True
		Dim x As Integer = 1
		Dim dcColumn As DataColumn
		For Each dcColumn In dstDataSet.Tables(TableName).Columns
			excelApp.ActiveSheet.Cells(1, x) = dcColumn.ColumnName
			x += 1
		Next
		Dim drDataRow As DataRow
		Dim ColumnCount, RowCount As Integer
		For Each dcColumn In dstDataSet.Tables(TableName).Columns
			ColumnCount += 1
			RowCount = 1
			For Each drDataRow In dstDataSet.Tables(TableName).Rows
				RowCount += 1
				excelApp.ActiveSheet.Cells(RowCount, ColumnCount) = drDataRow(dcColumn)
			Next
		Next
		excelApp.Columns.AutoFit()
	End Sub

 

[ArizonaRedneck]

I found it!

just in case anyone else out there would like to use the script for them selves, here's the updated sub.

Friend Sub ExportToExcel(ByVal dstDataSet As DataSet, ByVal TableName As String)
		Dim excelApp As New Excel.Application()
		Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
		Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
		excelApp.Visible = True
		[b][u][i]excelApp.Columns.Cells.NumberFormat = "@"[/i][/u][/b]
		Dim x As Integer = 1
		Dim dcColumn As DataColumn
		For Each dcColumn In dstDataSet.Tables(TableName).Columns
			excelApp.ActiveSheet.Cells(1, x) = dcColumn.ColumnName
			x += 1
		Next
		Dim drDataRow As DataRow
		Dim ColumnCount, RowCount As Integer
		For Each dcColumn In dstDataSet.Tables(TableName).Columns
			ColumnCount += 1
			RowCount = 1
			For Each drDataRow In dstDataSet.Tables(TableName).Rows
				RowCount += 1
				excelApp.ActiveSheet.Cells(RowCount, ColumnCount) = drDataRow(dcColumn)
			Next
		Next
		excelApp.Columns.AutoFit()
	End Sub

 

[Mali2012]

I am transferring a dataset into excel workbook, however i need certain columns to be in text format. When i have added the above code, the dates have changed to following format m/dd/yyyy but the other cells are exactly how i need them. I am beginner to VB.net so any help will be much appreciated.