how to add column header and a title when exporting excel from vb.net

Question

1.00/5 (2 votes)

See more:

VB

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

Posted 21-Aug-13 18:41pm

sudeshna from bangkok

Updated 22-Aug-13 3:57am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v4

Add a Solution

Comments

Thanks7872 22-Aug-13 0:54am

Use improve question widget and post this in question itself. Dumping code here in comments is a bad practice.

sudeshna from bangkok 22-Aug-13 0:47am

can anyone please help me.
thanks in advance

Thanks7872 22-Aug-13 0:52am

Use improve question widget and post this in question itself. Dumping code here in comments is a bad practice.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dushant Reddy · Answer 1 · 2013-08-22T01:45:00

Hi,

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

VB

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