/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="hssfworkbook">workbook对象</param>
/// <param name="font">单元格字体</param>
/// <param name="fillBackgroundColor">单元格背景</param>
/// <param name="ha">垂直对齐方式</param>
/// <param name="va">水平对齐方式</param>
/// <returns></returns>
public static ICellStyle GetCellStyle(IWorkbook hssfworkbook, IFont font, HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va)
{
ICellStyle cellstyle = hssfworkbook.CreateCellStyle();
cellstyle.Alignment = ha;
cellstyle.VerticalAlignment = va;
if (fillBackgroundColor != null)
{
cellstyle.FillBackgroundColor = fillBackgroundColor.GetIndex();
}
if (font != null)
{
cellstyle.SetFont(font);
}
//有边框
cellstyle.BorderBottom = BorderStyle.THIN;
cellstyle.BorderLeft = BorderStyle.THIN;
cellstyle.BorderRight = BorderStyle.THIN;
cellstyle.BorderTop = BorderStyle.THIN;
return(cellstyle);
}
public void TestDefaultPalette()
{
PaletteRecord palette = new PaletteRecord();
//make sure all the HSSFColor constants match
Hashtable colors = HSSFColor.GetIndexHash();
IEnumerator indexes = colors.Keys.GetEnumerator();
while (indexes.MoveNext())
{
int index = (int)indexes.Current;
HSSFColor c = (HSSFColor)colors[index];
short[] rgbTriplet = c.GetTriplet();
byte[] paletteTriplet = palette.GetColor((short)index);
String msg = "Expected HSSFColor constant to match PaletteRecord at index 0x"
+ NPOI.Util.StringUtil.ToHexString(c.GetIndex());
Assert.AreEqual(rgbTriplet[0], paletteTriplet[0] & 0xff, msg);
Assert.AreEqual(rgbTriplet[1], paletteTriplet[1] & 0xff, msg);
Assert.AreEqual(rgbTriplet[2], paletteTriplet[2] & 0xff, msg);
}
}
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();
}
protected override void AssertColour(String hexExpected, IColor actual)
{
Assert.IsNotNull(actual, "Colour must be given");
if (actual is HSSFColor)
{
HSSFColor colour = (HSSFColor)actual;
Assert.AreEqual(hexExpected, colour.GetHexString());
}
else
{
HSSFExtendedColor colour = (HSSFExtendedColor)actual;
if (hexExpected.Length == 8)
{
Assert.AreEqual(hexExpected, colour.ARGBHex);
}
else
{
Assert.AreEqual(hexExpected, colour.ARGBHex.Substring(2));
}
}
}
private void PrintColor()
{
for (int i = sheet.FirstRowNum; i < sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
continue;
}
Console.Write(i + "," + j + ":");
HSSFColor color = (HSSFColor)cell.CellStyle.FillForegroundColorColor;
Console.Write("(" + color.RGB[0] + "," + color.RGB[1] + "," + color.RGB[2] + ") ");
}
Console.WriteLine();
}
}
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;
}
}
/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="hssfworkbook">Excel操作类</param>
/// <param name="font">单元格字体</param>
/// <param name="fillForegroundColor">图案的颜色</param>
/// <param name="fillPattern">图案样式</param>
/// <param name="fillBackgroundColor">单元格背景</param>
/// <param name="ha">垂直对齐方式</param>
/// <param name="va">垂直对齐方式</param>
/// <returns></returns>
private ICellStyle GetCellStyle(IWorkbook hssfworkbook, IFont font, HSSFColor fillForegroundColor, FillPattern fillPattern, HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va, bool haveBorder = true)
{
ICellStyle cellstyle = hssfworkbook.CreateCellStyle();
cellstyle.FillPattern = fillPattern;
cellstyle.Alignment = ha;
cellstyle.VerticalAlignment = va;
if (fillForegroundColor != null)
{
cellstyle.FillForegroundColor = fillForegroundColor.Indexed;
}
else
{
cellstyle.FillPattern = FillPattern.NoFill;
}
if (fillBackgroundColor != null)
{
cellstyle.FillBackgroundColor = fillBackgroundColor.Indexed;
}
if (font != null)
{
cellstyle.SetFont(font);
}
//有边框
if (haveBorder)
{
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
}
return(cellstyle);
}
public override void SetFontColor(System.Drawing.Color color)
{
IWorkbook workbook = _npoiWorksheet.Workbook;
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(_npoiWorksheet.GetColumnStyle(_columnNum));
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);
IFont font = cellStyle.GetFont(workbook);
font.Color = hssFColor.Indexed;
cellStyle.SetFont(font);
}
else
{
//No way!
}
_npoiWorksheet.SetDefaultColumnStyle(_columnNum, cellStyle);
}
protected byte[] GetRGBOrARGB()
{
if (IsIndexed && Index > 0)
{
int indexNum = Index;
var hashIndex = HSSFColor.GetIndexHash();
HSSFColor indexed = null;
if (hashIndex.ContainsKey(indexNum))
{
indexed = hashIndex[indexNum];
}
if (indexed != null)
{
byte[] rgb = new byte[3];
rgb[0] = (byte)indexed.GetTriplet()[0];
rgb[1] = (byte)indexed.GetTriplet()[1];
rgb[2] = (byte)indexed.GetTriplet()[2];
return(rgb);
}
}
// Grab the colour
return(StoredRBG);
}
private HSSFFont MatchFont(Font font)
{
HSSFColor hssfColor = workbook.GetCustomPalette()
.FindColor((byte)foreground.R, (byte)foreground.G, (byte)foreground.B);
if (hssfColor == null)
{
hssfColor = workbook.GetCustomPalette().FindSimilarColor((byte)foreground.R, (byte)foreground.G, (byte)foreground.B);
}
bool bold = font.Bold;
bool italic = font.Italic;
HSSFFont hssfFont = (HSSFFont)workbook.FindFont(bold ? (short)NPOI.SS.UserModel.FontBoldWeight.Bold : (short)NPOI.SS.UserModel.FontBoldWeight.Normal,
hssfColor.GetIndex(),
(short)(font.Size * 20),
font.Name,
italic,
false,
(short)NPOI.SS.UserModel.FontSuperScript.None,
(byte)NPOI.SS.UserModel.FontUnderlineType.None
);
if (hssfFont == null)
{
hssfFont = (HSSFFont)workbook.CreateFont();
hssfFont.Boldweight = (short)(bold ? NPOI.SS.UserModel.FontBoldWeight.Bold : 0);
hssfFont.Color = (hssfColor.GetIndex());
hssfFont.FontHeight = ((short)(font.Size * 20));
hssfFont.FontName = font.Name;
hssfFont.IsItalic = (italic);
hssfFont.IsStrikeout = (false);
hssfFont.TypeOffset = 0;
hssfFont.Underline = 0;
}
return(hssfFont);
}
public void SetColor(int k, int mrow, int mcol, short R, short G, short B)
{
k = k - 1;
string Result = "";
if (wb == null)
{
return;
}
ISheet sheet = wb.GetSheetAt(k);
mrow = mrow - 1;
mcol = mcol - 1;
IRow row = sheet.GetRow(mrow);
if (row == null)
{
row = sheet.CreateRow(mrow);
}
ICell cell = row.GetCell(mcol); //|| (cell.CellType != CellType.String)
if ((cell == null))
{
cell = row.CreateCell(mcol);
}
ICellStyle s = wb.CreateCellStyle();
//HSSFColor.GetIndexHash();
HSSFPalette palette = wb.GetCustomPalette();
HSSFColor hssFColor = palette.FindColor((Byte)R, (Byte)G, (Byte)B);
s.FillForegroundColor = hssFColor.Indexed;
s.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = s;
}
public static string GetColor(HSSFColor color)
{
StringBuilder stringBuilder = new StringBuilder(7);
stringBuilder.Append('#');
foreach (byte s in color.RGB)
{
//if (s < 10)
// stringBuilder.Append('0');
stringBuilder.Append(s.ToString("x2"));
}
string result = stringBuilder.ToString();
if (result.Equals("#ffffff"))
{
return("white");
}
if (result.Equals("#c0c0c0"))
{
return("silver");
}
if (result.Equals("#808080"))
{
return("gray");
}
if (result.Equals("#000000"))
{
return("black");
}
return(result);
}
private void BuildStyle_Border(HSSFWorkbook workbook, StringBuilder style,
String type, BorderStyle xlsBorder, short borderColor)
{
if (xlsBorder == BorderStyle.NONE)
{
return;
}
StringBuilder borderStyle = new StringBuilder();
borderStyle.Append(ExcelToHtmlUtils.GetBorderWidth(xlsBorder));
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetBorderStyle(xlsBorder));
HSSFColor color = workbook.GetCustomPalette().GetColor(borderColor);
if (color != null)
{
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
}
style.Append("border-" + type + ": " + borderStyle + "; ");
}
/**
* org.Openxmlformats.schemas.spreadsheetml.x2006.main.CTRPrElt to
* org.Openxmlformats.schemas.Drawingml.x2006.main.CTFont adapter
*/
private static void ApplyAttributes(CT_RPrElt pr, CT_TextCharacterProperties rPr)
{
if (pr.SizeOfBArray() > 0)
{
rPr.b = (/*setter*/ pr.GetBArray(0).val);
}
if (pr.SizeOfUArray() > 0)
{
ST_UnderlineValues u1 = pr.GetUArray(0).val;
if (u1 == ST_UnderlineValues.single)
{
rPr.u = (/*setter*/ ST_TextUnderlineType.sng);
}
else if (u1 == ST_UnderlineValues.@double)
{
rPr.u = (/*setter*/ ST_TextUnderlineType.dbl);
}
else if (u1 == ST_UnderlineValues.none)
{
rPr.u = (/*setter*/ ST_TextUnderlineType.none);
}
}
if (pr.SizeOfIArray() > 0)
{
rPr.i = (/*setter*/ pr.GetIArray(0).val);
}
if (pr.SizeOfRFontArray() > 0)
{
CT_TextFont rFont = rPr.IsSetLatin() ? rPr.latin : rPr.AddNewLatin();
rFont.typeface = (/*setter*/ pr.GetRFontArray(0).val);
}
if (pr.SizeOfSzArray() > 0)
{
int sz = (int)(pr.GetSzArray(0).val * 100);
rPr.sz = (/*setter*/ sz);
}
if (pr.SizeOfColorArray() > 0)
{
CT_SolidColorFillProperties fill = rPr.IsSetSolidFill() ? rPr.solidFill : rPr.AddNewSolidFill();
NPOI.OpenXmlFormats.Spreadsheet.CT_Color xlsColor = pr.GetColorArray(0);
if (xlsColor.IsSetRgb())
{
CT_SRgbColor clr = fill.IsSetSrgbClr() ? fill.srgbClr : fill.AddNewSrgbClr();
clr.val = (/*setter*/ xlsColor.rgb);
}
else if (xlsColor.IsSetIndexed())
{
HSSFColor indexed = (HSSFColor)HSSFColor.GetIndexHash()[((int)xlsColor.indexed)];
if (indexed != null)
{
byte[] rgb = new byte[3];
rgb[0] = (byte)indexed.GetTriplet()[0];
rgb[1] = (byte)indexed.GetTriplet()[1];
rgb[2] = (byte)indexed.GetTriplet()[2];
CT_SRgbColor clr = fill.IsSetSrgbClr() ? fill.srgbClr : fill.AddNewSrgbClr();
clr.val = (/*setter*/ rgb);
}
}
}
}
void BuildStyle_Font(IWorkbook workbook, StringBuilder style,
IFont font)
{
switch (font.Boldweight)
{
case (short)FontBoldWeight.Bold:
style.Append("font-weight: bold; ");
break;
case (short)FontBoldWeight.Normal:
// by default, not not increase HTML size
// style.Append( "font-weight: normal; " );
break;
}
if (workbook is HSSFWorkbook)
{
HSSFColor fontColor = ((HSSFWorkbook)workbook).GetCustomPalette()?.GetColor(font.Color);
if (fontColor != null)
{
style.AppendFormat("color:{0}; ", ExcelToHtmlUtils.GetColor(fontColor));
}
}
else
{
IndexedColors clr = IndexedColors.ValueOf(font.Color);
string hexstring = null;
if (clr != null)
{
hexstring = clr.HexString;
}
else
{
StylesTable st = ((XSSFWorkbook)workbook).GetStylesSource();
XSSFColor fontColor = null;
if (st != null && st.GetTheme() != null)
{
fontColor = st.GetTheme().GetThemeColor(font.Color);
}
else
{
fontColor = ((XSSFFont)font).GetXSSFColor();
}
if (fontColor != null)
{
hexstring = ExcelToHtmlUtils.GetColor(fontColor);
}
}
if (hexstring != null)
{
style.AppendFormat("color:{0}; ", hexstring);
}
}
if (font.FontHeightInPoints != 0)
{
style.Append("font-size: " + font.FontHeightInPoints + "pt; ");
}
if (font.IsItalic)
{
style.Append("font-style: italic; ");
}
}
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());
}
//NPOI.HSSF.Util.HSSFColor.BLUE.index
public static void SetBackColor(this ISheet ISheet1, int rowId, int columnId, HSSFColor color)
{
var style1 = ISheet1.Workbook.CreateFillColor(color);
ISheet1.CreateRow(rowId).CreateCell(columnId).CellStyle = style1;
}
public static void SetBackColor(this ICell cell, HSSFColor color)
{
var style1 = cell.Sheet.Workbook.CreateFillColor(color);
cell.CellStyle = style1;
}
public void SetFontColor(HSSFColor color)
{
fontColor = color;
}
public static MemoryStream ProductRenderToExcel(DataTable table, int count)
{
MemoryStream ms = new MemoryStream();
using (table)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
// handling header.
HSSFColor color = new HSSFColor();
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
headerRow.Cells[column.Ordinal].CellStyle = GetCellStyle(workbook,
GetFontStyle(workbook, "宋体", color, 12), null, FillPatternType.NO_FILL, null, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
if (rowIndex == count)
{
IRow dataRow = sheet.CreateRow(rowIndex + 5);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
dataRow.Cells[column.Ordinal].CellStyle = GetCellStyle(workbook, null, null,
FillPatternType.NO_FILL, null,
HorizontalAlignment.LEFT,
VerticalAlignment.CENTER);
}
rowIndex += 6;
}
else if (rowIndex > count)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
dataRow.Cells[column.Ordinal].CellStyle = GetCellStyle(workbook, null, null,
FillPatternType.NO_FILL, null,
HorizontalAlignment.LEFT,
VerticalAlignment.CENTER);
dataRow.Cells[column.Ordinal].CellStyle.WrapText = true;
}
dataRow.Height = 150 * 20;
}
else
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
dataRow.Cells[column.Ordinal].CellStyle = GetCellStyle(workbook, null, null,
FillPatternType.NO_FILL, null,
HorizontalAlignment.LEFT,
VerticalAlignment.CENTER);
}
rowIndex++;
}
}
AutoSizeColumns(sheet);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 17));
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 22));
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 32, 34));
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return(ms);
}
IndexedColors(int idx, HSSFColor color)
{
index = idx;
this.hssfColor = color;
}
/// <summary>
/// 返回单元格样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="fontSize"></param>
/// <param name="horizontalAlignment"></param>
/// <param name="verticalAlignment"></param>
/// <param name="color"></param>
/// <returns></returns>
private static ICellStyle GetCellStyle(IWorkbook workbook, double fontSize, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, HSSFColor color = null)
{
var style = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.FontHeightInPoints = fontSize;
if (color != null)
{
font.Color = color.Indexed;
}
style.VerticalAlignment = verticalAlignment;
style.Alignment = horizontalAlignment;
style.SetFont(font);
return(style);
}
public static short ConvertToColor(this string v)
{
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);
/*switch (v)
* {
* case "AQUA":
* return 49;
*
* case "AUTOMATIC":
* return 64;
*
* case "BLACK":
* return 8;
*
* case "BLUE":
* return 39;
*
* case "BLUE_GREY":
* case "BLUEGREY":
* return 54;
*
* case "BRIGHT_GREEN":
* case "BRIGHTGREEN":
* return 35;
*
* case "BROWN":
* return 60;
*
* case "CORAL":
* return 29;
*
* case "CORNFLOWER_BLUE":
* case "CORNFLOWERBLUE":
* return 24;
*
* case "DARK_BLUE":
* case "DARKBLUE":
* return 32;
*
* case "DARK_GREEN":
* case "DARKGREEN":
* return 58;
* case "DARK_RED":
* case "DARKRED":
* return 37;
*
* case "DARK_TEAL":
* case "DARKTEAL":
* return 56;
*
* case "DARK_YELLOW":
* case "DARKYELLOW":
* return 19;
*
* case "GOLD":
* return 51;
*
* case "GREEN":
* return 17;
* case "GREY_25_PERCENT":
* case "GREY25PERCENT":
* return 22;
*
* case "GREY_40_PERCENT":
* case "GREY40PERCENT":
* return 55;
*
* case "GREY_50_PERCENT":
* case "GREY50PERCENT":
* return 23;
*
* case "GREY_80_PERCENT":
* case "GREY80PERCENT":
* return 63;
*
* case "INDIGO":
* return 62;
*
* case "LAVENDER":
* return 46;
*
* case "LEMON_CHIFFON":
* case "LEMONCHIFFON":
* return 26;
*
* case "LIGHT_BLUE":
* case "LIGHTBLUE":
* return 48;
*
* case "LIGHT_CORNFLOWERBLUE":
* case "LIGHTCORNFLOWERBLUE":
* return 31;
*
* case "LIGHT_GREEN":
* case "LIGHTGREEN":
* return 42;
*
* case "LIGHT_ORANGE":
* case "LIGHTORANGE":
* return 52;
*
* case "LIGHT_TURQUOISE":
* case "LIGHTTURQUOISE":
* return 27;
*
* case "LIGHT_YELLOW":
* case "LIGHTYELLOW":
* return 43;
*
* case "LIME":
* return 50;
*
* case "MAROON":
* return 25;
*
* case "OLIVE_GREEN":
* case "OLIVEGREEN":
* return 59;
*
* case "ORANGE":
* return 53;
*
* case "ORCHID":
* return 28;
*
* case "PALE_BLUE":
* case "PALEBLUE":
* return 44;
*
* case "PINK":
* return 33;
*
* case "PLUM":
* return 25;
*
* case "RED":
* return 10;
*
* case "ROSE":
* return 45;
*
* case "ROYAL_BLUE":
* case "ROYALBLUE":
* return 30;
*
* case "SEA_GREEN":
* case "SEAGREEN":
* return 57;
*
* case "SKY_BLUE":
* case "SKYBLUE":
* return 40;
*
* case "TAN":
* return 47;
*
* case "TEAL":
* return 38;
*
* case "TURQUOISE":
* return 35;
*
* case "VIOLET":
* return 36;
*
* case "WHITE":
* return 9;
*
* case "YELLOW":
* return 34;
*
* default:
* return 32767;
* };*/
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="lists"></param>
/// <param name="head">中文列名对照</param>
/// <param name="fileName">文件名</param>
public static void ExportExcel(List <T> lists, Dictionary <string, string> head, string fileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = null;
HSSFRow headerRow = null;
//设置表头样式
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeight = 14 * 14;
font.Boldweight = 1000;
headStyle.SetFont(font);
//设置分组样式
HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex((byte)8, (byte)204, (byte)204, (byte)0);
HSSFColor hssFColor = palette.FindColor((byte)204, (byte)204, (byte)0);
ICellStyle GroupStyle = workbook.CreateCellStyle();
GroupStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
GroupStyle.FillForegroundColor = hssFColor.GetIndex();
IFont Groupfont = workbook.CreateFont();
Groupfont.Boldweight = 1000;
GroupStyle.SetFont(Groupfont);
bool h = false;
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
var mod = lists.Count() % 65535;
var index = lists.Count() / 65535;
if (mod > 0)
{
index = index + 1;
}
//没有数据时导出表头
if (index == 0)
{
int i = 0;
sheet = workbook.CreateSheet("Sheet0") as HSSFSheet;
headerRow = sheet.CreateRow(0) as HSSFRow;
foreach (var dic in head)//循环列表头集合作为列数
{
string[] names = dic.Key.ToString().Split('@');
string colname = dic.Value.ToString();
ICell cell = headerRow.CreateCell(i);
cell.CellStyle = headStyle;
cell.SetCellValue(colname);
i++;
}
}
for (int idx = 1; idx <= index; idx++)
{
sheet = workbook.CreateSheet("Sheet" + idx) as HSSFSheet;
headerRow = sheet.CreateRow(0) as HSSFRow;
var count = 65535;
if (idx == index)
{
count = mod;
}
for (var j = 0; j < count; j++)//循环记录总数作为行数
{
HSSFRow dataRow = sheet.CreateRow(j + 1) as HSSFRow;
int i = 0;
foreach (var dic in head)//循环列表头集合作为列数
{
string[] names = dic.Key.ToString().Split('@');
string colname = dic.Value.ToString();
string name = names[0];
bool isTrue = false;//是否基础数据
if (names.Length == 2)
{
isTrue = bool.Parse(names[1]);
}
var info = properties.Where(x => x.Name == name).FirstOrDefault();
object value = info == null ? null : info.GetValue(lists[65535 * (idx - 1) + j], null);
string colvalue = value == null ? "" : value.ToString();
//if (isTrue)//获取基础数据
//{
// colvalue = HtmlExtensions.GetBasicObjectNameByValue(colvalue);
//}
if (!h)
{
if ((!name.Equals("IsGroup")))
{
ICell cell = headerRow.CreateCell(i);
cell.CellStyle = headStyle;
cell.SetCellValue(colname);
if (value != null)
{
Type t = value.GetType();
string strt = t.Name;
if (t.Name == "Nullable`1")
{
strt = t.GetGenericArguments()[0].Name;
}
switch (strt)
{
case "Decimal":
case "Double":
dataRow.CreateCell(i).SetCellValue(Double.Parse(value.ToString()));
break;
case "Int":
dataRow.CreateCell(i).SetCellValue(int.Parse(value.ToString()));
break;
case "Float":
dataRow.CreateCell(i).SetCellValue(float.Parse(value.ToString()));
break;
default:
dataRow.CreateCell(i).SetCellValue(colvalue);
break;
}
}
else
{
dataRow.CreateCell(i).SetCellValue(colvalue);
}
}
}
else
{
if ((!name.Equals("IsGroup")))
{
if (value != null)
{
Type t = value.GetType();
string strt = t.Name;
if (t.Name == "Nullable`1")
{
strt = t.GetGenericArguments()[0].Name;
}
switch (strt)
{
case "Decimal":
case "Double":
dataRow.CreateCell(i).SetCellValue(Double.Parse(value.ToString()));
break;
case "Int":
dataRow.CreateCell(i).SetCellValue(int.Parse(value.ToString()));
break;
case "Float":
dataRow.CreateCell(i).SetCellValue(float.Parse(value.ToString()));
break;
default:
dataRow.CreateCell(i).SetCellValue(colvalue);
break;
}
}
else
{
dataRow.CreateCell(i).SetCellValue(colvalue);
}
}
}
//========================对特定标志行设置颜色========================
if (name.Equals("IsGroup") && colvalue.Equals("Y"))
{
for (int m = 0; m < i; m++)
{
dataRow.GetCell(m).CellStyle = GroupStyle;
}
}
//================================end=================================
i++;
}
h = true;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
/// <summary>
/// 获取字体样式
/// </summary>
/// <param name="hssfworkbook">Excel操作类</param>
/// <param name="fontname">字体名</param>
/// <param name="fontcolor">字体颜色</param>
/// <param name="fontsize">字体大小</param>
/// <returns></returns>
public static IFont GetFontStyle(HSSFWorkbook hssfworkbook, string fontfamily, HSSFColor fontcolor, int fontsize)
{
IFont font1 = hssfworkbook.CreateFont();
if (string.IsNullOrEmpty(fontfamily))
{
font1.FontName = fontfamily;
}
if (fontcolor != null)
{
font1.Color = fontcolor.Indexed; //.GetIndex();
}
font1.IsItalic = true;
font1.FontHeightInPoints = (short)fontsize;
return(font1);
}
public void SetForegroundColor(HSSFColor color)
{
this.foregroundColor = color;
}
public void Compare(HSSFColor expected, HSSFColor palette)
{
Assert.AreEqual(expected.GetHexString(), palette.GetHexString());
}
public void SetBorderColor(HSSFColor topBorderColor, HSSFColor bottomBorderColor, HSSFColor leftBorderColor, HSSFColor rightBorderColor)
{
this.topBorderColor = topBorderColor;
this.bottomBorderColor = bottomBorderColor;
this.leftBorderColor = leftBorderColor;
this.rightBorderColor = rightBorderColor;
}
static CellFormatPart()
{
NAMED_COLORS = new Dictionary <String, Color>(CASE_INSENSITIVE_ORDER);
Hashtable colors = HSSFColor.GetIndexHash();
foreach (object v in colors.Values)
{
HSSFColor hc = (HSSFColor)v;
Type type = hc.GetType();
String name = type.Name;
if (name.Equals(name.ToUpper()))
{
short[] rgb = hc.GetTriplet();
Color c = Color.FromArgb(rgb[0], rgb[1], rgb[2]);
if (!NAMED_COLORS.ContainsKey(name))
{
NAMED_COLORS.Add(name, c);
}
if (name.IndexOf('_') > 0)
{
if (!NAMED_COLORS.ContainsKey(name.Replace('_', ' ')))
{
NAMED_COLORS.Add(name.Replace('_', ' '), c);
}
}
if (name.IndexOf("_PERCENT") > 0)
{
if (!NAMED_COLORS.ContainsKey(name.Replace("_PERCENT", "%").Replace('_', ' ')))
{
NAMED_COLORS.Add(name.Replace("_PERCENT", "%").Replace('_', ' '), c);
}
}
}
}
// A condition specification
String condition = "([<>=]=?|!=|<>) # The operator\n" +
" \\s*([0-9]+(?:\\.[0-9]*)?)\\s* # The constant to test against\n";
String color =
"\\[(black|blue|cyan|green|magenta|red|white|yellow|color [0-9]+)\\]";
// A number specification
// Note: careful that in something like ##, that the trailing comma is not caught up in the integer part
// A part of a specification
String part = "\\\\. # Quoted single character\n" +
"|\"([^\\\\\"]|\\\\.)*\" # Quoted string of characters (handles escaped quotes like \\\") \n" +
"|_. # Space as wide as a given character\n" +
"|\\*. # Repeating fill character\n" +
"|@ # Text: cell text\n" +
"|([0?\\#](?:[0?\\#,]*)) # Number: digit + other digits and commas\n" +
"|e[-+] # Number: Scientific: Exponent\n" +
"|m{1,5} # Date: month or minute spec\n" +
"|d{1,4} # Date: day/date spec\n" +
"|y{2,4} # Date: year spec\n" +
"|h{1,2} # Date: hour spec\n" +
"|s{1,2} # Date: second spec\n" +
"|am?/pm? # Date: am/pm spec\n" +
"|\\[h{1,2}\\] # Elapsed time: hour spec\n" +
"|\\[m{1,2}\\] # Elapsed time: minute spec\n" +
"|\\[s{1,2}\\] # Elapsed time: second spec\n" +
"|[^;] # A character\n" + "";
String format = "(?:" + color + ")? # Text color\n" +
"(?:\\[" + condition + "\\])? # Condition\n" +
"((?:" + part + ")+) # Format spec\n";
RegexOptions flags = RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled;
COLOR_PAT = new Regex(color, flags);
CONDITION_PAT = new Regex(condition, flags);
SPECIFICATION_PAT = new Regex(part, flags);
FORMAT_PAT = new Regex(format, flags);
// Calculate the group numbers of important groups. (They shift around
// when the pattern is Changed; this way we figure out the numbers by
// experimentation.)
COLOR_GROUP = FindGroup(FORMAT_PAT, "[Blue]@", "Blue");
CONDITION_OPERATOR_GROUP = FindGroup(FORMAT_PAT, "[>=1]@", ">=");
CONDITION_VALUE_GROUP = FindGroup(FORMAT_PAT, "[>=1]@", "1");
SPECIFICATION_GROUP = FindGroup(FORMAT_PAT, "[Blue][>1]\\a ?", "\\a ?");
}
public void SetBackgroundColor(HSSFColor color)
{
this.backgroundColor = color;
}