NPOI设置单元格格式
NPOI设置单元格格式
设置⽔平居中
//设置style
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellstyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
设置⾏⾼
IRow row1 = sh.CreateRow(1);
row1.Height =20*20;
设置单元的宽度
IWorkbook wb =new HSSFWorkbook();
//创建表
ISheet sh = wb.CreateSheet("zhiyuan");
//设置单元的宽度
sh.SetColumnWidth(0,15*256);
sh.SetColumnWidth(1,35*256);
sh.SetColumnWidth(2,15*256);
sh.SetColumnWidth(3,10*256);
设置单元格颜⾊(固定⾊彩)
使⽤NPOI设置Excel单元格背景颜⾊时，应该设置FillForegroundColor属性，⽽且还要设置FillPattern才⾏。
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PINK.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
设置单元格颜⾊（RGB）
HSSFPalette palette = wk.GetCustomPalette();//调⾊板实例
palette.SetColorAtIndex((short)8,141,180,226);//第⼀个参数：设置调⾊板新增颜⾊的编号，⾃已设置即可；取值范围8-64
HSSFColor hssFColor = palette.FindColor(141,180,226);
style_2.FillForegroundColor = hssFColor.Indexed;htmlborder
style_2.FillPattern = FillPattern.SolidForeground;
//合并操作
sheet.AddMergedRegion(new CellRangeAddress(index["firstRow"], index["lastRow"], index["firstCol"], index["lastCol"]));//起始⾏，结束⾏，起始列，结束列//设置合并后style
var cell = sheet.GetRow(index["firstRow"]).GetCell(index["firstCol"]);
cell.CellStyle = cellstyle;
参考⽂献：
ICell icell1top = row1.CreateCell(0);
icell1top.CellStyle =Getcellstyle(wb, stylexls.头);
icell1top.SetCellValue("⽹站名");
ICell icell2top = row1.CreateCell(1);
icell2top.CellStyle =Getcellstyle(wb, stylexls.头);
//创建⼀个常⽤的xls⽂件
private void button3_Click(object sender, EventArgs e)
private void button3_Click(object sender, EventArgs e)
{
IWorkbook wb =new HSSFWorkbook();
//创建表
ISheet sh = wb.CreateSheet("zhiyuan");
//设置单元的宽度
sh.SetColumnWidth(0,15*256);
sh.SetColumnWidth(1,35*256);
sh.SetColumnWidth(2,15*256);
sh.SetColumnWidth(3,10*256);
int i =0;
#region 练习合并单元格
sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0,0,0,3));
/
/CellRangeAddress（）该⽅法的参数次序是：开始⾏号，结束⾏号，开始列号，结束列号。
IRow row0 = sh.CreateRow(0);
row0.Height =20*20;
ICell icell1top0 = row0.CreateCell(0);
icell1top0.CellStyle =Getcellstyle(wb, stylexls.头);
icell1top0.SetCellValue("标题合并单元");
#endregion
i++;
#region 设置表头
IRow row1 = sh.CreateRow(1);
row1.Height =20*20;
ICell icell1top = row1.CreateCell(0);
icell1top.CellStyle =Getcellstyle(wb, stylexls.头);
icell1top.SetCellValue("⽹站名");
ICell icell2top = row1.CreateCell(1);
icell2top.CellStyle =Getcellstyle(wb, stylexls.头);
icell2top.SetCellValue("⽹址");
ICell icell3top = row1.CreateCell(2);
icell3top.CellStyle =Getcellstyle(wb, stylexls.头);
icell3top.SetCellValue("百度快照");
ICell icell4top = row1.CreateCell(3);
icell4top.CellStyle =Getcellstyle(wb, stylexls.头);
icell4top.SetCellValue("百度收录");
#endregion
using(FileStream stm=File.OpenWrite(@"c:/myMergeCell.xls"))
{
wb.Write(stm);
MessageBox.Show("提⽰：创建成功！");
}
}
#region 定义单元格常⽤到样式的枚举
public enum stylexls
{
头,
url,
时间,
数字,
钱,
百分⽐,
中⽂⼤写,
科学计数法,
默认
}
}
#endregion
#region 定义单元格常⽤到样式
static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义⼏种字体
//也可以⼀种字体，写⼀些公共属性，然后在下⾯需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints =10;
font12.FontName ="微软雅⿊";
IFont font = wb.CreateFont();
font.FontName ="微软雅⿊";
//font.Underline = 1；下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
fontcolorblue.IsItalic =true;//下划线
fontcolorblue.FontName ="微软雅⿊";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
/
/边框颜⾊
cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//背景图形，我没有⽤到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
cellStyle.FillForegroundColor = HSSFColor.WHITE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
cellStyle.FillBackgroundColor = HSSFColor.BLUE.index;
//⽔平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
/
/垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
//⾃动换⾏
cellStyle.WrapText =true;
//缩进；当设置为1时，前⾯留的空⽩太⼤了。希旺官⽹改进。或者是我设置的不对            cellStyle.Indention =0;
//上⾯基本都是设共公的设置
//下⾯列出了常⽤的字段类型
switch(str)
{
case stylexls.头:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline =1;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分⽐:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中⽂⼤写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");                    cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
}
return cellStyle;
}
#endregion

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。