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 ThenIf NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64 Then NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B)ElseXlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B)End IfReturn XlColour.GetIndex()ElseReturn XlColour.GetIndex()End IfEnd 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();}}elses= 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;}

推荐博客：RyanDoc数据字典 For SqlServer版 RyanCoder代码生成器 For SqlServer版

，一个人身边的位置只有这么多，