从vb.net导出excel时如何添加列标题和标题 [英] how to add column header and a title when exporting excel from vb.net

问题描述

Dim ExcelApp As Object, ExcelBook As Object
Dim ExcelSheet As Object
Dim i As Integer
Dim j As Integer

'create object of excel
ExcelApp = CreateObject("Excel.Application")
ExcelBook = ExcelApp.WorkBooks.Add
ExcelSheet = ExcelBook.WorkSheets(1)

With ExcelSheet
    For i = 1 To Me.FactoryDataGridView.RowCount
        .cells(i, 1) = Me.FactoryDataGridView.Rows(i - 1).Cells("lot_no").Value
        For j = 1 To FactoryDataGridView.Columns.Count - 1
            .cells(i, j + 1) = FactoryDataGridView.Rows(i - 1).Cells(j).Value
        Next
    Next
End With

ExcelApp.Visible = True

ExcelSheet = Nothing
ExcelBook = Nothing
ExcelApp = Nothing

推荐答案

查看此代码。使用数据集代替grideveiw。

这里只传递数据集。 />

Hi,

Look in this code.Here using dataset instead of grideveiw.
here only pass dataset.

Public Sub ConvertToExcel(ByVal ds As DataSet)
    'create object of excel
    Dim oXL As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim oRng As Excel.Range
    ' Start Excel and get Application object.
    oXL = CreateObject("Excel.Application")
    oXL.Visible = False
    oWB = oXL.Workbooks.Add

    Dim iSheet As Integer = 1
    oSheet = oWB.Sheets(iSheet)
    Dim k, jj As Integer
    jj = 0
    Dim iCountCol As Integer = ds.Tables(0).Columns.Count
    jj = 1
    Dim str As String = ChrW(64 + 1) & jj & ":" & ChrW(64 + iCountCol) & jj

    oRng = oSheet.Range(str)
    With oRng
        .MergeCells = True
        .Value = "Title"
        .CurrentRegion.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter
    End With
    Range(oSheet, str)

    jj = 2
    str = ChrW(64 + 1) & jj & ":" & ChrW(64 + iCountCol) & jj

    oRng = oSheet.Range(str)
    With oRng
        .CurrentRegion.Font.Bold = True
    End With

    For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
        oSheet.Cells(jj, i + 1).Value = ds.Tables(0).Columns(i).ToString
        str = ChrW(64 + i + 1) & jj & ":" & ChrW(64 + i + 1) & jj
        Range(oSheet, str)
    Next

    For j As Integer = 0 To ds.Tables(0).Rows.Count - 1
        For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
            oSheet.Cells(j + 3, i + 1).Value = ds.Tables(0).Rows(j).Item(i).ToString
            str = ChrW(64 + i + 1) & j + 3 & ":" & ChrW(64 + i + 1) & j + 3
            Range(oSheet, str)
        Next
    Next

    oXL.Visible = False
    oXL.UserControl = False
    oXL.DisplayAlerts = False
    oWB.SaveAs("C:\aa.xls")
    oWB.Close()

    'oXL.DisplayAlerts = True
    ' Make sure that you release object references.
    oRng = Nothing
    oSheet = Nothing
    oWB = Nothing
    oXL.Quit()
    oXL = Nothing

End Sub

Private Function Range(ByVal oSheet As Excel.Worksheet, ByVal sRange As String) As Excel.Range
    Dim oRng As Excel.Range = oSheet.Range(sRange)

    '  oRng.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous
    With oRng
        With .Borders(Excel.XlBordersIndex.xlEdgeTop)
            .ColorIndex = 0
            .Weight = Excel.XlBorderWeight.xlThin
            .LineStyle = Excel.XlLineStyle.xlContinuous
            .TintAndShade = 0
        End With

        With .Borders(Excel.XlBordersIndex.xlEdgeRight)
            .ColorIndex = 0
            .Weight = Excel.XlBorderWeight.xlThin
            .LineStyle = Excel.XlLineStyle.xlContinuous
            .TintAndShade = 0
        End With

        With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
            .ColorIndex = 0
            .Weight = Excel.XlBorderWeight.xlThin
            .LineStyle = Excel.XlLineStyle.xlContinuous
            .TintAndShade = 0
        End With

        With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
            .ColorIndex = 0
            .Weight = Excel.XlBorderWeight.xlThin
            .LineStyle = Excel.XlLineStyle.xlContinuous
            .TintAndShade = 0
        End With

    End With
    Return oRng
End Function

这篇关于从vb.net导出excel时如何添加列标题和标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！