public static ExcelStyles GetExcelStyles(IWorkbook workbook) { ICellStyle eurStyle = workbook.CreateCellStyle(); eurStyle.DataFormat = workbook.CreateDataFormat().GetFormat(EurMoneyFormat); ICellStyle monthStyle = workbook.CreateCellStyle(); monthStyle.DataFormat = workbook.CreateDataFormat().GetFormat(MonthDateFormat); ICellStyle dateStyle = workbook.CreateCellStyle(); dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat(DateFormat); ICellStyle percentage = workbook.CreateCellStyle(); percentage.DataFormat = workbook.CreateDataFormat().GetFormat(PercentageFormat); ICellStyle rightAlignment = workbook.CreateCellStyle(); rightAlignment.Alignment = HorizontalAlignment.Right; rightAlignment.VerticalAlignment = VerticalAlignment.Top; ICellStyle textStyle = workbook.CreateCellStyle(); textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("text"); var styles = new ExcelStyles() { DateStyle = dateStyle, EurStyle = eurStyle, HeaderStyle = rightAlignment, MonthStyle = monthStyle, Percentage = percentage, TextStyle = textStyle }; return(styles); }
/** * Create a library of cell styles */ private static Dictionary <String, ICellStyle> CreateStyles(IWorkbook wb) { Dictionary <String, ICellStyle> styles = new Dictionary <String, ICellStyle>(); ICellStyle style; IFont titleFont = wb.CreateFont(); titleFont.FontHeightInPoints = 18; titleFont.IsBold = true; style = wb.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; style.SetFont(titleFont); styles.Add("title", style); IFont monthFont = wb.CreateFont(); monthFont.FontHeightInPoints = 11; monthFont.Color = (IndexedColors.White.Index); style = wb.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; style.FillForegroundColor = (IndexedColors.Grey50Percent.Index); style.FillPattern = FillPattern.SolidForeground; style.SetFont(monthFont); style.WrapText = (true); styles.Add("header", style); style = wb.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.WrapText = (true); style.BorderRight = BorderStyle.Thin; style.RightBorderColor = (IndexedColors.Black.Index); style.BorderLeft = BorderStyle.Thin; style.LeftBorderColor = (IndexedColors.Black.Index); style.BorderTop = BorderStyle.Thin; style.TopBorderColor = (IndexedColors.Black.Index); style.BorderBottom = BorderStyle.Thin; style.BottomBorderColor = (IndexedColors.Black.Index); styles.Add("cell", style); style = wb.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; style.FillForegroundColor = (IndexedColors.Grey25Percent.Index); style.FillPattern = FillPattern.SolidForeground; style.DataFormat = wb.CreateDataFormat().GetFormat("0.00"); styles.Add("formula", style); style = wb.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; style.FillForegroundColor = (IndexedColors.Grey40Percent.Index); style.FillPattern = FillPattern.SolidForeground; style.DataFormat = wb.CreateDataFormat().GetFormat("0.00"); styles.Add("formula_2", style); return(styles); }
/** * 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 ICellStyle GetDouleStyle() { //设置数据显示格式 IDataFormat dataFormatCustom = _wb.CreateDataFormat(); var dateStyle = _wb.CreateCellStyle(); dateStyle.CloneStyleFrom(_commonCellStyle); dateStyle.DataFormat = dataFormatCustom.GetFormat("0.00"); return(dateStyle); }
/// <summary> /// 设置百分百:% /// </summary> /// <param name="workBook"></param> /// <param name="rgbs"></param> public static void SetPercent(this ICell cell) { if (cell == null) { return; } cell.DealParam(); IDataFormat sdf = workBook.CreateDataFormat(); cellStyle.DataFormat = sdf.GetFormat("0%"); cell.CellStyle = cellStyle; }
///// <summary> ///// 取得Excel的所有工作表名 ///// </summary> ///// <param name="excelPath">Excel文件绝对路径</param> ///// <returns></returns> //public static List<string> GetExcelTablesName(string excelPath) //{ // return ExcelToWorkbook(excelPath).Select(a => a.SheetName).ToList(); //} //switch(cell.CellType) //{ // case HSSFCellType.BLANK: // dr[i] = "[null]"; // break; // case HSSFCellType.BOOLEAN: // dr[i] = cell.BooleanCellValue; // break; // case HSSFCellType.NUMERIC: // dr[i] = cell.ToString(); //This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number. // break; // case HSSFCellType.STRING: // dr[i] = cell.StringCellValue; // break; // case HSSFCellType.ERROR: // dr[i] = cell.ErrorCellValue; // break; // case HSSFCellType.FORMULA: // default: // dr[i] = "="+cell.CellFormula; // break; //} /// <summary> /// 取得单元格的日期类型样式 /// </summary> /// <param name="workbook"></param> /// <param name="dateFormat"></param> /// <returns></returns> private static ICellStyle GetDateCellStyle(IWorkbook workbook, string dateFormat = "yyyy-MM-dd") { ICellStyle style = workbook.CreateCellStyle(); style.DataFormat = workbook.CreateDataFormat().GetFormat(dateFormat); return(style); }
public void Set(IWorkbook wb, string alineamiento, string formato) { if (wb == null) { throw new ArgumentNullException("El libro al que pertenece el estilo no puede estar vacío.", "wb"); } estilo = wb.CreateCellStyle(); switch (alineamiento) { case "Izquierda": estilo.Alignment = HorizontalAlignment.LEFT; break; case "Centro": estilo.Alignment = HorizontalAlignment.CENTER; break; case "Derecha": estilo.Alignment = HorizontalAlignment.RIGHT; break; default: estilo.Alignment = HorizontalAlignment.GENERAL; break; } if (!string.IsNullOrEmpty(formato)) { IDataFormat format = wb.CreateDataFormat(); estilo.DataFormat = format.GetFormat(formato); } }
private void SetCell(IWorkbook workbook, ICell newCell, ICellStyle dataStyle, string drValue, bool isDateTime) { if (isDateTime) { IDataFormat format = workbook.CreateDataFormat(); dataStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm"); DateTime dtV = DateTime.Parse(drValue); newCell.SetCellValue(dtV); newCell.CellStyle = dataStyle; return; } double dvalue = 0.0; bool isDouble = double.TryParse(drValue, out dvalue); if (isDouble) { dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000000"); newCell.SetCellValue(dvalue); // newCell.CellStyle = dataStyle; } else { newCell.SetCellValue(drValue); } }
/// <summary> /// 构造函数初始化 /// </summary> /// <param name="dateFormate"></param> public ExcelTool(string dateFormate = "yyyy-mm-dd") { workbook = new XSSFWorkbook(); TType = typeof(T); //1:设置属性 properties = TType.GetProperties(); //2:设置Col的默认最大长度 MaxColumnLength = 50; //3:默认的字体样式 _defaultStyle = workbook.CreateCellStyle(); _fontColor = workbook.CreateFont(); _fontColor.Color = HSSFColor.Black.Index; _fontColor.FontHeightInPoints = 11; //4:设置日期样式 _dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); _dateStyle.DataFormat = format.GetFormat(dateFormate); _dateStyle.SetFont(_fontColor); //2:检查是否有 if (typeof(IHasFontColor).IsAssignableFrom(TType)) { IsHasColor = true; } }
/// <summary> /// 获取单元格值的默认格式 /// </summary> /// <param name="workbook"></param> /// <param name="cellValue"></param> /// <returns></returns> public virtual short GetDefaultFormat(IWorkbook workbook, CellValueDefaultFormatEnum cellValue = CellValueDefaultFormatEnum.文本) { IDataFormat format = workbook.CreateDataFormat(); switch (cellValue) { case CellValueDefaultFormatEnum.日期: return(format.GetFormat("yyyy-MM-dd HH:mm:ss")); case CellValueDefaultFormatEnum.时间: return(format.GetFormat("HH:mm:ss")); case CellValueDefaultFormatEnum.数字: return(format.GetFormat("0.00")); //小数点后有几个0表示精确到小数点后几位 case CellValueDefaultFormatEnum.金额: return(format.GetFormat("¥#,##0.00_ ")); case CellValueDefaultFormatEnum.百分比: return(format.GetFormat("0.00%")); case CellValueDefaultFormatEnum.中文大写: return(format.GetFormat("[DbNum2][$-804]0")); case CellValueDefaultFormatEnum.科学计数法: return(format.GetFormat("0.00E+00")); case CellValueDefaultFormatEnum.文本: return(HSSFDataFormat.GetBuiltinFormat("@")); default: return(HSSFDataFormat.GetBuiltinFormat("@")); } }
protected override void ExportCore(Stream stream, TBodyList tList) { workBook = NPOIExtension.CreateWorkbook(exportSetting.XlsFormat); //----set summary //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); //dsi.Company = "BlackEyes"; //workBook.DocumentSummaryInformation = dsi; //SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); //si.Subject = "HKH.Exchange.ExcelExport"; //workBook.SummaryInformation = si; //----end //----create dateformat dateFormat = workBook.CreateDataFormat().GetFormat(dateFormatString); //----end sheet = workBook.CreateSheet(exportSetting.Sheet); CustomPageHeader(sheet.Header); CustomTableHeader(sheet); WriteBody(tList); CustomTableFooter(sheet); workBook.Write(stream); }
private void Fill <THeader>(TBodyList tList, THeader tHeader) { mode = ExportMode.Fill; exportSetting = GetExport(ExportId); exportSetting.CalculateColumnIndex(null); dateFormatString = string.IsNullOrEmpty(exportSetting.DateFormat) ? DEFAULTDATEFORMATSTRING : exportSetting.DateFormat; numberFormatSTring = string.IsNullOrEmpty(exportSetting.NumberFormat) ? DEFAULTNUMBERFORMATSTRING : exportSetting.NumberFormat; //looks as sheet index if it is Integer, otherwise TableName int sheetIndex = 0; if (int.TryParse(exportSetting.Sheet, out sheetIndex)) { sheet = workBook.GetSheetAt(sheetIndex); } else { sheet = workBook.GetSheet(exportSetting.Sheet); } //----create date format dateFormat = workBook.CreateDataFormat().GetFormat(dateFormatString); //----end WriteBody(tList); WriteHeader(tHeader); }
public StyleHelper(IWorkbook workbook) { _cellStyle = workbook.CreateCellStyle(); _workbook = workbook; _format = _workbook.CreateDataFormat(); _font = _workbook.CreateFont(); }
public ICellStyle GetStyle() { var style = workBook.CreateCellStyle(); style.DataFormat = workBook.CreateDataFormat().GetFormat("yyyy/mm/dd"); return(style); }
private IDictionary <string, ICellStyle> CreateStyles(IWorkbook workbook) { var headerStyle = workbook.CreateCellStyle(); headerStyle.Alignment = HorizontalAlignment.Center; var xssFont = new XSSFFont(new CT_Font()); xssFont.Boldweight = (short)FontBoldWeight.Bold; xssFont.IsBold = true; headerStyle.SetFont(xssFont); headerStyle.FillForegroundColor = HSSFColor.LightBlue.Index; headerStyle.FillPattern = FillPattern.SolidForeground; headerStyle.BorderTop = BorderStyle.Thin; headerStyle.BorderBottom = BorderStyle.Thin; headerStyle.TopBorderColor = HSSFColor.Black.Index; headerStyle.BottomBorderColor = HSSFColor.Black.Index; var dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss"); return(new Dictionary <string, ICellStyle> { { "header", headerStyle }, { "date", dateStyle }, }); }
private static ICellStyle CopyStyle(this ICellStyle dCellStyle, ICellStyle sCellStyle, IWorkbook dWb, IWorkbook sWb, List <ICellStyle> dCellStyles, List <IFont> dFonts) { ICellStyle currCellStyle = dCellStyle; currCellStyle.Alignment = sCellStyle.Alignment; currCellStyle.VerticalAlignment = sCellStyle.VerticalAlignment; currCellStyle.BorderTop = sCellStyle.BorderTop; currCellStyle.BorderBottom = sCellStyle.BorderBottom; currCellStyle.BorderLeft = sCellStyle.BorderLeft; currCellStyle.BorderRight = sCellStyle.BorderRight; currCellStyle.TopBorderColor = sCellStyle.TopBorderColor; currCellStyle.LeftBorderColor = sCellStyle.LeftBorderColor; currCellStyle.RightBorderColor = sCellStyle.RightBorderColor; currCellStyle.BottomBorderColor = sCellStyle.BottomBorderColor; currCellStyle.Indention = sCellStyle.Indention; currCellStyle.IsHidden = sCellStyle.IsHidden; currCellStyle.IsLocked = sCellStyle.IsLocked; currCellStyle.Rotation = sCellStyle.Rotation; currCellStyle.ShrinkToFit = sCellStyle.ShrinkToFit; currCellStyle.WrapText = sCellStyle.WrapText; currCellStyle.DataFormat = dWb.CreateDataFormat().GetFormat(sWb.CreateDataFormat().GetFormat(sCellStyle.DataFormat)); IFont sFont = sCellStyle.GetFont(sWb); IFont dFont = dWb.FindFont(sFont, dFonts) ?? dWb.CreateFont().CopyFont(sFont, dFonts); currCellStyle.SetFont(dFont); dCellStyles.Add(currCellStyle); return(currCellStyle); }
/// <summary> /// 新規ブックを作成 /// </summary> /// <returns>作成の成否</returns> protected bool CreateBook() { try { if (book != null) { book.Close(); } if (isOldMode) { book = new HSSFWorkbook(); } else { book = new XSSFWorkbook(); } styles.Clear(); fonts.Clear(); format = book.CreateDataFormat(); return(true); } catch (Exception) { book = null; return(false); } }
public static void BuildXLSFile <T> (Stream stream, IWorkbook xls, string sheet, string[] headers, IEnumerable <T> data, double timezone) where T : IDataFileFormatConverter { var ss = xls.CreateSheet(sheet); var header = ss.CreateRow(0); var col = 0; foreach (var field in headers) { var cell = header.CreateCell(col++); cell.SetCellValue(field); } var dateformat = xls.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss"); var datestyle = xls.CreateCellStyle(); datestyle.DataFormat = dateformat; var row = 1; foreach (var record in data) { var line = ss.CreateRow(row); record.FillXlsRow(xls, ss, headers, line, datestyle, timezone); row++; } xls.Write(stream); }
private static ICellStyle GetDateFormat(IWorkbook workbook) { ICellStyle style = workbook.CreateCellStyle(); style.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-mm-dd"); return(style); }
private static ICellStyle GetStyleCellStyle(IWorkbook workbook, string columnName, string columnDataType) { var style = workbook.CreateCellStyle(); style.WrapText = true;//设置自动换行 style.VerticalAlignment = VerticalAlignment.Center; SetCellBorder(style); switch (columnDataType) { case "System.DateTime": //日期类型 style.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-mm-dd"); break; case "System.Decimal": //浮点型 case "System.Double": case "System.Single": if (columnName.Substring(columnName.Length - 1, 1) == "%") { style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0%"); } else { style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); } style.Alignment = HorizontalAlignment.Right; break; default: style.Alignment = HorizontalAlignment.Left; break; } return(style); }
/// <summary> /// Generates a cell style for date formatting. /// </summary> /// <param name="book">excel sheet.</param> /// <param name="options">excel sheet options.</param> /// <returns>cell style.</returns> private ICellStyle createCellStyleForDateTime(IWorkbook book, ExcelSpreadSheetOptions options) { var style = book.CreateCellStyle(); style.DataFormat = book.CreateDataFormat().GetFormat(options.DataTimeCellFormat.Format); return(style); }
private static void SetCell(IWorkbook hssfworkbook, ICell cell, PropertyInfo propertyInfo, Model model) { ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); IDataFormat format = hssfworkbook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); var value = propertyInfo.GetValue(model); switch (value) { case string s: cell.SetCellValue($"{s.Replace("\"", "\"\"")}"); break; case DateTimeOffset dto: cell.SetCellValue(dto.UtcDateTime); cell.CellStyle = cellStyle; break; case DateTime dto: cell.SetCellValue(dto); break; case bool b: cell.SetCellValue(b); break; default: cell.SetCellValue(Convert.ToString(value)); break; } }
public void TestBuiltinFormats() { IWorkbook wb = _testDataProvider.CreateWorkbook(); IDataFormat df = wb.CreateDataFormat(); List <String> formats = HSSFDataFormat.GetBuiltinFormats(); for (int idx = 0; idx < formats.Count; idx++) { String fmt = formats[idx]; Assert.AreEqual(idx, df.GetFormat(fmt)); } //default format for new cells is General ISheet sheet = wb.CreateSheet(); ICell cell = sheet.CreateRow(0).CreateCell(0); Assert.AreEqual(0, cell.CellStyle.DataFormat); Assert.AreEqual("General", cell.CellStyle.GetDataFormatString()); //create a custom data format String customFmt = "#0.00 AM/PM"; //check it is not in built-in formats Assert.AreEqual(-1, HSSFDataFormat.GetBuiltinFormat(customFmt)); int customIdx = df.GetFormat(customFmt); //The first user-defined format starts at 164. Assert.IsTrue(customIdx >= HSSFDataFormat.FIRST_USER_DEFINED_FORMAT_INDEX); //read and verify the string representation Assert.AreEqual(customFmt, df.GetFormat((short)customIdx)); }
/// <summary> /// 获取时间格式 /// </summary> /// <param name="wb"></param> /// <returns></returns> private static ICellStyle DataTimeStyle(IWorkbook wb) { var cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = wb.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss"); return(cellStyle); }
private static ICellStyle CreateIntegerStyle(IWorkbook workbook) { var Result = workbook.CreateCellStyle(); Result.DataFormat = workbook.CreateDataFormat().GetFormat("###0"); return(Result); }
/// <summary> /// 插入数据行 /// </summary> protected static void InsertRow(DataTable dtSource, IWorkbook excelWorkbook) { int sheetCount = 1; ISheet newsheet = null; //循环数据源导出数据集 newsheet = excelWorkbook.GetSheetAt(excelWorkbook.NumberOfSheets - 1); int rowCount = newsheet.LastRowNum; CreateHeader(newsheet, dtSource.Columns); ICellStyle dateCellStyle = excelWorkbook.CreateCellStyle(); IDataFormat format = excelWorkbook.CreateDataFormat(); dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); foreach (DataRow dr in dtSource.Rows) { rowCount++; //超出65530条数据 创建新的工作簿 if (rowCount == 65530) { rowCount = 1; sheetCount++; newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount); CreateHeader(newsheet, dtSource.Columns); } IRow newRow = newsheet.CreateRow(rowCount); InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook, dateCellStyle); } }
private static void BuildXLSFile(Stream stream, IWorkbook xls, string sheet, IEnumerable <string> headers, IEnumerable <LogEntry> data) { var ss = xls.CreateSheet(sheet); var header = ss.CreateRow(0); var col = 0; foreach (var field in headers) { var cell = header.CreateCell(col++); cell.SetCellValue(field); } var dateformat = xls.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss"); var datestyle = xls.CreateCellStyle(); datestyle.DataFormat = dateformat; var row = 1; foreach (var record in data.OrderBy(log => log.Time)) { var line = ss.CreateRow(row); record.FillXlsRow(xls, ss, line, datestyle); row++; } xls.Write(stream); }
/// <summary> /// 设置值 /// </summary> /// <param name="cell"></param> /// <param name="value"></param> /// <param name="colEntity"></param> /// <param name="cellStyle"></param> public static void SetCellValue(this ICell cell, object value, ExportColumnEntity colEntity, ICellStyle cellStyle) { IWorkbook workBook = cell.Sheet.Workbook; if (value == null) { cell.SetCellValue(string.Empty); cell.CellStyle = cellStyle; return; } string strValue = value.ToString(); switch (colEntity.CellType) { case CellType.Blank: cell.SetCellValue(strValue); break; case CellType.Boolean: bool blValue = false; bool.TryParse(strValue, out blValue); cell.SetCellValue(blValue); break; case CellType.Error: cell.SetCellValue(strValue); break; case CellType.Formula: cell.SetCellFormula(strValue); break; case CellType.Numeric: double dbValue = 0; double.TryParse(strValue, out dbValue); cell.SetCellValue(dbValue); break; case CellType.String: cell.SetCellValue(strValue); break; case CellType.Unknown: cell.SetCellValue(strValue); break; default: cell.SetCellValue(strValue); break; } cellStyle.Alignment = colEntity.HAlign; cellStyle.VerticalAlignment = colEntity.VAlign; if (!string.IsNullOrEmpty(colEntity.DataFormat)) { IDataFormat df = workBook.CreateDataFormat(); cellStyle.DataFormat = df.GetFormat(colEntity.DataFormat); } cell.CellStyle = cellStyle; }
/// <summary> /// 创建单元格值 /// </summary> /// <param name="rowType">需要填入单元格字段的值的类型</param> /// <param name="newCell">单元格对象</param> /// <param name="drValue">单元格需要填入的值</param> /// <param name="workbook">工作簿对象</param> private static void CellCreate(Type rowType, ICell newCell, string drValue, IWorkbook workbook) { switch (rowType.ToString()) { case "System.String": //字符串类型 drValue = drValue.Replace("&", "&"); drValue = drValue.Replace(">", ">"); drValue = drValue.Replace("<", "<"); newCell.SetCellValue(drValue); break; case "System.Guid": //GUID类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); //格式化显示 ICellStyle cellStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss"); newCell.CellStyle = cellStyle; break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); //设置值 newCell.SetCellValue(intV.ToString()); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 case "System.Collections.Generic.List`1[Mysoft.Clgyl.Utility.Model.Excel.RowFile]": newCell.SetCellValue(""); break; default: newCell.SetCellValue(drValue); break; } }
private static ICellStyle GetDateFormat(this IWorkbook workbook) { IDataFormat format = workbook.CreateDataFormat(); ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); return(cellStyle); }
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"); }
/// <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); } }
//生成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; }
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)); }
/** * 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; }
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 { var col = Array.IndexOf(indexes, c.ColumnIndex); if (col >= 0) { var cell = ActiveRow.GetOrCreateCol(sheet.Elements.Columns[col].HeaderCol); if (r.RowStyle != null) { ActiveRow.RowStyle = dateStyle; ActiveRow.RowStyle.CloneStyleFrom(r.RowStyle); } cell.CellStyle = dateStyle; switch (c.CellType) { case CellType.Numeric: try { cell.SetCellValue(c.DateCellValue); } catch { try { cell.SetCellValue(c.NumericCellValue); } catch { } } break; case CellType.Boolean: cell.SetCellValue(c.BooleanCellValue); break; case CellType.String: default: cell.SetCellValue(c.StringCellValue); break; } cell.SetCellType(c.CellType); cell.CellStyle.CloneStyleFrom(c.CellStyle); if (sheet.Elements.Columns[col].Width == 0) { wbsheet.SetColumnWidth(sheet.Elements.Columns[col].HeaderCol, Math.Max(srcwbsheet.GetColumnWidth(c.ColumnIndex), wbsheet.GetColumnWidth(sheet.Elements.Columns[col].HeaderCol))); } } } } } } } Console.WriteLine("gedaan"); } using (FileStream stream = new FileStream(_settings.ApplicationSettings.FileLocations.Destination, FileMode.Create, FileAccess.Write)) { _workbook.Write(stream); } }
private ICellStyle GetCellStyleDateTime(IWorkbook hssfworkbook) { ICellStyle cellStyleDateTime = hssfworkbook.CreateCellStyle(); CellStyleBasic(cellStyleDateTime); cellStyleDateTime.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss"); return cellStyleDateTime; }
/** * 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; }
public static void DataFormat(this ICellStyle style, IWorkbook wb, string v) { var df = wb.CreateDataFormat(); style.DataFormat = df.GetFormat(v); ; }
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; }
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); cell = row.CreateCell(0); cell.CellStyle = fontBoldAllBorder; for (int i = 0; i < 5; i+=4) { cell = row.CreateCell(1 + i); cell.CellStyle = fontBoldAllBorder; cell.SetCellValue("Normal Time(Approved)"); cell = row.CreateCell(2 + i); cell.CellStyle = fontBoldAllBorder; cell.SetCellValue("Normal Time(For Approval)"); cell = row.CreateCell(3 + i); cell.CellStyle = fontBoldAllBorder; cell.SetCellValue("Overtime(Approved)"); cell = row.CreateCell(4 + i); cell.CellStyle = fontBoldAllBorder; cell.SetCellValue("Overtime(For Approved)"); } cell = row.CreateCell(9); cell.CellStyle = fontBoldAllBorder; cell.SetCellValue("Time"); cell = row.CreateCell(10); cell.CellStyle = fontBoldAllBorder; cell.SetCellValue("Cost"); //Merging sheetReport.AddMergedRegion(new CellRangeAddress(currentrow - 1, currentrow, 0, 0)); #endregion #region Data List<JobTracker> uniqueJobType = jobtracker.GetUniqueComputedJobType(project.HWNo == null ? "" : project.HWNo.Trim(), project.HWNo == null ? "" : project.SWNo.Trim()); #region DETAIL Page ISheet detailSheet = workbook.CreateSheet(jobheader.Replace(":","")); int detailRowCount = 0; IRow detailRow = detailSheet.CreateRow(detailRowCount++); detailRow.CreateCell(0).SetCellValue(jobheader); detailRow.GetCell(0).CellStyle = fontBoldNoBorder; detailRowCount++; detailRow = detailSheet.CreateRow(detailRowCount++); detailRow.CreateCell(0).SetCellValue("Labor Cost"); detailRow.GetCell(0).CellStyle = fontBoldNoBorder; detailRow = detailSheet.CreateRow(detailRowCount++); string[] detailHeader = { "Department","Name", "Job Task", "Start Time", "End Time","Date", "Remarks","Status", "Normal Time","Overtime", "Normal Time Cost","Overtime Cost" }; for (int i = 0; i < 12; i++) { ICell detailCell = detailRow.CreateCell(i); detailCell.CellStyle = fontBoldAllBorder; detailCell.SetCellValue(detailHeader[i]); } for (int i = 0; i < uniqueJobType.Count; i++) { row = sheetReport.CreateRow(++currentrow); cell = row.CreateCell(0); cell.SetCellValue(uniqueJobType[i].jobtype); if (i < uniqueJobType.Count - 1) { cell.CellStyle = fontNormalBorderLeftRight; } else { cell.CellStyle = fontNormalBorderLeftRightBottom; } List<JobTracker> data = jobtracker.GetJobTrackerListWithJobTypeIdHWSO(Convert.ToInt32(uniqueJobType[i].JobTypeId), project.HWNo == null ? "" : project.HWNo.Trim(), project.SWNo == null ? "" : project.SWNo.Trim(), true); double ntApproved = 0; double ntForApproval = 0; double otApproved = 0; double otForApproval = 0; double ntApprovedCost = 0; double ntForApprovalCost = 0; double otApprovedCost = 0; double otForApprovalCost = 0; for (int x = 0; x < data.Count;x++ ) { detailRow = detailSheet.CreateRow(detailRowCount++); if (data[x].Status == "Approved") { ntApproved += data[x].normalmins; otApproved += data[x].otmins; ntApprovedCost += data[x].normalcost; otApprovedCost += data[x].otcost; } else if (data[x].Status == "For Approval") { ntForApproval += data[x].normalmins; otForApproval += data[x].otmins; ntForApprovalCost += data[x].normalcost; otForApprovalCost += data[x].otcost; } for (int y = 0; y < 12; y++) { ICell detailCell = detailRow.CreateCell(y); if (y == 0) detailCell.SetCellValue(data[x].department); else if (y == 1) detailCell.SetCellValue(data[x].fullname); else if (y == 2) detailCell.SetCellValue(data[x].jobtype); else if (y == 3) detailCell.SetCellValue(Convert.ToDateTime(data[x].StartTime).ToString("hh:mm tt")); else if (y == 4) detailCell.SetCellValue(Convert.ToDateTime(data[x].EndTime).ToString("hh:mm tt")); else if (y == 5) detailCell.SetCellValue(Convert.ToDateTime(data[x].ScheduleDate).ToString("dd-MMM-yyyy")); else if (y == 6) detailCell.SetCellValue(data[x].Remarks); else if (y == 7) detailCell.SetCellValue(data[x].Status); else if (y == 8) detailCell.SetCellValue(data[x].normalhours.Trim() == "0 min" ? "-" : data[x].normalhours); else if (y == 9) detailCell.SetCellValue(data[x].othours.Trim() == "0 min" ? "-" : data[x].othours); else if (y == 10) detailCell.SetCellValue(data[x].normalcost); else if (y == 11) detailCell.SetCellValue(data[x].otcost); if (x == data.Count - 1 && i == uniqueJobType.Count -1) { if (y < 10) detailCell.CellStyle = fontNormalBorderLeftRightBottom; else detailCell.CellStyle = fontCurrencyBorderLeftRightBottom; } else { if (y < 10) detailCell.CellStyle = fontNormalBorderLeftRight; else detailCell.CellStyle = fontCurrencyBorderLeftRight; } } } #endregion #region GETTING THE TIME //Running Time runningNTApproved += ntApproved; runningNTForApproval += ntForApproval; runningOTApproved += otApproved; runningOTForApproval += otForApproval; runningNTApprovedCost += ntApprovedCost; runningNTForApprovalCost += ntForApprovalCost; runningOTApprovedCost += otApprovedCost; runningOTForApprovalCost += otForApprovalCost; string NTApproved = GenerateTimeConsumed(ntApproved); string NTForApproval = GenerateTimeConsumed(ntForApproval); string OTApproved = GenerateTimeConsumed(otApproved); string OTForApproval = GenerateTimeConsumed(otForApproval); string StotalTime = GenerateTimeConsumed(ntApproved + otApproved + ntForApproval + otForApproval); #endregion for (int j = 1; j < 11; j++) { cell = row.CreateCell(j); if (j == 1) cell.SetCellValue(NTApproved.Trim() == "0 min" ? "-" : NTApproved.Trim()); else if (j == 2) cell.SetCellValue(NTForApproval.Trim() == "0 min" ? "-" : NTForApproval.Trim()); else if (j == 3) cell.SetCellValue(OTApproved.Trim() == "0 min" ? "-" : OTApproved.Trim()); else if (j == 4) cell.SetCellValue(OTForApproval.Trim() == "0 min" ? "-" : OTForApproval.Trim()); else if (j == 5) cell.SetCellValue(ntApprovedCost); else if (j == 6) cell.SetCellValue(ntForApprovalCost); else if (j == 7) cell.SetCellValue(otApprovedCost); else if (j == 8) cell.SetCellValue(otForApprovalCost); else if (j == 9) cell.SetCellValue(StotalTime.Trim() == "0 min" ? "-" : StotalTime.Trim()); else if (j == 10) cell.SetCellValue(ntForApprovalCost + ntApprovedCost + otApprovedCost + otForApprovalCost); if (i < uniqueJobType.Count - 1) { if(j < 5 || j == 9) cell.CellStyle = fontNormalBorderLeftRight; else cell.CellStyle = fontCurrencyBorderLeftRight; } else { if (j < 5 || j == 9) cell.CellStyle = fontNormalBorderLeftRightBottom; else cell.CellStyle = fontCurrencyBorderLeftRightBottom; } } } #endregion #region TOTAL, MATERIAL COST, OUTSIDE SERVICE string projNTApproved = GenerateTimeConsumed(runningNTApproved); string projNTForApproval = GenerateTimeConsumed(runningNTForApproval); string projOTApproved = GenerateTimeConsumed(runningOTApproved); string projOTForApproval = GenerateTimeConsumed(runningOTForApproval); string projTotalTime = GenerateTimeConsumed(runningNTApproved + runningNTForApproval + runningOTApproved + runningOTForApproval); string projNTTotal = GenerateTimeConsumed(runningNTApproved + runningNTForApproval); string projOTTotal = GenerateTimeConsumed(runningOTApproved + runningOTForApproval); #region DETAIL TOTAL detailRow = detailSheet.CreateRow(detailRowCount++); for (int y = 0; y < 12; y++) { ICell detailCell = detailRow.CreateCell(y); if (y == 0) detailCell.SetCellValue("TOTAL"); else if (y == 8) detailCell.SetCellValue(projNTTotal.Trim() == "0 min" ? "-" : projNTTotal.Trim()); else if (y == 9) detailCell.SetCellValue(projOTTotal.Trim() == "0 min" ? "-" : projOTTotal.Trim()); else if (y == 10) detailCell.SetCellValue(runningNTApprovedCost + runningNTForApprovalCost); else if (y == 11) detailCell.SetCellValue(runningOTApprovedCost + runningOTForApprovalCost); if (y < 10) detailCell.CellStyle = fontBoldAllBorder; else detailCell.CellStyle = fontCurrencyBoldAllBorder; } #endregion #region MATERIAL COST MaterialCost materialCost = new MaterialCost(); List<MaterialCost> projMaterialCost = new List<MaterialCost>(); projMaterialCost = materialCost.GetMaterialCost(project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : project.HWNo, project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : project.SWNo, project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : project.EvalNo); double totalmaterialBookedCost = 0; double totalmaterialActualCost = 0; if (projMaterialCost.Count > 0) { detailRowCount++; //Blank Row #region HEADER detailRow = detailSheet.CreateRow(detailRowCount++); detailRow.CreateCell(0).SetCellValue("Material Cost"); detailRow.GetCell(0).CellStyle = fontBoldNoBorder; string[] materialHeader = { "Item", "Description", "Part Number", "Booked Quantity", "Booked Unit Cost", "Booked Extended Cost", "Actual Quantity", "Actual Unit Cost", "Actual Extended Cost","Cost Overrun (Underrun)" }; detailRow = detailSheet.CreateRow(detailRowCount++); for (int i = 0; i < 10; i++) { ICell detailCell = detailRow.CreateCell(i); detailCell.CellStyle = fontBoldAllBorder; detailCell.SetCellValue(materialHeader[i]); } #endregion #region DETAIL for (int i = 0; i < projMaterialCost.Count; i++) { detailRow = detailSheet.CreateRow(detailRowCount++); double bookcost = projMaterialCost[i].BookedQuantity * projMaterialCost[i].BookedUnitCost; double actualcost = projMaterialCost[i].ActualUnitCost * projMaterialCost[i].ActualQuantity; for (int y = 0; y < 10; y++) { ICell detailCell = detailRow.CreateCell(y); if (y == 0) detailCell.SetCellValue(i + 1); else if (y == 1) detailCell.SetCellValue(projMaterialCost[i].PartDescription); else if (y == 2) detailCell.SetCellValue(projMaterialCost[i].PartNo); else if (y == 3) detailCell.SetCellValue(projMaterialCost[i].BookedQuantity); else if (y == 4) detailCell.SetCellValue(projMaterialCost[i].BookedUnitCost); else if (y == 5) detailCell.SetCellValue(bookcost); else if (y == 6) detailCell.SetCellValue(projMaterialCost[i].ActualQuantity); else if (y == 7) detailCell.SetCellValue(projMaterialCost[i].ActualUnitCost); else if (y == 8) detailCell.SetCellValue(actualcost); else if (y == 9) detailCell.SetCellValue(actualcost - bookcost); if (i < projMaterialCost.Count - 1) { if (y < 4 || y == 6) detailCell.CellStyle = fontNormalBorderLeftRight; else detailCell.CellStyle = fontCurrencyBorderLeftRight; } else { if (y < 4 || y == 6) detailCell.CellStyle = fontNormalBorderLeftRightBottom; else detailCell.CellStyle = fontCurrencyBorderLeftRightBottom; } } totalmaterialBookedCost += bookcost; totalmaterialActualCost += actualcost; } #endregion #region TOTAL detailRow = detailSheet.CreateRow(detailRowCount++); for (int y = 0; y < 10; y++) { ICell detailCell = detailRow.CreateCell(y); if (y == 0) detailCell.SetCellValue("TOTAL"); else if (y == 5) detailCell.SetCellValue(totalmaterialBookedCost); else if (y == 8) detailCell.SetCellValue(totalmaterialActualCost); else if (y == 9) detailCell.SetCellValue(totalmaterialActualCost - totalmaterialBookedCost); if (y == 5 || y == 8 || y == 9) detailCell.CellStyle = fontCurrencyBoldAllBorder; else detailCell.CellStyle = fontBoldAllBorder; } #endregion } #endregion #region OUTSIDE SERVICE OutsideService outsideServiceCost = new OutsideService(); List<OutsideService> projOutServCost = new List<OutsideService>(); projOutServCost = outsideServiceCost.GetOutsideServiceCost(project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : project.HWNo, project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : project.SWNo, project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : project.EvalNo); double totalOutServBookedCost = 0; double totalOutServActualCost = 0; if (projOutServCost.Count > 0) { detailRowCount++; //Blank Row #region HEADER detailRow = detailSheet.CreateRow(detailRowCount++); detailRow.CreateCell(0).SetCellValue("Outside Service Cost"); detailRow.GetCell(0).CellStyle = fontBoldNoBorder; string[] outsideServiceHeader = { "Item","Type", "Description", "Vendor", "Booked Quantity", "Booked Unit Cost", "Booked Extended Cost", "Actual Quantity", "Actual Unit Cost", "Actual Extended Cost", "Cost Overrun (Underrun)" }; detailRow = detailSheet.CreateRow(detailRowCount++); for (int i = 0; i < 11; i++) { ICell detailCell = detailRow.CreateCell(i); detailCell.CellStyle = fontBoldAllBorder; detailCell.SetCellValue(outsideServiceHeader[i]); } #endregion #region DETAIL for (int i = 0; i < projOutServCost.Count; i++) { detailRow = detailSheet.CreateRow(detailRowCount++); double bookcost = projOutServCost[i].BookedQuantity * projOutServCost[i].BookedUnitCost; double actualcost = projOutServCost[i].ActualUnitCost * projOutServCost[i].ActualQuantity; for (int y = 0; y < 11; y++) { ICell detailCell = detailRow.CreateCell(y); if (y == 0) detailCell.SetCellValue(i + 1); else if (y == 1) detailCell.SetCellValue(projOutServCost[i].PartType); else if (y == 2) detailCell.SetCellValue(projOutServCost[i].PartDescription); else if (y == 3) detailCell.SetCellValue(projOutServCost[i].Vendor); else if (y == 4) detailCell.SetCellValue(projOutServCost[i].BookedQuantity); else if (y == 5) detailCell.SetCellValue(projOutServCost[i].BookedUnitCost); else if (y == 6) detailCell.SetCellValue(bookcost); else if (y == 7) detailCell.SetCellValue(projOutServCost[i].ActualQuantity); else if (y == 8) detailCell.SetCellValue(projOutServCost[i].ActualUnitCost); else if (y == 9) detailCell.SetCellValue(actualcost); else if (y == 10) detailCell.SetCellValue(actualcost - bookcost); if (i < projMaterialCost.Count - 1) { if (y < 5 || y == 7) detailCell.CellStyle = fontNormalBorderLeftRight; else detailCell.CellStyle = fontCurrencyBorderLeftRight; } else { if (y < 5 || y == 7) detailCell.CellStyle = fontNormalBorderLeftRightBottom; else detailCell.CellStyle = fontCurrencyBorderLeftRightBottom; } } totalOutServBookedCost += bookcost; totalOutServActualCost += actualcost; } #endregion #region TOTAL detailRow = detailSheet.CreateRow(detailRowCount++); for (int y = 0; y < 11; y++) { ICell detailCell = detailRow.CreateCell(y); if (y == 0) detailCell.SetCellValue("TOTAL"); else if (y == 6) detailCell.SetCellValue(totalOutServBookedCost); else if (y == 9) detailCell.SetCellValue(totalOutServActualCost); else if (y == 10) detailCell.SetCellValue(totalOutServActualCost - totalOutServBookedCost); if (y == 6 || y == 9 || y == 10) detailCell.CellStyle = fontCurrencyBoldAllBorder; else detailCell.CellStyle = fontBoldAllBorder; } #endregion } #endregion row = sheetReport.CreateRow(++currentrow); for (int j = 0; j < 11; j++) { cell = row.CreateCell(j); if (j == 0) cell.SetCellValue("Total Labor Cost"); else if (j == 1) cell.SetCellValue(projNTApproved.Trim() == "0 min" ? "-" : projNTApproved.Trim()); else if (j == 2) cell.SetCellValue(projNTForApproval.Trim() == "0 min" ? "-" : projNTForApproval.Trim()); else if (j == 3) cell.SetCellValue(projOTApproved.Trim() == "0 min" ? "-" : projOTApproved.Trim()); else if (j == 4) cell.SetCellValue(projOTForApproval.Trim() == "0 min" ? "-" : projOTForApproval.Trim()); else if (j == 5) cell.SetCellValue(runningNTApprovedCost); else if (j == 6) cell.SetCellValue(runningNTForApprovalCost); else if (j == 7) cell.SetCellValue(runningOTApprovedCost); else if (j == 8) cell.SetCellValue(runningOTForApprovalCost); else if (j == 9) cell.SetCellValue(projTotalTime.Trim() == "0 min" ? "-" : projTotalTime.Trim()); else if (j == 10) cell.SetCellValue(runningNTApprovedCost + runningNTForApprovalCost + runningOTApprovedCost + runningOTForApprovalCost); if (j < 5 || j == 9) cell.CellStyle = fontBoldAllBorder; else cell.CellStyle = fontCurrencyBoldAllBorder; } row = sheetReport.CreateRow(++currentrow); row.CreateCell(0).SetCellValue("Material Cost"); row.GetCell(0).CellStyle = fontBoldAllBorder; for (int j = 1; j < 11; j++) { cell = row.CreateCell(j); if (j == 1) cell.SetCellValue("Booked Cost:"); else if (j == 2) cell.SetCellValue(totalmaterialBookedCost); else if (j == 4) cell.SetCellValue("Actual Cost:"); else if (j == 5) cell.SetCellValue(totalmaterialActualCost); else if (j == 7) cell.SetCellValue("Cost Overrun (Underrun)"); else if (j == 8) cell.SetCellValue(totalmaterialActualCost - totalmaterialBookedCost); if (j == 1 || j == 4 || j == 7) cell.CellStyle = fontBoldTopBottom; else cell.CellStyle = fontCurrencyBoldRigthBottom; } sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 2, 3)); sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 5, 6)); sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 8, 10)); row = sheetReport.CreateRow(++currentrow); row.CreateCell(0).SetCellValue("Outside Service Cost"); row.GetCell(0).CellStyle = fontBoldAllBorder; for (int j = 1; j < 11; j++) { cell = row.CreateCell(j); if (j == 1) cell.SetCellValue("Booked Cost:"); else if (j == 2) cell.SetCellValue(totalOutServBookedCost); else if (j == 4) cell.SetCellValue("Actual Cost:"); else if (j == 5) cell.SetCellValue(totalOutServActualCost); else if (j == 7) cell.SetCellValue("Cost Overrun (Underrun)"); else if (j == 8) cell.SetCellValue(totalOutServActualCost - totalOutServBookedCost); if (j == 1 || j == 4 || j == 7) cell.CellStyle = fontBoldTopBottom; else cell.CellStyle = fontCurrencyBoldRigthBottom; } sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 2, 3)); sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 5, 6)); sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 8, 10)); #endregion } }
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); }
/** * 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 Dictionary<String, ICellStyle> createStyles(IWorkbook wb) { Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>(); IDataFormat df = wb.CreateDataFormat(); ICellStyle style; IFont headerFont = wb.CreateFont(); headerFont.Boldweight = (short)(FontBoldWeight.Bold); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index); style.FillPattern = FillPattern.SolidForeground; style.SetFont(headerFont); styles.Add("header", style); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index); style.FillPattern = FillPattern.SolidForeground; style.SetFont(headerFont); style.DataFormat = (df.GetFormat("d-mmm")); styles.Add("header_date", style); IFont font1 = wb.CreateFont(); font1.Boldweight = (short)(FontBoldWeight.Bold); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.SetFont(font1); styles.Add("cell_b", style); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.SetFont(font1); styles.Add("cell_b_centered", style); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.SetFont(font1); style.DataFormat = (df.GetFormat("d-mmm")); styles.Add("cell_b_date", style); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.SetFont(font1); style.FillForegroundColor = (IndexedColors.Grey25Percent.Index); style.FillPattern = FillPattern.SolidForeground; style.DataFormat = (df.GetFormat("d-mmm")); styles.Add("cell_g", style); IFont font2 = wb.CreateFont(); font2.Color = (IndexedColors.Blue.Index); font2.Boldweight = (short)(FontBoldWeight.Bold); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.SetFont(font2); styles.Add("cell_bb", style); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.SetFont(font1); style.FillForegroundColor = (IndexedColors.Grey25Percent.Index); style.FillPattern = FillPattern.SolidForeground; style.DataFormat = (df.GetFormat("d-mmm")); styles.Add("cell_bg", style); IFont font3 = wb.CreateFont(); font3.FontHeightInPoints = ((short)14); font3.Color = (IndexedColors.DarkBlue.Index); font3.Boldweight = (short)(FontBoldWeight.Bold); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.SetFont(font3); style.WrapText = (true); styles.Add("cell_h", style); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.WrapText = (true); styles.Add("cell_normal", style); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.WrapText = (true); styles.Add("cell_normal_centered", style); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.WrapText = (true); style.DataFormat = (df.GetFormat("d-mmm")); styles.Add("cell_normal_date", style); style = CreateBorderedStyle(wb); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.Indention = ((short)1); style.WrapText = (true); styles.Add("cell_indented", style); style = CreateBorderedStyle(wb); style.FillForegroundColor = (IndexedColors.Blue.Index); style.FillPattern = FillPattern.SolidForeground; styles.Add("cell_blue", style); return styles; }
/** * create a library of cell styles */ private static Dictionary<String, ICellStyle> createStyles(IWorkbook wb) { Dictionary<String, ICellStyle> styles = new Dictionary<String, ICellStyle>(); IDataFormat df = wb.CreateDataFormat(); ICellStyle style; IFont headerFont = wb.CreateFont(); headerFont.Boldweight = (short)(FontBoldWeight.BOLD); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.FillForegroundColor = (IndexedColors.LIGHT_CORNFLOWER_BLUE.Index); style.FillPattern = FillPatternType.SOLID_FOREGROUND; style.SetFont(headerFont); styles.Add("header", style); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.FillForegroundColor = (IndexedColors.LIGHT_CORNFLOWER_BLUE.Index); style.FillPattern = FillPatternType.SOLID_FOREGROUND; style.SetFont(headerFont); style.DataFormat = (df.GetFormat("d-mmm")); styles.Add("header_date", style); IFont font1 = wb.CreateFont(); font1.Boldweight = (short)(FontBoldWeight.BOLD); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.SetFont(font1); styles.Add("cell_b", style); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.SetFont(font1); styles.Add("cell_b_centered", style); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.SetFont(font1); style.DataFormat = (df.GetFormat("d-mmm")); styles.Add("cell_b_date", style); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.SetFont(font1); style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index); style.FillPattern = FillPatternType.SOLID_FOREGROUND; style.DataFormat = (df.GetFormat("d-mmm")); styles.Add("cell_g", style); IFont font2 = wb.CreateFont(); font2.Color = (IndexedColors.BLUE.Index); font2.Boldweight = (short)(FontBoldWeight.BOLD); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.SetFont(font2); styles.Add("cell_bb", style); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.SetFont(font1); style.FillForegroundColor = (IndexedColors.GREY_25_PERCENT.Index); style.FillPattern = FillPatternType.SOLID_FOREGROUND; style.DataFormat = (df.GetFormat("d-mmm")); styles.Add("cell_bg", style); IFont font3 = wb.CreateFont(); font3.FontHeightInPoints = ((short)14); font3.Color = (IndexedColors.DARK_BLUE.Index); font3.Boldweight = (short)(FontBoldWeight.BOLD); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.SetFont(font3); style.WrapText = (true); styles.Add("cell_h", style); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.WrapText = (true); styles.Add("cell_normal", style); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.WrapText = (true); styles.Add("cell_normal_centered", style); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.WrapText = (true); style.DataFormat = (df.GetFormat("d-mmm")); styles.Add("cell_normal_date", style); style = CreateBorderedStyle(wb); style.Alignment = HorizontalAlignment.CENTER; style.Indention = ((short)1); style.WrapText = (true); styles.Add("cell_indented", style); style = CreateBorderedStyle(wb); style.FillForegroundColor = (IndexedColors.BLUE.Index); style.FillPattern = FillPatternType.SOLID_FOREGROUND; styles.Add("cell_blue", style); return styles; }
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; }