导出到 Excel 并带有其他标题

Question

我有一个可以完美导出到 Excel 的按钮，但是我想在 excel 上的数据单元格上方包含标题，例如。报告名称、期间等

我该怎么做？我的导出代码如下

Protected Sub btnexp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnexp.Click
    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    Dim sw As New StringWriter()
    Dim hw As New HtmlTextWriter(sw)
    gv1.AllowPaging = False
    Call btnsubmit_Click(sender, e)
    'Change the Header Row back to white color 
    gv1.HeaderRow.Style.Add("background-color", "#FFFFFF")
    'Apply style to Individual Cells 
    gv1.HeaderRow.Cells(0).Style.Add("background-color", "white")
    gv1.HeaderRow.Cells(1).Style.Add("background-color", "white")
    gv1.HeaderRow.Cells(2).Style.Add("background-color", "white")
    gv1.HeaderRow.Cells(3).Style.Add("background-color", "white")
    gv1.HeaderRow.Cells(4).Style.Add("background-color", "white")
    gv1.HeaderRow.Cells(5).Style.Add("background-color", "white")
    gv1.HeaderRow.Cells(6).Style.Add("background-color", "white")

    For i As Integer = 0 To gv1.Rows.Count - 1
        Dim row As GridViewRow = gv1.Rows(i)
        'Change Color back to white 
        row.BackColor = System.Drawing.Color.White
        'Apply text style to each Row 
        row.Attributes.Add("class", "textmode")
        'Apply style to Individual Cells of Alternating Row 
        If i Mod 2 <> 0 Then
            row.Cells(0).Style.Add("background-color", "#C2D69B")
            row.Cells(1).Style.Add("background-color", "#C2D69B")
            row.Cells(2).Style.Add("background-color", "#C2D69B")
            row.Cells(3).Style.Add("background-color", "#C2D69B")
            row.Cells(4).Style.Add("background-color", "#C2D69B")
            row.Cells(5).Style.Add("background-color", "#C2D69B")
            row.Cells(6).Style.Add("background-color", "#C2D69B")
        End If
    Next
    gv1.RenderControl(hw)
    'style to format numbers to string 
    Dim style As String = "<style>.textmode{mso-number-format:@;}</style>"
    Response.Write(style)
    Response.Output.Write(sw.ToString())
    Response.Flush()
    Response.End()
End Sub
Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
    ' Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.
End Sub

Answer 1

这应该可以做你想要的。

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
        Private Sub Button2_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button2.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer
        Dim x As Integer

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        For x = 0 To DataGridView1.ColumnCount - 1
            xlWorkSheet.Cells(1, x + 1) = DataGridView1.Columns(x).HeaderText
        Next
        For i = 1 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i).Value.ToString()
            Next
        Next
        xlWorkSheet.SaveAs("C:vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
        MsgBox("You can find the file C:vbexcel.xlsx")
    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class