2020年6月9日 星期二

vb.net 寫入Excel並製作Chart [Write to Excel and Create Chart] 


  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
    Private Function ExportXLSG_LHH() As Boolean
        Try
            pgbExport.Value = 0
            pgbExport.Maximum = Val(frmScreenChart.lvwScreenChart.Items.Count + 1) * Val(frmScreenChart.lvwScreenChart.Columns.Count)

            Dim xls As New Excel.Application
            Dim book As Excel.Workbook
            Dim sheet As Excel.Worksheet
            'create a workbook and get reference to first worksheet
            xls.Workbooks.Add()
            book = xls.ActiveWorkbook
            sheet = book.ActiveSheet
            'step through rows and columns and copy data to worksheet
            Dim row As Integer = 1
            Dim col As Integer = 1

            If cboSize.Text = "Letter" And cboOrientation.Text = "Vertical" Then
                sheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperLetter
                sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
            ElseIf cboSize.Text = "Letter" And cboOrientation.Text = "Horizontal" Then
                sheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperLetter
                sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            ElseIf cboSize.Text = "A4" And cboOrientation.Text = "Vertical" Then
                sheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
                sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
            ElseIf cboSize.Text = "A4" And cboOrientation.Text = "Horizontal" Then
                sheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
                sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            End If

            sheet.PageSetup.TopMargin = Val(nudTop.Value)
            sheet.PageSetup.BottomMargin = Val(nudDown.Value)
            sheet.PageSetup.LeftMargin = Val(nudLeft.Value)
            sheet.PageSetup.RightMargin = Val(nudRight.Value)

            sheet.Range(sheet.Cells(1, 1), sheet.Cells(1, frmScreenChart.lvwScreenChart.Columns.Count)).Merge()
            sheet.Cells(row, col) = frmScreenChart.Text
            sheet.Cells(row, col).HorizontalAlignment = Excel.Constants.xlCenter
            sheet.Cells(row, col).Font.Size = 20
            sheet.Cells(row, col).EntireColumn.AutoFit()

            row += 2
            col = 1

            sheet.Columns("A:B").ColumnWidth = 15

            For i As Integer = 1 To frmScreenChart.lvwScreenChart.Columns.Count
                sheet.Cells(row, col) = frmScreenChart.lvwScreenChart.Columns.Item(i - 1).Text
                sheet.Cells(row, col).HorizontalAlignment = Excel.Constants.xlCenter
                'sheet.Cells(row, col).EntireColumn.AutoFit()
                sheet.Cells(row, col).Interior.ColorIndex = 48
                pgbExport.Value = Val(pgbExport.Value) + 1
                col = col + 1
            Next
            row += 1
            col = 1

            For Each item As ListViewItem In frmScreenChart.lvwScreenChart.Items
                If item.BackColor = Drawing.Color.SkyBlue Then
                    For i As Integer = 0 To item.SubItems.Count - 1
                        sheet.Cells(row, col) = item.SubItems(i).Text
                        'sheet.Cells(row, col).EntireColumn.AutoFit()
                        sheet.Cells(row, col).Interior.ColorIndex = 48
                        col = col + 1
                        pgbExport.Value = Val(pgbExport.Value) + 1
                    Next
                Else
                    For i As Integer = 0 To item.SubItems.Count - 1
                        sheet.Cells(row, col) = item.SubItems(i).Text
                        'sheet.Cells(row, col).EntireColumn.AutoFit()
                        col = col + 1
                        pgbExport.Value = Val(pgbExport.Value) + 1
                    Next
                End If
                row += 1
                col = 1
            Next

            With sheet.Range(sheet.Cells(3, 1), sheet.Cells(frmScreenChart.lvwScreenChart.Items.Count + 3, frmScreenChart.lvwScreenChart.Columns.Count))
                With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                    .LineStyle = Excel.XlLineStyle.xlDouble
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideVertical)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
            End With

            Dim chartPage As Excel.Chart
            Dim xlCharts As Excel.ChartObjects
            Dim myChart As Excel.ChartObject
            Dim chartRange As Excel.Range

            xlCharts = sheet.ChartObjects
            myChart = xlCharts.Add(200, 10, 600, row * 15)
            chartPage = myChart.Chart
            chartRange = sheet.Range(sheet.Cells(4, 1), sheet.Cells(row - 1, 2))
            'chartPage.SetSourceData(chartRange, Excel.XlRowCol.xlColumns) '切換欄列
            chartPage.SetSourceData(Source:=chartRange)
            chartPage.ChartType = Excel.XlChartType.xlColumnClustered

            'save the workbook and clean up
            sheet.PageSetup.RightFooter = "-" & frmScreenChart.Text & "- generado por ""Corporation Inventory Management"""
            sheet.PageSetup.RightHeader = "Fecha generado: " & DateTime.Now

            pgbExport.Value = pgbExport.Maximum

            Dim folderPath As String = txtDir.Text & "\"
            If Not Directory.Exists(folderPath) Then
                Directory.CreateDirectory(folderPath)
            End If

            book.SaveAs(folderPath & txtFile.Text & ".xlsx")
            xls.Workbooks.Close()
            xls.Quit()
            releaseObject(sheet)
            releaseObject(book)
            releaseObject(xls)
            Return True
        Catch ex As Exception
            MsgBox("Error Desconocido", 16, "Error")
            Return False
        End Try
    End Function
於
標籤： , , , ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)