private void LoadCustomColors(IWorkbook wb, ExcelFormat format)
{
if (format == ExcelFormat.xls)
{
var wbHssf = wb as HSSFWorkbook;
palette = wbHssf.GetCustomPalette();
palette.SetColorAtIndex(SUB_TOTAL_BACK_COLOR_INDEX, (byte)239, (byte)240, (byte)241);
palette.SetColorAtIndex(ALTERNATE_BACK_COLOR_INDEX, (byte)210, (byte)210, (byte)210);
palette.SetColorAtIndex(DARK_BLUE_EXCEL_2007, (byte)31, (byte)73, (byte)125);
customColors.Add(SUB_TOTAL_BACK_COLOR_INDEX, palette.GetColor(SUB_TOTAL_BACK_COLOR_INDEX));
customColors.Add(ALTERNATE_BACK_COLOR_INDEX, palette.GetColor(ALTERNATE_BACK_COLOR_INDEX));
customColors.Add(DARK_BLUE_EXCEL_2007, palette.GetColor(DARK_BLUE_EXCEL_2007));
}
else
{
customColors.Add(SUB_TOTAL_BACK_COLOR_INDEX, new XSSFColor(System.Drawing.Color.FromArgb(239, 240, 241)));
customColors.Add(ALTERNATE_BACK_COLOR_INDEX, new XSSFColor(System.Drawing.Color.FromArgb(210, 210, 210)));
customColors.Add(DARK_BLUE_EXCEL_2007, new XSSFColor(System.Drawing.Color.FromArgb(31, 73, 125)));
}
}
private void CompareToDefaults(ColorComparator c)
{
var colors = HSSFColor.GetIndexHash();
IEnumerator it = colors.Keys.GetEnumerator();
while (it.MoveNext())
{
int index = (int)it.Current;
HSSFColor expectedColor = (HSSFColor)colors[index];
HSSFColor paletteColor = hssfPalette.GetColor((short)index);
c.Compare(expectedColor, paletteColor);
}
}
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 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());
}
}
private string returnrgbcolor(short color)
{
HSSFPalette mypalette = new HSSFPalette(new PaletteRecord());
HSSFColor hssFColor = mypalette.GetColor(color);
if (hssFColor == null)
{
return("");
}
byte[] rgb = hssFColor.RGB;
int r = rgb[0];
int g = rgb[1];
int b = rgb[2];
//Console.WriteLine("r=" + r + "g=" + g + "b=" + b);
if (r == 0 && b == 0 && g == 0)
{
return("");
}
string R = Convert.ToString(r, 16);
if (R == "0")
{
R = "00";
}
string G = Convert.ToString(g, 16);
if (G == "0")
{
G = "00";
}
string B = Convert.ToString(b, 16);
if (B == "0")
{
B = "00";
}
string Result = "#" + R + G + B + ";";
return(Result);
}
public void Test48403()
{
HSSFWorkbook wb = new HSSFWorkbook();
var color = new Rgb24(0, 0x6B, 0x6B); //decode("#006B6B");
HSSFPalette palette = wb.GetCustomPalette();
HSSFColor hssfColor = palette.FindColor(color.R, color.G, color.B);
Assert.IsNull(hssfColor);
palette.SetColorAtIndex(
(short)(PaletteRecord.STANDARD_PALETTE_SIZE - 1),
(byte)color.R, (byte)color.G,
(byte)color.B);
hssfColor = palette.GetColor((short)(PaletteRecord.STANDARD_PALETTE_SIZE - 1));
Assert.IsNotNull(hssfColor);
Assert.AreEqual(55, hssfColor.Indexed);
CollectionAssert.AreEqual(new short[] { 0, 107, 107 }, hssfColor.GetTriplet());
wb.Close();
}
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();
}
public static HSSFColor setColor(HSSFWorkbook workbook, byte r, byte g, byte b)
{
try
{
HSSFPalette palette = workbook.GetCustomPalette();
HSSFColor color = null;
color = palette.FindColor(r, g, b);
if (color == null)
{
palette.SetColorAtIndex(HSSFColor.Blue.Index, r, g, b);
color = palette.GetColor(HSSFColor.Blue.Index);
}
return(color);
}
catch (Exception ex)
{
throw ex;
}
}
protected String BuildStyle(IWorkbook workbook, ICellStyle cellStyle)
{
StringBuilder style = new StringBuilder();
if (workbook is HSSFWorkbook)
{
HSSFPalette palette = ((HSSFWorkbook)workbook).GetCustomPalette();
style.Append("white-space: pre-wrap; ");
ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);
if (cellStyle.FillPattern == FillPattern.NoFill)
{
// no fill
}
else if (cellStyle.FillPattern == FillPattern.SolidForeground)
{
//cellStyle.
//HSSFColor.
HSSFColor foregroundColor = palette.GetColor(cellStyle.FillForegroundColor);
if (foregroundColor != null)
{
style.AppendFormat("background-color:{0}; ", ExcelToHtmlUtils.GetColor(foregroundColor));
}
}
else
{
HSSFColor backgroundColor = palette.GetColor(cellStyle.FillBackgroundColor);
if (backgroundColor != null)
{
style.AppendFormat("background-color:{0}; ", ExcelToHtmlUtils.GetColor(backgroundColor));
}
}
}
else
{
style.Append("white-space: pre-wrap; ");
ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);
if (cellStyle.FillPattern == FillPattern.NoFill)
{
// no fill
}
else if (cellStyle.FillPattern == FillPattern.SolidForeground)
{
//cellStyle
IndexedColors clr = IndexedColors.ValueOf(cellStyle.FillForegroundColor);
string hexstring = null;
if (clr != null)
{
hexstring = clr.HexString;
}
else
{
XSSFColor foregroundColor = (XSSFColor)cellStyle.FillForegroundColorColor;
if (foregroundColor != null)
{
hexstring = ExcelToHtmlUtils.GetColor(foregroundColor);
}
}
if (hexstring != null)
{
style.AppendFormat("background-color:{0}; ", hexstring);
}
}
else
{
IndexedColors clr = IndexedColors.ValueOf(cellStyle.FillBackgroundColor);
string hexstring = null;
if (clr != null)
{
hexstring = clr.HexString;
}
else
{
XSSFColor backgroundColor = (XSSFColor)cellStyle.FillBackgroundColorColor;
if (backgroundColor != null)
{
hexstring = ExcelToHtmlUtils.GetColor(backgroundColor);
}
}
if (hexstring != null)
{
style.AppendFormat("background-color:{0}; ", hexstring);
}
}
}
BuildStyle_Border(workbook, style, "top", cellStyle.BorderTop, cellStyle.TopBorderColor);
BuildStyle_Border(workbook, style, "right", cellStyle.BorderRight, cellStyle.RightBorderColor);
BuildStyle_Border(workbook, style, "bottom", cellStyle.BorderBottom, cellStyle.BottomBorderColor);
BuildStyle_Border(workbook, style, "left", cellStyle.BorderLeft, cellStyle.LeftBorderColor);
IFont font = cellStyle.GetFont(workbook);
BuildStyle_Font(workbook, style, font);
return(style.ToString());
}
/// <summary>
/// 设置颜色
/// </summary>
/// <param name="pattern"></param>
/// <param name="rgb"></param>
/// <param name="indexed"></param>
/// <returns></returns>
private static short SetCustomColor(this HSSFPalette pattern, string rgb, short indexed)
{
if (string.IsNullOrEmpty(rgb))
{
return(-1);
}
string[] colors = rgb.Split(',');
if (colors.Length != 3)
{
return(-1);
}
byte red = 0;
byte green = 0;
byte blue = 0;
// 处理RGB数据
bool result = DealRGB(colors, ref red, ref green, ref blue);
if (result == false)
{
return(-1);
}
var temp = pattern.FindColor(red, green, blue);
if (temp != null)
{
return(temp.Indexed);
}
if (indexed == -1)
{
indexed = 8;
}
// 此位置下画板 原始rgb颜色
string originalColor = originalRGBs.Where(t => t.Item1 == indexed).Select(t => t.Item2).FirstOrDefault();
// 此位置下画板 rgb颜色
string originalColor1 = string.Join(",", pattern.GetColor(indexed).RGB);
// 如果两种颜色不一致，说明此位置已经设置了其他颜色，换个位置去设置
if (originalColor != originalColor1)
{
indexed++;
// 循环判断此位置颜色是否是原始颜色，如果是则设置，否则找其他位置
// 如果此位置已经是最后位置了，则使用开始位置设置
while (originalColor != originalColor1 || indexed < 64)
{
originalColor = originalRGBs.Where(t => t.Item1 == indexed).Select(t => t.Item2).FirstOrDefault();
originalColor1 = string.Join(",", pattern.GetColor(indexed).RGB);
if (originalColor == originalColor1)
{
break;
}
indexed++;
}
if (indexed > 63)
{
indexed = 8;
}
}
pattern.SetColorAtIndex(indexed, red, green, blue);
return(indexed);
}
