操作excel表，在某一行后面添加多行(添加的是拷贝的带有格式行)

提前引入

Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
Imports HeianSeisaku.SystemConst
Imports Microsoft.Office.Interop.Excel
Imports DataTable = System.Data.DataTable

具体代码

        ' 选中并拷贝B9整行
        Dim sourceRange As Range = worksheet.Range("B9").EntireRow
        ' 行全体をコピーする 拷贝B9整行
        sourceRange.Copy()
        '设置要添加的行的范围，这里是从 B9后面的B10开始，添加的行是dt里面数据的行数
        Dim targetRange As Range = worksheet.Range($"B{9 + 1}:B{10 + (dt.Rows.Count() - 2)}").EntireRow
        '在设置好的范围添加以B9行为模板的行，是添加！！！不是覆盖
        targetRange.Insert(XlInsertShiftDirection.xlShiftDown)
        '清空粘贴板里的内容，方便下次复制
        excelApp.CutCopyMode = False

完整代码

Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
Imports HeianSeisaku.SystemConst
Imports Microsoft.Office.Interop.Excel
Imports DataTable = System.Data.DataTable

    ''' <summary>
    ''' データ書き込み
    ''' </summary>
    ''' <param name="excelApp"></param>
    ''' <param name="worksheet"></param>
    ''' <param name="range"></param>
    ''' <param name="nichiji"></param>
    ''' <param name="dt"></param>
    Private Sub WriteData(ByRef excelApp As Application, ByRef worksheet As Worksheet, ByRef range As Range, nichiji As String, dt As DataTable)

        ' 工番でグループ
        Dim groups = dt.Rows.OfType(Of DataRow).GroupBy(Function(p) p.Field(Of Integer)("KUBUN"))
        For index = 1 To 3
            Dim dateByKubun = groups.FirstOrDefault(Function(g) g.Key = index)

            Dim shiireKingakuKubun As Integer? = If(dateByKubun IsNot Nothing, dateByKubun.Sum(Function(row) row.Field(Of Integer?)("SHIIRE_KINGAKU")), 0)
            Dim shukkaKingakuKubun As Integer? = If(dateByKubun IsNot Nothing, dateByKubun.Sum(Function(row) row.Field(Of Integer?)("SHUKKA_KINGAKU")), 0)

        Next

        Dim detailColCount As Integer = 7
        Dim data(0 To dt.Rows.Count() - 1, 0 To detailColCount - 1) As Object

        Dim rowIdx As Integer = 0
        Dim colIdx As Integer = 0

        For Each dr As DataRow In dt.Rows
            colIdx += 1
            data(rowIdx, colIdx) = dr.Field(Of Integer?)("SHUKKA_KINGAKU")

            rowIdx += 1
        Next

        Dim sourceRange As Range = worksheet.Range("B9").EntireRow
        ' 行全体をコピーする
        sourceRange.Copy()
        'dt行分の範囲を設定する
        Dim targetRange As Range = worksheet.Range($"B{9 + 1}:B{10 + (dt.Rows.Count() - 2)}").EntireRow
        targetRange.Insert(XlInsertShiftDirection.xlShiftDown)

        excelApp.CutCopyMode = False

        ' 明細データの出力箇所
        Dim range1 As Range = worksheet.Range("B9")

        ' 明細データ出力
        range1.Resize(dt.Rows.Count(), detailColCount).Value = data
    End Sub