NOPI导出Excel

Imports System.IO
Imports NPOI.HSSF.UserModel
Imports NPOI.HPSF
Imports NPOI.POIFS.FileSystem
Imports NPOI.HSSF.Util
Imports NPOI.SS.UserModel
Imports NPOI.SS.Util
Imports Common
Public Class NopExcel


    Private Init_Date As String = String.Empty





    Private Gc As New GeneralCommon()

    Public Sub New(ByVal Init_Date As String)

        Me.Init_Date = Init_Date

    End Sub

    ''报废
    Public Sub Export_BF(ByVal fileName As String)


        Dim sHeader_0 As String = String.Format("长材制造部{0}全月份轧辊报废表（盘螺线）", Me.Init_Date)


        Dim sHeader_1 As String() = New String() {"序号", "轧辊类型", "轧辊辊号", "上下线时间", "单只轧辊重量", "本次轧制量/累计轧制量（ t）", "本次轧制量/累计轧制量（ t）", "每对轧辊理论轧制量（t)", "车削次数/本次车削直径（mm）", "当前直径/原始直径（mm）", "理论报废直径", "报废支数", "备注", "生产厂家"}


        Dim width_S As Integer() = New Integer() {10, 10, 10, 10, 10, 10, 10, 15, 16, 20, 10, 10, 10, 10}


        If String.IsNullOrEmpty(fileName) Then
            Return
        End If

        Dim SQL_BF As String = "{CALL RMES.BP8100.P_REFERBF('" + Init_Date + "')}"
        Dim Table_BF As DataTable = Gc.GetDataTable(SQL_BF)
        If Table_BF Is Nothing Then
            Return
        End If

        ''创建Excel
        Dim fileExcel As New FileStream(fileName, FileMode.Open, FileAccess.Read)
        Dim hssfworkbook As New HSSFWorkbook(fileExcel)
        Using file As New FileStream(fileName, FileMode.Create, FileAccess.Write)


            'Dim sheet As ISheet = hssfworkbook.CreateSheet("轧辊报废")
            Dim sheet As ISheet = hssfworkbook.CreateSheet("轧辊报废")


            Dim dsi As DocumentSummaryInformation = PropertySetFactory.CreateDocumentSummaryInformation()
            dsi.Company = "RVSoft"
            hssfworkbook.DocumentSummaryInformation = dsi
            'Create a entry of SummaryInformation
            Dim si As SummaryInformation = PropertySetFactory.CreateSummaryInformation()
            hssfworkbook.SummaryInformation = si
            si.CreateDateTime = DateTime.Now


            '设置样式
            Dim style_header As ICellStyle = hssfworkbook.CreateCellStyle()
            style_header.Alignment = HorizontalAlignment.CENTER
            style_header.BorderBottom = BorderStyle.THIN
            style_header.BorderLeft = BorderStyle.THIN
            style_header.BorderRight = BorderStyle.THIN
            style_header.BorderTop = BorderStyle.THIN

            '设置字体
            Dim font_header As IFont = hssfworkbook.CreateFont()
            font_header.Boldweight = 500
            font_header.FontHeightInPoints = 20
            style_header.SetFont(font_header)

            Dim row_index As Integer = 0

            Dim Row_0 As IRow = sheet.CreateRow(row_index)

            For col As Integer = 0 To 13
                Dim cell As ICell = Row_0.CreateCell(0)
                cell.SetCellValue(sHeader_0)
                cell.CellStyle = style_header
                sheet.SetColumnWidth(col, width_S(col) * 256)
            Next

            sheet.AddMergedRegion(New CellRangeAddress(0, 0, 0, 10))


            ''
            Dim style_val As ICellStyle = hssfworkbook.CreateCellStyle()
            style_val.Alignment = HorizontalAlignment.CENTER
            style_val.BorderBottom = BorderStyle.THIN
            style_val.BorderLeft = BorderStyle.THIN
            style_val.BorderRight = BorderStyle.THIN
            style_val.BorderTop = BorderStyle.THIN

            ''/设置字体
            Dim font_val As IFont = hssfworkbook.CreateFont()
            font_val.FontHeightInPoints = 8
            font_val.FontName = "宋体"
            style_val.SetFont(font_val)

            ''创建第二行
            row_index = row_index + 1
            Dim Row_1 As IRow = sheet.CreateRow(row_index)


            For col As Integer = 0 To sHeader_1.Length - 1

                Dim cell As ICell = Row_1.CreateCell(col)
                cell.SetCellValue(sHeader_1(col))
                cell.CellStyle = style_val


            Next

            ''创建数据
            row_index = row_index + 1


            For row As Integer = 1 To Table_BF.Rows.Count
                Dim Row_Data As IRow = sheet.CreateRow(row_index)

                Dim Cell_A As ICell = Row_Data.CreateCell(0)
                Cell_A.CellStyle = style_val
                Cell_A.SetCellValue(row_index - 1)


                Dim Cell_B As ICell = Row_Data.CreateCell(1)
                Cell_B.CellStyle = style_val
                Cell_B.SetCellValue(Table_BF.Rows(row - 1)("轧辊类型").ToString())


                Dim Cell_C As ICell = Row_Data.CreateCell(2)
                Cell_C.CellStyle = style_val
                Cell_C.SetCellValue(Table_BF.Rows(row - 1)("轧辊辊号").ToString())


                Dim Cell_D As ICell = Row_Data.CreateCell(3)
                Cell_D.CellStyle = style_val
                '' Cell_D.SetCellType(CellType.STRING)
                Cell_D.SetCellValue(Convert.ToDateTime(Table_BF.Rows(row - 1)("上下线时间")).ToString("yyyy-MM-dd"))

                Dim Cell_E As ICell = Row_Data.CreateCell(4)
                Cell_E.CellStyle = style_val
                Cell_E.SetCellValue(Table_BF.Rows(row - 1)("轧辊重量").ToString())


                Dim Cell_F As ICell = Row_Data.CreateCell(5)
                Cell_F.CellStyle = style_val
                Cell_F.SetCellValue(Table_BF.Rows(row - 1)("本次轧制量").ToString())


                Dim Cell_G As ICell = Row_Data.CreateCell(6)
                Cell_G.CellStyle = style_val
                Cell_G.SetCellValue(Table_BF.Rows(row - 1)("累计轧制量").ToString())

                Dim Cell_H As ICell = Row_Data.CreateCell(7)
                Cell_H.CellStyle = style_val
                Cell_H.SetCellValue(Table_BF.Rows(row - 1)("理论轧制量").ToString())


                Dim Cell_I As ICell = Row_Data.CreateCell(8)
                Cell_I.CellStyle = style_val
                Cell_I.SetCellValue(String.Format("{0}/{1}", Table_BF.Rows(row - 1)("车削次数").ToString(), Table_BF.Rows(row - 1)("车削直径").ToString()))


                ''当前直径/原始直径
                Dim Cell_J As ICell = Row_Data.CreateCell(9)
                Cell_J.CellStyle = style_val
                Dim s_J As String = String.Format("{0}/{1}", Table_BF.Rows(row - 1)("当前直径").ToString(), Table_BF.Rows(row - 1)("原始直径").ToString())
                Cell_J.SetCellValue(s_J)


                Dim Cell_K As ICell = Row_Data.CreateCell(10)
                Cell_K.CellStyle = style_val
                Cell_K.SetCellValue(Table_BF.Rows(row - 1)("理论报废直径").ToString())


                Dim Cell_L As ICell = Row_Data.CreateCell(11)
                Cell_L.CellStyle = style_val
                Cell_L.SetCellValue(Table_BF.Rows(row - 1)("报废支数").ToString())


                Dim Cell_M As ICell = Row_Data.CreateCell(12)
                Cell_M.CellStyle = style_val
                Cell_M.SetCellValue(Table_BF.Rows(row - 1)("备注").ToString())


                Dim Cell_N As ICell = Row_Data.CreateCell(13)
                Cell_N.CellStyle = style_val
                Cell_N.SetCellValue(Table_BF.Rows(row - 1)("生产厂家").ToString())

                row_index = row_index + 1
            Next

            ''合并单元格

            ''第一行
            sheet.AddMergedRegion(New CellRangeAddress(1, 1, 5, 6))
            sheet.SetZoom(4, 3)
            hssfworkbook.Write(file)


        End Using

    End Sub


    Public Sub Export_XH(ByVal fileName As String)
        If String.IsNullOrEmpty(fileName) Then
            Return
        End If

        ''查找数据
        Dim SQL_XH As String = "{CALL RMES.BP8100.P_REFER('" + Init_Date + "')}"

        Dim sHeader_0 As String = String.Format("长材制造部{0}轧辊消耗表（盘螺线）", Init_Date)

        Dim sHeader_1 As String() = New String() {" 轧制量(t)", "32832", "消耗轧辊重量（t)", "消耗轧辊重量（t)", "计算公式(K4:K685)", "计算公式(K4:K685)", "吨钢消耗（kg/t）", "吨钢消耗（kg/t）", "", "计算公式E2/B2*1000", "计算公式E2/B2*1000"}

        Dim sHeader_2 As String() = New String() {"轧辊类型", "轧辊辊号", "上下线时间", "单只轧辊重量(kg)", "本次轧制量/累计轧制量（ t）", "本次轧制量/累计轧制量（ t）", "每对轧辊理论轧制量（t)", "车削次数/本次车削直径（mm）", "当前直径/原始直径（mm）", "备注", ""}

        Dim SFoot_1 As String() = New String() {"", "制表：亓峰（62086）", "制表：亓峰（62086）", "审核:沈克非(61264)", "审核:沈克非(61264)", "", "", "", "", "批准:", ""}

        Dim width_S As Integer() = New Integer() {10, 10, 10, 15, 10, 10, 18, 18, 20, 10, 10}


        Dim Table_XH As DataTable = Gc.GetDataTable(SQL_XH)
        If Table_XH Is Nothing Then
            Return
        End If
        ''创建Excel
        Dim hssfworkbook As New HSSFWorkbook()
        Using file As New FileStream(fileName, FileMode.Create, FileAccess.Write)
            Dim sheet As ISheet = hssfworkbook.CreateSheet("轧辊消耗")

            Dim dsi As DocumentSummaryInformation = PropertySetFactory.CreateDocumentSummaryInformation()
            dsi.Company = "RVSoft"
            hssfworkbook.DocumentSummaryInformation = dsi
            'Create a entry of SummaryInformation
            Dim si As SummaryInformation = PropertySetFactory.CreateSummaryInformation()
            hssfworkbook.SummaryInformation = si
            si.CreateDateTime = DateTime.Now


            '设置样式
            Dim style_header As ICellStyle = hssfworkbook.CreateCellStyle()
            style_header.Alignment = HorizontalAlignment.CENTER
            style_header.BorderBottom = BorderStyle.THIN
            style_header.BorderLeft = BorderStyle.THIN
            style_header.BorderRight = BorderStyle.THIN
            style_header.BorderTop = BorderStyle.THIN

            '设置字体
            Dim font_header As IFont = hssfworkbook.CreateFont()
            font_header.Boldweight = 500
            font_header.FontHeightInPoints = 20
            style_header.SetFont(font_header)

            Dim row_index As Integer = 0

            Dim Row_0 As IRow = sheet.CreateRow(row_index)

            For col As Integer = 0 To 10
                Dim cell As ICell = Row_0.CreateCell(0)
                cell.SetCellValue(sHeader_0)
                cell.CellStyle = style_header
                sheet.SetColumnWidth(col, width_S(col) * 256)
            Next

            sheet.AddMergedRegion(New CellRangeAddress(0, 0, 0, 10))


            ''
            Dim style_val As ICellStyle = hssfworkbook.CreateCellStyle()
            style_val.Alignment = HorizontalAlignment.CENTER
            style_val.BorderBottom = BorderStyle.THIN
            style_val.BorderLeft = BorderStyle.THIN
            style_val.BorderRight = BorderStyle.THIN
            style_val.BorderTop = BorderStyle.THIN

            ''/设置字体
            Dim font_val As IFont = hssfworkbook.CreateFont()
            font_val.FontHeightInPoints = 8
            font_val.FontName = "宋体"
            style_val.SetFont(font_val)



            ''
            ''
            Dim style_foot As ICellStyle = hssfworkbook.CreateCellStyle()
         

            ''/设置字体 
            style_foot.SetFont(font_val)


            ''创建第二行
            row_index = row_index + 1
            Dim Row_1 As IRow = sheet.CreateRow(row_index)


            For col As Integer = 0 To sHeader_1.Length - 1

                Dim cell As ICell = Row_1.CreateCell(col)
                cell.SetCellValue(sHeader_1(col))
                cell.CellStyle = style_val


            Next

            ''创建第三行
            row_index = row_index + 1
            Dim Row_2 As IRow = sheet.CreateRow(row_index)


            For col As Integer = 0 To sHeader_2.Length - 1

                Dim cell As ICell = Row_2.CreateCell(col)
                cell.SetCellValue(sHeader_2(col))
                cell.CellStyle = style_val
            Next

            ''创建数据
            row_index = row_index + 1


            For row As Integer = 1 To Table_XH.Rows.Count
                Dim Row_Data As IRow = sheet.CreateRow(row_index)

                Dim Cell_A As ICell = Row_Data.CreateCell(0)
                Cell_A.CellStyle = style_val
                Cell_A.SetCellValue(Table_XH.Rows(row - 1)("类型").ToString())


                Dim Cell_B As ICell = Row_Data.CreateCell(1)
                Cell_B.CellStyle = style_val
                Cell_B.SetCellValue(Table_XH.Rows(row - 1)("轧号").ToString())


                Dim Cell_C As ICell = Row_Data.CreateCell(2)
                Cell_C.CellStyle = style_val
                Cell_C.SetCellValue(Convert.ToDateTime(Table_XH.Rows(row - 1)("上线时间")).ToString("yyyy-MM-dd"))


                Dim Cell_D As ICell = Row_Data.CreateCell(3)
                Cell_D.CellStyle = style_val
                Cell_D.SetCellValue(Table_XH.Rows(row - 1)("轧重").ToString())

                Dim Cell_E As ICell = Row_Data.CreateCell(4)
                Cell_E.CellStyle = style_val
                Cell_E.SetCellValue(Table_XH.Rows(row - 1)("轧制量").ToString())


                Dim Cell_F As ICell = Row_Data.CreateCell(5)
                Cell_F.CellStyle = style_val
                Cell_F.SetCellValue(Table_XH.Rows(row - 1)("累计重量").ToString())


                Dim Cell_G As ICell = Row_Data.CreateCell(6)
                Cell_G.CellStyle = style_val
                Cell_G.SetCellValue(Table_XH.Rows(row - 1)("理论轧制重量").ToString())



                Dim Cell_H As ICell = Row_Data.CreateCell(7)
                Cell_H.CellStyle = style_val
                Cell_H.SetCellValue(String.Format("{0}/{1}", Table_XH.Rows(row - 1)("车削次数").ToString(), Table_XH.Rows(row - 1)("车削直径").ToString()))


                Dim Cell_I As ICell = Row_Data.CreateCell(8)
                Cell_I.CellStyle = style_val


                Dim s_J As String = String.Format("{0}/{1}", Table_XH.Rows(row - 1)("当前直径").ToString(), Table_XH.Rows(row - 1)("原始直径").ToString())
                Cell_I.SetCellValue(s_J)


                Dim Cell_J As ICell = Row_Data.CreateCell(9)
                Cell_J.CellStyle = style_val
                Cell_J.SetCellValue(Table_XH.Rows(row - 1)("报废原因").ToString())


                Dim Cell_K As ICell = Row_Data.CreateCell(10)
                Cell_K.CellStyle = style_val
                ''Cell_K.SetCellValue("计算公式")

                Dim format As String = String.Format("IF((F{0}-E{0}-G{0})>0,0,IF((F{0}<=G{0}),(E{0}/G{0}*D{0}/1000*2),(D{0}*(E{0}-F{0}+G{0})/(G{0}*1000)*2)))", row_index + 1)
                Cell_K.SetCellFormula(format)

                row_index = row_index + 1
            Next

            ''\\\

            ''创建残余值行
            row_index = row_index + 1
            Dim Row_cyz As IRow = sheet.CreateRow(row_index)


            For col As Integer = 0 To sHeader_2.Length - 1

                Dim cell As ICell = Row_cyz.CreateCell(col)
                cell.SetCellValue("残余值")
                cell.CellStyle = style_val
            Next
            sheet.AddMergedRegion(New CellRangeAddress(Row_cyz.RowNum, Row_cyz.RowNum, 0, sHeader_2.Length - 1))



            '''创建残余值列头


            ''创建行
            row_index = row_index + 1
            Dim Row_657 As IRow = sheet.CreateRow(row_index)


            For col As Integer = 0 To sHeader_2.Length - 1

                Dim cell As ICell = Row_657.CreateCell(col)
                cell.SetCellValue(sHeader_2(col))
                cell.CellStyle = style_val


            Next
            sheet.AddMergedRegion(New CellRangeAddress(Row_657.RowNum, Row_657.RowNum, 4, 5)) 

            '‘’‘制表


            row_index = row_index + 1
            Dim Row_Foot_1 As IRow = sheet.CreateRow(row_index)

            For col As Integer = 0 To SFoot_1.Length - 1

                Dim cell As ICell = Row_Foot_1.CreateCell(col)
                cell.SetCellValue(SFoot_1(col))
                cell.CellStyle = style_Foot


            Next

            sheet.AddMergedRegion(New CellRangeAddress(Row_Foot_1.RowNum, Row_Foot_1.RowNum, 1, 2))

            sheet.AddMergedRegion(New CellRangeAddress(Row_Foot_1.RowNum, Row_Foot_1.RowNum, 3, 4))


            row_index = row_index + 1


            Dim style_Foot_2 As ICellStyle = hssfworkbook.CreateCellStyle() 
            style_Foot_2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER
            style_Foot_2.VerticalAlignment = VerticalAlignment.CENTER

            ''/设置字体 
            Dim font_Foot_2 As IFont = hssfworkbook.CreateFont()
            font_Foot_2.FontHeightInPoints = 8
            font_Foot_2.FontName = "宋体" 
            style_Foot_2.SetFont(font_val)


            Dim Row_Foot_2 As IRow = sheet.CreateRow(row_index)
            Dim cell_8 As ICell = Row_Foot_2.CreateCell(8)
            cell_8.SetCellValue("长材制造部")
            cell_8.CellStyle = style_Foot_2

            Dim cell_9 As ICell = Row_Foot_2.CreateCell(9)
            cell_9.SetCellValue("长材制造部")
            cell_9.CellStyle = style_Foot_2
            Row_Foot_2.Height = 100 * 5

            sheet.AddMergedRegion(New CellRangeAddress(Row_Foot_2.RowNum, Row_Foot_2.RowNum, 8, 9))







            '‘、、、、、、、、、、、、、、、、、、、、、、、、、、、、
            '''设置第2行E2,F2计算公式

            Dim format_E2 As String = String.Format("SUM(K{0}:K{1})", 4, row_index + 1)
            sheet.GetRow(1).Cells(4).SetCellFormula(format_E2)
            sheet.GetRow(1).Cells(5).SetCellFormula(format_E2)


            Dim format_J2 As String = String.Format("E2/B2*1000")

            sheet.GetRow(1).Cells(9).SetCellFormula(format_J2)
            sheet.GetRow(1).Cells(10).SetCellFormula(format_J2)


            ''合并单元格

            ''第一行
            sheet.AddMergedRegion(New CellRangeAddress(1, 1, 2, 3))

            sheet.AddMergedRegion(New CellRangeAddress(1, 1, 4, 5))

            sheet.AddMergedRegion(New CellRangeAddress(1, 1, 6, 7))

            sheet.AddMergedRegion(New CellRangeAddress(1, 1, 9, 10))
            '’第二行
            sheet.AddMergedRegion(New CellRangeAddress(2, 2, 4, 5))


            sheet.CreateFreezePane(0, 3, 0, 3)

            sheet.SetZoom(4, 3)
            hssfworkbook.Write(file)


        End Using

    End Sub
End Class