Export datagridview to excel and format font (1 Viewer)

desmond

Registered User.
Local time
Tomorrow, 09:41
Joined
Dec 8, 2009
Messages
28
I have a process in VB.net that writes out an XML file. However, I am unable to format the font. Does anyone know how to update the font to ‘courier new’?

I have tried adding:
fs.WriteLine(" <ss:Font FontName=""Courier New"" ss:size=""10"" ss:Bold=""1""/>")
to my code, but it doesn’t seem to work. L

Dim myFile As String = filePath & "\" & fileName & "." & fileExtension

' Open the file and write the headers
Dim fs As New IO.StreamWriter(myFile, 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"">")

' Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>")

' Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" <ss:Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" " & _
"ss:WrapText=""1""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C0C0"" ss:pattern=""Solid""/>")
fs.WriteLine(" </ss:Style>")

' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")

' Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=""Data Export"">")
fs.WriteLine(" <ss:Table>")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" <ss:Column ss:Width=""{0}""/>", _
grdView.Columns.Item(i).Width))
Next
fs.WriteLine(" <ss:Row>")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" <ss:Cell ss:StyleID=""1"">" & _
"<ss: Data ss:Type=""String"">{0}</ss: Data></ss:Cell>", _
grdView.Columns.Item(i).HeaderText))
Next
fs.WriteLine(" </ss:Row>")

' Check for an empty row at the end due to Adding allowed on the DataGridView
Dim subtractBy As Integer, cellText As String
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
' Write contents for each cell
For i As Integer = 0 To grdView.RowCount - subtractBy
fs.WriteLine(String.Format(" <ss:Row ss:Height=""{0}"">", _
grdView.Rows(i).Height))
For intCol As Integer = 0 To grdView.Columns.Count - 1
cellText = grdView.Item(intCol, i).Value
' Check for null cell and change it to empty to avoid error
If cellText = vbNullString Then cellText = ""
fs.WriteLine(String.Format(" <ss:Cell ss:StyleID=""2"">" & _
"<ss: Data ss:Type=""String"">{0}</ss: Data></ss:Cell>", _
cellText.ToString))
Next
fs.WriteLine(" </ss:Row>")
Next

' Close up the document
fs.WriteLine(" </ss:Table>")
fs.WriteLine("</ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()
 
Last edited:

Users who are viewing this thread

Top Bottom