Community discussion forum

Back to discussion

Help me how to fast export data from datagridview to Excel with many format cell ?

Last post was 23 Jun 2009 at 15:38

Tags: csharp, java, sql, vb.net Mesquite, United States

- Reply
6 months ago

by anatha1

nimol men

Sieam Reap, Cambodia

Joined 2 years ago
Hi there ! i have a problem that want you to help me now > i write a program to export data from datagridview to excel worksheet with many format like border , mergcell,....etc but it run slowly , i feel bored so much.. so do you have any ideas or the way how to do it faster than , so please speak out ,
i wait for you all reply to me na. Thanks in advance
Best Regard from
Nimol Men (Cambodia)

Post was edited on 20/05/2009 12:14:24 Report abuse
- Reply
6 months ago

by yf2009

YF Wong

Seri Kembangan, Malaysia

Joined 6 months ago
Hi, can you share with us on what and how the data is exported to excel worksheet? Is the format of data in excel worksheet fixed (same format for all the set of data)? Is the amount of data to be exported fixed (eg. 10 rows, 10 columns)?

Report abuse

5 months ago

anatha1

nimol men

Sieam Reap, Cambodia

Joined 2 years ago

Hi yf2009 !

i so sorry , late to reply to u na,

anyway sorry cox i confuse that i used datagridview infact i used Listview .

my data is load from database , fixed columns=16 and Rows is not fix but up to data store.

my target is : want to export data from listview to excel worksheet with format like:( border , font , font size , number , align ,Merge Cell ) so my code procedure is :

Sub Export_2Excel(ByVal FileName As String) Try System.Windows.Forms.Control.CheckForIllegalCrossThreadCalls = False Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlRange As Excel.Range

        xlBook = xlApp.Workbooks.Add
        xlSheet = xlBook.Worksheets.Application.ActiveSheet
        xlSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape

        With xlSheet.PageSetup
            .LeftMargin = xlApp.InchesToPoints(0)
            .RightMargin = xlApp.InchesToPoints(0)
            .TopMargin = xlApp.InchesToPoints(0)
            .BottomMargin = xlApp.InchesToPoints(0)
            .HeaderMargin = xlApp.InchesToPoints(0)
            .FooterMargin = xlApp.InchesToPoints(0)
            .PaperSize = Excel.XlPaperSize.xlPaperA4
            .Zoom = 85
        End With

        '-----------set width Columns-----------------
        xlRange = xlSheet.Range("A1") : xlRange.ColumnWidth = 3.9
        xlRange = xlSheet.Range("B1") : xlRange.ColumnWidth = 25
        xlRange = xlSheet.Range("C1:D1") : xlRange.ColumnWidth = 9
        xlRange = xlSheet.Range("E1") : xlRange.ColumnWidth = 7.5
        xlRange = xlSheet.Range("F1") : xlRange.ColumnWidth = 7.5
        xlRange = xlSheet.Range("G1") : xlRange.ColumnWidth = 7.5

        xlRange = xlSheet.Range("H1:I1") : xlRange.ColumnWidth = 21.5
        xlRange = xlSheet.Range("J1") : xlRange.ColumnWidth = 6.3
        xlRange = xlSheet.Range("K1") : xlRange.ColumnWidth = 7.5
        xlRange = xlSheet.Range("L1:M1") : xlRange.ColumnWidth = 15.5
        'Merge Cell  Kingdom of Cambodia
        xlRange = xlSheet.Range("L1:M1") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "RBHraCNacRkkmú<Ca" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("L2:M2") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "Cati sasna RBHmhakSRt" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("L3:M3") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        '---------------------------------------
        xlRange = xlSheet.Range("A2:B2") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 1 : xlRange.VerticalAlignment = 2 : xlRange.Value = Space(3) & "RksYgBaNiC¢kmµ" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("A3:B3") : xlRange.MergeCells = True : xlRange.Value = "naykdæankmú<CaRtYtBinitüTMnij" : xlRange.Font.Name = "Limon s1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("A4:B4") : xlRange.MergeCells = True : xlRange.Value = "nIh½rN-Gah½rNnigb®gáabkarbnøM" : xlRange.Font.Name = "Limon s1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("A5:B5") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 1 : xlRange.VerticalAlignment = 2 : xlRange.Value = Space(8) & "}kaMkugRtUl}" : xlRange.Font.Name = "Limon s1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("A6:B6") : xlRange.MergeCells = True : xlRange.Value = "saxa³ GakasyandæanGnþrCatiPñMeBj" : xlRange.Font.Name = "Limon s1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("A7:B7") : xlRange.MergeCells = True : xlRange.Value = "elx³>>>>>>>>>>>>>>>>>>>>kGkGP" & Now.Year : xlRange.Font.Name = "Limon s1" : xlRange.Font.Size = 18
        '---------------------------------------
        '----------------Center----------------
        xlRange = xlSheet.Range("F6:J6")
        xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = PanelLabel.Text : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("D7:K7") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = lblDateReport.Text : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("F8:J8") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = ">>>>>>>>" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18


        '--HEader
        ' No
        xlRange = xlSheet.Range("A9:A10") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "l>r" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18 : xlRange.Font.Bold = True
        '' ComName
        xlRange = xlSheet.Range("B9:B10") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "eQµaHRkumh‘un" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        '' 
        xlRange = xlSheet.Range("C9:D9") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "viBaØabnb½RtkaMkugRtUl" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        ''
        xlRange = xlSheet.Range("C10") : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "elx" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("D10") : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "kalbriecäT" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("E9:E10") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "CAT" : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
        xlRange = xlSheet.Range("F9:F10") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "HEAD" : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10 : xlRange.Font.Bold = True
        xlRange = xlSheet.Range("G9:G10") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "elxkUd" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18 : xlRange.Font.Bold = True

        xlRange = xlSheet.Range("H9:I10") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "eQµaHTMnij" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("J9:J10") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "Ékta" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("k9:k10") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "brimaN" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("L9:L10") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "éføedIm¬duløa¦" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
        xlRange = xlSheet.Range("M9:M10") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "TisedA" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18

        xlRange = xlSheet.Range("H11") : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "GRANDTOTAL:" : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10 : xlRange.Font.Bold = True
        'Application.DoEvents()
        xlRange = xlSheet.Range("I11") : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = Me.cboProKind.Text.Trim : xlRange.Font.Name = "Limon F1" : xlRange.Font.Size = 18
        Dim LastRow As Integer = 12
        '---------Write GRANGTOTAL----
        With ListGrandTotal
            For i As Integer = 0 To .Items.Count - 1
                xlRange = xlSheet.Range("J" & (11 + i)) : xlRange.Value = .Items(i).SubItems(1).Text
                xlRange = xlSheet.Range("K" & (11 + i)) : xlRange.Value = Format(CDbl(.Items(i).SubItems(2).Text), "#,##.00")
                If i = 0 Then xlRange = xlSheet.Range("L" & (11 + i)) : xlRange.Value = Format(CDbl(.Items(i).SubItems(3).Text), "#,##.00")
                LastRow = LastRow + i

            Next
            'Application.DoEvents()
            xlRange = xlSheet.Range("A11:M" & (11 + .Items.Count))
            xlRange.Borders(Excel.XlBordersIndex.xlInsideVertical).Weight = 2
            'xlRange.Borders(Excel.XlBordersIndex.xlInsideHorizontal).Weight = 2
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = -4119
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = -4119
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = -4119
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = -4119

            xlRange = xlSheet.Range("J11:J" & (11 + .Items.Count))
            'xlRange.Borders(Excel.XlBordersIndex.xlInsideVertical).Weight = 2
            xlRange.Borders(Excel.XlBordersIndex.xlInsideHorizontal).Weight = 3
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = -4119
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = -4119
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = -4119
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = -4119
        End With
        '----------Group Country-------------------
        Dim sGroup As String = ""
        Dim Trow As Integer = 0
        Dim Re As Int32 = 0
        For Each lg As ListViewGroup In lst3.Groups
            sGroup = lg.Header
            Dim T As Boolean = False
            If sGroup.Trim = "USA".Trim Then xlRange = xlSheet.Range("B" & (LastRow)) : xlRange.Value = "shrdæGaemrik" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
            If sGroup.Trim = "EUROP".Trim Then xlRange = xlSheet.Range("B" & (LastRow)) : xlRange.Value = "shKmn¾GWr:ub" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
            If sGroup.Trim = "OTHER".Trim Then xlRange = xlSheet.Range("B" & (LastRow)) : xlRange.Value = "RbeTsepSg²" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
            'xlRange = xlSheet.Range("B" & LastRow) : xlRange.Value = sGroup : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18 : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
            Dim skip As Integer = 0
            For i As Integer = 0 To lg.Items.Count - 1
                Re = Re + 1
                ToolStripStatusLabel2.Text = " Creating report please wait .."
                lbldone.Text = " Pending on " & lg.Header
                'Label6.Text = Re
                Dim ComName As String = lg.Items(i).SubItems(1).Text.Trim
                Dim Unit As String = lg.Items(i).SubItems(9).Text.Trim
                Dim QTY As String = Format(CDbl(lg.Items(i).SubItems(10).Text.Trim), "#,#.00")
                Dim Price As String = lg.Items(i).SubItems(11).Text.Trim : If Price <> "" Then
                    Price = Format(CDbl(lg.Items(i).SubItems(11).Text.Trim), "#,##.00")
                Else
                    Price = ""
                End If
                'Application.DoEvents()
                If ComName = "" Then
                    If i = 0 Then xlRange = xlSheet.Range("I" & LastRow + i) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = Me.cboProKind.Text.Trim : xlRange.Font.Name = "Limon S1" : xlRange.Font.Size = 18
                    xlRange = xlSheet.Range("J" & LastRow + i) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = Unit : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("K" & LastRow + i) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = Format(CDbl(QTY), "#,##0.00") : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("L" & LastRow + i) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = Price : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    skip = skip + 1
                    'Application.DoEvents()
                Else
                    xlRange = xlSheet.Range("A" & LastRow + i + 1) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = i - skip + 1 : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("B" & LastRow + i + 1) : xlRange.Value = lg.Items(i).SubItems(1).Text.Trim : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("C" & LastRow + i + 1) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = lg.Items(i).SubItems(2).Text.Trim : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("D" & LastRow + i + 1) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = lg.Items(i).SubItems(3).Text.Trim : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("E" & LastRow + i + 1) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = lg.Items(i).SubItems(4).Text.Trim : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("F" & LastRow + i + 1) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = lg.Items(i).SubItems(5).Text.Trim : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("G" & LastRow + i + 1) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = lg.Items(i).SubItems(6).Text.Trim : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("H" & LastRow + i + 1) : xlRange.Value = lg.Items(i).SubItems(7).Text.Trim : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("I" & LastRow + i + 1) : xlRange.Value = lg.Items(i).SubItems(8).Text.Trim : xlRange.Font.Name = "Limon S1" : xlRange.Font.Size = 18
                    xlRange = xlSheet.Range("J" & LastRow + i + 1) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = lg.Items(i).SubItems(9).Text.Trim : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("K" & LastRow + i + 1) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = Format(CDbl(lg.Items(i).SubItems(10).Text.Trim), "#,#.00") : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("L" & LastRow + i + 1) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = Format(CDbl(lg.Items(i).SubItems(11).Text.Trim), "#,##.00") : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    xlRange = xlSheet.Range("M" & LastRow + i + 1) : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = lg.Items(i).SubItems(12).Text.Trim : xlRange.Font.Name = "Times New Roman" : xlRange.Font.Size = 10
                    'Application.DoEvents()
                End If
                BgWork.ReportProgress(CInt((100 * Re) / lst3.Items.Count))
            Next
            'Application.DoEvents()
            LastRow = LastRow + lg.Items.Count + 2
            xlRange = xlSheet.Range("A11:M" & LastRow - 1)
            xlRange.Borders(Excel.XlBordersIndex.xlInsideVertical).Weight = 2
            'xlRange.Borders(Excel.XlBordersIndex.xlInsideHorizontal).Weight = 2
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = -4119
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = -4119
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = -4119
            xlRange.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = -4119
            'BgWork.ReportProgress(CInt((100 * Re) / lst3.Items.Count))
        Next

        xlRange = xlSheet.Range("A9:M10")
        xlRange.Borders(Excel.XlBordersIndex.xlInsideVertical).Weight = 2
        xlRange.Borders(Excel.XlBordersIndex.xlInsideHorizontal).Weight = 2
        xlRange.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = -4119
        xlRange.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = -4119
        xlRange.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = -4119
        xlRange.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = -4119

        'Application.DoEvents()

        xlBook.SaveAs(FileName)
        'xlSheet = Nothing
        'xlBook.Close(False)

        'xlApp.Quit()
        xlApp.Workbooks.Open(FileName)
        xlApp.Visible = True
        MsgBox("Export Success ")
        'Me.Cursor = Cursors.Default
    Catch ex As Exception
        'Me.Cursor = Cursors.Default
        'MsgBox("Error !" & ex.Message, MsgBoxStyle.Critical)
    End Try
End Sub

Private Sub Bg_Disposed(ByVal sender As Object, ByVal e As System.EventArgs) Handles Bg.Disposed Bg.Dispose() End Sub

Private Sub BgDoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles Bg.DoWork Export2Excel(Me.lblpath.Text) End Sub

Private Sub Bg_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles Bg.ProgressChanged ProgB.Value = e.ProgressPercentage ToolStripStatusLabel4.Text = e.ProgressPercentage & "%" End Sub

Private Sub Bg_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles Bg.RunWorkerCompleted lbldone.Text = " Successful !" ToolStripStatusLabel2.Text = "Progress " TabControl1.Enabled = True Panel1.Enabled = True End Sub

It work correctly but the process is so slow , take more minutes to complete it .

pls help me to derease the process , how to faster than my code ?, that code below it is not run with Excel2007..

Best Regards ,

Nimol Men (Cambodia)

Report abuse

- Reply
5 months ago

by yf2009

YF Wong

Seri Kembangan, Malaysia

Joined 6 months ago
Hi, will try the code and let you know the result. This may take few days.

Report abuse
- Reply
5 months ago

by yf2009

YF Wong

Seri Kembangan, Malaysia

Joined 6 months ago
Hi Nimol Men, would like to suggest the below that you may want to try.
a. Open blank report insted of Add. ie. xlBook = xlApp.Workbooks.Open(reportName) instead of xlBook = xlApp.Workbooks.Add - The blank report is report with formatting required. This can be created by deleting all the data in existing report and save it as reportName. b. Export data to report (the openned blank report) with data only without formatting the cell (eg. no need to set the width and height of the cell). For example, use
```
xlSheet.Range("L1").Value = "RBHraCNacRkkmú<Ca"

instead of xlRange = xlSheet.Range("L1:M1") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "RBHraCNacRkkmú<Ca" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
```
However, this may required some changes in display of row record in display. For example, row record without border drawing.
Hope this is helpful and please do not hesitate to let me know if you have any query regarding this.
Report abuse
- Reply
5 months ago

by yf2009

YF Wong

Seri Kembangan, Malaysia

Joined 6 months ago
Hi Nimol Men, would like to suggest the below that you may want to try.
a. Open blank report insted of Add. ie. xlBook = xlApp.Workbooks.Open(reportName) instead of xlBook = xlApp.Workbooks.Add - The blank report is report with formatting required. This can be created by deleting all the data in existing report and save it as reportName. b. Export data to report (the openned blank report) with data only without formatting the cell (eg. no need to set the width and height of the cell). For example, use
```
xlSheet.Range("L1").Value = "RBHraCNacRkkmú<Ca"

instead of xlRange = xlSheet.Range("L1:M1") : xlRange.MergeCells = True : xlRange.HorizontalAlignment = 3 : xlRange.VerticalAlignment = 2 : xlRange.Value = "RBHraCNacRkkmú<Ca" : xlRange.Font.Name = "Limon R1" : xlRange.Font.Size = 18
```
However, this may required some changes in display of row record in display. For example, row record without border drawing.
Hope this is helpful and please do not hesitate to let me know if you have any query regarding this.
Report abuse
- Reply
5 months ago

by anatha1

nimol men

Sieam Reap, Cambodia

Joined 2 years ago
Yeah seem better than the old one thanks so much for take care my post na.:-) regards nimol men

Report abuse
- Reply
5 months ago

by amitbond

Amit pathak

delhi, India

Joined 5 months ago
lets i'll use your way to export in xls currently i using different way to do that

Report abuse
- Reply
5 months ago

by yf2009

YF Wong

Seri Kembangan, Malaysia

Joined 6 months ago
Hi Nimol Men, happy that can give suggestion regarding this.
Hi amitbond, can you share with us on what is your current way of doing this?
You may visit the below regarding VB. Net software application to export data to Ms. Excel. And hope that this is useful.
http://littledeveloper.blogspot.com/2009/06/vb-net-application-to-export-data-to.html http://littledeveloper.blogspot.com/2009/06/vb-net-application-to-export-data-to_20.html

Report abuse
- Reply
5 months ago

by anatha1

nimol men

Sieam Reap, Cambodia

Joined 2 years ago
Yeah , Good Ideas and helpful i c it.
export without any formatting is a good way for exporting to excel faster when our data is larger than 1000 Rows and 16 columns. However it is not the best way but it's better than nothing way to go.
Sub Export_2Excel(ByVal strFileName As String, ByVal Lv As ListView) Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass Dim wBook As Microsoft.Office.Interop.Excel.Workbook Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
```
    wBook = excel.Workbooks.Add()
    wSheet = wBook.ActiveSheet()
    With Lv
        For i As Int32 = 0 To .Items.Count - 1
            excel.Cells(10 + i, 10) = .Items(i).SubItems(1).Text
            excel.Cells(10 + i, 11) = .Items(i).SubItems(2).Text
            excel.Cells(10 + i, 12) = .Items(i).SubItems(3).Text
            excel.Cells(10 + i, 13) = .Items(i).SubItems(4).Text
        Next
    End With
    wBook.SaveAs(strFileName)
    excel.Workbooks.Open(strFileName)
    excel.Visible = True
End Sub
```
Report abuse

Post a reply

Enter your message below

Ask a question

Related discussion

High-Performance .NET Application Development & Architecture

by Manjot Bawa (0 replies)
Application failed to initialize (0xc000007b). Click OK to terminate

by anatha1 (0 replies)
want to get a know-how sms can be sent from a programming language

by erickbrower (1 replies)
VB.NET or C#?

by siddeshwar (3 replies)
VB.NET: Hide and show table using radio buttons

by converter2009 (1 replies)

Quick links

Recent activity

alex nikle replied to Free convert mpeg to wmv wi...
gfd dfg replied to Convert 3GP to video for Mac?
gfd dfg replied to Convert 3GP to video for Mac?
cenfhu ll replied to How to rip DVD, convert vid...
shirley smith replied to How to convert videos with ...
shirley smith replied to How to convert videos with ...

Community discussion forum

Help me how to fast export data from datagridview to Excel with many format cell ?

nimol men

YF Wong

nimol men

YF Wong

YF Wong

YF Wong

nimol men

Amit pathak

YF Wong

nimol men

Post a reply

Enter your message below

Related discussion

High-Performance .NET Application Development & Architecture

by Manjot Bawa (0 replies)

Application failed to initialize (0xc000007b). Click OK to terminate

by anatha1 (0 replies)

want to get a know-how sms can be sent from a programming language

by erickbrower (1 replies)

VB.NET or C#?

by siddeshwar (3 replies)

VB.NET: Hide and show table using radio buttons

by converter2009 (1 replies)

Quick links

Recent activity

Languages

Web Development

Frameworks & Architecture

Other major sections