本文整理汇总了C#中IWorkbook.CreateDataFormat方法的典型用法代码示例。如果您正苦于以下问题：C# IWorkbook.CreateDataFormat方法的具体用法？C# IWorkbook.CreateDataFormat怎么用？C# IWorkbook.CreateDataFormat使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类IWorkbook的用法示例。
在下文中一共展示了IWorkbook.CreateDataFormat方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: RegisterCustomStyle
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");
}示例2: ApplyStyle
/// <summary>
/// Applies the style by copying the fluent style to the NPOI style object,.
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <param name="destination">The destination NPOI style object to apply the FluentStyle to.</param>
public void ApplyStyle(IWorkbook workbook, ICellStyle destination)
{
// If users sets format string this overrides the DataFormat property.
if (Format != null)
{
var dataFormat = workbook.CreateDataFormat();
DataFormat = dataFormat.GetFormat(Format);
}
if (Alignment != null) destination.Alignment = Alignment.Value;
if (BorderBottom != null) destination.BorderBottom = BorderBottom.Value;
if (BorderDiagonal != null) destination.BorderDiagonal = BorderDiagonal.Value;
if (BorderDiagonalColor != null) destination.BorderDiagonalColor = BorderDiagonalColor.Value;
if (BorderDiagonalLineStyle != null) destination.BorderDiagonalLineStyle = BorderDiagonalLineStyle.Value;
if (BorderLeft != null) destination.BorderLeft = BorderLeft.Value;
if (BorderRight != null) destination.BorderRight = BorderRight.Value;
if (BorderTop != null) destination.BorderTop = BorderTop.Value;
if (BottomBorderColor != null) destination.BottomBorderColor = BottomBorderColor.Value;
if (DataFormat != null) destination.DataFormat = DataFormat.Value;
if (FillBackgroundColor != null) destination.FillBackgroundColor = FillBackgroundColor.Value;
if (FillForegroundColor != null) destination.FillForegroundColor = FillForegroundColor.Value;
if (FillPattern != null) destination.FillPattern = FillPattern.Value;
if (Indention != null) destination.Indention = Indention.Value;
if (LeftBorderColor != null) destination.LeftBorderColor = LeftBorderColor.Value;
if (RightBorderColor != null) destination.RightBorderColor = RightBorderColor.Value;
if (Rotation != null) destination.Rotation = Rotation.Value;
if (ShrinkToFit != null) destination.ShrinkToFit = ShrinkToFit.Value;
if (TopBorderColor != null) destination.TopBorderColor = TopBorderColor.Value;
if (VerticalAlignment != null) destination.VerticalAlignment = VerticalAlignment.Value;
if (WrapText != null) destination.WrapText = WrapText.Value;
if (FontIsNeeded)
{
var font = workbook.CreateFont();
if (FontWeight != null) font.Boldweight = (short)FontWeight.Value;
if (Charset != null) font.Charset = Charset.Value;
if (Color != null) font.Color = Color.Value;
if (FontHeight != null) font.FontHeight = FontHeight.Value;
if (FontHeightInPoints != null) font.FontHeightInPoints = FontHeightInPoints.Value;
if (FontName != null) font.FontName = FontName;
if (Italic != null) font.IsItalic = Italic.Value;
if (Strikeout != null) font.IsStrikeout = Strikeout.Value;
if (SuperScript != null) font.TypeOffset = SuperScript.Value;
if (Underline != null) font.Underline = Underline.Value;
destination.SetFont(font);
}
}示例3: GetContentStyles
//生成Dictionary<NickName, ICellStyle>， 每一列对应每一个ICellStyle，通过NickName来获取ICellStyle
private Dictionary<string, ICellStyle> GetContentStyles(IWorkbook workbook)
{
Dictionary<string, ICellStyle> NameToStyle = new Dictionary<string, ICellStyle>();
IDataFormat format = workbook.CreateDataFormat();
foreach (Tk5FieldInfoEx fieldInfo in fMetaData.Table.TableList)
{
ICellStyle cellStyle = BorderAndFontSetting(workbook, fieldInfo, Model.Content);
if (fieldInfo.Extension != null && !string.IsNullOrEmpty(fieldInfo.Extension.Format))
cellStyle.DataFormat = format.GetFormat("@");
else
{
switch (fieldInfo.DataType)
{
case TkDataType.Long:
case TkDataType.Int:
case TkDataType.Short:
case TkDataType.Byte:
case TkDataType.Double:
case TkDataType.Decimal:
case TkDataType.Money:
if (fieldInfo.DataType == TkDataType.Money)
cellStyle.DataFormat = format.GetFormat("￥#,##0");
else
cellStyle.DataFormat = format.GetFormat("0");
break;
case TkDataType.String:
case TkDataType.Text:
case TkDataType.Guid:
case TkDataType.Xml:
cellStyle.DataFormat = format.GetFormat("@");
break;
case TkDataType.DateTime:
case TkDataType.Date:
if (fieldInfo.DataType == TkDataType.DateTime)
cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm");
else
cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
break;
}
}
NameToStyle.Add(fieldInfo.NickName, cellStyle);
}
return NameToStyle;
}示例4: doTest49928Core
public void doTest49928Core(IWorkbook wb)
{
DataFormatter df = new DataFormatter();
ISheet sheet = wb.GetSheetAt(0);
ICell cell = sheet.GetRow(0).GetCell(0);
ICellStyle style = cell.CellStyle;
String poundFmt = "\"\u00a3\"#,##0;[Red]\\-\"\u00a3\"#,##0";
// not expected normally, id of a custom format should be greater
// than BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX
short poundFmtIdx = 6;
Assert.AreEqual(poundFmt, style.GetDataFormatString());
Assert.AreEqual(poundFmtIdx, style.DataFormat);
Assert.AreEqual("\u00a31", df.FormatCellValue(cell));
IDataFormat dataFormat = wb.CreateDataFormat();
Assert.AreEqual(poundFmtIdx, dataFormat.GetFormat(poundFmt));
Assert.AreEqual(poundFmt, dataFormat.GetFormat(poundFmtIdx));
}示例5: CreateStyles
/**
* 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;
}示例6: createStyles
/**
* create a library of cell styles
*/
private static Dictionary<String, ICellStyle> createStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
IDataFormat df = wb.CreateDataFormat();
ICellStyle style;
IFont headerFont = wb.CreateFont();
headerFont.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.LIGHT_CORNFLOWER_BLUE.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(headerFont);
styles.Add("header", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.FillForegroundColor = (IndexedColors.LIGHT_CORNFLOWER_BLUE.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(headerFont);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("header_date", style);
IFont font1 = wb.CreateFont();
font1.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
styles.Add("cell_b", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
styles.Add("cell_b_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_b_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_g", style);
IFont font2 = wb.CreateFont();
font2.Color = (IndexedColors.BLUE.Index);
font2.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font2);
styles.Add("cell_bb", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index);
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_bg", style);
IFont font3 = wb.CreateFont();
font3.FontHeightInPoints = ((short)14);
font3.Color = (IndexedColors.DARK_BLUE.Index);
font3.Boldweight = (short)(FontBoldWeight.BOLD);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font3);
style.WrapText = (true);
styles.Add("cell_h", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
styles.Add("cell_normal", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
styles.Add("cell_normal_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = (true);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_normal_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.CENTER;
style.Indention = ((short)1);
style.WrapText = (true);
styles.Add("cell_indented", style);
//.........这里部分代码省略.........
示例7: Getcellstyle
//.........这里部分代码省略.........
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;示例8: createStyles
private static Dictionary<String, ICellStyle> createStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
IDataFormat df = wb.CreateDataFormat();
ICellStyle style;
IFont headerFont = wb.CreateFont();
headerFont.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(headerFont);
styles.Add("header", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(headerFont);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("header_date", style);
IFont font1 = wb.CreateFont();
font1.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
styles.Add("cell_b", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
styles.Add("cell_b_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_b_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_g", style);
IFont font2 = wb.CreateFont();
font2.Color = (IndexedColors.Blue.Index);
font2.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font2);
styles.Add("cell_bb", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_bg", style);
IFont font3 = wb.CreateFont();
font3.FontHeightInPoints = ((short)14);
font3.Color = (IndexedColors.DarkBlue.Index);
font3.Boldweight = (short)(FontBoldWeight.Bold);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font3);
style.WrapText = (true);
styles.Add("cell_h", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.WrapText = (true);
styles.Add("cell_normal", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.WrapText = (true);
styles.Add("cell_normal_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.WrapText = (true);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_normal_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.Indention = ((short)1);
style.WrapText = (true);
styles.Add("cell_indented", style);
style = CreateBorderedStyle(wb);
style.FillForegroundColor = (IndexedColors.Blue.Index);
style.FillPattern = FillPattern.SolidForeground;
//.........这里部分代码省略.........
示例9: CreateStyles
/**
* 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;
//.........这里部分代码省略.........
示例10: AddDateTimeValidations
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);
}示例11: GenerateReport
private void GenerateReport(IWorkbook workbook)
{
JobTracker jobtracker = new JobTracker();
List<JobTracker> projectList = jobtracker.GetDistinctProjectListIncludingForApproval(Convert.ToDateTime(txtBoxFrom.Text+ " 00:00:00"), Convert.ToDateTime(txtBoxTo.Text+" 23:59:59"));
int currentrow = 1;
IDataFormat format = workbook.CreateDataFormat();
ICellStyle fontBoldNoBorder = CreateSheetStyle(workbook, false, false, false, false, true, false, false, false,false);
ICellStyle fontBoldAllBorder = CreateSheetStyle(workbook, true, true, true, true, true, false, false, false);
ICellStyle fontCurrencyBoldRigthBottom = CreateSheetStyle(workbook, false, false, true, true, true, false, false, false);
fontCurrencyBoldRigthBottom.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontBoldTopBottom = CreateSheetStyle(workbook, false, true, false, true, true, false, false, false);
fontCurrencyBoldRigthBottom.Alignment = HorizontalAlignment.Center;
fontCurrencyBoldRigthBottom.VerticalAlignment = VerticalAlignment.Center;
ICellStyle fontCurrencyBoldAllBorder = CreateSheetStyle(workbook, true, true, true, true, true, false, false, false);
fontBoldAllBorder.Alignment = HorizontalAlignment.Center;
fontCurrencyBoldAllBorder.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontNormalBorderLeftRight = CreateSheetStyle(workbook, true, false, true, false, false, false, false, false);
fontBoldAllBorder.VerticalAlignment = VerticalAlignment.Center;
ICellStyle fontNormalBorderLeftRightBottom = CreateSheetStyle(workbook, true, false, true,true, false, false, false, false);
ICellStyle fontCurrencyBorderLeftRight = CreateSheetStyle(workbook, true, false, true, false, false, false, false, false);
fontCurrencyBorderLeftRight.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontCurrencyBorderLeftRightBottom = CreateSheetStyle(workbook, true, false, true, true, false, false, false, false);
fontCurrencyBorderLeftRightBottom.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
//fontNormalBorderLeftRightBottom.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//fontNormalBorderLeftRightBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//fontNormalBorderLeftRightBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBorderLeftRightBottom.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//fontCurrencyBorderLeftRightBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBorderLeftRightBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBoldRigthBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
ISheet sheetReport = workbook.GetSheet("Report");
foreach (JobTracker project in projectList)
{
//Running Total Variable
double runningNTApproved = 0;
double runningNTForApproval = 0;
double runningOTApproved = 0;
double runningOTForApproval = 0;
double runningNTApprovedCost = 0;
double runningNTForApprovalCost = 0;
double runningOTApprovedCost = 0;
double runningOTForApprovalCost = 0;
string jobheader = project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : "HW SO: " + project.HWNo;
jobheader += project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : jobheader == "" ? "SW SO: " + project.SWNo : "; SW SO: " + project.SWNo;
jobheader += project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : jobheader == "" ? "EVAL NO: " + project.EvalNo : "; EVAL NO: " + project.EvalNo;
currentrow += 2;
IRow row = sheetReport.CreateRow(currentrow++);
ICell cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue(jobheader);
for (int i = 1; i < 11; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
#region Header Upper Row
row = sheetReport.CreateRow(currentrow);
cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("JobStage");
cell = row.CreateCell(1);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Time");
for (int i = 2; i < 5; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
cell = row.CreateCell(5);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Labor Cost");
for (int i = 6; i < 9; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
cell = row.CreateCell(9);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Total");
cell = row.CreateCell(10);
cell.CellStyle = fontBoldAllBorder;
//Merging
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow -1, currentrow-1, 0, 10));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,1,4));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,5,8));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,9,10));
#endregion
#region Header Lower Row
row = sheetReport.CreateRow(++currentrow);
//.........这里部分代码省略.........
示例12: Getcellstyle
private static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 14;
font12.FontName = "宋体";
IFont font = wb.CreateFont();
font.FontName = "宋体";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
fontcolorblue.IsItalic = true; //下划线
fontcolorblue.FontName = "宋体";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//背景图形，我没有用到过。感觉很丑
// cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.GREEN.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
// cellStyle.FillForegroundColor = HSSFColor.WHITE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
cellStyle.FillBackgroundColor = HSSFColor.MAROON.index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
//自动换行
// cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = 1;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
}
return cellStyle;
}示例13: DataFormat
public static void DataFormat(this ICellStyle style, IWorkbook wb, string v)
{
var df = wb.CreateDataFormat();
style.DataFormat = df.GetFormat(v); ;
}示例14: Execute
public void Execute()
{
//set destination
if (File.Exists(_settings.ApplicationSettings.FileLocations.Destination))
{
File.Delete(_settings.ApplicationSettings.FileLocations.Destination);
}
//get source files
if (File.Exists(_settings.ApplicationSettings.FileLocations.Source))
{
_sourceFiles.Add(_settings.ApplicationSettings.FileLocations.Source);
}
else if (Directory.Exists(_settings.ApplicationSettings.FileLocations.Source))
{
GetSourceFiles(_settings.ApplicationSettings.FileLocations.Source);
}
_workbook = new XSSFWorkbook();
//create sheets
foreach (var sheet in _settings.Sheets)
{
if (_workbook.GetSheet(sheet.Name) == null)
{
_workbook.CreateSheet(sheet.Name);
}
}
var dateStyle = _workbook.CreateCellStyle();
dateStyle.DataFormat = _workbook.CreateDataFormat().GetFormat("dd-MM-yyyy");
//add columns
foreach (var sheet in _settings.Sheets)
{
var wbsheet = _workbook.GetSheet(sheet.Name);
sheet.ItemCount = 0;
foreach (var column in sheet.Elements.Columns)
{
ICell cell = wbsheet.GetOrCreateCell(sheet.HeaderRow, column.HeaderCol);
cell.SetCellValue(column.Header);
cell.CellStyle = dateStyle;
if (column.Width != 0)
{
wbsheet.SetColumnWidth(column.HeaderCol, column.Width);
}
wbsheet.GetRow(sheet.HeaderRow).RowStyle = dateStyle;
}
}
//run through all documents
for (int i = 0; i < _sourceFiles.Count; i++)
{
Console.Write(string.Format("{0}...", Path.GetFileName(_sourceFiles[i])));
var srcworkbook = WorkbookFactory.Create(_sourceFiles[i]);
foreach (var sheet in _settings.Sheets)
{
var wbsheet = _workbook.GetSheet(sheet.Name);
var srcwbsheet = srcworkbook.GetSheet(sheet.Name);
if (srcwbsheet != null)
{
//find first row by searching first column header name
var allheaders = (from a in sheet.Elements.Columns select a.Header.ToLower()).ToArray();
var indexes = new int?[allheaders.Length];
var rowenum = srcwbsheet.GetRowEnumerator();
while (rowenum.MoveNext())
{
var r = rowenum.Current as XSSFRow;
IRow ActiveRow = null;
var searchHeader = (from a in indexes where a != null select a).FirstOrDefault() == null;
if (!searchHeader)
{
ActiveRow = wbsheet.GetOrCreateRow(sheet.ItemCount + sheet.HeaderRow + 1);
sheet.ItemCount++;
ActiveRow.Height = r.Height;
}
foreach (var c in r.Cells)
{
if (searchHeader)
{
var index = Array.IndexOf(allheaders, (c.StringCellValue ?? "").ToLower());
if (index >= 0)
{
indexes[index] = c.ColumnIndex;
if (sheet.ItemCount == 0)
{
if (sheet.Elements.Columns[index].Width == 0)
{
wbsheet.SetColumnWidth(sheet.Elements.Columns[index].HeaderCol, Math.Max(srcwbsheet.GetColumnWidth(c.ColumnIndex), wbsheet.GetColumnWidth(sheet.Elements.Columns[index].HeaderCol)));
}
if (r.RowStyle != null)
{
wbsheet.GetRow(sheet.HeaderRow).RowStyle.CloneStyleFrom(r.RowStyle);
}
wbsheet.GetRow(sheet.HeaderRow).GetCell(sheet.Elements.Columns[index].HeaderCol).CellStyle.CloneStyleFrom(c.CellStyle);
}
}
}
else
//.........这里部分代码省略.........
示例15: CreateStyles
/**
* Create a library of cell styles
*/
private static Dictionary<String, ICellStyle> CreateStyles(IWorkbook wb)
{
Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>();
ICellStyle style;
IFont titleFont = wb.CreateFont();
titleFont.FontHeightInPoints = ((short)18);
titleFont.Boldweight = (short)FontBoldWeight.Bold;
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFont(titleFont);
styles.Add("title", style);
IFont monthFont = wb.CreateFont();
monthFont.FontHeightInPoints = ((short)11);
monthFont.Color = (IndexedColors.White.Index);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey50Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(monthFont);
style.WrapText = (true);
styles.Add("header", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
style.BorderRight = BorderStyle.Thin;
style.RightBorderColor = (IndexedColors.Black.Index);
style.BorderLeft = BorderStyle.Thin;
style.LeftBorderColor = (IndexedColors.Black.Index);
style.BorderTop = BorderStyle.Thin;
style.TopBorderColor = (IndexedColors.Black.Index);
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = (IndexedColors.Black.Index);
styles.Add("cell", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (wb.CreateDataFormat().GetFormat("0.00"));
styles.Add("formula", style);
style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = (IndexedColors.Grey40Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = wb.CreateDataFormat().GetFormat("0.00");
styles.Add("formula_2", style);
return styles;
}