.NET
- C#
- VB.NET
- F#
- Azure
- ASP.NET
  - ASP.NET AJAX
  - ASP.NET MVC
- LINQ
- ADO.NET
Java
Open Source
Mobile
Database
Architecture
RIA & Web
Toolbox

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

Last post was 23 Jun 2009 at 15:38

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

- Reply
11 years ago

by anatha1

nimol men

Sieam Reap, Cambodia

Joined 13 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)

Report abuse
- Reply
11 years ago

by yf2009

YF Wong

Seri Kembangan, Malaysia

Joined 11 years 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

Reply

11 years ago

by

anatha1

nimol men

Sieam Reap, Cambodia

Joined 13 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
11 years ago

by yf2009

YF Wong

Seri Kembangan, Malaysia

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

Report abuse
- Reply
11 years ago

by yf2009

YF Wong

Seri Kembangan, Malaysia

Joined 11 years 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
11 years ago

by yf2009

YF Wong

Seri Kembangan, Malaysia

Joined 11 years 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
11 years ago

by anatha1

nimol men

Sieam Reap, Cambodia

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

Report abuse
- Reply
11 years ago

by amitbond

Amit pathak

delhi, India

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

Report abuse
- Reply
11 years ago

by yf2009

YF Wong

Seri Kembangan, Malaysia

Joined 11 years 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
11 years ago

by anatha1

nimol men

Sieam Reap, Cambodia

Joined 13 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

Sign in or Join us (it's free).

C# forum discussion

USB Drive Activity Logger!

by coque0912 (7 replies)
Exporting data from a repeater to an Excel sheet

by TomBlack (28 replies)
Calculate Date of Birth from Age

by k.prashanth (7 replies)
Windows Service, getting outOf memory exception

by programmerdude2341 (3 replies)
How to Make Default Button?

by puspitaonlinebooks (24 replies)

C# tutorials

Quick links

Recent activity

arif ahmad replied to How to receive data in web ...
William Thompson replied to What is the name of the Win...
Sameera Piyadigamage replied to Point of Sale Developers: H...
Scott Carline replied to 4 x C# Developers for large...
Rajendra Dhakal replied to Restore SQL Server text dat...
cloud rainda replied to How to convert between TS f...

Managed hosting by Everycity

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

C# forum discussion

USB Drive Activity Logger!

by coque0912 (7 replies)

Exporting data from a repeater to an Excel sheet

by TomBlack (28 replies)

Calculate Date of Birth from Age

by k.prashanth (7 replies)

Windows Service, getting outOf memory exception

by programmerdude2341 (3 replies)

How to Make Default Button?

by puspitaonlinebooks (24 replies)

C# tutorials

Quick links

Recent activity

Contribute

Web Development

Developer Jobs

Our tools