/// <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); }