Dim Book As New XLS.Book(ProjectPath & "Attachments\\德国发票分组.xls")
Dim lastrow As Long
\'Book.Marks.Add("订单号",s)
Dim sm As String
Dim sms As List(Of String)
    sms = DataTables("Invoice3").GetValues("pksm")
       For i As Integer = 0 To sms.Count - 1
        sm = sm & chr(10) & sms(i)
    Next
   
    sm= sm.Trim(chr(10))
Book.Marks.Add("pksm",sm)
Book.Build()
Dim w As String = Tables("Invoice1").Current("Invoiceno")
w = w.Replace("/","-")

Book.Save(ProjectPath & "Reports\\发票1\\" & w & ".xls")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
\'Dim sm As String = Tables("pkl1").Current("pksm")
\'If Tables("pkl1").Current.Isnull("pksm") =True Then
\'Sheet.Rows(12).Visible = False
\'End If
\'If Tables("pkl1").Current.Isnull("rem") =True Then
\'Sheet.Rows(Sheet.Rows.Count-4).Visible = False
\'End If

\'With Sheet.PrintSetting
    \'.Footer = "&R( 文件:&f )...Page &P Of &N"
\'End With

\'With Sheet.PrintSetting
    \'.Footer = "&LP.&P Of &N"
\'End With
With Sheet.PrintSetting
    .Header = "&C&U&""Arial Narrow""&14 INVOICE NO. " & w & ""
End With

Book.Save(ProjectPath & "Reports\\发票1\\" & w & ".xls")

Dim App As New MSExcel.Application
App.Visible = True
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open(ProjectPath & "Reports\\发票1\\" & w & ".xls")
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
Dim range As MSExcel.Range

\'Wb.Save
\'\'Dim rng As Integer
lastrow = Ws.usedrange.rows.count
\'If Tables("pkl1").Current.Isnull("pksm") =False Then
\'range = Ws.Range("B13")
\'range.EntireRow.AutoFit
\'End If
\'\'Dim dt As Table = Tables("Invoice2")

\'\'If dt.rows(0).Isnull("pksm") = False Then
\'\'Ws.Range("B" & 14).EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown)\'在基准单元格上面插入一行
\'\'Ws.Range("B" & 14).Value =dt.rows(0)("pksm")
\'\'Ws.Range("B" & 14).HorizontalAlignment = MSExcel.Constants.xlLeft
\'\'End If
Dim n1 As Integer = lastrow
Dim n2 As Integer = 0
\'MessageBox.Show("od:" & n1)
\'\'Dim c As Integer = 0
For i As Integer = 21 To n1
If Ws.Range("F" & i).Value Like "ORDER*" Then
n2 = n2 +1
End If
Next
For i As Integer = 22 To n1+n2
   
    \'\'If dt.rows(i-14).Isnull("pksm") = False Then
    \'\'c=c+1
    \'\' Ws.Range("B" & i+c).EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown)\'在基准单元格上面插入一行
    \'\' Ws.Range("B" & i+c).Value =dt.rows(i-14)("pksm")
    \'\'Ws.Range("B" & i+c).HorizontalAlignment = MSExcel.Constants.xlLeft
    \'\' End If
    If Ws.Range("F" & i).Value Like "ORDER*" Then
        \'c=c-1
        Ws.Range("C" & i).EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown)\'在基准单元格上面插入一行
        i = i+1
    End If
    \'\'n1 = n1+1
Next
lastrow = Ws.usedrange.rows.count - 1
\'MessageBox.Show("od:" & lastrow)
range = Ws.Range("A"& lastrow)
Dim rs As String = Range.Value
Dim Values() As String
Values = rs.split(chr(13) & chr(10))
\'MessageBox.Show("od:" & Values.Length)
range.EntireRow.RowHeight = Values.Length*18

\'\'MessageBox.Show("od:" & n1)
\'\'lastrow = Ws.usedrange.rows.count-3

\'\'rng = ws.range(Ws.cells(14,1),Ws.cells(lastrow-1,1)).entirerow.autofit

\'\'If Tables("pkl1").Current.Isnull("rem") =False Then
\'\'range = Ws.Range("A"& lastrow)
\'range.EntireRow.AutoFit
\'\'Dim rs As String =Ws.Range("A"& lastrow).Value
\'\'Dim Values() As String

\'\'Values = rs.split(chr(13) & chr(10))
\'\'range.EntireRow.RowHeight=Values.Length*18

\'Ws.Range("Q"& lastrow).Value = Values.Length
\'\'End If

\'\'Dim n As Integer = lastrow-1
\'\'For i As Integer = 14 To n
\'\'range = Ws.Range("A"& i)
\'\'Dim Chr As MSExcel.Characters = range.Characters(5,15)
\'\'With Chr.Font
\'\'.Name = "Calibri" \'字体
\'\' .Size = 7 \'字号
\'\' \'.Bold = True   \'加粗
\'\'  \'.Italic = True \'斜体
\'\'  \'.ColorIndex = 3\'颜色
\'\'End With
\'\'Next

With Ws.PageSetup
    .PrintTitleRows = Ws.Rows("1:19").Address
    \'.CenterHeader = "&20"
End With