/// <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();
}
public CellStyleCache(IWorkbook xlWorkbook)
{
this.xlWorkbook = xlWorkbook;
this.cellStyles = new Dictionary<string, CellStyleWrapper>();
this.cellStyle = xlWorkbook.CreateCellStyle();
this.defaultCellStyle = xlWorkbook.CreateCellStyle();
}
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);
}
public override void RegisterCustomStyle(IWorkbook workbook)
{
var format = workbook.CreateDataFormat();
ICellStyle cellStyle = workbook.CreateCellStyle();
RegisterContentStyle(workbook, cellStyle);
cellStyle.DataFormat = format.GetFormat("0.00%");
ICellStyle cellStyle2 = workbook.CreateCellStyle();
RegisterContentStyle(workbook, cellStyle2);
cellStyle2.DataFormat = format.GetFormat("￥0.00");
}
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 ICellStyle GetBaseStyle(IWorkbook workbook)
{
var style = workbook.CreateCellStyle();
style.BorderBottom = style.BorderLeft = style.BorderRight = style.BorderTop = BorderStyle.Thin;
style.FillPattern = FillPattern.SolidForeground;
return style;
}
static void SetValueAndFormat(IWorkbook workbook, ICell cell, double value, short formatId)
{
cell.SetCellValue(value);
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = formatId;
cell.CellStyle = cellStyle;
}
private ICellStyle BorderAndFontSetting(IWorkbook wb, Tk5FieldInfoEx metaInfo, Model model)
{
ICellStyle cellStyle = wb.CreateCellStyle();
if (fUseBorder)
{
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
}
if (model == Model.Content)
{
IFont fontContent = ExcelUtil.FontSetting(wb, fContent);
cellStyle.SetFont(fontContent);
if (metaInfo.Extension != null)
{
ExcelUtil.AlignSetting(metaInfo.Extension.Align, cellStyle);
}
else
{
ExcelUtil.AlignSetting(fContent.Align, cellStyle);
}
}
else
{
ExcelUtil.AlignSetting(fHeader.Align, cellStyle);
IFont fontHeader = ExcelUtil.FontSetting(wb, fHeader);
cellStyle.SetFont(fontHeader);
}
return cellStyle;
}
internal static ICell CreateCell(IWorkbook workbook, IRow row, int column, decimal? content, bool isCentered)
{
ICellStyle style = workbook.CreateCellStyle();
ICell cell = row.CreateCell(column);
if (content == null)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(Convert.ToDouble(content.Value));
}
if (isCentered)
{
style.Alignment = HorizontalAlignment.Center;
}
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
cell.CellStyle = style;
return cell;
}
private ICellStyle CreateBoldStyle(IWorkbook workbook)
{
var style = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.Boldweight = 1000;
style.SetFont(font);
return style;
}
private static ICellStyle GetBoldInfoFont(IWorkbook workbook)
{
IFont infoFont = workbook.CreateFont();
infoFont.FontName = "Segoe UI";
infoFont.FontHeightInPoints = 10;
infoFont.IsBold = true;
ICellStyle boldInfoStyle = workbook.CreateCellStyle();
boldInfoStyle.SetFont(infoFont);
return(boldInfoStyle);
}
static void SetValueAndFormat(IWorkbook workbook, ICell cell, DateTime value, short formatId)
{
//set value for the cell
if (value != null)
{
cell.SetCellValue(value);
}
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = formatId;
cell.CellStyle = cellStyle;
}
/// <summary>
/// 获取标题样式
/// </summary>
/// <returns></returns>
private ICellStyle GetTitleStyle()
{
ICellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.Left;
titleStyle.FillForegroundColor = HSSFColor.Orange.Index;
IFont font = workbook.CreateFont();
font.Color = HSSFColor.Orange.Index;
font.FontHeightInPoints = 12;
titleStyle.SetFont(font);
return(titleStyle);
}
public ICellStyle GetTitleStyle()
{
if (_defaultTitleStyle != null)
{
return(_defaultTitleStyle);
}
var font = _workbook.CreateFont();
font.Color = White.Index;
font.IsBold = true;
_defaultTitleStyle = _workbook.CreateCellStyle();
_defaultTitleStyle.Alignment = HorizontalAlignment.Center;
_defaultTitleStyle.VerticalAlignment = VerticalAlignment.Center;
IColor color = GetWorkbookColor(54, 96, 146);
SetStyleFillForegroundColor(_defaultTitleStyle, color);
_defaultTitleStyle.FillPattern = FillPattern.SolidForeground;
_defaultTitleStyle.SetFont(font);
return(_defaultTitleStyle);
}
/// <summary>
/// 创建表格头单元格
/// </summary>
/// <returns></returns>
private static ICellStyle GetCellStyle(IWorkbook workbook)
{
if (workbook == null)
{
throw new ArgumentNullException("workbook");
}
ICellStyle style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
return(style);
}
/// <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 void CreateHeader(ISheet sheet1, IWorkbook workbook, IRow row, IFont font, ICell cell,
int rowIndex, int headerColIndex, string header)
{
sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, headerColIndex, headerColIndex + 6));
row = sheet1.CreateRow(rowIndex);
row.Height = 1500;
ICellStyle cs = workbook.CreateCellStyle();
cs.WrapText = true;
cell = row.CreateCell(headerColIndex);
cell.SetCellValue(header);
cell.CellStyle = cs;
}
public void Set(IWorkbook wb)
{
if (wb == null)
{
throw new ArgumentNullException("El libro al que pertenece el estilo no puede estar vacío.", "wb");
}
IDataFormat format = wb.CreateDataFormat();
estilo = wb.CreateCellStyle();
estilo.Alignment = HorizontalAlignment.CENTER;
estilo.DataFormat = format.GetFormat("yyyy/mm/dd");
}
protected static ICellStyle BorderAlignCenterColoured(IWorkbook workbook)
{
ICellStyle center = workbook.CreateCellStyle();
center.BorderTop = BorderStyle.Medium;
center.BorderLeft = BorderStyle.Medium;
center.BorderRight = BorderStyle.Medium;
center.BorderBottom = BorderStyle.Medium;
center.FillBackgroundColor = IndexedColors.Grey25Percent.Index;
//center.FillPattern = FillPattern.SolidForeground;
center.Alignment = HorizontalAlignment.Center;
return(center);
}
private static ICellStyle DateTimeFormat(IWorkbook workbook)
{
var dateTimeFormat = workbook.CreateDataFormat();
var style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.DataFormat = dateTimeFormat.GetFormat("MM/dd/yyyy HH:mm:ss");
return(style);
}
/// <summary>
/// 获取样式
/// </summary>
/// <param name="book"></param>
/// <param name="colorIndex">如：NPOI.HSSF.Util.HSSFColor.RED.index</param>
/// <returns></returns>
private static ICellStyle GetStyle(IWorkbook book, short colorIndex)
{
ICellStyle style = book.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.FillForegroundColor = colorIndex;// NPOI.HSSF.Util.HSSFColor.RED.index;
style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
style.Alignment = HorizontalAlignment.Center;
return(style);
}
public HttpResponseMessage Export
(List exportData, string fileName, string sheetName = DefaultSheetName)
{
_fileName = fileName;
_sheetName = sheetName;
_workbook = new XSSFWorkbook(); //Creating New Excel object
_sheet = _workbook.CreateSheet(_sheetName); //Creating New Excel Sheet object
var headerStyle = _workbook.CreateCellStyle(); //Formatting
var headerFont = _workbook.CreateFont();
headerFont.IsBold = true;
headerStyle.SetFont(headerFont);
WriteData(exportData); //your list object to NPOI excel conversion happens here
//Header
var header = _sheet.CreateRow(0);
for (var i = 0; i < _headers.Count; i++)
{
var cell = header.CreateCell(i);
cell.SetCellValue(_headers[i]);
cell.CellStyle = headerStyle;
}
for (var i = 0; i < _headers.Count; i++)
{
_sheet.AutoSizeColumn(i);
}
using (var memoryStream = new MemoryStream()) //creating memoryStream
{
_workbook.Write(memoryStream);
var response = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(memoryStream.ToArray())
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue
("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.Content.Headers.ContentDisposition =
new ContentDispositionHeaderValue("attachment")
{
FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
};
return(response);
}
}
/// <summary>
/// 更新单元格格式 加边框
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
public static ICellStyle RowBorder(IWorkbook workbook)
{
ICellStyle rowsStyleColor = (XSSFCellStyle)workbook.CreateCellStyle();
rowsStyleColor.Alignment = HorizontalAlignment.Center;
rowsStyleColor.VerticalAlignment = VerticalAlignment.Center;
rowsStyleColor.BorderBottom = BorderStyle.Thin;
rowsStyleColor.BorderLeft = BorderStyle.Thin;
rowsStyleColor.BorderRight = BorderStyle.Thin;
rowsStyleColor.BorderTop = BorderStyle.Thin;
rowsStyleColor.WrapText = true;
return(rowsStyleColor);
}
public static ICellStyle CreateCenterAlignmentStyle(IWorkbook workbook)
{
if (_centerAlignmentStyle != null)
{
return(_centerAlignmentStyle);
}
_centerAlignmentStyle = workbook.CreateCellStyle();
_centerAlignmentStyle.CloneStyleFrom(CreateBorderStyle(workbook));
_centerAlignmentStyle.Alignment = HorizontalAlignment.Center;
_centerAlignmentStyle.VerticalAlignment = VerticalAlignment.Center;
return(_centerAlignmentStyle);
}
public static ICellStyle CreateSummaryStyle(IWorkbook workbook)
{
if (_summaryStyle != null)
{
return(_summaryStyle);
}
_summaryStyle = workbook.CreateCellStyle();
_summaryStyle.CloneStyleFrom(CreateBoldFontStyle(workbook));
_summaryStyle.FillForegroundColor = IndexedColors.Aqua.Index;
_summaryStyle.FillPattern = FillPattern.SolidForeground;
return(_summaryStyle);
}
/// <summary>
/// 默认正文样式
/// </summary>
/// <param name="workbook">工作簿</param>
public static ICellStyle DefaultBodyStyle(this IWorkbook workbook)
{
var style = workbook.CreateCellStyle();
var font = workbook.CreateFont();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.SetFont(font.DefaultFont());
return(style);
}
private static ICellStyle CreateHeadRowStyle(IWorkbook workbook)
{
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
headStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 15;
font.Boldweight = 700;
headStyle.SetFont(font);
return(headStyle);
}
private static void MakeRowBold(IWorkbook workbook, IRow row)
{
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 12;
foreach (ICell cell in row)
{
cell.CellStyle = cell.CellStyle == null ? style : cell.CellStyle;
cell.CellStyle.SetFont(font);
}
}
public void TestSetBlank_bug47028()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICellStyle style = wb.CreateCellStyle();
ICell cell = wb.CreateSheet("Sheet1").CreateRow(0).CreateCell(0);
cell.CellStyle = (style);
int i1 = cell.CellStyle.Index;
cell.SetCellType(CellType.Blank);
int i2 = cell.CellStyle.Index;
Assert.AreEqual(i1, i2);
}
private void CreateTitle(SheetTitle title, ISheet sheet, int columnCount)
{
var cellStyle = _workBook.CreateCellStyle();
cellStyle.Alignment = (HorizontalAlignment)title.HorizontalAlign;
var font = _workBook.CreateFont();
font.IsBold = title.IsBold;
font.FontName = title.FontName;
font.FontHeightInPoints = title.FontSize;
font.Color = title.FontColor;
cellStyle.SetFont(font);
var titleRow = sheet.CreateRow(0);
titleRow.CreateCell(0).SetCellValue(title.Title);
if (columnCount > 1)
{
CellRangeAddress region = new CellRangeAddress(0, 0, 0, columnCount - 1);
sheet.AddMergedRegion(region);
}
sheet.GetRow(0).GetCell(0).CellStyle = cellStyle;
}
/// <summary>
/// Set topic style format.
/// </summary>
/// <param name="workbook">The npoi workbook interface.</param>
/// <returns></returns>
private static ICellStyle SetTopicCellStyle(IWorkbook workbook)
{
var font = workbook.CreateFont();
font.FontName = FontStyle;
font.FontHeight = 20;
font.IsBold = true;
var style = workbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Left;
style.SetFont(font);
return(style);
}
/// <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 = FillPattern.SolidForeground;
return(cellStyle);
}
void SetCell(IWorkbook workbook, ISheet sheet, int r, int tor, int c, int toc, dynamic value, IFont font, bool isBorder = false, BorderStyle borderStyle = BorderStyle.Medium, bool isCenter = false, short?rowHeight = null)
{
var rang = new CellRangeAddress(r, tor, c, toc);
sheet.AddMergedRegion(rang);
var row = sheet.GetRow(r) == null?sheet.CreateRow(r) : sheet.GetRow(r);
if (rowHeight.HasValue)
{
row.Height = rowHeight.Value;
}
var cell = row.CreateCell(c);
if (value != null)
{
cell.SetCellValue(value);
}
var style = workbook.CreateCellStyle();
//设置字体
style.SetFont(font);
//设置边框
if (isBorder)
{
style.BorderLeft = borderStyle;
style.BorderRight = borderStyle;
style.BorderTop = borderStyle;
style.BorderBottom = borderStyle;
for (int i = rang.FirstRow; i <= rang.LastRow; i++)
{
var borderRow = CellUtil.GetRow(i, sheet);
for (int j = rang.FirstColumn; j <= rang.LastColumn; j++)
{
var singleCell = CellUtil.GetCell(borderRow, (short)j);
singleCell.CellStyle = style;
}
}
}
else
{
cell.CellStyle = style;
}
//设置内容居中
if (isCenter)
{
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
}
}
public ICellStyle CreateBorderedCellStyle(IWorkbook wb)
{
ICellStyle cell = wb.CreateCellStyle();
cell.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cell.RightBorderColor = (IndexedColors.Black.Index);
cell.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cell.BottomBorderColor = (IndexedColors.Black.Index);
cell.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cell.LeftBorderColor = (IndexedColors.Black.Index);
cell.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cell.TopBorderColor = (IndexedColors.Black.Index);
return(cell);
}
/// <summary>
/// 设置边框
/// </summary>
/// <param name="workbook">工作表</param
/// <param name="row">设置行</param>
/// <param name="col">设置列</param>
public static void SetBorderLine(this IWorkbook workbook, int row, int col)
{
ISheet sheet = workbook.GetSheetAt(0);
ICell cell = sheet.GetRow(row).GetCell(col);
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
cell.CellStyle = style;
}
/// <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);
}
protected static ICellStyle CreateCellStyle(IWorkbook workbook, short fontHeight, short boldWeight)
{
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontHeightInPoints = fontHeight;
// font.FontHeight = fontHeight;
font.Boldweight = boldWeight; //(short)FontBoldWeight.Bold;
style.SetFont(font);
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
return(style);
}
private static ICellStyle CreateBlueStyle(IWorkbook workbook)
{
var Result = workbook.CreateCellStyle();
Result.FillForegroundColor = HSSFColor.Automatic.Index;
Result.FillPattern = FillPattern.NoFill;
var UniversalFont = workbook.CreateFont();
UniversalFont.Color = 21;
Result.SetFont(UniversalFont);
return(Result);
}
private static ICellStyle CreateBorderedStyle(IWorkbook wb)
{
ICellStyle style = wb.CreateCellStyle();
style.BorderRight = BorderStyle.Thin;
style.RightBorderColor = (IndexedColors.Black.Index);
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = (IndexedColors.Black.Index);
style.BorderLeft = BorderStyle.Thin;
style.LeftBorderColor = (IndexedColors.Black.Index);
style.BorderTop = BorderStyle.Thin;
style.TopBorderColor = (IndexedColors.Black.Index);
return(style);
}
private static ICellStyle CreateHeaderCellStyle(IWorkbook workbook)
{
var boldFont = workbook.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle boldStyle = workbook.CreateCellStyle();
boldStyle.SetFont(boldFont);
boldStyle.BorderBottom = BorderStyle.Medium;
boldStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
boldStyle.FillPattern = FillPattern.SolidForeground;
return(boldStyle);
}
internal static void GrayBackground(IWorkbook workbook, ICell cell, bool isCentered)
{
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
if (isCentered)
{
style.Alignment = HorizontalAlignment.Center;
}
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
cell.CellStyle = style;
}
internal static ICell CreateCell(IWorkbook workbook, IRow row, int column, string content, bool isCentered)
{
ICellStyle style = workbook.CreateCellStyle();
ICell cell = row.CreateCell(column);
cell.SetCellValue(content);
if (isCentered)
{
style.Alignment = HorizontalAlignment.Center;
}
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
cell.CellStyle = style;
return cell;
}
private static void CreateRowItem(this ISheet target, IWorkbook workbook, DataTable dataSource)
{
IRow row = null;
ICell cell = null;
ICellStyle cellStyle = null;
IDrawing drawing = null;
IPicture picture = null;
cellStyle = workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
for (int rowIndex = 0; rowIndex < dataSource.Rows.Count; rowIndex++)
{
row = target.CreateRow(rowIndex + 1);
for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++)
{
cell = row.CreateCell(columnIndex);
if (dataSource.Columns[columnIndex].DataType == typeof(byte[]))
{
byte[] image = dataSource.Rows[rowIndex][columnIndex] as byte[];
if (image != null && image.Length > 0)
{
int pictureIndex = workbook.AddPicture(dataSource.Rows[rowIndex][columnIndex] as byte[], PictureType.JPEG);
drawing = target.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex + 1, columnIndex, rowIndex + 1);
picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize();
}
}
else
{
cell.SetCellValue(dataSource.Rows[rowIndex][columnIndex].ToString());
}
cell.CellStyle = cellStyle;
}
}
}
/// <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 void CreateRowHeader(this ISheet target, IWorkbook workbook, DataTable dataSource)
{
string[] columnInfo = null;
IRow row = target.CreateRow(0);
ICell cell = null;
ICellStyle cellStyle = null;
for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++)
{
columnInfo = dataSource.Columns[columnIndex].ColumnName.Split('_');
cell = row.CreateCell(columnIndex);
cellStyle = workbook.CreateCellStyle();
cell.SetCellValue(columnInfo[0]);
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
cell.CellStyle = cellStyle;
target.SetColumnWidth(columnIndex, 256 * columnInfo[1].ToInt());
}
target.CreateFreezePane(0, 1, 0, 1);
}
/**
* 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 CreateBorderedStyle(IWorkbook wb)
{
ICellStyle style = wb.CreateCellStyle();
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.RightBorderColor = (IndexedColors.Black.Index);
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BottomBorderColor = (IndexedColors.Black.Index);
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.LeftBorderColor = (IndexedColors.Black.Index);
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.TopBorderColor = (IndexedColors.Black.Index);
return style;
}
static ICellStyle GetRequireCellStyle(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.LightBlue.Index;
style.FillPattern = FillPattern.SolidForeground;
return style;
}
private static ICellStyle CreateBorderedStyle(IWorkbook wb)
{
ICellStyle style = wb.CreateCellStyle();
style.BorderRight = BorderStyle.THIN;
style.RightBorderColor = (IndexedColors.BLACK.Index);
style.BorderBottom = BorderStyle.THIN;
style.BottomBorderColor = (IndexedColors.BLACK.Index);
style.BorderLeft = BorderStyle.THIN;
style.LeftBorderColor = (IndexedColors.BLACK.Index);
style.BorderTop = BorderStyle.THIN;
style.TopBorderColor = (IndexedColors.BLACK.Index);
return style;
}
static ICellStyle GetCellStyle(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;
return style;
}
/**
* This method attempt to find an already existing CellStyle that matches what you want the
* style to be. If it does not find the style, then it creates a new one. If it does create a
* new one, then it applies the propertyName and propertyValue to the style. This is necessary
* because Excel has an upper limit on the number of Styles that it supports.
*
*@param workbook The workbook that is being worked with.
*@param propertyName The name of the property that is to be changed.
*@param propertyValue The value of the property that is to be changed.
*@param cell The cell that needs it's style changes
*/
public static void SetCellStyleProperty(ICell cell, IWorkbook workbook, String propertyName,
Object propertyValue)
{
ICellStyle originalStyle = cell.CellStyle;
ICellStyle newStyle = null;
Dictionary<String, Object> values = GetFormatProperties(originalStyle);
if (values.ContainsKey(propertyName))
values[propertyName] = propertyValue;
else
values.Add(propertyName, propertyValue);
// index seems like what index the cellstyle is in the list of styles for a workbook.
// not good to compare on!
short numberCellStyles = workbook.NumCellStyles;
for (short i = 0; i < numberCellStyles; i++)
{
ICellStyle wbStyle = workbook.GetCellStyleAt(i);
Dictionary<String, Object> wbStyleMap = GetFormatProperties(wbStyle);
if (wbStyleMap.Equals(values))
{
newStyle = wbStyle;
break;
}
}
if (newStyle == null)
{
newStyle = workbook.CreateCellStyle();
SetFormatProperties(newStyle, workbook, values);
}
cell.CellStyle=(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;
}
/// <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;
}
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>();
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;
}
/**
* 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 void AddDateTimeValidations(WorkbookFormatter wf, IWorkbook wb)
{
wf.CreateSheet("Dates and Times");
IDataFormat dataFormat = wb.CreateDataFormat();
short fmtDate = dataFormat.GetFormat("m/d/yyyy");
short fmtTime = dataFormat.GetFormat("h:mm");
ICellStyle cellStyle_date = wb.CreateCellStyle();
cellStyle_date.DataFormat = (/*setter*/fmtDate);
ICellStyle cellStyle_time = wb.CreateCellStyle();
cellStyle_time.DataFormat = (/*setter*/fmtTime);
wf.CreateDVTypeRow("Date ( cells are already formated as date - m/d/yyyy)");
wf.CreateHeaderRow();
ValidationAdder va = wf.CreateValidationAdder(cellStyle_date, ValidationType.DATE);
va.AddValidation(OperatorType.BETWEEN, "2004/01/02", "2004/01/06", ERRORSTYLE.STOP, "Between 1/2/2004 and 1/6/2004 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "2004/01/01", "2004/01/06", ERRORSTYLE.INFO, "Not between 1/2/2004 and 1/6/2004 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "2004/03/02", null, ERRORSTYLE.WARNING, "Equal to 3/2/2004", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "2004/03/02", null, ERRORSTYLE.WARNING, "Not equal to 3/2/2004", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "=DATEVALUE(\"4-Jul-2001\")", null, ERRORSTYLE.WARNING, "Greater than DATEVALUE('4-Jul-2001')", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "2004/03/02", null, ERRORSTYLE.WARNING, "Less than 3/2/2004", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "2004/03/02", null, ERRORSTYLE.STOP, "Greater than or equal to 3/2/2004", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "2004/03/04", null, ERRORSTYLE.STOP, "Less than or equal to 3/4/2004", "-", false, true, false);
// "Time" validation type
wf.CreateDVTypeRow("Time ( cells are already formated as time - h:mm)");
wf.CreateHeaderRow();
va = wf.CreateValidationAdder(cellStyle_time, ValidationType.TIME);
va.AddValidation(OperatorType.BETWEEN, "12:00", "16:00", ERRORSTYLE.STOP, "Between 12:00 and 16:00 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "12:00", "16:00", ERRORSTYLE.INFO, "Not between 12:00 and 16:00 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "13:35", null, ERRORSTYLE.WARNING, "Equal to 13:35", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "13:35", null, ERRORSTYLE.WARNING, "Not equal to 13:35", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "12:00", null, ERRORSTYLE.WARNING, "Greater than 12:00", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "=1/2", null, ERRORSTYLE.WARNING, "Less than (1/2) -> 12:00", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "14:00", null, ERRORSTYLE.STOP, "Greater than or equal to 14:00", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "14:00", null, ERRORSTYLE.STOP, "Less than or equal to 14:00", "-", false, true, false);
}
