.NET 使用NPOI操作Excel

Private Function ExportExcel(ByVal strProjGUID As String, ByVal strBldGUIDList As String) As String
        Try


            Dim INT_STARTROW As Integer = 9
            Dim INT_ENDCOL As Integer = 10
            Dim INT_STARTCOL As Integer = 7


            Dim templateFileName As String = Server.MapPath("/Slxt/CWGL/Excel模板.xls")


            Dim workbook As NPOI.HSSF.UserModel.HSSFWorkbook = CreateExcel(templateFileName)
            Dim ws As NPOI.HSSF.UserModel.HSSFSheet = workbook.GetSheetAt(0)
            Dim wsRange As NPOI.HSSF.UserModel.HSSFSheet = workbook.GetSheetAt(1)
            'Dim wsRange As NPOI.HSSF.UserModel.HSSFSheet = workbook.CreateSheet("ShtDictionary")
            Dim row As NPOI.HSSF.UserModel.HSSFRow
            Dim cell As NPOI.HSSF.UserModel.HSSFCell
            Dim constraint, constraint1 As NPOI.HSSF.UserModel.DVConstraint
            Dim dataValidation As NPOI.HSSF.UserModel.HSSFDataValidation
            Dim dataValidation2 As NPOI.HSSF.UserModel.HSSFDataValidation

            Dim styleReadonly As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
            Dim styleEdit As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
            Dim font As NPOI.HSSF.UserModel.HSSFFont

            '设置样式变量
            styleReadonly.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
            styleReadonly.FillPattern = NPOI.SS.UserModel.FillPattern.ThickBackwardDiagonals
            styleReadonly.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
            '1.2.设置字体
            font = workbook.CreateFont()
            font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index
            'font.FontHeightInPoints = 11.0
            font.FontName = "宋体"
            styleReadonly.SetFont(font)
            '1.3.设置只读
            styleReadonly.IsLocked = True
            '2.设置可编辑单元格样式
            '2.1.设置单元格背景色
            styleEdit.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index
            styleEdit.FillPattern = NPOI.SS.UserModel.FillPattern.ThickBackwardDiagonals
            styleEdit.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.White.Index
            styleEdit.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("@")
            '2.2.设置字体
            font = workbook.CreateFont()
            font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index
            'font.FontHeightInPoints = 11.0
            font.FontName = "宋体"
            styleEdit.SetFont(font)
            '2.3.设置可编辑
            styleEdit.IsLocked = False
            
            '3.0 设置标题行字体
            Dim styleRed As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
            Dim fontRed As NPOI.HSSF.UserModel.HSSFFont = workbook.CreateFont()
            '3.1.设置字体
            fontRed.Color = NPOI.HSSF.Util.HSSFColor.Red.Index
            fontRed.FontName = "宋体"
            '4.0 数据行“--”只读且居右
            Dim noneStyle As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
            Dim noneFont As NPOI.HSSF.UserModel.HSSFFont = workbook.CreateFont()
            '4.1.设置单元格背景色
            noneStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
            noneStyle.FillPattern = NPOI.SS.UserModel.FillPattern.ThickBackwardDiagonals
            noneStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
            noneStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right
            '4.2.设置字体
            noneFont.Color = NPOI.HSSF.Util.HSSFColor.Black.Index
            noneFont.FontName = "宋体"
            noneStyle.SetFont(noneFont)
            '4.3.设置不可编辑
            noneStyle.IsLocked = True
            

            Dim strSQL As String = ""

            '先填充表1，用于引用值范围
            strSQL = " select Jzkj from s_Jzkj where buGUID='" & Session("BUGUID") & "' and IsQy=1 order by JzkjGUID"
            Dim i, j As Integer
            Dim dtRange As DataTable = MyDB.GetDataTable(strSQL)
            Dim rowCountRange As Integer = dtRange.Rows.Count
            Dim SheetName = "Sheet2"

            For i = 0 To rowCountRange - 1
                row = GetRow(wsRange, i)
                cell = GetCell(row, 0)
                cell.SetCellValue(dtRange.Rows(i).Item("Jzkj").ToString)
            Next

            cell = GetCell(GetRow(wsRange, 0), 1)
            cell.SetCellValue("未结转")

            cell = GetCell(GetRow(wsRange, 1), 1)
            cell.SetCellValue("预结转")

            cell = GetCell(GetRow(wsRange, 2), 1)
            cell.SetCellValue("结转")
            'wsRange.ProtectSheet("slxt")

            Dim range1 As NPOI.SS.UserModel.IName = workbook.CreateName()
            range1.RefersToFormula = String.Format("Sheet2!$A$1:$A${0}", rowCountRange)
            range1.NameName = "TypeRange"

            Dim range2 As NPOI.SS.UserModel.IName = workbook.CreateName()
            range2.RefersToFormula = "Sheet2!$B$1:$B$3"
            range2.NameName = "StatusRange"


            strBldGUIDList = strBldGUIDList.Replace(";", "','")

            strSQL = "select " & _
                     "recordGUID,ProjName,AreaName,BldName,RoomCode,RoomInfo,CarryOverStatus,CarryOverType,isnull(CarryOverMonth,'') as CarryOverMonth, isnull(LEFT(CarryOverMonth,4),'') AS CarryOverYear, convert(varchar(10),FactJFDate,120) as FactJFDate " & _
                     "FROM vs_SaleCarryOver WHERE projGUID='" & strProjGUID & "'  AND CarryOverStatus='未结转' and bldGUID in('" & strBldGUIDList & "')"
            Dim dtTemp As DataTable = MyDB.GetDataTable(strSQL)

            '插入记录行



            Dim rowCount As Integer = dtTemp.Rows.Count

            For i = 0 To rowCount - 1
                row = GetRow(ws, i + INT_STARTROW)
                For j = 0 To INT_ENDCOL
                    cell = GetCell(row, j)
                    cell.SetCellValue(dtTemp.Rows(i)(j).ToString)
                    If j < 6 Then
                        cell.CellStyle = noneStyle
                    Else
                        cell.CellStyle = styleEdit
                    End If
                Next

            Next

            constraint = NPOI.HSSF.UserModel.DVConstraint.CreateNumericConstraint(NPOI.HSSF.UserModel.DVConstraint.ValidationType.DECIMAL, NPOI.HSSF.UserModel.DVConstraint.OperatorType.BETWEEN, "0", "99999999999.99")
            //constraint = NPOI.HSSF.UserModel.DVConstraint.CreateFormulaListConstraint("TypeRange")
            dataValidation = New NPOI.HSSF.UserModel.HSSFDataValidation(New NPOI.SS.Util.CellRangeAddressList(INT_STARTROW, 65535, 7, 7), constraint)
            ws.AddValidationData(dataValidation)

            constraint1 = NPOI.HSSF.UserModel.DVConstraint.CreateFormulaListConstraint("StatusRange")
            dataValidation2 = New NPOI.HSSF.UserModel.HSSFDataValidation(New NPOI.SS.Util.CellRangeAddressList(INT_STARTROW, 65535, 6, 6), constraint1)
            ws.AddValidationData(dataValidation2)

            ws.ProtectSheet("slxt")


            Dim strFileName As String = "/TempFiles/" & CInt(Int(&H7FFFFFFF * Rnd(9999) + 1)).ToString & ".xls"
            Using fs As New FileStream(Server.MapPath(strFileName), FileMode.Create)
                workbook.Write(fs)
                fs.Close()
            End Using
            Return String.Format("OK|{0}|{1}", strFileName, Date.Now.ToString("yyyy-mm-dd"))
        Catch ex As Exception
            Return String.Format("FAIL|", ex.Message)
        End Try

        If MyDB.GetDataItemInt(strSQL) = 0 Then
            Return "OK"
        Else
            Return "NO"
        End If

        ''
    End Function