/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="rownum"></param>
/// <param name="isFill">是否填充背景色</param>
/// <returns></returns>
//private HSSFCellStyle SetStyle(int rownum,bool isFill)
//{
// if (isFill)
// {
// if (rownum%2 == 0)
// {
// cellstyle.FillForegroundColor = HSSFColor.LIGHT_ORANGE.index;
// }
// else
// cellstyle.FillForegroundColor = HSSFColor.SKY_BLUE.index;
// cellstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
// //cellstyle.FillBackgroundColor = HSSFColor.RED.index;
// }
// return cellstyle;
//}
/// <summary>
/// 获取颜色
/// </summary>
/// <param name="sysColor"></param>
/// <returns></returns>
private short GetColor(System.Drawing.Color sysColor)
{
short s = 0;
HSSFPalette xlPalette = WorkBook.GetCustomPalette();
HSSFColor xlColor = xlPalette.FindColor(sysColor.R, sysColor.G, sysColor.B);
if (xlColor == 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;
xlColor = xlPalette.AddColor(sysColor.R, sysColor.G, sysColor.B);
}
else
{
xlColor = xlPalette.FindSimilarColor(sysColor.R, sysColor.G, sysColor.B);
}
s = xlColor.GetIndex();
}
}
else
{
s = xlColor.GetIndex();
}
return(s);
}
/// <summary>
/// 获取自定义颜色位置
/// </summary>
/// <param name="workBook"></param>
/// <param name="rgb"></param>
/// <returns></returns>
private static short GetCustomColor(this HSSFWorkbook workBook, string rgb)
{
SetOriginalRGB();
short indexed = defaultColorIndexed;
if (string.IsNullOrEmpty(rgb))
{
return(indexed);
}
string[] colors = rgb.Split(',');
if (colors.Length != 3)
{
return(indexed);
}
byte red = 0;
byte green = 0;
byte blue = 0;
bool result = DealRGB(colors, ref red, ref green, ref blue);
if (result == false)
{
return(indexed);
}
HSSFPalette pattern = workBook.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor hssfColor = pattern.FindColor(red, green, blue);
if (hssfColor == null)
{
return(pattern.SetCustomColor(rgb, -1));
}
indexed = hssfColor.Indexed;
return(indexed);
}
private static HSSFCellStyle generate(HSSFWorkbook wb_, int index, int r, int g, int b, bool bold)
{
short colorIndex = (short)index;
HSSFCellStyle style = (HSSFCellStyle)(wb_.CreateCellStyle());
HSSFPalette palette = ((HSSFWorkbook)wb_).GetCustomPalette();
palette.SetColorAtIndex(colorIndex, (byte)r, (byte)g, (byte)b);
style.FillForegroundColor = palette.GetColor(colorIndex).Indexed;
style.FillPattern = FillPattern.SolidForeground;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//是否换行
style.WrapText = true;
if (bold)
{
IFont font = wb_.CreateFont();
font.Boldweight = short.MaxValue;
style.SetFont(font);
}
return(style);
}
/// <summary>
/// 设置单元格背景色
/// </summary>
/// <returns></returns>
private ICellStyle setCellBackColor()
{
ICellStyle backColorStyle = workbook.CreateCellStyle();
IFont boldFont = workbook.CreateFont();
boldFont.FontHeightInPoints = 10;
boldFont.FontName = "微软雅黑";
boldFont.Boldweight = (short)FontBoldWeight.Bold;
HSSFPalette palette = ((HSSFWorkbook)workbook).GetCustomPalette();
Color backColor = Color.FromArgb(238, 236, 225);
short FIRST_COLOR_INDEX = (short)0x8;
//index的取值范围 0x8 - 0x40
palette.SetColorAtIndex((short)(FIRST_COLOR_INDEX), backColor.R, backColor.G, backColor.B);
backColorStyle.FillPattern = FillPattern.SolidForeground;
var v1 = palette.FindColor(backColor.R, backColor.G, backColor.B);
if (v1 == null)
{
throw new Exception("Color is not in Palette");
}
backColorStyle.FillForegroundColor = v1.GetIndex();
backColorStyle.SetFont(boldFont);
return(backColorStyle);
}
/// <summary>
/// create the colours for the COBie sheet
/// </summary>
/// <param name="colourName"></param>
/// <param name="red"></param>
/// <param name="green"></param>
/// <param name="blue"></param>
private void CreateColours(string colourName, byte red, byte green, byte blue)
{
IColor colour = null;
if (IsXlsx)
{
byte[] rgb = new byte[3] {
red, green, blue
};
colour = new XSSFColor(rgb) as IColor;
}
else
{
HSSFPalette palette = ((HSSFWorkbook)ExcelWorkbook).GetCustomPalette();
colour = palette.FindSimilarColor(red, green, blue);
if (colour == null)
{
// First 64 are system colours
//srl this code does not work with the latest version of NPOI
//if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64 )
//{
// NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
//}
//NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE++;
colour = palette.AddColor(red, green, blue);
}
}
_colours.Add(colourName, colour);
}
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);
}
private Formatting GetFormatting()
{
var result = (from f in _formattings where f.Key.CompareTo(_style) == 0 select f.Value).FirstOrDefault();
if (result == null)
{
result = new Formatting
{
Bold = _style.HasFontStyle() && _style.FontStyle.HasFlag(FontStyle.Bold),
Italic = _style.HasFontStyle() && _style.FontStyle.HasFlag(FontStyle.Italic),
UnderlineStyle = _style.HasFontStyle() && _style.FontStyle.HasFlag(FontStyle.Underline) ? UnderlineStyle.singleLine : UnderlineStyle.none,
Size = 12 + (_style.HasFontDSize() ? _style.FontDSize : 0)
};
if (_style.HasFontName())
{
result.FontFamily = new FontFamily(_style.FontName);
}
if (_style.HasFontColor())
{
var p = new HSSFPalette(new PaletteRecord());
var c = p.GetColor(_style.FontColor);
var triplet = c.GetTriplet();
result.FontColor = Color.FromArgb(triplet[0], triplet[1], triplet[2]);
}
/*if (_style.HasBgColor())
* result.Highlight = Highlight.blue;*/
_formattings.Add(new ContentStyle(_style), result);
}
return(result);
}
/// <summary>
/// 获取RGB对应NPOI颜色值
/// </summary>
/// <param name="workbook">当前wb</param>
/// <param name="R"></param>
/// <param name="G"></param>
/// <param name="B"></param>
/// <returns></returns>
public static short GetXLColour(this HSSFWorkbook workbook, int R, int G, int B)
{
short s = 0;
HSSFPalette XlPalette = workbook.GetCustomPalette();
HSSFColor XlColour = XlPalette.FindColor((byte)R, (byte)G, (byte)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((byte)R, (byte)G, (byte)B);
}
else
{
XlColour = XlPalette.FindSimilarColor((byte)R, (byte)G, (byte)B);
}
s = XlColour.GetIndex();
}
}
else
{
s = XlColour.GetIndex();
}
return(s);
}
public override void SetBackgroudColor(System.Drawing.Color color)
{
IWorkbook workbook = _npoiWorksheet.Workbook;
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(_row.RowStyle);
if (workbook is HSSFWorkbook)
{
HSSFWorkbook hssfWorkbook = (HSSFWorkbook)workbook;
HSSFPalette palette = hssfWorkbook.GetCustomPalette(); //调色板实例
//palette.SetColorAtIndex((short)8, color.R, color.G, color.B);
HSSFColor hssFColor = palette.FindSimilarColor(color.R, color.G, color.B);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = hssFColor.Indexed;
}
else
{
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFPalette palette = hssfWorkbook.GetCustomPalette(); //调色板实例
//palette.SetColorAtIndex((short)8, color.R, color.G, color.B);
HSSFColor hssFColor = palette.FindSimilarColor(color.R, color.G, color.B);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = hssFColor.Indexed;
//No way!
}
_row.RowStyle = cellStyle;
}
public override void SetFontColor(System.Drawing.Color color)
{
IWorkbook workbook = _npoiWorksheet.Workbook;
ICellStyle cellStyle = workbook.CreateCellStyle();
if (workbook is HSSFWorkbook)
{
HSSFWorkbook hssfWorkbook = (HSSFWorkbook)workbook;
HSSFPalette palette = hssfWorkbook.GetCustomPalette(); //调色板实例
//palette.SetColorAtIndex((short)8, color.R, color.G, color.B);
HSSFColor hssFColor = palette.FindSimilarColor(color.R, color.G, color.B);
cellStyle.CloneStyleFrom(_row.RowStyle);
IFont font = cellStyle.GetFont(workbook);
font.Color = hssFColor.Indexed;
cellStyle.SetFont(font);
_row.RowStyle = cellStyle;
}
else
{
//No way!
}
}
internal IColor CreateColor(string htmlColor)
{
Color color = ColorTranslator.FromHtml(htmlColor);
byte[] array = new byte[]
{
color.R,
color.G,
color.B
};
IColor result;
switch (this.ExcelVersion)
{
case ExcelVersion.XLS:
{
HSSFPalette customPalette = this.ExcelXls.GetCustomPalette();
if (this._palleteColorSize >= 63)
{
HSSFColor hSSFColor = customPalette.FindColor(color.R, color.G, color.B);
if (hSSFColor == null)
{
hSSFColor = customPalette.FindSimilarColor(color.R, color.G, color.B);
}
short?palleteColorSize = this._palleteColorSize;
this._palleteColorSize = (palleteColorSize.HasValue
? new short?((short)(palleteColorSize.GetValueOrDefault() + 1))
: null);
result = hSSFColor;
}
else
{
if (!this._palleteColorSize.HasValue)
{
this._palleteColorSize = new short?(8);
}
else
{
short?palleteColorSize = this._palleteColorSize;
this._palleteColorSize = (palleteColorSize.HasValue
? new short?((short)(palleteColorSize.GetValueOrDefault() + 1))
: null);
}
customPalette.SetColorAtIndex(this._palleteColorSize.Value, color.R, color.G, color.B);
HSSFColor hSSFColor = customPalette.GetColor(this._palleteColorSize.Value);
result = hSSFColor;
}
break;
}
case ExcelVersion.XLSX:
result = new XSSFColor(color);
break;
default:
throw new Exception(ErrorMessage.Excel_BadVersion);
}
return(result);
}
private short GetXLColour(System.Drawing.Color SystemColour)
{
HSSFPalette XlPalette = document.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
XlColour = XlColour ?? XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);
return(XlColour.Indexed);
}
/// <summary>
/// 设置自定义颜色
/// </summary>
/// <param name="workBook"></param>
/// <param name="rgb"></param>
private static void SetCustomColor(this HSSFWorkbook workBook, string rgb)
{
SetOriginalRGB();
HSSFPalette pattern = workBook.GetCustomPalette();
pattern.SetCustomColor(rgb, -1);
}
public void TestPaletteFromCellColours()
{
HSSFWorkbook book = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithColours.xls");
HSSFPalette p = book.GetCustomPalette();
ICell cellA = book.GetSheetAt(0).GetRow(0).GetCell(0);
ICell cellB = book.GetSheetAt(0).GetRow(1).GetCell(0);
ICell cellC = book.GetSheetAt(0).GetRow(2).GetCell(0);
ICell cellD = book.GetSheetAt(0).GetRow(3).GetCell(0);
ICell cellE = book.GetSheetAt(0).GetRow(4).GetCell(0);
// Plain
Assert.AreEqual("I'm plain", cellA.StringCellValue);
Assert.AreEqual(64, cellA.CellStyle.FillForegroundColor);
Assert.AreEqual(64, cellA.CellStyle.FillBackgroundColor);
Assert.AreEqual(HSSFColor.COLOR_NORMAL, cellA.CellStyle.GetFont(book).Color);
Assert.AreEqual(0, (short)cellA.CellStyle.FillPattern);
Assert.AreEqual("0:0:0", p.GetColor((short)64).GetHexString());
Assert.AreEqual(null, p.GetColor((short)32767));
// Red
Assert.AreEqual("I'm red", cellB.StringCellValue);
Assert.AreEqual(64, cellB.CellStyle.FillForegroundColor);
Assert.AreEqual(64, cellB.CellStyle.FillBackgroundColor);
Assert.AreEqual(10, cellB.CellStyle.GetFont(book).Color);
Assert.AreEqual(0, (short)cellB.CellStyle.FillPattern);
Assert.AreEqual("0:0:0", p.GetColor((short)64).GetHexString());
Assert.AreEqual("FFFF:0:0", p.GetColor((short)10).GetHexString());
// Red + green bg
Assert.AreEqual("I'm red with a green bg", cellC.StringCellValue);
Assert.AreEqual(11, cellC.CellStyle.FillForegroundColor);
Assert.AreEqual(64, cellC.CellStyle.FillBackgroundColor);
Assert.AreEqual(10, cellC.CellStyle.GetFont(book).Color);
Assert.AreEqual(1, (short)cellC.CellStyle.FillPattern);
Assert.AreEqual("0:FFFF:0", p.GetColor((short)11).GetHexString());
Assert.AreEqual("FFFF:0:0", p.GetColor((short)10).GetHexString());
// Pink with yellow
Assert.AreEqual("I'm pink with a yellow pattern (none)", cellD.StringCellValue);
Assert.AreEqual(13, cellD.CellStyle.FillForegroundColor);
Assert.AreEqual(64, cellD.CellStyle.FillBackgroundColor);
Assert.AreEqual(14, cellD.CellStyle.GetFont(book).Color);
Assert.AreEqual(0, (short)cellD.CellStyle.FillPattern);
Assert.AreEqual("FFFF:FFFF:0", p.GetColor((short)13).GetHexString());
Assert.AreEqual("FFFF:0:FFFF", p.GetColor((short)14).GetHexString());
// Pink with yellow - full
Assert.AreEqual("I'm pink with a yellow pattern (full)", cellE.StringCellValue);
Assert.AreEqual(13, cellE.CellStyle.FillForegroundColor);
Assert.AreEqual(64, cellE.CellStyle.FillBackgroundColor);
Assert.AreEqual(14, cellE.CellStyle.GetFont(book).Color);
Assert.AreEqual(0, (short)cellE.CellStyle.FillPattern);
Assert.AreEqual("FFFF:FFFF:0", p.GetColor((short)13).GetHexString());
Assert.AreEqual("FFFF:0:FFFF", p.GetColor((short)14).GetHexString());
}
public void openxls(string xls)
{
FileStream file = new FileStream(xls, FileMode.Open, FileAccess.Read);
wb = new HSSFWorkbook(file);
HSSFPalette palette = wb.GetCustomPalette();
//调色板实例
xlsfile = xls;
file.Close();
}
private int colorSpool = 10; //颜色值的精度
/// <summary>
/// 设置单元格样式，测试。
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="color"></param>
public bool SetCellColor(int row, int col, Color color)
{
IRow oneRow = sheet.GetRow(row);
if (oneRow == null)
{
oneRow = sheet.CreateRow(row);
}
ICell cell = oneRow.GetCell(col);
if (cell == null)
{
cell = oneRow.CreateCell(col);
}
#region 设置颜色
ICellStyle cellStyle = ThisCheckNeighbor(color);
if (cellStyle == null)
{
int nowColorrgb = color.ToArgb();
if (hssfPalette == null)
{
hssfPalette = workBook.GetCustomPalette();
}
//NPOI的调色板只允许 0x08-0x40共56种颜色的设置，设置多了就无法展示了。
int index = cellStyles.Count + 8;
if (index > 64)
{
return(false);
//throw new Exception("调色板超过了56种颜色！降低颜色精度试试！");
}
hssfPalette.SetColorAtIndex((short)index, color.R, color.G, color.B);
HSSFColor hssFColor = hssfPalette.FindColor(color.R, color.G, color.B);
if (hssFColor == null)
{
return(false);
}
cellStyle = workBook.CreateCellStyle();
cellStyles.Add(nowColorrgb, cellStyle);
cellStyle.FillForegroundColor = hssFColor.Indexed;
cellStyle.FillPattern = FillPattern.SolidForeground;
}
#endregion
cell.CellStyle = cellStyle;
return(true);
}
public static short ConvertToGroundColor(this string v)
{
//PaletteRecord palette = new PaletteRecord();
int r = Convert.ToInt32("0x" + v.Substring(1, 2), 16);
int g = Convert.ToInt32("0x" + v.Substring(3, 2), 16);
int b = Convert.ToInt32("0x" + v.Substring(5, 2), 16);
HSSFPalette mypalette = new HSSFPalette(new PaletteRecord());
HSSFColor hssFColor = mypalette.FindColor((Byte)r, (Byte)g, (Byte)(b));
return(hssFColor.Indexed);
}
/// <summary>
/// 获取颜色值
/// </summary>
/// <param name="color">颜色RGB</param>
/// <param name="workbook">Excel画布</param>
/// <returns></returns>
public static string GetColorIndex(HSSFWorkbook workbook, Color color)
{
HSSFPalette palette = workbook.GetCustomPalette();
var v = palette.FindSimilarColor(color.R, color.G, color.B);
if (v == null)
{
throw new Exception("Color is not in Palette");
}
else
{
return(v.GetHexString());
}
}
public ICellStyle TimeDataStyle()
{
if (timeDataStyle == null)
{
IDataFormat dataformat = hssfworkbook.CreateDataFormat();
timeDataStyle = hssfworkbook.CreateCellStyle();
timeDataStyle.Alignment = HorizontalAlignment.Center;
timeDataStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
timeDataStyle.FillPattern = FillPattern.SolidForeground;
HSSFPalette palette = hssfworkbook.GetCustomPalette(); //wb HSSFWorkbook对象
palette.SetColorAtIndex((short)61, (byte)(32), (byte)(218), (byte)(80));
timeDataStyle.FillForegroundColor = (short)61;
}
return(timeDataStyle);
}
private static short GetXlColour(HSSFWorkbook workbook, Color systemColor)
{
short s = 0;
HSSFPalette xlPalette = workbook.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor xlColour = xlPalette.FindColor(systemColor.R, systemColor.G, systemColor.B);
if (xlColour == null)
{
xlColour = xlPalette.FindSimilarColor(systemColor.R, systemColor.G, systemColor.B);
s = xlColour.Indexed;
}
else
{
s = xlColour.Indexed;
}
return(s);
}
public void TestFindSimilar()
{
HSSFWorkbook book = new HSSFWorkbook();
HSSFPalette p = book.GetCustomPalette();
// Add a few edge colours in
p.SetColorAtIndex((short)8, unchecked ((byte)-1), (byte)0, (byte)0);
p.SetColorAtIndex((short)9, (byte)0, unchecked ((byte)-1), (byte)0);
p.SetColorAtIndex((short)10, (byte)0, (byte)0, unchecked ((byte)-1));
// And some near a few of them
p.SetColorAtIndex((short)11, unchecked ((byte)-1), (byte)2, (byte)2);
p.SetColorAtIndex((short)12, unchecked ((byte)-2), (byte)2, (byte)10);
p.SetColorAtIndex((short)13, unchecked ((byte)-4), (byte)0, (byte)0);
p.SetColorAtIndex((short)14, unchecked ((byte)-8), (byte)0, (byte)0);
Assert.AreEqual(
"FFFF:0:0", p.GetColor((short)8).GetHexString()
);
// Now Check we get the right stuff back
Assert.AreEqual(
p.GetColor((short)8).GetHexString(),
p.FindSimilarColor(unchecked ((byte)-1), (byte)0, (byte)0).GetHexString()
);
Assert.AreEqual(
p.GetColor((short)8).GetHexString(),
p.FindSimilarColor(unchecked ((byte)-2), (byte)0, (byte)0).GetHexString()
);
Assert.AreEqual(
p.GetColor((short)8).GetHexString(),
p.FindSimilarColor(unchecked ((byte)-1), (byte)1, (byte)0).GetHexString()
);
Assert.AreEqual(
p.GetColor((short)11).GetHexString(),
p.FindSimilarColor(unchecked ((byte)-1), (byte)2, (byte)1).GetHexString()
);
Assert.AreEqual(
p.GetColor((short)12).GetHexString(),
p.FindSimilarColor(unchecked ((byte)-1), (byte)2, (byte)10).GetHexString()
);
book.Close();
}
private void SetParagraphFormat(Paragraph para)
{
if (para != null)
{
switch (_style.HAlign)
{
case HAlignment.Center:
para.Alignment = Alignment.center;
break;
case HAlignment.FullWidth:
para.Alignment = Alignment.both;
break;
case HAlignment.Left:
para.Alignment = Alignment.left;
break;
case HAlignment.Right:
para.Alignment = Alignment.right;
break;
}
if (_style.HasFontStyle() && _style.FontStyle.HasFlag(FontStyle.Bold))
{
para.Bold();
}
if (_style.HasFontStyle() && _style.FontStyle.HasFlag(FontStyle.Italic))
{
para.Italic();
}
para.FontSize(12 + (_style.HasFontDSize() ? _style.FontDSize : 0));
if (_style.HasFontName())
{
para.Font(new FontFamily(_style.FontName));
}
if (_style.HasFontColor())
{
var p = new HSSFPalette(new PaletteRecord());
var c = p.GetColor(_style.FontColor);
var triplet = c.GetTriplet();
para.Color(Color.FromArgb(triplet[0], triplet[1], triplet[2]));
}
}
}
private void CreateColours(string colourName, byte red, byte green, byte blue)
{
HSSFPalette palette = XlsWorkbook.GetCustomPalette();
HSSFColor colour = palette.FindSimilarColor(red, green, blue);
if (colour == null)
{
// First 64 are system colours
//srl this code does not work with the latest version of NPOI
//if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64 )
//{
// NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
//}
//NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE++;
colour = palette.AddColor(red, green, blue);
}
_colours.Add(colourName, colour);
}
/// <summary>
/// 获取Xls颜色
/// </summary>
/// <param name="workbook">工作簿</param>
/// <param name="color">系统颜色</param>
/// <returns></returns>
public static short GetXlsColour(this HSSFWorkbook workbook, Color color)
{
short s = 0;
HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例
HSSFColor hssfColor = palette.FindColor(color.R, color.G, color.B);
if (hssfColor == null)
{
hssfColor = palette.FindSimilarColor(color.R, color.G, color.B);
s = hssfColor.Indexed;
}
else
{
s = hssfColor.Indexed;
}
return(s);
}
static void Main(string[] args)
{
InitializeWorkbook();
HSSFPalette palette = workbook.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)255, (byte)234, (byte)222);
//HSSFColor myColor = palette.AddColor((byte)253, (byte)0, (byte)0);
ISheet sheet1 = workbook.CreateSheet("Sheet1");
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Pink.Index;
style1.FillPattern = FillPatternType.SolidForeground;
sheet1.CreateRow(0).CreateCell(0).CellStyle = style1;
WriteToFile();
}
private HSSFColor GetColor(HSSFWorkbook workbook, byte red, byte green, byte blue)
{
HSSFPalette palette = workbook.GetCustomPalette();
HSSFColor colour = palette.FindSimilarColor(red, green, blue);
if (colour == null)
{
// NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE is now const...
//// First 64 are system colours
//if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)
//{
// NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
//}
//NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE++;
colour = palette.AddColor(red, green, blue);
}
return(colour);
}
private Paragraph AppendParagraph(Cell cell)
{
var para = cell.Paragraphs.FirstOrDefault();
if (para == null)
{
var formatting = GetFormatting();
para = cell.InsertParagraph(string.Empty, false, formatting);
switch (_style.HAlign)
{
case HAlignment.Center:
para.Alignment = Alignment.center;
break;
case HAlignment.FullWidth:
para.Alignment = Alignment.both;
break;
case HAlignment.Left:
para.Alignment = Alignment.left;
break;
case HAlignment.Right:
para.Alignment = Alignment.right;
break;
}
}
else
{
SetParagraphFormat(para);
}
if (_style.HasBgColor())
{
var p = new HSSFPalette(new PaletteRecord());
var c = p.GetColor(_style.BgColor);
var triplet = c.GetTriplet();
cell.FillColor = Color.FromArgb(triplet[0], triplet[1], triplet[2]);
}
return(para);
}
/// <summary>
/// 颜色字符串转short类型
/// </summary>
/// <param name="workbook"></param>
/// <param name="htmlcolor"></param>
/// <returns></returns>
private static short GetXLColor(HSSFWorkbook workbook, string htmlcolor)
{
if (htmlcolor.StartsWith("#"))
{
HSSFPalette XlPalette = workbook.GetCustomPalette();
System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(htmlcolor);
HSSFColor hssfcolor = XlPalette.FindColor(color.R, color.G, color.B);
if (hssfcolor == null)
{
XlPalette.SetColorAtIndex(HSSFColor.Lavender.Index, color.R, color.G, color.B);
hssfcolor = XlPalette.GetColor(HSSFColor.Lavender.Index);//XlPalette.AddColor(color.R, color.G, color.B);
}
return(hssfcolor.Indexed);
}
else
{
return(htmlcolor.ToUpper().ConvertToColor());
}
}
public void TestCustomPalette()
{
//reading sample xls
HSSFWorkbook book = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls");
//creating custom palette
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex((short)0x12, (byte)101, (byte)230, (byte)100);
palette.SetColorAtIndex((short)0x3b, (byte)0, (byte)255, (byte)52);
//writing to disk; reading in and verifying palette
string tmppath = TempFile.GetTempFilePath("TestCustomPalette", ".xls");
FileStream fos = new FileStream(tmppath, FileMode.OpenOrCreate);
book.Write(fos);
fos.Close();
FileStream fis = new FileStream(tmppath, FileMode.Open, FileAccess.Read);
book = new HSSFWorkbook(fis);
fis.Close();
palette = book.GetCustomPalette();
HSSFColor color = palette.GetColor(HSSFColor.Coral.Index); //unmodified
Assert.IsNotNull(color, "Unexpected null in custom palette (unmodified index)");
byte[] expectedRGB = HSSFColor.Coral.Triplet;
byte[] actualRGB = color.RGB;
String msg = "Expected palette position to remain unmodified";
Assert.AreEqual(expectedRGB[0], actualRGB[0], msg);
Assert.AreEqual(expectedRGB[1], actualRGB[1], msg);
Assert.AreEqual(expectedRGB[2], actualRGB[2], msg);
color = palette.GetColor((short)0x12);
Assert.IsNotNull(color, "Unexpected null in custom palette (modified)");
actualRGB = color.RGB;
msg = "Expected palette modification to be preserved across save";
Assert.AreEqual((short)101, actualRGB[0], msg);
Assert.AreEqual((short)230, actualRGB[1], msg);
Assert.AreEqual((short)100, actualRGB[2], msg);
}
public static void BackgroundFormat(ICell cell, IHeadCell headCell, byte[] rgb)
{
if (cell.Sheet.Workbook is HSSFWorkbook)
{
HSSFPalette palette = (cell.Sheet.Workbook as HSSFWorkbook).GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Black.Index, rgb[0], rgb[1], rgb[2]);
var style = headCell.CellStyle as HSSFCellStyle;
style.FillForegroundColor = HSSFColor.Black.Index;
style.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = style;
}
else
{
var style = (headCell.CellStyle as XSSFCellStyle);
style.SetFillForegroundColor(new XSSFColor(rgb));
style.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = style;
}
}
