利用NPOI做出以下Excel中 設定格式化條件\管理規則 功能
'(1)======若cnt不在5-10這個數字區間的話，顯示紅底黃字=====
Protected Sub btnExport_Click(sender AsObject, e AsEventArgs) Handles btnExport.Click
Dim m_excelBook As HSSFWorkbook = New HSSFWorkbook
Dim m_filePath As String = Server.MapPath("~/Report/")
Dim m_fileName As String = "rebeccaTest_" + Now.ToString("mmssff")
Dim m_fs As New FileStream(m_filePath + m_fileName + ".xls", FileMode.Create)
Dim m_sheets(1) As HSSFSheet
Dim m_sheet As HSSFSheet
m_sheets(0) = m_excelBook.CreateSheet("瑞貝卡Test")
m_sheet = m_sheets(0)
Dim m_conFormat As HSSFSheetConditionalFormatting = m_sheet.SheetConditionalFormatting
'若cnt不在5-10此區間數字的話，顯示警示紅底
Dim m_rule AsHSSFConditionalFormattingRule
m_rule = m_conFormat.CreateConditionalFormattingRule(NPOI.SS.UserModel.ComparisonOperator.NotBetween, "5", "10")
'背景紅.字黃
Dim m_patt As HSSFPatternFormatting = m_rule.CreatePatternFormatting()
m_patt.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index
Dim m_font As HSSFFontFormatting = m_rule.CreateFontFormatting()
m_font.FontColorIndex = NPOI.HSSF.Util.HSSFColor.Yellow.Index
'警示紅底範圍
Dim regions As CellRangeAddress() = {New CellRangeAddress(1, 4, 1, 1)}
m_conFormat.AddConditionalFormatting(regions, m_rule)
setCellValue(m_sheet, 0, 0, "Product")
setCellValue(m_sheet, 0, 1, "cnt")
setCellValue(m_sheet, 1, 0, "Product1")
setCellValue(m_sheet, 2, 0, "Product2")
setCellValue(m_sheet, 3, 0, "Product3")
setCellValue(m_sheet, 4, 0, "Product4")
setCellValue(m_sheet, 1, 1, "0")
setCellValue(m_sheet, 2, 1, "0")
setCellValue(m_sheet, 3, 1, "0")
setCellValue(m_sheet, 4, 1, "0")
m_excelBook.Write(m_fs)
m_fs.Close()
m_excelBook = Nothing
EndSub
PrivateSub setCellValue(ByVal p_sheet As HSSFSheet, ByVal p_row As Integer, _
ByVal p_col As Integer, ByVal p_value As String, _
Optional ByVal p_style As NPOI.SS.UserModel.ICellStyle = Nothing)
Dim m_irow As NPOI.SS.UserModel.IRow
Dim m_icell As NPOI.SS.UserModel.ICell
If p_sheet.GetRow(p_row) IsNothing Then
m_irow = p_sheet.CreateRow(p_row)
Else
m_irow = p_sheet.GetRow(p_row)
EndIf
If m_irow.GetCell(p_col) IsNothing Then
m_icell = m_irow.CreateCell(p_col)
Else
m_icell = m_irow.GetCell(p_col)
EndIf
m_icell.SetCellValue(p_value)
If p_style IsNotNothing Then
m_icell.CellStyle = p_style
EndIf
EndSub
'(2)=====數字區間從DataTable來，並限制不可更改格式，只填size1-size5，size加總數=====
Protected Sub btnExport_Click(sender AsObject, e As EventArgs) Handles btnExport.Click
Dim m_excelBook As HSSFWorkbook = New HSSFWorkbook
Dim m_filePath As String = Server.MapPath("~/Report/")
Dim m_fileName As String = "rebeccaTest_" + Now.ToString("mmssff")
Dim m_fs As New FileStream(m_filePath + m_fileName + ".xls", FileMode.Create)
Dim m_sheets(1) As HSSFSheet
Dim m_sheet As HSSFSheet
m_sheets(0) = m_excelBook.CreateSheet("瑞貝卡Test")
m_sheet = m_sheets(0)
Dim m_conFormat As HSSFSheetConditionalFormatting = m_sheet.SheetConditionalFormatting
Dim m_font As HSSFFontFormatting
Dim m_patt As HSSFPatternFormatting
Dim regions As CellRangeAddress()
Dim m_lockFormat As NPOI.SS.UserModel.ICellStyle
m_lockFormat = m_excelBook.CreateCellStyle()
m_lockFormat.IsLocked = True
Dim m_normalFormat As NPOI.SS.UserModel.ICellStyle
m_normalFormat = m_excelBook.CreateCellStyle()
m_normalFormat.IsLocked = False
setCellValue(m_sheet, 0, 0, "Product")
setCellValue(m_sheet, 0, 1, "cnt")
setCellValue(m_sheet, 0, 2, "min")
setCellValue(m_sheet, 0, 3, "max")
setCellValue(m_sheet, 0, 4, "size1")
setCellValue(m_sheet, 0, 5, "size2")
setCellValue(m_sheet, 0, 6, "size3")
setCellValue(m_sheet, 0, 7, "size4")
setCellValue(m_sheet, 0, 8, "size5")
Dim m_dt AsDataTable = getDT()
Dim m_row AsInteger = 1
For i As Integer = 0 To m_dt.Rows.Count - 1
setCellValue(m_sheet, m_row, 0, m_dt.Rows(i).Item(0).ToString().Trim())
'將size1+...+size5
setCellValue(m_sheet, m_row, 1, String.Format("SUM(E{0}:I{0})", m_row + 1), m_lockFormat, True)
setCellValue(m_sheet, m_row, 2, m_dt.Rows(i).Item("min").ToString().Trim())
setCellValue(m_sheet, m_row, 3, m_dt.Rows(i).Item("max").ToString().Trim())
setCellValue(m_sheet, m_row, 4, "".Trim(), m_normalFormat)
setCellValue(m_sheet, m_row, 5, "".Trim(), m_normalFormat)
setCellValue(m_sheet, m_row, 6, "".Trim(), m_normalFormat)
setCellValue(m_sheet, m_row, 7, "".Trim(), m_normalFormat)
setCellValue(m_sheet, m_row, 8, "".Trim(), m_normalFormat)
Dim m_rule As HSSFConditionalFormattingRule
'設定區間，若不介於min與max此區間的話，顯示警示紅底
m_rule = m_conFormat.CreateConditionalFormattingRule(NPOI.SS.UserModel.ComparisonOperator.NotBetween, _
m_dt.Rows(i).Item("min").ToString().Trim(), m_dt.Rows(i).Item("max").ToString().Trim())
'背景紅.字黃
m_patt = m_rule.CreatePatternFormatting()
m_patt.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index
m_font = m_rule.CreateFontFormatting()
m_font.FontColorIndex = NPOI.HSSF.Util.HSSFColor.Yellow.Index
'警示紅底範圍
regions = {New CellRangeAddress(m_row, m_row, 1, 1)}
m_conFormat.AddConditionalFormatting(regions, m_rule)
m_row += 1
Next
'設定password
m_sheet.ProtectSheet("1111")
m_excelBook.Write(m_fs)
m_fs.Close()
m_excelBook = Nothing
EndSub
PrivateSub setCellValue(ByVal p_sheet As HSSFSheet, ByVal p_row As Integer, _
ByVal p_col As Integer, ByVal p_value As String, _
Optional ByVal p_style As NPOI.SS.UserModel.ICellStyle = Nothing, _
Optional ByVal p_isFormula AsBoolean = False)
Dim m_irow As NPOI.SS.UserModel.IRow
Dim m_icell As NPOI.SS.UserModel.ICell
If p_sheet.GetRow(p_row) IsNothing Then
m_irow = p_sheet.CreateRow(p_row)
Else
m_irow = p_sheet.GetRow(p_row)
EndIf
If p_isFormula Then
'加總用
If m_irow.GetCell(p_col) IsNothing Then
m_icell = m_irow.CreateCell(p_col)
Else
m_icell = p_sheet.GetRow(p_row).GetCell(p_col)
EndIf
m_icell.SetCellFormula(p_value)
Else
If m_irow.GetCell(p_col) IsNothing Then
m_icell = m_irow.CreateCell(p_col)
Else
m_icell = m_irow.GetCell(p_col)
EndIf
m_icell.SetCellValue(p_value)
EndIf
If p_style IsNot Nothing Then
m_icell.CellStyle = p_style
EndIf
EndSub
'製作DataTable
Private Function getDT() As DataTable
Dim m_dt As DataTable = New DataTable()
m_dt.Columns.Add("Product")
m_dt.Columns.Add("min")
m_dt.Columns.Add("max")
Dim m_dr As DataRow = m_dt.NewRow()
m_dr(0) = "13579"
m_dr(1) = "5"
m_dr(2) = "10"
m_dt.Rows.Add(m_dr)
m_dr = m_dt.NewRow()
m_dr(0) = "24680"
m_dr(1) = "6"
m_dr(2) = "11"
m_dt.Rows.Add(m_dr)
m_dr = m_dt.NewRow()
m_dr(0) = "35791"
m_dr(1) = "7"
m_dr(2) = "12"
m_dt.Rows.Add(m_dr)
m_dr = m_dt.NewRow()
m_dr(0) = "46802"
m_dr(1) = "8"
m_dr(2) = "13"
m_dt.Rows.Add(m_dr)
m_dr = m_dt.NewRow()
m_dr(0) = "57913"
m_dr(1) = "9"
m_dr(2) = "14"
m_dt.Rows.Add(m_dr)
Return m_dt
End Function
