Format Excel Column

Joined
Dec 17, 2004
Messages
22
Location
Phoenix
Programming Experience
3-5
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.
VB.NET:
 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
 
Joined
Dec 17, 2004
Messages
22
Location
Phoenix
Programming Experience
3-5
I found it!

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

VB.NET:
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

New member
Joined
Apr 12, 2012
Messages
1
Programming Experience
Beginner
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.
 
Top Bottom