记录vb操作Excel

zhang8790777

于 2020-12-10 16:06:45 发布

阅读量154

点赞数 1

分类专栏： vb从入门到放弃

本文链接：https://blog.csdn.net/zhang8790777/article/details/110954860

版权

vb从入门到放弃专栏收录该内容

2 篇文章 0 订阅

订阅专栏

导入对应的包就不多说了

直接上代码


            'EXCELの初期処理
            xls = CType(CreateObject("Excel.Application"), Excel.Application)
            xBook = CType(xls.Workbooks.Add, Excel.Workbook)
            xsheet = CType(xBook.Worksheets(1), Excel.Worksheet)
            excel_name = "SPPポイント残一覧_" & Format(CDate(Now.ToString), "yyyyMMddHHmmss")

            'シート名の設定
            xsheet.Name = Format(CDate(Now.ToString), "yyyy/MM/dd").Replace("/", "")

            'フォントサイズの変更
            xsheet.Range("D2:E2").Font.Size = 15
            xsheet.Range("D2:E2").Font.Bold = True
            xsheet.Range("B5:I5").Font.Size = 12
            xsheet.Range("B5:I5").Font.Bold = True

            'BackgroundColor
            xsheet.Range("B5:I5").Interior.colorindex = 15

            '行の高さ
            xsheet.Range("2:2").RowHeight = 30
            xsheet.Range("5:5").RowHeight = 16

            '列の幅
            xsheet.Range("A:A").ColumnWidth = 5
            xsheet.Range("B:B").ColumnWidth = 30
            xsheet.Range("C:C").ColumnWidth = 11
            xsheet.Range("D:D").ColumnWidth = 30
            xsheet.Range("E:E").ColumnWidth = 11
            xsheet.Range("F:F").ColumnWidth = 13
            xsheet.Range("G:G").ColumnWidth = 11
            xsheet.Range("H:H").ColumnWidth = 11
            xsheet.Range("I:I").ColumnWidth = 11
            xsheet.Range("J:J").ColumnWidth = 5

            'Border
            SetCellsBorder("B5", "B5")
            SetCellsBorder("C5", "C5")
            SetCellsBorder("D5", "D5")
            SetCellsBorder("E5", "E5")
            SetCellsBorder("F5", "F5")
            SetCellsBorder("G5", "G5")
            SetCellsBorder("H5", "H5")
            SetCellsBorder("I5", "I5")

            Dim cnt As Integer = 5

            '金額の書式設定
            xsheet.Range("F:G").NumberFormatLocal = "#,##0;[赤]-#,##0"

            '日付の書式設定
            xsheet.Range("H:I").NumberFormatLocal = "yyyy/mm/dd"

            '表の頭
            xsheet.Cells(2, 4) = "SPP ポイント残確認一覧表"
            xsheet.Cells(3, 8) = Format(CDate(Now.ToString), "yyyy/MM/dd")
            xsheet.Cells(3, 9) = "現在"
            '列タイトル
            xsheet.Cells(cnt, 2) = "サービスタイプ"
            xsheet.Cells(cnt, 3) = "顧客CD"
            xsheet.Cells(cnt, 4) = "顧客名"
            xsheet.Cells(cnt, 5) = "担当者"
            xsheet.Cells(cnt, 6) = "残ポイント額"
            xsheet.Cells(cnt, 7) = "運用予定額"
            xsheet.Cells(cnt, 8) = "最終入金日"
            xsheet.Cells(cnt, 9) = "最終稼働日"
            cnt = cnt + 1
 			'居中
            xsheet.Range("E" & cnt & ":E" & cnt).HorizontalAlignment = 3
             xsheet.Range("H" & cnt & ":i" & cnt).HorizontalAlignment = 3
             '居左
             xsheet.Range("C" & cnt & ":C" & cnt).HorizontalAlignment = 2
             //这里是循环数据结果集
 			Do Until rs.EOF
 			service_name_temp = rs.Fields("ko_nam").Value
 			ko_cd_temp =""
 			//部分字段省略
 			//这里是循环数据结果集
             If service_name_temp <> service_name_temp_r Then
                 xsheet.Cells(cnt, 2) = service_name_temp
             Else
                 xsheet.Cells(cnt, 2) = ""
             End If
             SetCellsBorder("B" & cnt, "B" & cnt)
             xsheet.Cells(cnt, 3) = ko_cd_temp
             SetCellsBorder("C" & cnt, "C" & cnt)
             xsheet.Cells(cnt, 4) = ko_nam_temp
             SetCellsBorder("D" & cnt, "D" & cnt)
             xsheet.Cells(cnt, 5) = eigyo_tanto_name_temp
             SetCellsBorder("E" & cnt, "E" & cnt)
             If remnant_sum <> 0 Then
                 xsheet.Cells(cnt, 6) = remnant_sum
             Else
                 xsheet.Cells(cnt, 6) = ""
             End If
             SetCellsBorder("F" & cnt, "F" & cnt)
             If sa_intro_teate <> 0 Then
                 xsheet.Cells(cnt, 7) = sa_intro_teate
             Else
                 xsheet.Cells(cnt, 7) = ""
             End If
             SetCellsBorder("G" & cnt, "G" & cnt)
             xsheet.Cells(cnt, 8) = nyukin_ymd
             SetCellsBorder("H" & cnt, "H" & cnt)
             xsheet.Cells(cnt, 9) = sy_sagyo_ymd_temp
             SetCellsBorder("I" & cnt, "I" & cnt)


             cnt = cnt + 1
             service_name_temp_r = service_name_temp
             rs.MoveNext()
            Loop
            
 			If Not (System.IO.Directory.Exists(outputDir)) Then
                MkDir(outputDir)
            End If

            'EXCELの保存
            xBook.SaveAs(outputDir & "\" & excel_name & ".xls")


            xBook.close()
            xls.quit()
            xBook = Nothing
            xls = Nothing