excle报表

 Public Sub exle(ByVal ds As System.Data.DataSet)

        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        xlApp = New Excel.Application
        '-----------------------------------
        Dim IBord As Integer = 0
        '右上标题
        Dim StrRTitle As String = ""
        '主标题
        Dim Middle As String = ""
        Dim Nmiddle As String = ""
        Dim txtCotant As String = "    "
        Dim dtAgreeMent As New DataTable
        Dim dtPay As New DataTable
        Dim dtDal As New DataTable
        Dim txtContant1 As String = ""
        Dim strCount As String = ""
        dtAgreeMent = ds.Tables("EQP_PAY_AGREEMENT")
        dtPay = ds.Tables("EQP_PAY_WAY")
        dtDal = ds.Tables("EQP_PAY_DTL")
        If ds.Tables("EQP_PAY_AGREEMENT").Rows.Count > 0 Then
            Middle = "关于" + dtAgreeMent.Rows(0)("EQP_SUPPLY_NAME").ToString.Trim
            Nmiddle = dtAgreeMent.Rows(0)("EQP_NAME").ToString.Trim + "余款的情况说明"
            StrRTitle = "扬妇院余款情况说明编号" + dtAgreeMent.Rows(0)("AGREEMENT_NO").ToString.Trim
            '---------------------------------------------------------------------------------------------
            txtCotant = txtCotant + "医院与 " + dtAgreeMent.Rows(0)("EQP_SUPPLY_NAME").ToString.Trim + "于" + CDate(dtAgreeMent.Rows(0)("AGREE_DATE")).ToString("yyyyMMdd").ToString.Substring(0, 4) + "年" + CDate(dtAgreeMent.Rows(0)("AGREE_DATE")).ToString("yyyyMMdd").ToString.Substring(4, 2) + "月" + CDate(dtAgreeMent.Rows(0)("AGREE_DATE")).ToString("yyyyMMdd").ToString.Substring(6, 2) + "日"

            txtCotant = txtCotant + "签订" + dtAgreeMent.Rows(0)("EQP_NAME").ToString.Trim + "的协议书,"
            '----------------------------------------------------------------------------------------------
            txtContant1 = "协议总金额为" + Common.NumberFormat(dtAgreeMent.Rows(0)("TOTAL")).ToString.Trim + "元人民币。"
            strCount = txtCotant + txtContant1
            If strCount.Length > 60 Then
                txtCotant = strCount.Substring(0, 60)
                txtContant1 = strCount.Substring(60, strCount.Length - 61)
            Else

                txtCotant = strCount.Substring(0, strCount.Length)
                txtContant1 = ""
            End If

        
        End If



        Try
            Dim xlBook As Excel.Workbook
            Dim xlSheet1 As Excel.Worksheet

            'Dim oMissing As Object = System.Reflection.Missing.Value
            'xlBook = xlApp.Workbooks.Add(oMissing)

            xlBook = xlApp.Workbooks.Add
            xlSheet1 = xlBook.Worksheets(1)
            '-------------------------横向打印预览-----------------------------------------
            xlSheet1.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            '--------------------------------------------------------------------
            Dim IDataCurrrow As Integer = 9
            Dim iStart As Integer = 8

            If dtPay.Rows.Count > 0 Then

                '------------标题 ----------------------
                Dim T1 As Excel.Range = xlSheet1.Cells(iStart, 1)
                T1.Value = "批次"
                T1.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                T1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                Dim T2 As Excel.Range = xlSheet1.Cells(iStart, 2)
                T2.Value = "日期"
                T2.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                T2.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                T2.ColumnWidth = 12
                Dim T3 As Excel.Range = xlSheet1.Cells(iStart, 3)
                T3.Value = "金额(元)"
                T3.ColumnWidth = 14
                T3.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                T3.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                Dim T4 As Excel.Range = xlSheet1.Cells(iStart, 4)
                T4.Value = " 占总数的%"

                T4.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                T4.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                Dim T5 As Excel.Range = xlSheet1.Cells(iStart, 5)
                T5.Value = "次数"
                T5.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                T5.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                Dim T6 As Excel.Range = xlSheet1.Cells(iStart, 6)
                T6.Value = "日期"
                T6.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                T6.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                Dim T7 As Excel.Range = xlSheet1.Cells(iStart, 7)
                T7.Value = "实付(元)"
                T7.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                T7.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                Dim T8 As Excel.Range = xlSheet1.Cells(iStart, 8)
                T8.Value = "比例(%)"
                T8.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                T8.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                Dim T9 As Excel.Range = xlSheet1.Cells(iStart, 9)
                T9.Value = "累计(元)"
                T9.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                T9.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                Dim T10 As Excel.Range = xlSheet1.Cells(iStart, 10)
                T10.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                T10.Value = "还 欠(元)"
                T10.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)

                '-------------------合并Excle--------------------------
                Dim ICurrrow As Integer = 9
                Dim dalCurrow As Integer = 9
                For i = 0 To dtPay.Rows.Count - 1
                    Dim strNo As String = dtPay.Rows(i)("PAY_WAY_CODE")
                    Dim dalCount As Integer = 0
                    dalCount = dtDal.Select(" PAY_WAY_NO='" & strNo.Trim & "'").Length
                    If dalCount > 0 Then
                        '------------ 项目---------

                        Dim strXy As String = ""
                        strXy = ("A" + (i + ICurrrow).ToString).ToString + ":" + ("A" + (i + dalCount + ICurrrow - 1).ToString).ToString
                        xlSheet1.Range(strXy).Merge()
                        xlSheet1.Range(strXy).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                        '-------------日期---------
                        strXy = ("B" + (i + ICurrrow).ToString).ToString + ":" + ("B" + (i + dalCount + ICurrrow - 1).ToString).ToString
                        xlSheet1.Range(strXy).Merge()
                        xlSheet1.Range(strXy).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                        '-------------金额--------------
                        strXy = ("C" + (i + ICurrrow).ToString).ToString + ":" + ("C" + (i + dalCount + ICurrrow - 1).ToString).ToString
                        xlSheet1.Range(strXy).Merge()
                        xlSheet1.Range(strXy).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                        'ICurrrow = ICurrrow + dalCount - 1
                        '----------------比例--------------------
                        strXy = ("D" + (i + ICurrrow).ToString).ToString + ":" + ("D" + (i + dalCount + ICurrrow - 1).ToString).ToString
                        xlSheet1.Range(strXy).Merge()
                        xlSheet1.Range(strXy).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                        ICurrrow = ICurrrow + dalCount - 1
                        IBord = ICurrrow
                    Else


                        IBord = IBord + 1
                    End If

                    Dim Drow As DataRow() = dtDal.Select(" PAY_WAY_NO='" & strNo.Trim & "'")
                    '--------------------------写明显----------------
                    If Drow.Length > 0 Then
                        For k = 0 To Drow.Length - 1

                            'For k = 1 To Drow.Length
                            Dim x0 As Excel.Range = xlSheet1.Cells(k + dalCurrow, 5)
                            x0.Value = Common.NumberFormat(Drow(k)("PAY_TIMES").ToString)
                            'x0.Value = k + 1
                            x0.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                            x0.ColumnWidth = 5
                            x0.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)

                            Dim x1 As Excel.Range = xlSheet1.Cells(k + dalCurrow, 6)
                            x1.Value = CDate(Drow(k)("PAY_DATE")).ToString("yyyy-MM-dd")
                            x1.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                            x1.ColumnWidth = 12
                            x1.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)


                            Dim xBl As Excel.Range = xlSheet1.Cells(k + dalCurrow, 7)
                            xBl.Value = Common.NumberFormat(Drow(k)("PAY_AMT").ToString)
                            xBl.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                            xBl.ColumnWidth = 14
                            xBl.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)

                            Dim x2 As Excel.Range = xlSheet1.Cells(k + dalCurrow, 8)
                            x2.Value = Common.NumberFormat(Drow(k)("Bl").ToString)
                            x2.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                            x2.ColumnWidth = 8
                            x2.Font.Size = 10
                            x2.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)

                            Dim x3 As Excel.Range = xlSheet1.Cells(k + dalCurrow, 9)
                            x3.Value = Common.NumberFormat(Drow(k)("CURRENT_REST_AMT"))
                            x3.ColumnWidth = 14
                            x3.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                            x3.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                            Dim x4 As Excel.Range = xlSheet1.Cells(k + dalCurrow, 10)
                            x4.Value = Common.NumberFormat(Drow(k)("SUB_TOTAL"))
                            x4.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                            x4.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                            x4.ColumnWidth = 14
                            'Dim x5 As Excel.Range = xlSheet1.Cells(k + dalCurrow, 11)
                            'x5.Value = Drow(k)("Demo")
                            'x5.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                            'dalCurrow = dalCurrow + 1

                        Next
                        dalCurrow = dalCurrow + Drow.Length - 1
                    Else

                        Dim x0 As Excel.Range = xlSheet1.Cells(dalCurrow, 5)
                        x0.Value = ""
                        'x0.Value = k + 1
                        x0.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                        x0.ColumnWidth = 5
                        x0.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)

                        Dim x1 As Excel.Range = xlSheet1.Cells(dalCurrow, 6)
                        x1.Value = ""
                        x1.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                        x1.ColumnWidth = 12
                        x1.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)


                        Dim xBl As Excel.Range = xlSheet1.Cells(dalCurrow, 7)
                        xBl.Value = ""
                        xBl.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                        xBl.ColumnWidth = 14
                        xBl.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)

                        Dim x2 As Excel.Range = xlSheet1.Cells(dalCurrow, 8)
                        x2.Value = ""
                        x2.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                        x2.ColumnWidth = 8
                        x2.Font.Size = 10
                        x2.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)

                        Dim x3 As Excel.Range = xlSheet1.Cells(dalCurrow, 9)
                        x3.Value = ""
                        x3.ColumnWidth = 14
                        x3.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                        x3.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                        Dim x4 As Excel.Range = xlSheet1.Cells(dalCurrow, 10)
                        x4.Value = ""
                        x4.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                        x4.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                        x4.ColumnWidth = 14
                        'Dim x5 As Excel.Range = xlSheet1.Cells(k + dalCurrow, 11)
                        'x5.Value = Drow(k)("Demo")
                        'x5.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)

                    End If

                   
                    dalCurrow = dalCurrow + 1
                    '---------------------写左边--------------------------------------------
                    Dim xS As Excel.Range = xlSheet1.Range("A" & (i + IDataCurrrow).ToString.Trim & "")
                    xS.Value = dtPay.Rows(i)("PAY_WAY_NAME")
                    xS.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                    '----------------------文字垂直显示----------------------------
                    'xS.Orientation = Excel.XlOrientation.xlVertical
                    'xS.RowHeight = 70
                    '--------------------------------------------------------------
                    xS.ColumnWidth = 10
                    xS.Font.Size = 11
                    '=------------------边框-------------------------
                    xS.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                    '------------------------------------------------------
                    Dim xS1 As Excel.Range = xlSheet1.Range("B" & (i + IDataCurrrow).ToString.Trim & "")
                    xS1.Value = CDate(dtPay.Rows(i)("PAY_DATE")).ToString("yyyy-MM-dd")
                    xS1.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                    xS1.ColumnWidth = 10
                    xS1.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                    'Dim xS3 As Excel.Range = xlSheet1.Cells(i + IDataCurrrow, 3)
                    Dim xS3 As Excel.Range = xlSheet1.Range("C" & (i + IDataCurrrow).ToString.Trim & "")
                    xS3.Value = Common.NumberFormat(dtPay.Rows(i)("AMT"))
                    xS3.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                    xS3.ColumnWidth = 14
                    xS3.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                    Dim xS4 As Excel.Range = xlSheet1.Range("D" & (i + IDataCurrrow).ToString.Trim & "")
                    xS4.Value = Math.Round(dtPay.Rows(i)("PAY_RATE"), 4, MidpointRounding.AwayFromZero)
                    xS4.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                    xS4.ColumnWidth = 12
                    xS4.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)

                    If Drow.Length > 0 Then
                        IDataCurrrow = IDataCurrrow + Drow.Length - 1
                    Else
                        IDataCurrrow = IDataCurrrow
                    End If
                    'Dim Blab As Excel.Range = xlSheet1.Range("E" & IDataCurrrow & ":" + "J" & IDataCurrrow & "")
                    'Blab.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
                Next




            End If
            '－－－－－－－－－－－－－－画边框－－－－－－－－－－－－


            '－－－－－－－－ －－－－－－－－－－－－－－－－－－－－
            '----------------------------------------------

            xlSheet1.Name = "分期付款单"
            xlSheet1.Range("A1:J1").Merge()
            xlSheet1.Range("A2:J2").Merge()
            xlSheet1.Range("A3:J3").Merge()
            xlSheet1.Range("A4:J4").Merge()
            xlSheet1.Range("A5:J5").Merge()
            xlSheet1.Range("A6:J6").Merge()
            xlSheet1.Range("A7:D7").Merge()
            xlSheet1.Range("A7:D7").BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
            xlSheet1.Range("E7:J7").Merge()
            xlSheet1.Range("E7:J7").BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)

            Dim rang1 As Excel.Range = xlSheet1.Range("A1：J1")
            rang1.Value = StrRTitle
            rang1.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            rang1.Font.Size = 10
            'rang1.Font.Bold = "黑体"

            Dim rang2 As Excel.Range = xlSheet1.Range("A2")
            rang2.Value = Middle
            rang2.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            rang2.Font.Name = "黑体"
            Dim rang3 As Excel.Range = xlSheet1.Range("A3")
            rang3.Value = Nmiddle
            rang3.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            rang3.Font.Name = "黑体"
            Dim rang4 As Excel.Range = xlSheet1.Range("A4")
            rang4.Value = txtCotant
            rang4.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
            Dim rang5 As Excel.Range = xlSheet1.Range("A5")
            rang5.Value = txtContant1
            rang5.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
            Dim rang6 As Excel.Range = xlSheet1.Range("A6")
            rang6.Value = "付款情况如下："
            rang6.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
            Dim rang7 As Excel.Range = xlSheet1.Range("A7:D7")
            rang7.Value = "按合同付款方式"
            rang7.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            Dim rang8 As Excel.Range = xlSheet1.Range("E7:J7")
            rang8.Value = "实际付款记录"
            rang8.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            xlApp.UserControl = True

            xlSheet1.Application.Visible = True
            'xlBook.Saved = True
            xlBook = Nothing
            xlApp = Nothing

            'xlApp.Visible = True
            'Dim save As New SaveFileDialog
            'save.Filter = "Excel文件|*.xls"
            'save.FileName = "分期付款" + DateTime.Now.ToString("yyyyMMdd-HHmm") + ".xls"
            'Dim dialog As DialogResult = save.ShowDialog
            'If dialog = System.Windows.Forms.DialogResult.OK Then
            '    Dim file As String = save.FileName
            'End If
            'xlBook.SaveAs(save.FileName, True)


        Catch ex As Exception

            MessageBox.Show("报表产生异常")
            xlApp.Quit()
        Finally

        End Try


    End Sub