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
