Datagridview to Clipboard with formatting

2019-07-18 05:40发布

站内问答 / 后端开发
1916 2 5

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?

2条回答
劫难
2楼-- · 2019-07-18 06:26 
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

查看更多
0人赞 添加讨论(0) 举报
等我变得足够好
3楼-- · 2019-07-18 06:40

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.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)