/// <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);
}
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);
}
/// <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">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>
public static ICellStyle GetCellStyle(IWorkbook workbook, IFont font, HSSFColor fillForegroundColor, FillPatternType fillPattern, HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va)
{
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.FillPattern = fillPattern;
cellstyle.Alignment = ha;
cellstyle.VerticalAlignment = va;
if (fillForegroundColor != null)
{
cellstyle.FillForegroundColor = fillForegroundColor.GetIndex();
}
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);
}
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);
}
/// <summary>
/// 获取字体样式
/// </summary>
/// <param name="workbook">Excel操作类</param>
/// <param name="fontname">字体名</param>
/// <param name="fontcolor">字体颜色</param>
/// <param name="fontsize">字体大小</param>
/// <returns></returns>
public static IFont GetFontStyle(IWorkbook workbook, string fontfamily, HSSFColor fontcolor, int fontsize)
{
IFont font1 = workbook.CreateFont();
if (string.IsNullOrEmpty(fontfamily))
{
font1.FontName = fontfamily;
}
if (fontcolor != null)
{
font1.Color = fontcolor.GetIndex();
}
font1.IsItalic = true;
font1.FontHeightInPoints = (short)fontsize;
return(font1);
}
/// <summary>
/// 获取字体样式
/// </summary>
/// <param name="hssfworkbook">workbook</param>
/// <param name="fontfamily">字体</param>
/// <param name="fontcolor">颜色</param>
/// <param name="fontsize">大小</param>
/// <param name="isWeight">是否加粗，默认不需要</param>
/// <returns></returns>
public static IFont GetFontStyle(IWorkbook hssfworkbook, string fontfamily, HSSFColor fontcolor, int fontsize, bool isWeight = false)
{
IFont font1 = hssfworkbook.CreateFont();
if (string.IsNullOrEmpty(fontfamily))
{
font1.FontName = fontfamily;
}
if (fontcolor != null)
{
font1.Color = fontcolor.GetIndex();
}
if (isWeight)
{//是否加粗
font1.Boldweight = short.MaxValue;
}
font1.IsItalic = true;
font1.FontHeightInPoints = (short)fontsize;
return(font1);
}
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);
}
}
/// <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>
/// 普通单表导出npoi（集合数据+样式）
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button2_Click(object sender, EventArgs e)
{
string filename = "test.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
Response.Clear();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1名称");
CellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.FillBackgroundColor = HSSFColor.PINK.index;
var row0 = sheet1.CreateRow(0).CreateCell(0);
row0.SetCellValue("This is a Sample");//sheet标题
row0.CellStyle = style;
var j = 17;
#region 居中/自动换行
CellStyle styleCenter = hssfworkbook.CreateCellStyle(); //样式
styleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //文字水平对齐方式
styleCenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; //文字垂直对齐方式
styleCenter.WrapText = true; //自动换行
sheet1.CreateRow(j).CreateCell(j).CellStyle = styleCenter;
var cell17 = sheet1.CreateRow(j).CreateCell(j);
var cell172 = sheet1.CreateRow(j).CreateCell(j + 1);
cell17.CellStyle = styleCenter;
cell17.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客");
//cell172.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客");
j++;
#endregion
#region 设置宽高度
sheet1.SetColumnWidth(1, 20 * 256); //宽度-每个字符宽度是1/256。 所以20 * 256就是20个字符宽度。
var rowwh = sheet1.CreateRow(j);
rowwh.HeightInPoints = 50; //高度
rowwh.CreateCell(j).SetCellValue("宽高度");
j++;
#endregion
#region 自适应宽度（对中文不友好）+自动换行
/*场景：
* 12林学1班
* 12林学1班
*/
CellStyle autoAndWrap = hssfworkbook.CreateCellStyle(); //样式
autoAndWrap.WrapText = true; //自动换行
var rowwhauto = sheet1.CreateRow(j);
var cellauto = rowwhauto.CreateCell(j);
cellauto.SetCellValue(j + "自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度");
sheet1.AutoSizeColumn(j);
cellauto.CellStyle = autoAndWrap;
j++;
#endregion
#region 设置背景色
CellStyle style1 = hssfworkbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index;
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
sheet1.CreateRow(j).CreateCell(j).CellStyle = style1;
j++;
#endregion
#region 自定义背景色
HSSFPalette palette = hssfworkbook.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex((short)8, (byte)184, (byte)204, (byte)228);
HSSFColor hssFColor = palette.FindColor((byte)184, (byte)204, (byte)228);
CellStyle style2 = hssfworkbook.CreateCellStyle();
style2.FillPattern = FillPatternType.SOLID_FOREGROUND;
style2.FillForegroundColor = hssFColor.GetIndex();
sheet1.CreateRow(j).CreateCell(j).CellStyle = style2;
j++;
#endregion
#region 设置字体颜色
CellStyle style3 = hssfworkbook.CreateCellStyle();
Font font1 = hssfworkbook.CreateFont();
font1.Color = hssFColor.GetIndex();//颜色
style3.SetFont(font1);
var cell20 = sheet1.CreateRow(j).CreateCell(j);
cell20.CellStyle = style3;
cell20.SetCellValue("666666666");
j++;
#endregion
List <ModelStu> data = StuDaTa.GetData();
string[] arrthead = { "ID", "name", "age", "pc" };
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, arrthead.Length - 1));
Row row1 = sheet1.CreateRow(1);
for (int i = 0; i < arrthead.Length; i++)
{
row1.CreateCell(i).SetCellValue(arrthead[i]);
}
for (int i = 0; i < data.Count; i++)
{
Row row = sheet1.CreateRow(i + 2);
var colIndex = 0;
row.CreateCell(colIndex++).SetCellValue(data[i].id);
row.CreateCell(colIndex++).SetCellValue(data[i].name);
row.CreateCell(colIndex++).SetCellValue(data[i].age);
row.CreateCell(colIndex).SetCellValue(data[i].pc);
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
Response.BinaryWrite(file.GetBuffer());
Response.End();
hssfworkbook = null;
file.Close();
file.Dispose();
}
