Datagridview to Clipboard with formatting
For a VB.Net application needing to output the data to clipboard, with formatting, I 开发者_开发问答am in need of some help. For now, I am exporting data from the clipboard using
MainView.ClipboardCopyMode = Windows.Forms.DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
System.Windows.Forms.Clipboard.SetDataObject(MainView.GetClipboardContent())
Now I need to extend this with the formatting / style from the DataGridView. I have read several ExcelExporters, all writing to an Excel file directly, but I need to write to the Clipboard.
The DataGridView exposes nothing other than the DataGridView.GetClipBoardContent() which just gives the raw data. I need to get some XML/HTML/RTF object. I have tried the following:
Dim test As New DataObject
test.SetData(DataFormats.EnhancedMetafile , True, DataGridView1.GetClipboardContent)
Clipboard.SetDataObject(test)
This does not work as of yet. Any tips to easily convert an unbound DataGridView to XML/HTML/RTF/Enhanced Metafile?
If it does not support it natively (which you imply), the best would be to render the content to HTML or RTF. HTML tables would be more suited for Excel and it seems to interpret it fairly well.
Imports System.Text
Imports System.Windows.Forms
Imports System.Drawing
Public Module GridToHTML_
Public Sub CopyDgvToClipboard(ByVal dgv As DataGridView)
If dgv.RowCount = 0 Then
Exit Sub
End If
Clipboard.SetData(DataFormats.Text, GridToHTML(dgv, ""))
End Sub
Public Function GridToHTML(ByVal dgv As DataGridView,
ByVal title As String,
Optional ByVal decimals As Boolean = True,
Optional GridLines As Boolean = False) As String
Dim firstrow As Integer = Integer.MaxValue
Dim firstcol As Integer = Integer.MaxValue
Dim lastrow As Integer = Integer.MinValue
Dim lastcol As Integer = Integer.MinValue
If dgv.SelectedCells.Count < 2 Then
firstrow = 0
firstcol = 0
lastrow = dgv.Rows.Count - 1
lastcol = dgv.Columns.Count - 1
Else
For Each cell As DataGridViewCell In dgv.SelectedCells
If cell.RowIndex < firstrow Then
firstrow = cell.RowIndex
End If
If cell.ColumnIndex < firstcol Then
firstcol = cell.ColumnIndex
End If
If cell.RowIndex > lastrow Then
lastrow = cell.RowIndex
End If
If cell.ColumnIndex > lastcol Then
lastcol = cell.ColumnIndex
End If
Next
End If
Dim spanned(lastrow, lastcol) As Boolean
Dim line As New StringBuilder(lastrow * 20)
Dim fontfamily As String = "Arial"
line.AppendLine("<html>")
line.AppendLine("<head>")
line.AppendLine("<title>" & title & "</title>")
If GridLines Then
line.AppendLine("<style>")
line.AppendLine("table {border: 1px solid #444444; border-collapse:collapse}")
line.AppendLine("thead,tr,td {border: 1px solid #222222}")
line.AppendLine("</style>")
End If
line.AppendLine("</head>")
line.AppendLine("<body>")
line.Append("<table style='color:#000000;vertical-align:middle;text-align:right;font-size:8.25pt;")
Dim tlc As DataGridViewCell = dgv.Rows(0).Cells(0)
If tlc.Style IsNot Nothing Then
If tlc.Style.Font IsNot Nothing Then
fontfamily = tlc.Style.Font.Name
End If
End If
line.Append("font-family:""" & fontfamily & """;'>")
If dgv.ColumnHeadersVisible Then
line.AppendLine(vbTab & "<thead style='display: table-header-group'><tr>")
For col = 0 To lastcol
If dgv.Columns(col).Visible Then
line.Append(New String(CChar(vbTab), 2) & "<th style='text-align:center'>" & dgv.Columns(col).HeaderText & "</th>")
End If
Next
line.AppendLine(vbTab & "</tr></thead>")
End If
line.AppendLine(vbTab & "<tbody style='display: table-row-group'>")
For row = firstrow To lastrow
line.AppendLine(vbTab & "<tr>")
For col = firstcol To lastcol
If dgv.Columns(col).Visible And Not spanned(row, col) Then
Dim cell As DataGridViewCell = dgv.Rows(row).Cells(col)
line.Append(New String(CChar(vbTab), 2) & "<td")
If cell.Style IsNot Nothing Then
Dim style As New StringBuilder
If cell.Style.BackColor.A > 0 Then
style.Append("background-color:#" & ColourToHex(cell.Style.BackColor) & ";")
End If
If cell.Style.ForeColor.A > 0 AndAlso cell.Style.ForeColor <> Color.Black Then
style.Append("color:#" & ColourToHex(cell.Style.ForeColor) & ";")
End If
If cell.Style.Font IsNot Nothing Then
If cell.Style.Font.Name <> fontfamily Then
style.Append("font-family:""" & cell.Style.Font.Name & """;")
End If
' Don't, Cell(0,0) will display ####
If cell.Style.Font.SizeInPoints <> 8.25 Then
style.Append("font-size:" & cell.Style.Font.SizeInPoints & "pt" & ";")
End If
If cell.Style.Font.Bold Then
style.Append("font-weight:bold;")
End If
If cell.Style.Font.Italic Then
style.Append("font-style:italic;")
End If
End If
Dim align As DataGridViewContentAlignment = cell.Style.Alignment
If align = DataGridViewContentAlignment.NotSet Then
align = dgv.Columns(col).DefaultCellStyle.Alignment
End If
Select Case align
Case DataGridViewContentAlignment.BottomCenter
style.Append("vertical-align:bottom;text-align:center;")
Case DataGridViewContentAlignment.BottomLeft
style.Append("vertical-align:bottom;text-align:left;")
Case DataGridViewContentAlignment.BottomRight
style.Append("vertical-align:bottom;text-align:right;")
Case DataGridViewContentAlignment.MiddleCenter
style.Append("vertical-align:middle;text-align:center;")
Case DataGridViewContentAlignment.MiddleLeft
style.Append("vertical-align:middle;text-align:left;")
Case DataGridViewContentAlignment.MiddleRight
' style.Append("vertical-align:middle;text-align:right;") ' Not needed because default on Body
Case DataGridViewContentAlignment.TopCenter
style.Append("vertical-align:top;text-align:center;")
Case DataGridViewContentAlignment.TopLeft
style.Append("vertical-align:top;text-align:left;")
Case DataGridViewContentAlignment.TopRight
style.Append("vertical-align:top;text-align:right;")
End Select
Dim s As String = style.ToString
If s <> "" Then
line.Append(" style='" & s & "'")
End If
End If ' cell.Style IsNot Nothing
If TypeOf cell Is SpanningTextBoxCell Then ' deal with rowspan / colspan
Dim stbc As SpanningTextBoxCell = DirectCast(cell, SpanningTextBoxCell)
If stbc.RowSpan > 1 Then
line.Append(" rowspan=" & stbc.RowSpan.ToString)
For r = row + 1 To row + stbc.RowSpan - 1
For c = col To col + stbc.ColumnSpan - 1
spanned(r, c) = True
Next
Next
End If
If stbc.ColumnSpan > 1 Then
line.Append(" colspan=" & stbc.ColumnSpan.ToString)
For c = col + 1 To col + stbc.ColumnSpan - 1
spanned(row, c) = True
Next
End If
End If
line.Append(">")
Dim value As String = ""
If TypeOf cell Is DataGridViewComboBoxCell Then
value = DirectCast(cell, DataGridViewComboBoxCell).FormattedValue.ToString
ElseIf TypeOf cell Is DataGridViewCheckBoxCell Then
value = cell.Value.ToString
ElseIf cell.Value IsNot Nothing Then
' Formatting with trailing "," divides by 1'000 for each comma
If cell.ValueType.IsPrimitive Then
Dim dcs As DataGridViewCellStyle = dgv.Columns(cell.ColumnIndex).DefaultCellStyle
If dcs Is Nothing Then
value = cell.Value.ToString
Else
Dim primitive As Double = CDbl(cell.Value)
Dim fmt As String = dcs.Format
Do While fmt.EndsWith(",")
primitive /= 1000
fmt = fmt.Substring(0, fmt.Length - 1)
Loop
If decimals Then
value = Format(primitive, "#,##0.######")
Else
value = Format(primitive, "#,##0")
End If
End If
Else
value = cell.Value.ToString
End If
End If
line.Append(value)
line.AppendLine("</td>")
End If ' visible and not spanned
Next
line.AppendLine(vbTab & "</tr>")
Next
line.AppendLine(vbTab & "</tbody>")
line.AppendLine("</table>")
line.AppendLine("</body>")
line.AppendLine("</html>")
Return line.ToString
End Function
End Module
加载中,请稍侯......
精彩评论