Exit Function
    End If
    
    Dim excelSheet As New Excel.Worksheet
    Dim i As Long
    Dim L As Long
    
    Set excelSheet = sheet
    Dim col, cols, rows, row As Integer
    row = dtlRow
    col = 0     ' 起始列
    rows = rs.RecordCount + 1
    cols = rs.Fields.Count
    If sequenceName <> "" Then
        col = col + 1   ' 起始列
        cols = cols + 1
        excelSheet.Cells(dtlRow, col) = sequenceName & ""       ' 輸出序號(hào)標(biāo)題
    End If
    
'  輸出各列的標(biāo)題
    For i = 1 To rs.Fields.Count
        excelSheet.Cells(dtlRow, i + col) = CStr(captionArray(i - 1)) & ""
    Next i
'   輸出各行數(shù)據(jù)
    row = 0
    Do While Not rs.EOF
        row = row + 1
        If sequenceName <> "" Then
            excelSheet.Cells(dtlRow + row, col) = CStr(row) & ""
        End If
        For i = 1 To rs.Fields.Count
            excelSheet.Cells(dtlRow + row, i + col) = CStr(rs.Fields(i - 1)) & ""
        Next i
        rs.MoveNext
    Loop
' 開始畫邊框線條，先選擇要畫邊框的區(qū)域
    Dim range As Excel.range
    Set range = excelSheet.range(getExcelCellArea(1, CLng(dtlRow)) & ":" & getExcelCellArea(CLng(cols), CLng(dtlRow + rs.RecordCount)))
    drawBordersLine range
' 畫邊框線條結(jié)束
    Set sqlDataToExcel = excelSheet
End Function
' 設(shè)置表頭報(bào)表名稱
Public Sub setRangeFormat(range As Excel.range, cellValue As String, fontBold As Boolean, fontSize As Integer, hAlign As Integer)
    range.MergeCells = True
    range.Value = cellValue
    range.Font.Bold = fontBold
    range.Font.Size = fontSize
    range.HorizontalAlignment = hAlign
End Sub
' 設(shè)置表頭公司名稱
Public Sub setCompanyNameOfReport(range As Excel.range)
    range.MergeCells = True
    range.Value = g_companyName
    range.Font.Bold = True
    range.Font.Size = 16
    range.HorizontalAlignment = xlCenter
End Sub
' 將結(jié)果集中的數(shù)據(jù)輸出到Excel Sheet中，從Excel中的第dtlRow行開始填充String中的數(shù)據(jù)；
'sql是沒有排序的數(shù)據(jù)源，rs已經(jīng)排序
Public Function sqlDataTo6ColsExcel(rs As Recordset, dtlRow As Integer, _
        sheet As Excel.Worksheet, sql As String) As Excel.Worksheet
    If rs.RecordCount = 0 Then
        Set sqlDataTo6ColsExcel = Null
        Exit Function
    End If
    Dim captionArray
    Dim productId As String
    Dim range As Excel.range
    Dim rsDtl As Recordset
    Dim excelSheet As New Excel.Worksheet
    Dim i As Long
'    Dim cols As Long
    Dim rows As Long
    Dim row As Long
    Dim borderStartRow As Long
    Dim borderEndRow As Long
    Dim rowsPerProduct As Long
    Dim strGroupContent As String
    Dim productSN As Integer '  第幾個(gè)產(chǎn)品
    captionArray = Array("編號(hào)", "凈 重", "編號(hào)", "凈 重", "編號(hào)", "凈 重", "編號(hào)", "凈 重", "編號(hào)", "凈 重", "編號(hào)", "凈 重")
    Set excelSheet = sheet
    row = dtlRow   '當(dāng)前行號(hào)
    productId = ""
    productSN = 0
    Do While Not rs.EOF
    '  根據(jù)不同的產(chǎn)品編號(hào)輸出表格
        productSN = productSN + 1
        row = row + 1
        productId = Trim(CStr(rs.Fields("productId")))
    '  如果不是第一個(gè)客戶的版本就按照編號(hào)排序
        Set rsDtl = g_db.OpenRecordset(sql + "  and D.productId=" + productId + " order by D.billId,CINT(D.rsvFld1)")
        Dim strSpace As String
        strSpace = Space(4)
        If g_outputSkinWeight = True Then
            strGroupContent = "型號(hào)：" + CStr(rs.Fields("productModel")) + strSpace _
                            & "規(guī)格(mm)：" + CStr(rs.Fields("productSpecs")) + strSpace _
                            & "凈重(KG)：" + Format(rs.Fields("netWeight"), g_barcode_weight_scale) + strSpace _
                            & "毛重(KG)：" + Format(rs.Fields("ttlWeight"), g_barcode_weight_scale) + strSpace _
                            & "箱/件數(shù)：" + CStr(rs.Fields("amount"))
        Else
            strGroupContent = "型號(hào)：" + CStr(rs.Fields("productModel")) + strSpace _
                            & "規(guī)格(mm)：" + CStr(rs.Fields("productSpecs")) + strSpace _
                            & "凈重(KG)：" + Format(rs.Fields("netWeight"), g_barcode_weight_scale) + strSpace _
                            & "箱/件數(shù)：" + CStr(rs.Fields("amount"))
        End If
        Set range = sheet.range(getExcelCellArea(1, row) & ":" & getExcelCellArea(CInt(g_billColCount), row))
        setRangeFormat range, strGroupContent, True, 11, Excel.Constants.xlCenter
' ==================================================  '
        '  另起一行 輸出各列的標(biāo)題
        row = row + 1
        borderStartRow = row
        For i = 1 To g_billColCount
            excelSheet.Cells(row, i) = CStr(captionArray(i - 1)) & ""
'            If (i Mod 2) = 0 Then
'               excelSheet.Cells(row, i).Font.Bold = True
'                excelSheet.Cells(row, i).Font.Color = &HFF0000
'            End If
        Next i
        Set range = sheet.range(getExcelCellArea(1, row) & ":" & getExcelCellArea(CInt(g_billColCount), row))
        range.Font.Size = 11
        
        Dim sequenceNo As Integer
        Dim col As Integer
        sequenceNo = 0
        row = row + 1
        Do While Not rsDtl.EOF
            sequenceNo = sequenceNo + 1
            col = sequenceNo Mod (g_billColCount / 2)
            If col = 0 Then
                col = g_billColCount / 2
            End If
            Set range = sheet.range(getExcelCellArea(2 * col - 1, row + CLng((sequenceNo - 1) \ (g_billColCount / 2))) & ":" & getExcelCellArea(2 * col - 1, row + CLng((sequenceNo - 1) \ (g_billColCount / 2))))
            range.NumberFormatLocal = "0_ "
'            setRangeFormat range, CStr(sequenceNo), False, 11, Excel.Constants.xlRight
            setRangeFormat range, rsDtl.Fields("rsvFld1"), False, 11, Excel.Constants.xlRight
            Set range = sheet.range(getExcelCellArea(2 * col, row + CLng((sequenceNo - 1) \ (g_billColCount / 2))) & ":" & getExcelCellArea(2 * col, row + CLng((sequenceNo - 1) \ (g_billColCount / 2))))
            range.NumberFormatLocal = g_barcode_weight_scale + "_ "
            setRangeFormat range, rsDtl.Fields("netWeight"), False, 11, Excel.Constants.xlRight
            range.Font.Bold = True
'            range.Font.Color = &HFF0000
            rsDtl.MoveNext
        Loop
        '  每個(gè)產(chǎn)品輸出到Excel中所占的行數(shù)
        rowsPerProduct = CLng(rsDtl.RecordCount \ (g_billColCount / 2))
        If rsDtl.RecordCount Mod (g_billColCount / 2) <> 0 Then
            rowsPerProduct = rowsPerProduct + 1
        End If
        borderEndRow = borderStartRow + rowsPerProduct
        row = row + rowsPerProduct
        
        ' 畫邊框線
        Set range = excelSheet.range(getExcelCellArea(1, borderStartRow) & ":" & getExcelCellArea(CLng(g_billColCount), borderEndRow))
        drawBordersLine range
        
        rs.MoveNext
    Loop

    dtlRow = row
    g_curMaxOutputRow = row - 1
    Set sqlDataTo6ColsExcel = excelSheet
End Function
' 將sql結(jié)果集中的數(shù)據(jù)(僅用于累計(jì)項(xiàng))輸出到Excel Sheet中，從Excel中的第dtlRow行開始填充String中的數(shù)據(jù)
' 僅僅適用于第二個(gè)客戶，針對(duì)第二個(gè)客戶表單要求定制，rs中的最后兩列不輸出，最后一列僅作排序用，不在報(bào)表中輸出
Public Function sqlTotalDataToExcel_2nd(rs As Recordset, captionArray, _
    dtlRow As Integer, sheet As Excel.Worksheet) As Excel.Worksheet
    If rs.RecordCount = 0 Then
        Set sqlTotalDataToExcel_2nd = Null
        Exit Function
    End If
   
    Dim excelSheet As New Excel.Worksheet
    Dim range As Excel.range
    Dim i As Long
    Dim L As Long
    Dim quotiety As Double
    quotiety = 1.5
    
    Set excelSheet = sheet
    Dim col, cols, rows, row As Integer
    row = dtlRow
    col = 0     ' 起始列
    rows = rs.RecordCount + 1
    cols = UBound(captionArray) + 1   'rs.Fields.Count - 1
   Dim hAlign As Integer
   hAlign = Excel.Constants.xlCenter
   
'  輸出各列的標(biāo)題
    For i = 1 To cols
        Set range = sheet.range(getExcelCellArea(3 * (i - 1) + 1, CLng(dtlRow)) & ":" & getExcelCellArea(3 * i, CLng(dtlRow)))
        If i > 2 Then
            hAlign = Excel.Constants.xlRight
        End If
        setRangeFormat range, CStr(captionArray(i - 1)), False, 11, hAlign
        range.Font.Size = range.Font.Size * quotiety
    Next i
'   輸出各行數(shù)據(jù)
    row = 0
    Do While Not rs.EOF
        row = row + 1
        hAlign = Excel.Constants.xlCenter
        For i = 1 To cols
            If i > 2 Then
                hAlign = Excel.Constants.xlRight
            End If
            Set range = sheet.range(getExcelCellArea(3 * (i - 1) + 1, CLng(dtlRow + row)) & ":" & getExcelCellArea(3 * i, CLng(dtlRow + row)))
            setRangeFormat range, CStr(rs.Fields(i - 1)), False, 11, hAlign
            range.Font.Size = range.Font.Size * quotiety
            If i = 3 Then
                range.NumberFormatLocal = g_barcode_weight_scale + "_ "
            End If
            If i = 4 Then
                range.NumberFormatLocal = "0_ "
            End If
        Next i
        rs.MoveNext
    Loop
    Set range = sheet.range(getExcelCellArea(1, CLng(dtlRow + rs.RecordCount + 1)) & ":" & getExcelCellArea(6, CLng(dtlRow + rs.RecordCount + 1)))
    setRangeFormat range, "合     計(jì)", True, 11, Excel.Constants.xlCenter
    range.Font.Size = range.Font.Size * quotiety
    Set range = sheet.range(getExcelCellArea(7, CLng(dtlRow + rs.RecordCount + 1)) & ":" & getExcelCellArea(9, CLng(dtlRow + rs.RecordCount + 1)))
    setRangeFormat range, "0.00", True, 11, Excel.Constants.xlRight
    range.Font.Size = range.Font.Size * quotiety
    range.FormulaR1C1 = "=SUM(R[-" + CStr(rs.RecordCount) + "]C:R[-1]C)"
    range.NumberFormatLocal = g_barcode_weight_scale + "_ "
    
    Set range = sheet.range(getExcelCellArea(10, CLng(dtlRow + rs.RecordCount + 1)) & ":" & getExcelCellArea(12, CLng(dtlRow + rs.RecordCount + 1)))
    setRangeFormat range, "0.00", True, 11, Excel.Constants.xlRight
    range.Font.Size = range.Font.Size * quotiety
    range.FormulaR1C1 = "=SUM(R[-" + CStr(rs.RecordCount) + "]C:R[-1]C)"
    range.NumberFormatLocal = "0_ "
' 開始畫邊框線條，先選擇要畫邊框的區(qū)域
    Set range = excelSheet.range(getExcelCellArea(1, CLng(dtlRow)) & ":" & getExcelCellArea(CLng(cols * 3), CLng(dtlRow + rs.RecordCount + 1)))
    drawBordersLine range
' 畫邊框線條結(jié)束
    Set sqlTotalDataToExcel_2nd = excelSheet
End Function
'   根據(jù)單據(jù)ID獲取最大箱號(hào):isIncome 為true表示入庫(kù)，false表示出庫(kù)
Public Function getPrevBillMaxBoxNo(billNo As String, isIncome As Boolean) As Integer
    Dim rs As Recordset
    Dim sql As String
    If isIncome Then
        sql = "SELECT MAX(CINT(rsvFld1)) as boxNo FROM hpos_StockIncomeBill_Dtl WHERE billId in(SELECT DISTINCT billId FROM hpos_StockIncomeBill_Master WHERE billId='" + billNo + "' )"
    Else
        sql = "SELECT MAX(CINT(rsvFld1)) as boxNo FROM hpos_StockOutBill_Dtl WHERE billId in(SELECT DISTINCT billId FROM hpos_StockOutBill_Master WHERE billId='" + billNo + "' )"
    End If
    Set rs = g_db.OpenRecordset(sql)
    If rs.RecordCount <> 1 Or IsNull(rs.Fields("boxNo")) Then
        getPrevBillMaxBoxNo = 0     ' 默認(rèn)為0
    Else
        getPrevBillMaxBoxNo = rs.Fields("boxNo")
    End If
End Function

'   設(shè)置MSFlexGrid的序號(hào)(即業(yè)務(wù)箱號(hào)):   startNo-起始序號(hào)；snCol－序號(hào)(即業(yè)務(wù)箱號(hào)的列號(hào)
Public Sub setGridSequence(grd As MSFlexGrid, startNo As Integer, snCol As Integer)
    Dim i As Integer
     For i = grd.FixedRows To grd.rows - grd.FixedRows
         grd.TextMatrix(i, snCol) = i - grd.FixedRows + startNo
     Next i
End Sub

'   將源表格srcFlxGrd中的數(shù)據(jù)追加到目標(biāo)表格desFlxGrd中
Public Sub appendData(srcFlxGrd As MSFlexGrid, desFlxGrd As MSFlexGrid)
'   獲取目標(biāo)表格的起始行號(hào)：從該行開始追加數(shù)據(jù)
    Dim i, j, startRow As Integer
    startRow = desFlxGrd.rows - 1
    For i = desFlxGrd.rows - 1 To desFlxGrd.FixedRows Step -1
        '   如果條形碼為空則起始行號(hào)減去1，否則退出循環(huán)
        If Trim(desFlxGrd.TextMatrix(i, 1)) = "" Then
            startRow = startRow - 1
        Else
            Exit For
        End If
    Next
    startRow = startRow + 1
    '   開始將源表格srcFlxGrd中的數(shù)據(jù)追加到目標(biāo)表格desFlxGrd中
    For i = srcFlxGrd.FixedRows To srcFlxGrd.rows - 1
        If Trim(srcFlxGrd.TextMatrix(i, 1)) <> "" Then
'            Dim sequenceNo As String
'            sequenceNo = desFlxGrd.TextMatrix(startRow + i - srcFlxGrd.FixedRows, 0)
'            desFlxGrd.TextMatrix(startRow + i - srcFlxGrd.FixedRows, 0) = startRow + i - srcFlxGrd.FixedRows
            desFlxGrd.TextMatrix(startRow + i - srcFlxGrd.FixedRows, 0) = startRow + i - srcFlxGrd.FixedRows
            
            For j = 1 To srcFlxGrd.cols - 1
                desFlxGrd.TextMatrix(startRow + i - srcFlxGrd.FixedRows, j) = srcFlxGrd.TextMatrix(i, j)
            Next
            desFlxGrd.rows = desFlxGrd.rows + 1