/// <summary> /// 根据ExcelResultType读取单元格的值 /// </summary> /// <param name="cell"></param> /// <param name="columnType"></param> /// <returns></returns> private static object GetCellValue(ICell cell, ExcelResultTypes columnType) { if (cell == null || cell.CellType == CellType.Blank) { return(null); } switch (columnType) { case ExcelResultTypes.String: return(GetCellValue(cell, CellType.String)); case ExcelResultTypes.Boolean: return(GetCellValue(cell, CellType.Boolean)); case ExcelResultTypes.Number: case ExcelResultTypes.Percent: return(GetCellValue(cell, CellType.Numeric)); case ExcelResultTypes.DateTime: return(GetCellValue(cell, CellType.String)); case ExcelResultTypes.Picture: return(null); } return(GetCellValue(cell)); }
/// <summary> /// 基本格式化列 /// </summary> /// <param name="field"></param> /// <param name="format"></param> /// <param name="caption"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, Func<object, object> format, string caption, ExcelResultTypes type = ExcelResultTypes.String) : this(field, caption, type) { if (format == null) throw new ArgumentNullException("format"); this.Format = format; this.FormatType = ColumnFormatTypes.Normal; }
/// <summary> /// 根据列数据类型设置单元格值 /// </summary> /// <param name="cell"></param> /// <param name="value"></param> /// <param name="resultType"></param> /// <param name="advance">是否生成高级版本(07及以上)Excel, 默认false,生成2003版</param> private static void SetCellValue(ICell cell, object value, ExcelResultTypes resultType, bool advance) { var wb = cell.Sheet.Workbook; switch (resultType) { case ExcelResultTypes.Boolean: cell.SetCellValue(Convert.ToBoolean(value)); return; case ExcelResultTypes.Number: cell.SetCellValue(Convert.ToDouble(value)); return; case ExcelResultTypes.DateTime: if (value == null) { cell.SetCellValue(""); return; } var date = Convert.ToDateTime(value); if (!advance) { cell.SetCellValue(date.ToString("yyyy-MM-dd")); return; } cell.SetCellValue(date); cell.CellStyle = GetCellStyle(resultType, wb); return; case ExcelResultTypes.Percent: if (value == null) { cell.SetCellValue("N/A"); return; } var num = Convert.ToDouble(value); if (!advance) { cell.SetCellValue(num.ToString("P2")); return; } cell.SetCellValue(Convert.ToDouble(value)); cell.CellStyle = GetCellStyle(resultType, wb); return; case ExcelResultTypes.Picture: throw new NotSupportedException("暂不支持图片导出。"); case ExcelResultTypes.String: default: cell.SetCellValue(Convert.ToString(value)); return; } }
/// <summary> /// 基本格式化列 /// 导出时使用给定格式化方法生成值写入Excel指定列 /// </summary> /// <param name="field"></param> /// <param name="format"></param> /// <param name="type"></param> /// <param name="caption"></param> internal ExtrinsicColumn(string field, Func <object> format, string caption, ExcelResultTypes type) : this(field, caption) { if (format == null) { throw new ArgumentNullException("format"); } this.Format = format; this.ResultType = type; this.FormatType = ColumnFormatTypes.Normal; }
/// <summary> /// 关联(多个)格式化 /// 导出 /// </summary> /// <param name="field"></param> /// <param name="relateFields"></param> /// <param name="format"></param> /// <param name="type"></param> /// <param name="caption"></param> internal ExtrinsicColumn(string field, string[] relateFields, Func <object[], object> format, string caption, ExcelResultTypes type) : this(field, relateFields, format) { if (string.IsNullOrEmpty(caption)) { throw new ArgumentNullException("caption"); } this.Caption = caption; this.ResultType = type; }
/// <summary> /// 基本列 /// </summary> /// <param name="field"></param> /// <param name="caption"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, string caption, ExcelResultTypes type = ExcelResultTypes.String) { if (field == null || string.IsNullOrEmpty(field)) throw new ArgumentNullException("field"); this.Field = field; if (string.IsNullOrEmpty(caption)) throw new ArgumentNullException("caption"); this.Caption = caption; this.ResultType = type; this.FormatType = ColumnFormatTypes.None; this.RelateField = string.Empty; this.PreRead = false; this.Required = false; }
/// <summary> /// 关联(多个)格式化列 /// </summary> /// <param name="field"></param> /// <param name="relateFields"></param> /// <param name="format"></param> /// <param name="caption"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, string[] relateFields, Func <object, object[], object> format, string caption, ExcelResultTypes type = ExcelResultTypes.String) : this(field, caption, type) { if (relateFields == null || relateFields.Length == 0) { throw new ArgumentNullException("relateFields"); } if (format == null) { throw new ArgumentNullException("format"); } this.RelateFields = relateFields; this.RelatesFormat = format; this.FormatType = ColumnFormatTypes.MultiRelate; }
/// <summary> /// 关联格式化列 /// </summary> /// <param name="field"></param> /// <param name="relateField"></param> /// <param name="format"></param> /// <param name="caption"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, string relateField, Func <object, object, object> format, string caption, ExcelResultTypes type = ExcelResultTypes.String) : this(field, caption, type) { if (string.IsNullOrEmpty(relateField)) { throw new ArgumentNullException("relateField"); } if (format == null) { throw new ArgumentNullException("format"); } this.RelateField = relateField; this.RelateFormat = format; this.FormatType = ColumnFormatTypes.Relate; }
/// <summary> /// 基本列 /// </summary> /// <param name="field"></param> /// <param name="caption"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, string caption, ExcelResultTypes type = ExcelResultTypes.String) { if (field == null || string.IsNullOrEmpty(field)) { throw new ArgumentNullException("field"); } this.Field = field; if (string.IsNullOrEmpty(caption)) { throw new ArgumentNullException("caption"); } this.Caption = caption; this.ResultType = type; this.FormatType = ColumnFormatTypes.None; this.RelateField = string.Empty; this.PreRead = false; this.Required = false; }
private static ICellStyle GetCellStyle(ExcelResultTypes type, IWorkbook wb) { if (CellStyles.ContainsKey(type)) { return(CellStyles[type]); } var style = wb.CreateCellStyle(); switch (type) { case ExcelResultTypes.DateTime: style.DataFormat = wb.CreateDataFormat().GetFormat("yyyy-MM-dd"); break; case ExcelResultTypes.Percent: style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%"); break; } return(CellStyles[type] = style); }
/// <summary> /// 关联(多个)非空预读取格式化列 /// </summary> /// <param name="field"></param> /// <param name="relateFields"></param> /// <param name="format"></param> /// <param name="required"></param> /// <param name="caption"></param> /// <param name="preRead"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, string[] relateFields, Func <object, object[], object> format, bool required, string caption, bool preRead, ExcelResultTypes type = ExcelResultTypes.String) : this(field, relateFields, format, required, caption, type) { this.PreRead = preRead; }
/// <summary> /// 构建固有列(导入时使用) /// </summary> /// <param name="field">字段名</param> /// <param name="relateField">相关字段名</param> /// <param name="format">自定义格式化方法</param> /// <param name="required">是否必需项</param> /// <param name="caption">列标题</param> /// <param name="preRead">是否预读取</param> /// <param name="type">Excel列类型</param> /// <returns></returns> public static IntrinsicColumn CreatIntrinsicColumn(string field, string relateField, Func<object, object, object> format, bool required, string caption, bool preRead, ExcelResultTypes type = ExcelResultTypes.String) { return new IntrinsicColumn(field, relateField, format, required, caption, preRead, type); }
/// <summary> /// 指定类型恒值列 /// 导出时使用,将恒定值写入指定类型列 /// </summary> /// <param name="field"></param> /// <param name="value"></param> /// <param name="type"></param> /// <param name="caption"></param> internal ExtrinsicColumn(string field, object value, string caption, ExcelResultTypes type) : this(field, caption) { this.Value = value; this.ResultType = type; }
/// <summary> /// 构建固有列(导入时使用) /// </summary> /// <param name="field">字段名</param> /// <param name="relateField">相关字段名</param> /// <param name="format">自定义格式化方法</param> /// <param name="required">是否必需项</param> /// <param name="caption">列标题</param> /// <param name="preRead">是否预读取</param> /// <param name="type">Excel列类型</param> /// <returns></returns> public static IntrinsicColumn CreatIntrinsicColumn(string field, string relateField, Func <object, object, object> format, bool required, string caption, bool preRead, ExcelResultTypes type = ExcelResultTypes.String) { return(new IntrinsicColumn(field, relateField, format, required, caption, preRead, type)); }
/// <summary> /// 构建固有列 /// </summary> /// <param name="field">字段名</param> /// <param name="relateFields">相关字段名数组</param> /// <param name="format">自定义格式化方法</param> /// <param name="caption">列标题</param> /// <param name="type">Excel列类型</param> /// <returns></returns> public static IntrinsicColumn CreatIntrinsicColumn(string field, string[] relateFields, Func <object, object[], object> format, string caption, ExcelResultTypes type = ExcelResultTypes.String) { return(new IntrinsicColumn(field, relateFields, format, caption, type)); }
/// <summary> /// 构建固有列 /// </summary> /// <param name="field">字段名</param> /// <param name="caption">列标题</param> /// <param name="type">Excel列类型</param> /// <returns></returns> public static IntrinsicColumn CreatIntrinsicColumn(string field, string caption, ExcelResultTypes type = ExcelResultTypes.String) { return(new IntrinsicColumn(field, caption, type)); }
/// <summary> /// 关联格式化列 /// </summary> /// <param name="field"></param> /// <param name="relateField"></param> /// <param name="format"></param> /// <param name="caption"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, string relateField, Func<object, object, object> format, string caption, ExcelResultTypes type = ExcelResultTypes.String) : this(field, caption, type) { if (string.IsNullOrEmpty(relateField)) throw new ArgumentNullException("relateField"); if (format == null) throw new ArgumentNullException("format"); this.RelateField = relateField; this.RelateFormat = format; this.FormatType = ColumnFormatTypes.Relate; }
/// <summary> /// 构建额外列(导出时使用) /// </summary> /// <param name="field">字段名</param> /// <param name="value">写入值</param> /// <param name="caption">标题</param> /// <param name="type">Excel列类型</param> /// <returns></returns> public static ExtrinsicColumn CreatExtrinsicColumn(string field, object value, string caption, ExcelResultTypes type) { return new ExtrinsicColumn(field, value, caption, type); }
/// <summary> /// 构建固有列 /// </summary> /// <param name="field">字段名</param> /// <param name="caption">列标题</param> /// <param name="type">Excel列类型</param> /// <returns></returns> public static IntrinsicColumn CreatIntrinsicColumn(string field, string caption, ExcelResultTypes type = ExcelResultTypes.String) { return new IntrinsicColumn(field, caption, type); }
/// <summary> /// 构建额外列(导出时使用) /// </summary> /// <param name="field">字段名</param> /// <param name="relateField">相关字段名</param> /// <param name="format">自定义格式化方法</param> /// <param name="caption">标题</param> /// <param name="type">Excel列类型</param> /// <returns></returns> public static ExtrinsicColumn CreatExtrinsicColumn(string field, string relateField, Func <object, object> format, string caption, ExcelResultTypes type) { return(new ExtrinsicColumn(field, relateField, format, caption, type)); }
/// <summary> /// 构建额外列(导出时使用) /// </summary> /// <param name="field">字段名</param> /// <param name="value">写入值</param> /// <param name="caption">标题</param> /// <param name="type">Excel列类型</param> /// <returns></returns> public static ExtrinsicColumn CreatExtrinsicColumn(string field, object value, string caption, ExcelResultTypes type) { return(new ExtrinsicColumn(field, value, caption, type)); }
/// <summary> /// 构建固有列 /// </summary> /// <param name="field">字段名</param> /// <param name="relateFields">相关字段名数组</param> /// <param name="format">自定义格式化方法</param> /// <param name="caption">列标题</param> /// <param name="type">Excel列类型</param> /// <returns></returns> public static IntrinsicColumn CreatIntrinsicColumn(string field, string[] relateFields, Func<object, object[], object> format, string caption, ExcelResultTypes type = ExcelResultTypes.String) { return new IntrinsicColumn(field, relateFields, format, caption, type); }
/// <summary> /// 非空列 /// </summary> /// <param name="field"></param> /// <param name="required"></param> /// <param name="caption"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, bool required, string caption, ExcelResultTypes type = ExcelResultTypes.String) : this(field, caption, type) { this.Required = required; }
/// <summary> /// 构建额外列(导出时使用) /// </summary> /// <param name="field">字段名</param> /// <param name="relateField">相关字段名</param> /// <param name="format">自定义格式化方法</param> /// <param name="caption">标题</param> /// <param name="type">Excel列类型</param> /// <returns></returns> public static ExtrinsicColumn CreatExtrinsicColumn(string field, string relateField, Func<object, object> format, string caption, ExcelResultTypes type) { return new ExtrinsicColumn(field, relateField, format, caption, type); }
/// <summary> /// 非空格式化列 /// </summary> /// <param name="field"></param> /// <param name="format"></param> /// <param name="required"></param> /// <param name="caption"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, Func<object, object> format, bool required, string caption, ExcelResultTypes type = ExcelResultTypes.String) : this(field, format, caption, type) { this.Required = required; }
/// <summary> /// 关联(多个)格式化列 /// </summary> /// <param name="field"></param> /// <param name="relateFields"></param> /// <param name="format"></param> /// <param name="caption"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, string[] relateFields, Func<object, object[], object> format, string caption, ExcelResultTypes type = ExcelResultTypes.String) : this(field, caption, type) { if (relateFields == null || relateFields.Length == 0) throw new ArgumentNullException("relateFields"); if (format == null) throw new ArgumentNullException("format"); this.RelateFields = relateFields; this.RelatesFormat = format; this.FormatType = ColumnFormatTypes.MultiRelate; }
/// <summary> /// 预读取格式化列 /// </summary> /// <param name="field"></param> /// <param name="format"></param> /// <param name="caption"></param> /// <param name="preRead"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, Func <object, object> format, string caption, bool preRead, ExcelResultTypes type = ExcelResultTypes.String) : this(field, format, caption, type) { this.PreRead = preRead; }
/// <summary> /// 关联(多个)非空预读取格式化列 /// </summary> /// <param name="field"></param> /// <param name="relateFields"></param> /// <param name="format"></param> /// <param name="required"></param> /// <param name="caption"></param> /// <param name="preRead"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, string[] relateFields, Func<object, object[], object> format, bool required, string caption, bool preRead, ExcelResultTypes type = ExcelResultTypes.String) : this(field, relateFields, format, required, caption, type) { this.PreRead = preRead; }
/// <summary> /// 非空预读取关联格式化列 /// </summary> /// <param name="field"></param> /// <param name="relateField"></param> /// <param name="format"></param> /// <param name="required"></param> /// <param name="caption"></param> /// <param name="preRead"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, string relateField, Func <object, object, object> format, bool required, string caption, bool preRead, ExcelResultTypes type = ExcelResultTypes.String) : this(field, relateField, format, caption, preRead, type) { this.Required = required; }
/// <summary> /// 关联(多个)格式化 /// 导出 /// </summary> /// <param name="field"></param> /// <param name="relateFields"></param> /// <param name="format"></param> /// <param name="type"></param> /// <param name="caption"></param> internal ExtrinsicColumn(string field, string[] relateFields, Func<object[], object> format, string caption, ExcelResultTypes type) : this(field, relateFields, format) { if (string.IsNullOrEmpty(caption)) throw new ArgumentNullException("caption"); this.Caption = caption; this.ResultType = type; }
/// <summary> /// 预读取格式化列 /// </summary> /// <param name="field"></param> /// <param name="format"></param> /// <param name="caption"></param> /// <param name="preRead"></param> /// <param name="type"></param> internal IntrinsicColumn(string field, Func<object, object> format, string caption, bool preRead, ExcelResultTypes type = ExcelResultTypes.String) : this(field, format, caption, type) { this.PreRead = preRead; }