/// <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="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="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="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;
}
