NPOI 自定义设置单元格背景颜色[RGB格式]
一.背景介绍
NPOI自带的颜色有时不能满足我们要求时,我们需要自己定义背景色,而且NPOI的颜色类型是short类型,而.Net颜色类是Color类型,怎么让它们相互之间转换呢？网上有一段代码是vb的可以解决上述问题,本人把它翻译成C#的,方便大家使用
VB：
Private Function GetXLColour(ByVal SystemColour As System.Drawing.Color) As Short 'Lookup RGB from .NET system colour in Excel pallete - or create a new entry (get nearest if palette full). Return the XL palette index. Dim XlPalette As HSSFPalette = xlWorkbook.GetCustomPalette() Dim XlColour As NPOI.HSSF.Util.HSSFColor = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B) If IsNothing(XlColour) Then 'Available colour palette entries: 65 to 32766 (0-64=standard palette; 64=auto, 32767=unspecified) If NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255 Then If NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64 Then NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64 NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1 XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B) Else XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B) End If Return XlColour.GetIndex() Else Return XlColour.GetIndex() End If End Function
C#：
private short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour) { short s = 0; HSSFPalette XlPalette = workbook.GetCustomPalette(); HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B); if (XlColour == null) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64) { NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64; NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1; XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B); } else { XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B); } s= XlColour.GetIndex(); } } else s= XlColour.GetIndex(); return s; }
使用方法：
Color LevelOneColor = Color.FromArgb(143, 176, 229); Color LevelTwoColor = Color.FromArgb(201, 217, 243); Color LevelThreeColor = Color.FromArgb(231, 238, 248); Color LevelFourColor = Color.FromArgb(232, 230, 231); Color LevelFiveColor = Color.FromArgb(250, 252, 213); /// <summary> /// 分层设置单元格样式 /// </summary> /// <param name="workbook"></param> /// <param name="alignment"></param> /// <param name="valingment"></param> /// <returns></returns> public HSSFCellStyle SetStyle(HSSFWorkbook workbook, short alignment, short valingment, int layer) { HSSFCellStyle style = workbook.CreateCellStyle(); style.Alignment = alignment; style.VerticalAlignment = valingment; style.BorderBottom = HSSFCellStyle.BORDER_THIN; style.BorderLeft = HSSFCellStyle.BORDER_THIN; style.BorderRight = HSSFCellStyle.BORDER_THIN; style.BorderTop = HSSFCellStyle.BORDER_THIN; switch (layer) { case 0: style.FillForegroundColor = GetXLColour(workbook, LevelOneColor); //调用GetXLColour方法 style.FillPattern = HSSFCellStyle.ALT_BARS; style.FillBackgroundColor = GetXLColour(workbook, LevelOneColor); break; case 1: style.FillForegroundColor = GetXLColour(workbook, LevelTwoColor); style.FillPattern = HSSFCellStyle.ALT_BARS; style.FillBackgroundColor = GetXLColour(workbook, LevelTwoColor); break; case 2: style.FillForegroundColor = GetXLColour(workbook, LevelThreeColor); style.FillPattern = HSSFCellStyle.ALT_BARS; style.FillBackgroundColor = GetXLColour(workbook, LevelThreeColor); break; case 3: style.FillForegroundColor = GetXLColour(workbook, LevelFourColor); style.FillPattern = HSSFCellStyle.ALT_BARS; style.FillBackgroundColor = GetXLColour(workbook, LevelFourColor); break; case 4: style.FillForegroundColor = GetXLColour(workbook, LevelFiveColor); style.FillPattern = HSSFCellStyle.ALT_BARS; style.FillBackgroundColor = GetXLColour(workbook, LevelFiveColor); break; default: break; } return style; }