public void TestCreateSave()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s1 = wb.CreateSheet();
IRow r1 = s1.CreateRow(0);
ICell r1c1 = r1.CreateCell(0);
r1c1.SetCellValue(2.2);
int num0 = wb.NumberOfFonts;
IFont font = wb.CreateFont();
font.Boldweight = (short)(FontBoldWeight.BOLD);
font.IsStrikeout = (true);
font.Color = (IndexedColors.YELLOW.Index);
font.FontName = ("Courier");
short font1Idx = font.Index;
wb.CreateCellStyle().SetFont(font);
Assert.AreEqual(num0 + 1, wb.NumberOfFonts);
ICellStyle cellStyleTitle = wb.CreateCellStyle();
cellStyleTitle.SetFont(font);
r1c1.CellStyle = (cellStyleTitle);
// Save and re-load
wb = _testDataProvider.WriteOutAndReadBack(wb);
s1 = wb.GetSheetAt(0);
Assert.AreEqual(num0 + 1, wb.NumberOfFonts);
short idx = s1.GetRow(0).GetCell(0).CellStyle.FontIndex;
IFont fnt = wb.GetFontAt(idx);
Assert.IsNotNull(fnt);
Assert.AreEqual(IndexedColors.YELLOW.Index, fnt.Color);
Assert.AreEqual("Courier", fnt.FontName);
// Now add an orphaned one
IFont font2 = wb.CreateFont();
font2.IsItalic = (true);
font2.FontHeightInPoints = (short)15;
short font2Idx = font2.Index;
wb.CreateCellStyle().SetFont(font2);
Assert.AreEqual(num0 + 2, wb.NumberOfFonts);
// Save and re-load
wb = _testDataProvider.WriteOutAndReadBack(wb);
s1 = wb.GetSheetAt(0);
Assert.AreEqual(num0 + 2, wb.NumberOfFonts);
Assert.IsNotNull(wb.GetFontAt(font1Idx));
Assert.IsNotNull(wb.GetFontAt(font2Idx));
Assert.AreEqual(15, wb.GetFontAt(font2Idx).FontHeightInPoints);
Assert.IsTrue(wb.GetFontAt(font2Idx).IsItalic);
}
public void TestNumberOfFonts()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
int num0 = wb.NumberOfFonts;
IFont f1 = wb.CreateFont();
f1.Boldweight = (short)(FontBoldWeight.BOLD);
short idx1 = f1.Index;
wb.CreateCellStyle().SetFont(f1);
IFont f2 = wb.CreateFont();
f2.Underline = (byte)(FontUnderlineType.DOUBLE);
short idx2 = f2.Index;
wb.CreateCellStyle().SetFont(f2);
IFont f3 = wb.CreateFont();
f3.FontHeightInPoints = ((short)23);
short idx3 = f3.Index;
wb.CreateCellStyle().SetFont(f3);
Assert.AreEqual(num0 + 3, wb.NumberOfFonts);
Assert.AreEqual((short)FontBoldWeight.BOLD, wb.GetFontAt(idx1).Boldweight);
Assert.AreEqual((byte)FontUnderlineType.DOUBLE, wb.GetFontAt(idx2).Underline);
Assert.AreEqual(23, wb.GetFontAt(idx3).FontHeightInPoints);
}
public static IFont GetFont(IWorkbook wb, int type)
{
if (type == 0)
{
IFont font = wb.CreateFont();
font.FontHeightInPoints = 13;
font.Boldweight = 13;
font.FontName = "微软雅黑";
return(font);
}
if (type == 1)
{
IFont font2 = wb.CreateFont();
font2.FontName = "微软雅黑";
return(font2);
}
if (type == 2)
{
IFont font3 = wb.CreateFont();
font3.Color = 12;
font3.IsItalic = true;
font3.Underline = FontUnderlineType.Single;
font3.FontName = "微软雅黑";
return(font3);
}
IFont font4 = wb.CreateFont();
font4.FontName = "微软雅黑";
return(font4);
}
/// <summary>
/// 设置单元格样式（“行”单元格）
/// </summary>
/// <returns></returns>
private ICellStyle SetRowCellStyle()
{
ICellStyle cellStyle = workbook.CreateCellStyle();
//1、内容位置（居中）
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//2、字体设置
IFont font = workbook.CreateFont();
font.FontName = "微软雅黑"; //字体名称
font.FontHeightInPoints = 10; //字号
font.Color = HSSFColor.Black.Index; //字体颜色
//font.Underline = FontUnderlineType.None; //下划线
font.IsStrikeout = false; //删除线
//3、背景色、前景色
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index; //图案颜色
cellStyle.FillPattern = FillPattern.NoFill; //图案样式
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.White.Index; //背景色
//4、设置边框样式
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
//5、设置边框颜色
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.SetFont(font);
return(cellStyle);
}
public SimpleExcelWorkbook(bool useXlsxIn)
{
if (useXlsxIn)
{
this.workbook = new XSSFWorkbook();
}
else
{
this.workbook = new HSSFWorkbook();
}
this.useXlsx = useXlsxIn;
// Set headers font.
ICellStyle bigTitleStyle = workbook.CreateCellStyle();
IFont bigTitleFont = workbook.CreateFont();
bigTitleFont.Boldweight = (short)FontBoldWeight.Bold;
bigTitleFont.FontHeightInPoints = 20;
bigTitleStyle.SetFont(bigTitleFont);
this.cellStyles.Add("BigTitle", bigTitleStyle);
ICellStyle titleStyle = workbook.CreateCellStyle();
IFont titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)FontBoldWeight.Bold;
titleStyle.SetFont(titleFont);
this.cellStyles.Add("Title", titleStyle);
}
private static Dictionary <String, ICellStyle> createStyles(IWorkbook workbook)
{
// Variables
IFont font;
ICellStyle style;
Dictionary <String, ICellStyle> styles = new Dictionary <String, ICellStyle>();
// Header cell
font = workbook.CreateFont();
font.Color = IndexedColors.White.Index;
font.FontHeightInPoints = 9;
font.IsBold = true;
font.FontName = "Tahoma";
style = CreateBorderedStyle(workbook);
style.Alignment = HorizontalAlignment.Center;
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = (IndexedColors.Green.Index);
style.SetFont(font);
styles.Add("header", style);
// Normal cell
font = workbook.CreateFont();
font.Color = IndexedColors.Black.Index;
font.FontHeightInPoints = 9;
font.FontName = "Tahoma";
style = CreateBorderedStyle(workbook);
style.SetFont(font);
styles.Add("normal", style);
return(styles);
}
/**
* Create a library of cell styles
*/
private static Dictionary <String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary <String, ICellStyle> styles = new Dictionary <String, ICellStyle>();
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = ((short)18);
titleFont.Boldweight = (short)FontBoldWeight.BOLD;
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = ((short)11);
monthFont.Color = (IndexedColors.WHITE.Index);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_50_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(monthFont);
style.WrapText = (true);
styles.Add("header", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
style.BorderRight = BorderStyle.THIN;
style.RightBorderColor = (IndexedColors.BLACK.Index);
style.BorderLeft = BorderStyle.THIN;
style.LeftBorderColor = (IndexedColors.BLACK.Index);
style.BorderTop = BorderStyle.THIN;
style.TopBorderColor = (IndexedColors.BLACK.Index);
style.BorderBottom = BorderStyle.THIN;
style.BottomBorderColor = (IndexedColors.BLACK.Index);
styles.Add("cell", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = (wb.CreateDataFormat().GetFormat("0.00"));
styles.Add("formula", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_40_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula_2", style);
return(styles);
}
/**
* Create a library of cell styles
*/
private static Dictionary <String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary <String, ICellStyle> styles = new Dictionary <String, ICellStyle>();
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = 18;
titleFont.IsBold = true;
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = 11;
monthFont.Color = (IndexedColors.White.Index);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey50Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(monthFont);
style.WrapText = (true);
styles.Add("header", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
style.BorderRight = BorderStyle.Thin;
style.RightBorderColor = (IndexedColors.Black.Index);
style.BorderLeft = BorderStyle.Thin;
style.LeftBorderColor = (IndexedColors.Black.Index);
style.BorderTop = BorderStyle.Thin;
style.TopBorderColor = (IndexedColors.Black.Index);
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = (IndexedColors.Black.Index);
styles.Add("cell", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey40Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula_2", style);
return(styles);
}
/// <summary>
/// 字体颜色
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
public void FontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
{
ICellStyle style = _myExcel.CreateCellStyle();
//新建一个字体样式对象
IFont font = _myExcel.CreateFont();
font.Color = (short)color;
style.SetFont(font);
for (int rowIndex = startRow; rowIndex <= endRow; rowIndex++)
{
IRow row = _activeSheet.GetRow(rowIndex);
if (null == row)
{
row = _activeSheet.CreateRow(rowIndex);
}
for (int colIndex = startColumn; colIndex <= endColumn; colIndex++)
{
ICell cell = row.GetCell(colIndex);
if (null == cell)
{
cell = row.CreateCell(colIndex);
}
cell.CellStyle = style;
}
}
}
private ICellStyle CreateTitleStyle()
{
IFont titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = TITLE_FONT_SIZE;
ICellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
return(titleStyle);
}
/// <summary>
/// 设置字体加粗
/// </summary>
/// <returns></returns>
private ICellStyle setFontBold()
{
IFont headerFont = workbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.FontName = "微软雅黑";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.SetFont(headerFont);
return(cellStyle);
}
void InitStyles(IWorkbook workbook)
{
BoldCourierNew = workbook.CreateFont();
BoldCourierNew.FontHeightInPoints = 11;
BoldCourierNew.FontName = "Courier New";
BoldCourierNew.IsBold = true;
CourierNew = workbook.CreateFont();
CourierNew.FontHeightInPoints = 11;
CourierNew.FontName = "Courier New";
ExpressionStyle = workbook.CreateCellStyle();
ExpressionStyle.WrapText = true;
ExpressionStyle.SetFont(CourierNew);
}
static void WriteHeaderRow(IWorkbook wb, ISheet sheet)
{
sheet.SetColumnWidth(0, 6000);
sheet.SetColumnWidth(1, 6000);
sheet.SetColumnWidth(2, 3600);
sheet.SetColumnWidth(3, 3600);
sheet.SetColumnWidth(4, 2400);
sheet.SetColumnWidth(5, 2400);
sheet.SetColumnWidth(6, 2400);
sheet.SetColumnWidth(7, 2400);
sheet.SetColumnWidth(8, 2400);
IRow row = sheet.CreateRow(0);
ICellStyle style = wb.CreateCellStyle();
IFont font = wb.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
style.SetFont(font);
WriteHeaderCell(row, 0, "Raw Long Bits A", style);
WriteHeaderCell(row, 1, "Raw Long Bits B", style);
WriteHeaderCell(row, 2, "Value A", style);
WriteHeaderCell(row, 3, "Value B", style);
WriteHeaderCell(row, 4, "Exp Cmp", style);
WriteHeaderCell(row, 5, "LT", style);
WriteHeaderCell(row, 6, "EQ", style);
WriteHeaderCell(row, 7, "GT", style);
WriteHeaderCell(row, 8, "Check", style);
}
/// <summary>
/// 单元格样式
/// </summary>
/// <param name="workbook">工作区</param>
/// <param name="cellType">单元格类型</param>
/// <returns></returns>
private static ICellStyle CreateCellStyle(IWorkbook workbook, string cellType)
{
ICellStyle cellStyle = workbook.CreateCellStyle(); //单元格 并设置样式
IFont font = workbook.CreateFont(); //字体
//居中
cellStyle.Alignment = HorizontalAlignment.LEFT;
//垂直居中
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
//设置字体
font.Color = HSSFColor.OLIVE_GREEN.BLACK.index;
cellStyle.SetFont(font);
switch (cellType)
{
case "tbHeadName":
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.GetFont(workbook).FontHeightInPoints = 20;
cellStyle.GetFont(workbook).Boldweight = 700;
break;
case "colHeadName":
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.GetFont(workbook).FontHeightInPoints = 10;
cellStyle.GetFont(workbook).Boldweight = 300;
break;
default:
break;
}
return(cellStyle);
}
private static ICellStyle CreateStyle(IWorkbook wb, HorizontalAlignment h_align, short color,
bool bold)
{
IFont font = wb.CreateFont();
if (bold)
{
font.Boldweight = (short)(/*setter*/ FontBoldWeight.Bold);
}
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.SetFont(font);
cellStyle.FillForegroundColor = (/*setter*/ color);
cellStyle.FillPattern = (/*setter*/ FillPattern.SolidForeground);
cellStyle.VerticalAlignment = (/*setter*/ VerticalAlignment.Center);
cellStyle.Alignment = (/*setter*/ h_align);
cellStyle.BorderLeft = (/*setter*/ BorderStyle.Thin);
cellStyle.LeftBorderColor = (/*setter*/ HSSFColor.Black.Index);
cellStyle.BorderTop = (/*setter*/ BorderStyle.Thin);
cellStyle.TopBorderColor = (/*setter*/ HSSFColor.Black.Index);
cellStyle.BorderRight = (/*setter*/ BorderStyle.Thin);
cellStyle.RightBorderColor = (/*setter*/ HSSFColor.Black.Index);
cellStyle.BorderBottom = (/*setter*/ BorderStyle.Thin);
cellStyle.BottomBorderColor = (/*setter*/ HSSFColor.Black.Index);
return(cellStyle);
}
private IFont GetFont(FONTSTYLE mode)
{
// 登録済みのスタイルは流用
if (fonts.ContainsKey(mode))
{
return(fonts[mode]);
}
// 未登録の場合は作成
var font = book.CreateFont();
if (mode == FONTSTYLE.PGOTHIC_11_BOLD)
{
font.FontName = "ＭＳ Ｐゴシック";
font.FontHeightInPoints = 11;
font.IsBold = true;
}
else if (mode == FONTSTYLE.NORMAL)
{
font.FontName = "ＭＳ Ｐゴシック";
font.FontHeightInPoints = 11;
}
fonts.Add(mode, font);
return(font);
}
public void SetFont()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh = wb.CreateSheet();
IRow row = sh.CreateRow(0);
ICell A1 = row.CreateCell(0);
ICell B1 = row.CreateCell(1);
short defaultFontIndex = 0;
IFont font = wb.CreateFont();
font.IsItalic = true;
short customFontIndex = font.Index;
// Assumptions
Assert.AreNotEqual(defaultFontIndex, customFontIndex);
Assert.AreEqual(A1.CellStyle, B1.CellStyle);
// should be Assert.AreSame, but a new HSSFCellStyle is returned for each GetCellStyle() call.
// HSSFCellStyle wraps an underlying style record, and the underlying
// style record is the same between multiple GetCellStyle() calls.
Assert.AreEqual(defaultFontIndex, A1.CellStyle.FontIndex);
Assert.AreEqual(defaultFontIndex, B1.CellStyle.FontIndex);
// Get/set alignment modifies the cell's style
CellUtil.SetFont(A1, font);
Assert.AreEqual(customFontIndex, A1.CellStyle.FontIndex);
// Get/set alignment doesn't affect the style of cells with
// the same style prior to modifying the style
Assert.AreNotEqual(A1.CellStyle, B1.CellStyle);
Assert.AreEqual(defaultFontIndex, B1.CellStyle.FontIndex);
wb.Close();
}
static void WriteHeaderRow(IWorkbook wb, ISheet sheet)
{
sheet.SetColumnWidth(0, 6000);
sheet.SetColumnWidth(1, 6000);
sheet.SetColumnWidth(2, 3600);
sheet.SetColumnWidth(3, 3600);
sheet.SetColumnWidth(4, 2400);
sheet.SetColumnWidth(5, 2400);
sheet.SetColumnWidth(6, 2400);
sheet.SetColumnWidth(7, 2400);
sheet.SetColumnWidth(8, 2400);
IRow row = sheet.CreateRow(0);
ICellStyle style = wb.CreateCellStyle();
IFont font = wb.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
WriteHeaderCell(row, 0, "Raw Long Bits A", style);
WriteHeaderCell(row, 1, "Raw Long Bits B", style);
WriteHeaderCell(row, 2, "Value A", style);
WriteHeaderCell(row, 3, "Value B", style);
WriteHeaderCell(row, 4, "Exp Cmp", style);
WriteHeaderCell(row, 5, "LT", style);
WriteHeaderCell(row, 6, "EQ", style);
WriteHeaderCell(row, 7, "GT", style);
WriteHeaderCell(row, 8, "Check", style);
}
private static ICellStyle CopyStyle(this ICellStyle dCellStyle, ICellStyle sCellStyle, IWorkbook dWb, IWorkbook sWb, List <ICellStyle> dCellStyles, List <IFont> dFonts)
{
ICellStyle currCellStyle = dCellStyle;
currCellStyle.Alignment = sCellStyle.Alignment;
currCellStyle.VerticalAlignment = sCellStyle.VerticalAlignment;
currCellStyle.BorderTop = sCellStyle.BorderTop;
currCellStyle.BorderBottom = sCellStyle.BorderBottom;
currCellStyle.BorderLeft = sCellStyle.BorderLeft;
currCellStyle.BorderRight = sCellStyle.BorderRight;
currCellStyle.TopBorderColor = sCellStyle.TopBorderColor;
currCellStyle.LeftBorderColor = sCellStyle.LeftBorderColor;
currCellStyle.RightBorderColor = sCellStyle.RightBorderColor;
currCellStyle.BottomBorderColor = sCellStyle.BottomBorderColor;
currCellStyle.Indention = sCellStyle.Indention;
currCellStyle.IsHidden = sCellStyle.IsHidden;
currCellStyle.IsLocked = sCellStyle.IsLocked;
currCellStyle.Rotation = sCellStyle.Rotation;
currCellStyle.ShrinkToFit = sCellStyle.ShrinkToFit;
currCellStyle.WrapText = sCellStyle.WrapText;
currCellStyle.DataFormat = dWb.CreateDataFormat().GetFormat(sWb.CreateDataFormat().GetFormat(sCellStyle.DataFormat));
IFont sFont = sCellStyle.GetFont(sWb);
IFont dFont = dWb.FindFont(sFont, dFonts) ?? dWb.CreateFont().CopyFont(sFont, dFonts);
currCellStyle.SetFont(dFont);
dCellStyles.Add(currCellStyle);
return(currCellStyle);
}
/// <summary>
/// 增加一个合并功能
/// </summary>
/// <param name="fristRow">开始行号</param>
/// <param name="lastRow">结束行号</param>
/// <param name="fristColumn">开始列号</param>
/// <param name="lastColumn">结束列号</param>
/// <param name="title">内容</param>
/// <param name="hssfworkbook">EXCEL工作簿</param>
/// <param name="sheet">工作薄</param>
/// <param name="fontSize">字体大小</param>
/// <param name="alignment">字体对齐方式</param>
protected void AddRow(int fristRow, int lastRow, int fristColumn, int lastColumn, string title, IWorkbook hssfworkbook, ISheet sheet, short fontSize, NPOI.SS.UserModel.HorizontalAlignment alignment)
{
sheet.AddMergedRegion(new CellRangeAddress(fristRow, lastRow, fristColumn, lastColumn - 1));
sheet.GetRow(fristRow).CreateCell(fristColumn).SetCellValue(title);
for (var i = 1; i < lastColumn; i++)
{
sheet.GetRow(fristRow).CreateCell(i).SetCellValue("");
}
var subtotalStyle = hssfworkbook.CreateCellStyle();
subtotalStyle.Alignment = alignment;
//subtotalStyle.VerticalAlignment = VerticalAlignment.CENTER;
//subtotalStyle.BorderTop = BorderStyle.THIN;
//subtotalStyle.BorderBottom = BorderStyle.THIN;
//subtotalStyle.BorderLeft = BorderStyle.THIN;
//subtotalStyle.BorderRight = BorderStyle.THIN;
//subtotalStyle.TopBorderColor = IndexedColors.BLACK.Index;
//subtotalStyle.BottomBorderColor = IndexedColors.BLACK.Index;
//subtotalStyle.LeftBorderColor = IndexedColors.BLACK.Index;
//subtotalStyle.RightBorderColor = IndexedColors.BLACK.Index;
var font = hssfworkbook.CreateFont();
font.FontHeightInPoints = fontSize;
font.Boldweight = 100 * 100;
var currentRow = sheet.GetRow(fristRow);
for (var i = 0; i < lastColumn; i++)
{
currentRow.GetCell(i).CellStyle = subtotalStyle;
currentRow.GetCell(i).CellStyle.SetFont(font);
}
}
protected void AddSheet(IWorkbook workBook, DataTable dt, string[] headers)
{
//头部样式
ICellStyle hStyle = workBook.CreateCellStyle();
hStyle.FillForegroundColor = HSSFColor.Yellow.Index;
hStyle.FillPattern = FillPattern.BigSpots;
hStyle.FillBackgroundColor = HSSFColor.Yellow.Index;
hStyle.Alignment = HorizontalAlignment.Center;
hStyle.VerticalAlignment = VerticalAlignment.Center;
hStyle.BorderBottom = hStyle.BorderLeft = hStyle.BorderRight = hStyle.BorderTop = BorderStyle.Medium;
hStyle.BottomBorderColor = hStyle.LeftBorderColor = hStyle.RightBorderColor = hStyle.TopBorderColor = HSSFColor.Black.Index;
IFont hFont = workBook.CreateFont();
hFont.Boldweight = (short)FontBoldWeight.Bold;
hStyle.SetFont(hFont);
int RecordCounts = dt.Rows.Count;
int PageSize = 65501;
int TotalPages = (RecordCounts + PageSize - 1) / PageSize;
for (int i = 1; i <= TotalPages; i++)
{
ISheet sheet = workBook.CreateSheet(string.Format("Sheet{0}", i));
this.AddHeader(sheet, hStyle, headers);
if (i == TotalPages)
{
this.FillSheet(sheet, dt, PageSize * (i - 1), RecordCounts);
}
else
{
this.FillSheet(sheet, dt, PageSize * (i - 1), PageSize * i);
}
}
}
/// <summary>
/// 设置样式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
private static ICellStyle SetStyle(IWorkbook workbook)
{
//样式
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
//style.FillForegroundColor = HSSFColor.Automatic.Index;
//style.FillPattern = FillPattern.SolidForeground;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
//新建一个字体样式对象
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.Bold;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
return(style);
}
/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="hssfworkbook"></param>
/// <param name="type">1表示有边框，0表示没有边框</param>
/// <returns></returns>
protected ICellStyle GetCellStyle(IWorkbook hssfworkbook, int type)
{
ICellStyle cellstyle = hssfworkbook.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Center;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
IFont fontLeft = hssfworkbook.CreateFont();
//字号
fontLeft.FontHeightInPoints = 16;
//字体
fontLeft.FontName = "宋体";
cellstyle.SetFont(fontLeft);
if (type == 1)
{
//有边框
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
}
else if (type == 0)
{
cellstyle.BorderBottom = BorderStyle.None;
cellstyle.BorderLeft = BorderStyle.None;
cellstyle.BorderRight = BorderStyle.None;
cellstyle.BorderTop = BorderStyle.None;
}
return(cellstyle);
}
public IFont getCellStyle(IWorkbook wb)
{
IFont f = wb.CreateFont();
if (Nombre_Fuente == null)
{
f.FontName = "Calibri";
}
else
{
f.FontName = Nombre_Fuente;
}
if (Negrita == true)
{
f.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
}
if (Size == 0)
{
f.FontHeightInPoints = 11;
}
else
{
f.FontHeightInPoints = Size;
}
if (color != null)
{
f.Color = (short)(color.R + color.G + color.B);
}
return(f);
}
public void CreateHeader <T>(IWorkbook workbook, ISheet sheet)
{
//表头格式
var headerRow = sheet.CreateRow(0);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.IsBold = true;
headStyle.SetFont(font);
//设置表头
var pops = typeof(T).GetProperties();
for (int i = 0; i < pops.Length; i++)
{
//获取displayname如无则字段名称
var colName = pops[i].GetCustomAttribute <DisplayNameAttribute>()?.DisplayName ?? pops[i].Name;
var colLength = Encoding.UTF8.GetBytes(colName).Length;
headerRow.CreateCell(i).SetCellValue(colName);
headerRow.GetCell(i).CellStyle = headStyle;
sheet.SetColumnWidth(i, colLength > 100 ? 100 * 256 : colLength < 20 ? 10 * 256 : colLength * 256);
}
}
private static ICellStyle SetEveyTeamInfoCommonStyle(IWorkbook workbook, ISheet sheet, int row, bool fontIsBold = false, int fontHeight = 0)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 9));
ICellStyle style = workbook.CreateCellStyle();
//新建一个字体样式对象
IFont font = workbook.CreateFont();
if (fontIsBold)
{
//设置字体加粗样式
font.IsBold = true;
}
if (fontHeight != 0)
{
// 设置字体大小
font.FontHeight = fontHeight;
}
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Left;
return(style);
}
/// <summary>
/// 设置内容单元格样式
/// </summary>
private static ICellStyle[] GetArryColStyle(ExcelConfig excelConfig, IWorkbook workbook)
{
ICellStyle[] arryColumStyle = new ICellStyle[excelConfig.Columns.Count];//样式表
foreach (ColumnModel item in excelConfig.Columns)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
int columnentityIndex = excelConfig.Columns.FindIndex(t => t.Column == item.Column);
if (item.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(item.Background);
}
IFont columnFont = workbook.CreateFont();
if (item.Font != null)
{
columnFont.FontName = item.Font;
}
if (item.ForeColor != new Color())
{
columnFont.Color = GetXLColour(item.ForeColor);
}
columnFont.FontHeightInPoints = item.Point;
columnStyle.SetFont(columnFont);
columnStyle.Alignment = getAlignment(item.Alignment);
arryColumStyle[columnentityIndex] = columnStyle;
}
return(arryColumStyle);
}
public static ICellStyle HeaderStyle(IWorkbook workbook)
{
ICellStyle style = workbook.CreateCellStyle();
//设置字体为粗体
IFont font = workbook.CreateFont();
//font.IsItalic = true;//斜体
font.FontHeightInPoints = (short)10;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
//设置居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置边框
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index;
style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index;
style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index;
style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Orange.Index;
style.FillPattern = FillPattern.Squares;
style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Orange.Index;
style.SetFont(font);
return(style);
}
private static ICellStyle GetCellStyleXlsx(IWorkbook workbook, bool isHeaderRow = false)
{
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
if (isHeaderRow)
{
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
XSSFFont defaultFont = (XSSFFont)workbook.CreateFont();
defaultFont.FontHeightInPoints = (short)10;
defaultFont.FontName = "Arial";
defaultFont.Color = IndexedColors.Black.Index;
defaultFont.IsBold = false;
defaultFont.IsItalic = false;
defaultFont.Boldweight = 700;
style.SetFont(defaultFont);
}
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
return(style);
}
private void CreateHeader(IWorkbook book, ISheet sheet, Model.ColumnModel colModel)
{
var rownum = 0;
IFont font = book.CreateFont();
font.IsBold = true;
foreach (var cols in colModel)
{
IRow headRow = sheet.CreateRow(rownum++);
foreach (var col in cols)
{
if (col.Width > 0)
{
sheet.SetColumnWidth(col.X, col.Width * 36);
}
var cell = headRow.CreateCell(col.X);
var cellstyle = book.CreateCellStyle();
cell.CellStyle = cellstyle;
cellstyle.SetFont(font);
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
cell.SetCellValue(col.Title);
if (col.ColSpan > 1 || col.RowSpan > 1)
{
sheet.AddMergedRegion(new CellRangeAddress(col.Y, col.Y + col.RowSpan - 1, col.X, col.X + col.ColSpan - 1));
}
}
}
}
private void setTitleSyle()
{
//标题单元格样式
titleStyle = _workBook.CreateCellStyle();
borderStyle = _workBook.CreateCellStyle();
//标题字体样式对象
titleFont = _workBook.CreateFont();
//设置单元格的样式：水平对齐居中
titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
//设置边框
titleStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
titleStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
titleStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
titleStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
borderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
borderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
borderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
borderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//设置字体加粗样式
titleFont.Boldweight = short.MaxValue;
//设置字体大小
titleFont.FontHeight = 12;
//使用SetFont方法将字体样式添加到单元格样式中
titleStyle.SetFont(titleFont);
}
public void InitCellStyle(IWorkbook workbook)
{
var font = workbook.CreateFont();
var title = workbook.CreateCellStyle();
var content = workbook.CreateCellStyle();
RegisterFont(font);
RegisterTitleStyle(workbook, title);
RegisterContentStyle(workbook, content);
}
private ICellStyle CreateBoldStyle(IWorkbook workbook)
{
var style = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.Boldweight = 1000;
style.SetFont(font);
return style;
}
/// <summary>
/// Initializes a new instance of the <see cref="BetListExcelBuilder" /> class.
/// </summary>
public BetListExcelBuilder()
{
_workbook = new HSSFWorkbook();
_isDisposed = false;
OddCellStyle = _workbook.CreateCellStyle();
EvenCellStyle = _workbook.CreateCellStyle();
OddCellStyleCenterAligned = _workbook.CreateCellStyle();
EvenCellStyleCenterAligned = _workbook.CreateCellStyle();
_nextCellIndex = -1;
RTFHelper = new RTFHelper()
{
NegFont = _workbook.CreateFont(),
NormalFont = _workbook.CreateFont(),
PosFont = _workbook.CreateFont(),
NegFontCrossed = _workbook.CreateFont(),
NormalFontCrossed = _workbook.CreateFont(),
PosFontCrossed = _workbook.CreateFont(),
RTFRenderer = new RtfTextRender()
};
InitDefaultRTFHelper();
InitDefaultCellStyles();
}
/// <summary>
/// 创建表格头单元格
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
public static ICellStyle GetCellStyle(IWorkbook workbook, bool isHeaderRow = false)
{
ICellStyle style = workbook.CreateCellStyle();
if (isHeaderRow)
{
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
IFont f = workbook.CreateFont();
f.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(f);
}
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
return style;
}
public static void SetBorderStyle(IWorkbook workbook, IRow row, int columns)
{
row.Height = 14 * 30;
IFont font18 = workbook.CreateFont();
font18.FontName = "宋体";
font18.FontHeightInPoints = 10;
for (int i = 0; i < columns; i++)
{
ICellStyle style12 = workbook.CreateCellStyle();
style12.SetFont(font18);
//style12.Alignment = HorizontalAlignment.CENTER;
style12.VerticalAlignment = VerticalAlignment.CENTER;
style12.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
row.GetCell(i).CellStyle = style12;
}
}
public static void SetHeaderStyle(IWorkbook workbook, IRow row, int columns)
{
row.Height = 16 * 30;
IFont font18 = workbook.CreateFont();
font18.FontName = "宋体";
font18.FontHeightInPoints = 12;
font18.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
for (int i = 0; i < columns; i++)
{
ICellStyle style12 = workbook.CreateCellStyle();
style12.SetFont(font18);
// style12.Alignment = HorizontalAlignment.CENTER;
style12.VerticalAlignment = VerticalAlignment.CENTER;
style12.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
//style12.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index2;
row.GetCell(i).CellStyle = style12;
}
}
private static ICellStyle CreateStyle(IWorkbook wb, HorizontalAlignment h_align, short color,
bool bold)
{
IFont font = wb.CreateFont();
if (bold)
{
font.Boldweight = (short)(/*setter*/FontBoldWeight.BOLD);
}
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.SetFont(font);
cellStyle.FillForegroundColor = (/*setter*/color);
cellStyle.FillPattern = (/*setter*/FillPatternType.SOLID_FOREGROUND);
cellStyle.VerticalAlignment = (/*setter*/VerticalAlignment.CENTER);
cellStyle.Alignment = (/*setter*/h_align);
cellStyle.BorderLeft = (/*setter*/BorderStyle.THIN);
cellStyle.LeftBorderColor = (/*setter*/IndexedColors.BLACK.Index);
cellStyle.BorderTop = (/*setter*/BorderStyle.THIN);
cellStyle.TopBorderColor = (/*setter*/IndexedColors.BLACK.Index);
cellStyle.BorderRight = (/*setter*/BorderStyle.THIN);
cellStyle.RightBorderColor = (/*setter*/IndexedColors.BLACK.Index);
cellStyle.BorderBottom = (/*setter*/BorderStyle.THIN);
cellStyle.BottomBorderColor = (/*setter*/IndexedColors.BLACK.Index);
return cellStyle;
}
/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="hssfworkbook"></param>
/// <param name="type">1表示有边框，0表示没有边框</param>
/// <returns></returns>
protected ICellStyle GetCellStyle(IWorkbook hssfworkbook, int type)
{
ICellStyle cellstyle = hssfworkbook.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Center;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
IFont fontLeft = hssfworkbook.CreateFont();
//字号
fontLeft.FontHeightInPoints = 16;
//字体
fontLeft.FontName = "宋体";
cellstyle.SetFont(fontLeft);
if (type == 1)
{
//有边框
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
}
else if (type == 0)
{
cellstyle.BorderBottom = BorderStyle.None;
cellstyle.BorderLeft = BorderStyle.None;
cellstyle.BorderRight = BorderStyle.None;
cellstyle.BorderTop = BorderStyle.None;
}
return cellstyle;
}
static void WriteHeaderRow(IWorkbook wb, ISheet sheet)
{
sheet.SetColumnWidth(0, 3000);
sheet.SetColumnWidth(1, 6000);
sheet.SetColumnWidth(2, 6000);
sheet.SetColumnWidth(3, 6000);
sheet.SetColumnWidth(4, 6000);
sheet.SetColumnWidth(5, 1600);
sheet.SetColumnWidth(6, 20000);
IRow row = sheet.CreateRow(0);
ICellStyle style = wb.CreateCellStyle();
IFont font = wb.CreateFont();
WriteHeaderCell(row, 0, "Value", style);
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
WriteHeaderCell(row, 1, "Raw Long Bits", style);
WriteHeaderCell(row, 2, "JDK Double Rendering", style);
WriteHeaderCell(row, 3, "Actual Rendering", style);
WriteHeaderCell(row, 4, "Expected Rendering", style);
WriteHeaderCell(row, 5, "Match", style);
WriteHeaderCell(row, 6, "Java Metadata", style);
}
private static ICellStyle CreateHeaderStyle(IWorkbook wb)
{
IFont font = wb.CreateFont();
font.Color = (/*setter*/ IndexedColors.WHITE.Index);
font.Boldweight = (short)(/*setter*/FontBoldWeight.BOLD);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.FillForegroundColor = (/*setter*/IndexedColors.BLUE_GREY.Index);
cellStyle.FillPattern = (/*setter*/FillPatternType.SOLID_FOREGROUND);
cellStyle.Alignment = (/*setter*/HorizontalAlignment.CENTER);
cellStyle.VerticalAlignment = (/*setter*/VerticalAlignment.CENTER);
cellStyle.BorderLeft = (/*setter*/BorderStyle.THIN);
cellStyle.LeftBorderColor = (/*setter*/IndexedColors.WHITE.Index);
cellStyle.BorderTop = (/*setter*/BorderStyle.THIN);
cellStyle.TopBorderColor = (/*setter*/IndexedColors.WHITE.Index);
cellStyle.BorderRight = (/*setter*/BorderStyle.THIN);
cellStyle.RightBorderColor = (/*setter*/IndexedColors.WHITE.Index);
cellStyle.BorderBottom = (/*setter*/BorderStyle.THIN);
cellStyle.BottomBorderColor = (/*setter*/IndexedColors.WHITE.Index);
cellStyle.SetFont(font);
return cellStyle;
}
public static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font10 = wb.CreateFont();
font10.FontHeightInPoints = 10;
font10.FontName = "宋体";
font10.Boldweight = 700;
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 10;
font12.FontName = "宋体";
font12.Boldweight = 700;
IFont fontw = wb.CreateFont();
fontw.FontName = "宋体";
fontw.Boldweight = 700;
IFont font14 = wb.CreateFont();
font14.FontHeightInPoints = 14;
font14.FontName = "宋体";
font14.Boldweight = 700;
IFont font = wb.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "宋体";
IFont fontHT = wb.CreateFont();
fontHT.FontHeightInPoints = 10;
fontHT.FontName = "宋体";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "宋体";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
//cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.投缴头:
cellStyle.FillForegroundColor = HSSFColor.PaleBlue.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font12);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.月报头:
cellStyle.FillForegroundColor = HSSFColor.Rose.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font10);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.月报底:
cellStyle.FillForegroundColor = HSSFColor.Rose.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font10);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.月报内容:
cellStyle.FillForegroundColor = HSSFColor.White.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font10);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.补缴头:
cellStyle.FillForegroundColor = HSSFColor.Rose.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font12);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
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.Alignment = HorizontalAlignment.Center;
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.Single;
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;
case stylexls.表头:
cellStyle.SetFont(font14);
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.加粗:
cellStyle.SetFont(fontw);
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.迟到早退标题:
cellStyle.SetFont(fontHT);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.迟到早退内容:
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.不含底线:
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
break;
case stylexls.迟到早退标题加左线:
cellStyle.SetFont(fontHT);
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.迟到早退标题加左线左对齐:
cellStyle.SetFont(fontHT);
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
}
return cellStyle;
}
static ICellStyle GetHeaderCellStyle(IWorkbook book)
{
ICellStyle style = book.CreateCellStyle();
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.FillForegroundColor = HSSFColor.LightTurquoise.Index;
style.FillPattern = FillPattern.SolidForeground;
IFont font = book.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
return style;
}
/**
* cell styles used for formatting calendar sheets
*/
private static Dictionary<String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
ICellStyle style=null;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = (short)14;
titleFont.FontName = "Trebuchet MS";
style = wb.CreateCellStyle();
style.SetFont(titleFont);
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
styles.Add("title", style);
IFont itemFont = wb.CreateFont();
itemFont.FontHeightInPoints = (short)9;
itemFont.FontName = "Trebuchet MS";
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.LEFT);
style.SetFont(itemFont);
styles.Add("item_left", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
styles.Add("item_right", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.DataFormat = (wb.CreateDataFormat().GetFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
styles.Add("input_$", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.DataFormat = (wb.CreateDataFormat().GetFormat("0.000%"));
styles.Add("input_%", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.DataFormat =wb.CreateDataFormat().GetFormat("0");
styles.Add("input_i", style);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.CENTER);
style.SetFont(itemFont);
style.DataFormat =wb.CreateDataFormat().GetFormat("m/d/yy");
styles.Add("input_d", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.DataFormat =wb.CreateDataFormat().GetFormat("$##,##0.00");
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.FillForegroundColor = IndexedColors.GREY_25_PERCENT.Index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
styles.Add("formula_$", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(itemFont);
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.DataFormat =wb.CreateDataFormat().GetFormat("0");
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = IndexedColors.GREY_40_PERCENT.Index;
style.FillForegroundColor = IndexedColors.GREY_25_PERCENT.Index;
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
styles.Add("formula_i", style);
return styles;
}
private static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 14;
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.GREEN.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
// cellStyle.FillForegroundColor = HSSFColor.WHITE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
cellStyle.FillBackgroundColor = HSSFColor.MAROON.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;
}
/**
* cell styles used for formatting calendar sheets
*/
private static Dictionary<String, ICellStyle> createStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
short borderColor = IndexedColors.GREY_50_PERCENT.Index;
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = ((short)48);
titleFont.Color = (IndexedColors.DARK_BLUE.Index);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.CENTER);
style.VerticalAlignment = VerticalAlignment.CENTER;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = ((short)12);
monthFont.Color = (IndexedColors.WHITE.Index);
monthFont.Boldweight = (short)(FontBoldWeight.BOLD);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.CENTER);
style.VerticalAlignment = (VerticalAlignment.CENTER);
style.FillForegroundColor = (IndexedColors.DARK_BLUE.Index);
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
style.SetFont(monthFont);
styles.Add("month", style);
IFont dayFont = wb.CreateFont();
dayFont.FontHeightInPoints = ((short)14);
dayFont.Boldweight = (short)(FontBoldWeight.BOLD);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.LEFT);
style.VerticalAlignment = (VerticalAlignment.TOP);
style.FillForegroundColor = (IndexedColors.LIGHT_CORNFLOWER_BLUE.Index);
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
style.BorderLeft = (BorderStyle.THIN);
style.LeftBorderColor = (borderColor);
style.BorderBottom = (BorderStyle.THIN);
style.BottomBorderColor = (borderColor);
style.SetFont(dayFont);
styles.Add("weekend_left", style);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.CENTER);
style.VerticalAlignment = (VerticalAlignment.TOP);
style.FillForegroundColor = (IndexedColors.LIGHT_CORNFLOWER_BLUE.Index);
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
style.BorderRight = (BorderStyle.THIN);
style.RightBorderColor = (borderColor);
style.BorderBottom = (BorderStyle.THIN);
style.BottomBorderColor = (borderColor);
styles.Add("weekend_right", style);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.LEFT);
style.VerticalAlignment = (VerticalAlignment.TOP);
style.BorderLeft = (BorderStyle.THIN);
style.FillForegroundColor = (IndexedColors.WHITE.Index);
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
style.LeftBorderColor = (borderColor);
style.BorderBottom = (BorderStyle.THIN);
style.BottomBorderColor = (borderColor);
style.SetFont(dayFont);
styles.Add("workday_left", style);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.CENTER);
style.VerticalAlignment = (VerticalAlignment.TOP);
style.FillForegroundColor = (IndexedColors.WHITE.Index);
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
style.BorderRight = (BorderStyle.THIN);
style.RightBorderColor = (borderColor);
style.BorderBottom = (BorderStyle.THIN);
style.BottomBorderColor = (borderColor);
styles.Add("workday_right", style);
style = wb.CreateCellStyle();
style.BorderLeft = (BorderStyle.THIN);
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
style.BorderBottom = (BorderStyle.THIN);
style.BottomBorderColor = (borderColor);
styles.Add("grey_left", style);
style = wb.CreateCellStyle();
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
style.BorderRight = (BorderStyle.THIN);
style.RightBorderColor = (borderColor);
style.BorderBottom = (BorderStyle.THIN);
style.BottomBorderColor = (borderColor);
styles.Add("grey_right", style);
return styles;
}
private static Dictionary<String, ICellStyle> createStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
IDataFormat df = wb.CreateDataFormat();
ICellStyle style;
IFont headerFont = wb.CreateFont();
headerFont.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(headerFont);
styles.Add("header", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(headerFont);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("header_date", style);
IFont font1 = wb.CreateFont();
font1.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
styles.Add("cell_b", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
styles.Add("cell_b_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_b_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_g", style);
IFont font2 = wb.CreateFont();
font2.Color = (IndexedColors.Blue.Index);
font2.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font2);
styles.Add("cell_bb", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_bg", style);
IFont font3 = wb.CreateFont();
font3.FontHeightInPoints = ((short)14);
font3.Color = (IndexedColors.DarkBlue.Index);
font3.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font3);
style.WrapText = (true);
styles.Add("cell_h", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.WrapText = (true);
styles.Add("cell_normal", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.WrapText = (true);
styles.Add("cell_normal_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.WrapText = (true);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_normal_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.Indention = ((short)1);
style.WrapText = (true);
styles.Add("cell_indented", style);
style = CreateBorderedStyle(wb);
style.FillForegroundColor = (IndexedColors.Blue.Index);
style.FillPattern = FillPattern.SolidForeground;
styles.Add("cell_blue", style);
return styles;
}
/**
* create a library of cell styles
*/
private static Dictionary<String, ICellStyle> createStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
IDataFormat df = wb.CreateDataFormat();
ICellStyle style;
IFont headerFont = wb.CreateFont();
headerFont.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.LIGHT_CORNFLOWER_BLUE.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(headerFont);
styles.Add("header", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.LIGHT_CORNFLOWER_BLUE.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(headerFont);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("header_date", style);
IFont font1 = wb.CreateFont();
font1.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
styles.Add("cell_b", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
styles.Add("cell_b_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_b_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_g", style);
IFont font2 = wb.CreateFont();
font2.Color = (IndexedColors.BLUE.Index);
font2.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font2);
styles.Add("cell_bb", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_bg", style);
IFont font3 = wb.CreateFont();
font3.FontHeightInPoints = ((short)14);
font3.Color = (IndexedColors.DARK_BLUE.Index);
font3.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font3);
style.WrapText = (true);
styles.Add("cell_h", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
styles.Add("cell_normal", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
styles.Add("cell_normal_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_normal_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.Indention = ((short)1);
style.WrapText = (true);
styles.Add("cell_indented", style);
style = CreateBorderedStyle(wb);
style.FillForegroundColor = (IndexedColors.BLUE.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
styles.Add("cell_blue", style);
return styles;
}
/**
* Create a library of cell styles
*/
private static Dictionary<String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = ((short)18);
titleFont.Boldweight = (short)FontBoldWeight.BOLD;
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = ((short)11);
monthFont.Color = (IndexedColors.WHITE.Index);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_50_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(monthFont);
style.WrapText = (true);
styles.Add("header", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
style.BorderRight = BorderStyle.THIN;
style.RightBorderColor = (IndexedColors.BLACK.Index);
style.BorderLeft = BorderStyle.THIN;
style.LeftBorderColor = (IndexedColors.BLACK.Index);
style.BorderTop = BorderStyle.THIN;
style.TopBorderColor = (IndexedColors.BLACK.Index);
style.BorderBottom = BorderStyle.THIN;
style.BottomBorderColor = (IndexedColors.BLACK.Index);
styles.Add("cell", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = (wb.CreateDataFormat().GetFormat("0.00"));
styles.Add("formula", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.GREY_40_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula_2", style);
return styles;
}
private static ICellStyle CreateStyle(IWorkbook wb, HorizontalAlignment h_align, short color,
bool bold)
{
IFont font = wb.CreateFont();
if (bold)
{
font.Boldweight = (short)(/*setter*/FontBoldWeight.Bold);
}
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.SetFont(font);
cellStyle.FillForegroundColor = (/*setter*/color);
cellStyle.FillPattern = (/*setter*/FillPattern.SolidForeground);
cellStyle.VerticalAlignment = (/*setter*/VerticalAlignment.Center);
cellStyle.Alignment = (/*setter*/h_align);
cellStyle.BorderLeft = (/*setter*/BorderStyle.Thin);
cellStyle.LeftBorderColor = (/*setter*/HSSFColor.Black.Index);
cellStyle.BorderTop = (/*setter*/BorderStyle.Thin);
cellStyle.TopBorderColor = (/*setter*/HSSFColor.Black.Index);
cellStyle.BorderRight = (/*setter*/BorderStyle.Thin);
cellStyle.RightBorderColor = (/*setter*/HSSFColor.Black.Index);
cellStyle.BorderBottom = (/*setter*/BorderStyle.Thin);
cellStyle.BottomBorderColor = (/*setter*/HSSFColor.Black.Index);
return cellStyle;
}
private static ICellStyle CreateHeaderStyle(IWorkbook wb)
{
IFont font = wb.CreateFont();
font.Color = (/*setter*/ IndexedColors.White.Index);
font.Boldweight = (short)(/*setter*/FontBoldWeight.Bold);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.FillForegroundColor = (/*setter*/IndexedColors.BlueGrey.Index);
cellStyle.FillPattern = (/*setter*/FillPattern.SolidForeground);
cellStyle.Alignment = (/*setter*/HorizontalAlignment.Center);
cellStyle.VerticalAlignment = (/*setter*/VerticalAlignment.Center);
cellStyle.BorderLeft = (/*setter*/BorderStyle.Thin);
cellStyle.LeftBorderColor = (/*setter*/IndexedColors.White.Index);
cellStyle.BorderTop = (/*setter*/BorderStyle.Thin);
cellStyle.TopBorderColor = (/*setter*/IndexedColors.White.Index);
cellStyle.BorderRight = (/*setter*/BorderStyle.Thin);
cellStyle.RightBorderColor = (/*setter*/IndexedColors.White.Index);
cellStyle.BorderBottom = (/*setter*/BorderStyle.Thin);
cellStyle.BottomBorderColor = (/*setter*/IndexedColors.White.Index);
cellStyle.SetFont(font);
return cellStyle;
}
/// <summary>
/// 获取数据行的错误信息提示样式
/// </summary>
/// <returns>错误数据行样式</returns>
public static ICellStyle GetErrorCellStyle(IWorkbook wb)
{
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
cellStyle.Alignment = HorizontalAlignment.LEFT;
IFont font = wb.CreateFont();
//font.FontHeightInPoints = 12;
font.Color = HSSFColor.RED.index;
cellStyle.SetFont(font);
return cellStyle;
}
private ICellStyle CreateSheetStyle(IWorkbook workbook, bool BorderLeft, bool BorderTop, bool BorderRight, bool BorderBottom, bool IsFontBold, bool IsFontItalize, bool IsFontUnderline, bool IsFontStrikeOut,bool applyColor = true)
{
IFont newfont = workbook.CreateFont();
if(IsFontBold)
newfont.Boldweight = (short)FontBoldWeight.Bold;
newfont.IsItalic = IsFontItalize;
newfont.IsStrikeout = IsFontStrikeOut;
if(IsFontUnderline)
newfont.Underline = FontUnderlineType.Single;
ICellStyle newstyle = workbook.CreateCellStyle();
newstyle.SetFont(newfont);
if (BorderLeft)
newstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
else
newstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
if (BorderTop)
newstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
else
newstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
if (BorderRight)
newstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
else
newstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
if (BorderBottom)
newstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
else
newstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
if (applyColor == true)
{
newstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightYellow.Index;
newstyle.FillPattern = FillPattern.SolidForeground;
}
return newstyle;
}
/// <summary>
/// 获取标题行的错误信息提示样式
/// </summary>
/// <returns>错误标题行样式</returns>
public static ICellStyle GetErrorHeadCellStyle(IWorkbook wb)
{
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
cellStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = wb.CreateFont();
font.Boldweight = short.MaxValue;
font.Color = HSSFColor.RED.index;
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
return cellStyle;
}
private void SetColumnStyle(IWorkbook hssfworkbook, ICell cell, string name)
{
var cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.WrapText = true;
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
font.Boldweight = 10;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
cell.SetCellValue(name);
}
// 字体设置
private static IFont FontSetting(IWorkbook workbook, ExcelContentFormat excelFormat)
{
IFont fontContent = workbook.CreateFont();
if (!string.IsNullOrEmpty(excelFormat.FontName))
fontContent.FontName = excelFormat.FontName;
if (excelFormat.FontBold)
fontContent.Boldweight = (short)FontBoldWeight.Bold;
if (excelFormat.FontSize > 0)
fontContent.FontHeightInPoints = excelFormat.FontSize;
return fontContent;
}
/**
* Create a library of cell styles
*/
private static Dictionary<String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = ((short)18);
titleFont.Boldweight = (short)FontBoldWeight.Bold;
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = ((short)11);
monthFont.Color = (IndexedColors.White.Index);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey50Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(monthFont);
style.WrapText = (true);
styles.Add("header", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
style.BorderRight = BorderStyle.Thin;
style.RightBorderColor = (IndexedColors.Black.Index);
style.BorderLeft = BorderStyle.Thin;
style.LeftBorderColor = (IndexedColors.Black.Index);
style.BorderTop = BorderStyle.Thin;
style.TopBorderColor = (IndexedColors.Black.Index);
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = (IndexedColors.Black.Index);
styles.Add("cell", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (wb.CreateDataFormat().GetFormat("0.00"));
styles.Add("formula", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey40Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula_2", style);
return styles;
}
/**
* cell styles used for formatting calendar sheets
*/
private static Dictionary<String, ICellStyle> createStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
short borderColor = IndexedColors.Grey50Percent.Index;
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = ((short)48);
titleFont.Color = (IndexedColors.DarkBlue.Index);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.Center);
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = ((short)12);
monthFont.Color = (IndexedColors.White.Index);
monthFont.Boldweight = (short)(FontBoldWeight.Bold);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.Center);
style.VerticalAlignment = (VerticalAlignment.Center);
style.FillForegroundColor = (IndexedColors.DarkBlue.Index);
style.FillPattern = (FillPattern.SolidForeground);
style.SetFont(monthFont);
styles.Add("month", style);
IFont dayFont = wb.CreateFont();
dayFont.FontHeightInPoints = ((short)14);
dayFont.Boldweight = (short)(FontBoldWeight.Bold);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.Left);
style.VerticalAlignment = (VerticalAlignment.Top);
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = (FillPattern.SolidForeground);
style.BorderLeft = (BorderStyle.Thin);
style.LeftBorderColor = (borderColor);
style.BorderBottom = (BorderStyle.Thin);
style.BottomBorderColor = (borderColor);
style.SetFont(dayFont);
styles.Add("weekend_left", style);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.Center);
style.VerticalAlignment = (VerticalAlignment.Top);
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = (FillPattern.SolidForeground);
style.BorderRight = (BorderStyle.Thin);
style.RightBorderColor = (borderColor);
style.BorderBottom = (BorderStyle.Thin);
style.BottomBorderColor = (borderColor);
styles.Add("weekend_right", style);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.Left);
style.VerticalAlignment = (VerticalAlignment.Top);
style.BorderLeft = (BorderStyle.Thin);
style.FillForegroundColor = (IndexedColors.White.Index);
style.FillPattern = (FillPattern.SolidForeground);
style.LeftBorderColor = (borderColor);
style.BorderBottom = (BorderStyle.Thin);
style.BottomBorderColor = (borderColor);
style.SetFont(dayFont);
styles.Add("workday_left", style);
style = wb.CreateCellStyle();
style.Alignment = (HorizontalAlignment.Center);
style.VerticalAlignment = (VerticalAlignment.Top);
style.FillForegroundColor = (IndexedColors.White.Index);
style.FillPattern = (FillPattern.SolidForeground);
style.BorderRight = (BorderStyle.Thin);
style.RightBorderColor = (borderColor);
style.BorderBottom = (BorderStyle.Thin);
style.BottomBorderColor = (borderColor);
styles.Add("workday_right", style);
style = wb.CreateCellStyle();
style.BorderLeft = (BorderStyle.Thin);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = (FillPattern.SolidForeground);
style.BorderBottom = (BorderStyle.Thin);
style.BottomBorderColor = (borderColor);
styles.Add("grey_left", style);
style = wb.CreateCellStyle();
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = (FillPattern.SolidForeground);
style.BorderRight = (BorderStyle.Thin);
style.RightBorderColor = (borderColor);
style.BorderBottom = (BorderStyle.Thin);
style.BottomBorderColor = (borderColor);
styles.Add("grey_right", style);
return styles;
}
/// <summary>
/// Applies the style by copying the fluent style to the NPOI style object,.
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <param name="destination">The destination NPOI style object to apply the FluentStyle to.</param>
public void ApplyStyle(IWorkbook workbook, ICellStyle destination)
{
// If users sets format string this overrides the DataFormat property.
if (Format != null)
{
var dataFormat = workbook.CreateDataFormat();
DataFormat = dataFormat.GetFormat(Format);
}
if (Alignment != null) destination.Alignment = Alignment.Value;
if (BorderBottom != null) destination.BorderBottom = BorderBottom.Value;
if (BorderDiagonal != null) destination.BorderDiagonal = BorderDiagonal.Value;
if (BorderDiagonalColor != null) destination.BorderDiagonalColor = BorderDiagonalColor.Value;
if (BorderDiagonalLineStyle != null) destination.BorderDiagonalLineStyle = BorderDiagonalLineStyle.Value;
if (BorderLeft != null) destination.BorderLeft = BorderLeft.Value;
if (BorderRight != null) destination.BorderRight = BorderRight.Value;
if (BorderTop != null) destination.BorderTop = BorderTop.Value;
if (BottomBorderColor != null) destination.BottomBorderColor = BottomBorderColor.Value;
if (DataFormat != null) destination.DataFormat = DataFormat.Value;
if (FillBackgroundColor != null) destination.FillBackgroundColor = FillBackgroundColor.Value;
if (FillForegroundColor != null) destination.FillForegroundColor = FillForegroundColor.Value;
if (FillPattern != null) destination.FillPattern = FillPattern.Value;
if (Indention != null) destination.Indention = Indention.Value;
if (LeftBorderColor != null) destination.LeftBorderColor = LeftBorderColor.Value;
if (RightBorderColor != null) destination.RightBorderColor = RightBorderColor.Value;
if (Rotation != null) destination.Rotation = Rotation.Value;
if (ShrinkToFit != null) destination.ShrinkToFit = ShrinkToFit.Value;
if (TopBorderColor != null) destination.TopBorderColor = TopBorderColor.Value;
if (VerticalAlignment != null) destination.VerticalAlignment = VerticalAlignment.Value;
if (WrapText != null) destination.WrapText = WrapText.Value;
if (FontIsNeeded)
{
var font = workbook.CreateFont();
if (FontWeight != null) font.Boldweight = (short)FontWeight.Value;
if (Charset != null) font.Charset = Charset.Value;
if (Color != null) font.Color = Color.Value;
if (FontHeight != null) font.FontHeight = FontHeight.Value;
if (FontHeightInPoints != null) font.FontHeightInPoints = FontHeightInPoints.Value;
if (FontName != null) font.FontName = FontName;
if (Italic != null) font.IsItalic = Italic.Value;
if (Strikeout != null) font.IsStrikeout = Strikeout.Value;
if (SuperScript != null) font.TypeOffset = SuperScript.Value;
if (Underline != null) font.Underline = Underline.Value;
destination.SetFont(font);
}
}
private ICellStyle CreateCellStyle(IWorkbook hssfworkbook)
{
var cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.WrapText = true;
cellStyle.Alignment = HorizontalAlignment.Left;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
cellStyle.SetFont(font);
return cellStyle;
}