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

vb.net , csharp , java , sql Mesquite, United States
  • 9 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)

  • 9 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)?

  • 9 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)

  • 9 years ago

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

  • 9 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.

  • 9 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.

  • 9 years ago

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

  • 9 years ago

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

  • 8 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

  • 8 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
    

Post a reply

Enter your message below

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

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The generation of random numbers is too important to be left to chance.” - Robert R. Coveyou