.NET项目中灵活数据导出与导入方法
在项目开发过程中，经常会接到有关数据的导入与导出的需求，在不同的项目或不同的模块中，处理数据的导出与导入相对比较麻烦，每次都要写一些大相径庭的代码。那么有没有一种简单而又通用的方法来处理模块数据的导入与导出呢？本文将向你揭露一个相对简单灵活通用的方法。
在以前我们写Excel的导出，通常会将一个数据源如List集合，Arrary集合，DataTable集合转换解析到Excel中，然后重定向Url地址或导向Excel让客户下载打开。在解析的过程中通常需要很明确的指定Excel的Sheet名，列头名称，列数据。正是由于这种需要很明确的指定，往往就会导致我们的方法死板，不易于扩展和通用。如果我们在解析的过程中能动态的得到我们需要的数据，那么用这种方法导出数据就相对灵活与通用了。
如下代码：
/// <summary>
/// 新增Class导入导出特性。
/// </summary>
[Serializable]
[AttributeUsage(AttributeTargets.Class,
AllowMultiple = false,
Inherited = false)]
public class ImportExportAttribute : Attribute
{
#region 属性
/// <summary>
/// 获取或者设置表名称。
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 获取或者设置Sheet名称。
/// </summary>
public string SheetName { get; set; }
#endregion
#region 构造方法
/// <summary>
/// 构造方法。
/// </summary>
/// <param name="tableName">数据库表名</param>
public ImportExportAttribute(string tableName)
{
this.TableName = tableName;
this.SheetName = tableName;
}
#endregion
}
/// <summary>
/// 新增class属性的导出特性。
/// </summary>
[AttributeUsage(AttributeTargets.Property,
AllowMultiple = false,
Inherited = true)]
public class ExportAttribute : Attribute
{
#region 属性
/// <summary>
/// 获取或者设置属性对应的数据库表字段名。
/// </summary>
public string ColumnName { get; set; }
/// <summary>
/// 获取或者设置导入导出时数据库表字段的显示名称。
/// </summary>
public string DisplayName { get; set; }
/// <summary>
/// 获取或者设置显示顺序。
/// </summary>
public int Order { get; set; }
/// <summary>
/// 获取或者设置数据输出格式。
/// </summary>
public string FormatString { get; set; }
/// <summary>
/// 获取或者设置导出时是否可见。
/// </summary>
public bool IsExportVisible { get; set; }
/// <summary>
/// 获取或者设置是否导出时忽略。
/// </summary>
public bool IsExportIgnore { get; set; }
#endregion
#region 构造方法
/// <summary>
/// 默认构造方法。
/// </summary>
public ExportAttribute()
{
this.Order = 1000;
this.IsExportVisible = true;
}
#endregion
}
特性定义完成，我们就可以在实体Class上定义一些我们需要的属性，如下代码：
/// <summary>
/// 教学计划
/// </summary>
[ImportExportAttribute("arrange_planaaa", SheetName = "教学任务计划")]
public class EducationPlan
{
/// <summary>
/// 主键
/// </summary>
[ExportAttribute(DisplayName = "主键", ColumnName = "Plan_No",Order = 0)]
public string ID { get; set; }
/// <summary>
/// 专业ID
/// </summary>
[ExportAttribute(IsExportIgnore = true)]
public string ProfessionID { get; set; }
/// <summary>
/// 年
/// </summary>
[ExportAttribute(IsExportIgnore = true)]
public int Year { get; set; }
/// <summary>
/// 课程体系ID
/// </summary>
[Display(Name = "课程体系ID")]
[ExportAttribute(IsExportIgnore = true)]
public string CurriculumSystemID { get; set; }
/// <summary>
/// 课程ID
/// </summary>
[Display(Name = "课程编号")]
[ExportAttribute(DisplayName = "课程编号", Order = 0, ColumnName = "CurID")]
public string CurriculumID { get; set; }
/// <summary>
/// 课程属性ID
/// </summary>
[Display(Name = "课程性质ID")]
[ExportAttribute(IsExportIgnore = true)]
public string CurriculumPropertyID { get; set; }
/// <summary>
/// 课程类别ID
/// </summary>
[Display(Name = "课程类型ID")]
[ExportAttribute(IsExportIgnore = true)]
public string CurriculumCategoryID { get; set; }
/// <summary>
/// 学分
/// </summary>
[Range(0, 29, ErrorMessage = "学分为数字0.0-29.0，如10、10.0")]
[Display(Name = "学分")]
[ExportAttribute(DisplayName = "学分", Order = 5, ColumnName = "CurCreditHour")]
public double? XueFen { get; set; }
}
定义完实体后，接下来需要些解析这些实体特性的辅佐类来帮助我们动态的解析：
/// <summary>
/// 获取需要导出的数据。
/// </summary>
/// <typeparam name="T">数据类型</typeparam>
/// <param name="sources">需要导出的数据</param>
/// <param name="options">导出数据选项</param>
/// <returns>数据表对象</returns>
public static DataTable GetExportData<T>(IList<T> sources, IList<ExportOption> options = null)
{
DataTable result = null;
if (sources != null && sources.Count > 0)
{
options = options ?? GetExportOptions(typeof(T));
var classProperty = GetImportExportAttribute(typeof(T));
result = NewDataTable(options);
result.TableName = classProperty.SheetName;
foreach (var source in sources)
{
var dataRow = result.NewRow();
foreach (var option in options)
{
if (!option.IsExportIgnore)
{
dataRow[option.DisplayName] = GetValue(source, option);
}
}
result.Rows.Add(dataRow);
}
}
return result;
}
/// <summary>
/// 获取数据类型的默认导入导出选项。
/// </summary>
/// <typeparam name="T">数据类型</typeparam>
/// <returns>导入导出选项集合</returns>
public static IList<ExportOption> GetExportOptions(Type type)
{
var result = new List<ExportOption>();
var properties = type.GetProperties();
if (properties != null && properties.Length > 0)
{
foreach (var property in properties)
{
if (!(property.PropertyType.IsValueType || property.PropertyType == typeof(string)))
{
continue;
}
var order = int.MinValue;
var isExportIgnore = false;
var columnName = property.Name;
var displayName = property.Name;
var formatString = string.Empty;
var attrs = property.GetCustomAttributes(typeof(ExportAttribute), true);
if (attrs != null && attrs.Length > 0)
{
var attr = attrs[0] as ExportAttribute;
if (!attr.IsExportVisible)
{
continue;
}
order = attr.Order;
formatString = attr.FormatString;
isExportIgnore = attr.IsExportIgnore;
if (!string.IsNullOrWhiteSpace(attr.ColumnName))
{
columnName = attr.ColumnName;
}
if (!string.IsNullOrWhiteSpace(attr.DisplayName))
{
displayName = attr.DisplayName;
}
}
result.Add(new ExportOption()
{
Order = order,
ColumnName = columnName,
DisplayName = displayName,
FormatString = formatString,
PropertyName = property.Name,
IsExportIgnore = isExportIgnore
});
}
}
return result.OrderBy(e => e.Order).ToList();
}
/// <summary>
/// 添加导入导出获取class的特性值
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static ImportExportAttribute GetImportExportAttribute(Type type)
{
return type.GetCustomAttributes(typeof(ImportExportAttribute), false).FirstOrDefault() as ImportExportAttribute;
}
通过上面代码中GetExportData将list类型的对象转换为DataTable类型的对象，其中将List集合中对象的属性特性分别转换为DataTable对象的列标题，将List集合中对象的Class特性转换为DataTable的TableName。通过这样的匿名类型的转换就能解决以前的导出方法中需要明确的数据了。
在这里转换成Excel过程中需要引用NPOI第三方动态库。转换完毕后我们就可以将DataTable导出生成Excel文件流了，如下：
/// <summary>
/// 将数据写入Excel文件，并输出到IO流中。
/// </summary>
/// <param name="dataTable">数据表对象</param>
/// <param name="stream">IO流</param>
public void Export(DataTable dataTable, Stream stream)
{
if (dataTable != null)
{
var rowIndex = 0;
var cellIndex = 0;
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet(dataTable.TableName);
var headerRow = sheet.CreateRow(rowIndex);
var headerFont = workbook.CreateFont();
var headerCellStyle = workbook.CreateCellStyle();
// 标题字体。
headerFont.FontName = "Arial";
headerFont.FontHeightInPoints = (short)13;
headerFont.Boldweight = (short)700;
headerFont.Color = HSSFColor.WHITE.index;
// 标题样式。
headerCellStyle.WrapText = false;
headerCellStyle.SetFont(headerFont);
// 设置标题单元格样式：纯色填充时FillForegroundColor值必须
// 和FillBackgroundColor值一致，且必须FillPattern设置为非NO_FILL。
headerCellStyle.FillPattern = FillPatternType.LESS_DOTS;
headerCellStyle.FillForegroundColor = HSSFColor.SKY_BLUE.index;
headerCellStyle.FillBackgroundColor = HSSFColor.SKY_BLUE.index;
// 设置单元格的水平对齐、垂直对齐方式。
headerCellStyle.Alignment = HorizontalAlignment.CENTER;
headerCellStyle.VerticalAlignment = VerticalAlignment.CENTER;
// 添加标题。
foreach (DataColumn column in dataTable.Columns)
{
if (column.ColumnName.ToUpper().Contains("_FK"))
{
continue;
}
var cell = headerRow.CreateCell(cellIndex, CellType.STRING);
cellIndex++;
cell.SetCellValue(column.ColumnName);
sheet.AutoSizeColumn(cellIndex);
cell.CellStyle = headerCellStyle;
}
headerRow.Height = (short)500;
rowIndex++;
// 添加数据。
foreach (DataRow dataRow in dataTable.Rows)
{
cellIndex = 0;
var row = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dataTable.Columns)
{
if (column.ColumnName.ToUpper().Contains("_FK"))
{
continue;
}
var cell = row.CreateCell(cellIndex, CellType.STRING);
cellIndex++;
cell.SetCellValue(Convert.ToString(dataRow[column]));
}
rowIndex++;
if (rowIndex > 15000)
{
break;
}
}
// 设置列的自动宽度。
for (var index = 0; index < dataTable.Columns.Count; index++)
{
sheet.AutoSizeColumn(index);
}
// 冻结首行。
sheet.CreateFreezePane(0, 1);
workbook.Write(stream);
}
}
我的前台代码是MVC，将流输出到用户端转换为xls文件即可。如下：
var stream = new MemoryStream();
DataExport.ExcleExport(list, stream);//list是数据源
stream.Seek(0, SeekOrigin.Begin);
return File(stream, "application/vnd.ms-excel", Encoding.GetEncoding("gb2312").GetString(Encoding.Default.GetBytes(title + ".xls")));
同理导入也很简单，跟导出反过来思考。将导入的文件流转换为我们需要的DataTable，然后通过SqlBulkCopy进行导入数据库，如下代码：
/// 将Excel文件数据解析成DataSet对象。
/// </summary>
/// <param name="stream">Excel文件流</param>
/// <param name="options">导入的列于数据库的列明对应项</param>
/// <returns>DataSet对象</returns>
public static DataSet Parser<T>(Stream stream)
{
var result = new DataSet();
var workbook = WorkbookFactory.Create(stream);
var properties = DataResolver.GetExportOptions(typeof(T)).Where(p => p.IsExportIgnore == false).ToList();
var classProperty = DataResolver.GetImportExportAttribute(typeof(T));
// 获取有效Sheet数目。
var sheetCount = workbook.NumberOfSheets;
for (int i = 0; i < sheetCount; i++)
{
var rowIndex = 0;
var sheetName = workbook.GetSheetName(i);
var sheet = workbook.GetSheet(sheetName);
var headerRow = sheet.GetRow(rowIndex++);
var dataTable = new DataTable(classProperty.TableName);
if (headerRow == null)
{
break;
}
// 取得数据列信息。
var cellCount = headerRow.Cells.Count;
for (int j = 0; j < cellCount; j++)
{
var firstOrDefault = (from p in properties where p.DisplayName == headerRow.Cells[j].StringCellValue select p.ColumnName).FirstOrDefault();
if (firstOrDefault != null)
{
dataTable.Columns.Add(firstOrDefault);
}
}
var row = sheet.GetRow(rowIndex);
// 获取当前Sheet的所有数据。
while (row != null)
{
var dtRow = dataTable.NewRow();
for (int k = 0; k < cellCount; k++)
{
if (row.Cells.Count <= k)
{
break;
}
var columnIndex = row.Cells[k].ColumnIndex;
dtRow[columnIndex] = row.Cells[k].ToString();
}
rowIndex++;
dataTable.Rows.Add(dtRow);
row = sheet.GetRow(rowIndex);
}
result.Tables.Add(dataTable);
}
// 释放资源。
stream.Dispose();
return result;
}
/// <summary>
/// 李天元 2013-10-25 批处理操作
/// </summary>
public int BatchDataTable(DataTable dt)
{
var connection = new SqlMapSession(this.SqlMapper);
connection.CreateConnection();
var connStr = connection.Connection.ConnectionString;
connection.OpenConnection();
using (SqlBulkCopy sbc = new SqlBulkCopy(connStr))
{
sbc.BatchSize = dt.Rows.Count;
sbc.BulkCopyTimeout = 60;
sbc.NotifyAfter = 10000;
sbc.DestinationTableName = dt.TableName;
for (int i = 0; i < dt.Columns.Count; i++)
{
sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
try
{
sbc.WriteToServer(dt);
dt.Dispose();
return dt.Rows.Count;
}
catch (Exception e)
{
throw e;
}
finally
{
sbc.ColumnMappings.Clear();
connection.CloseConnection();
}
}
}
目前完整的导入导出功能已经全部实现了。使用起来比较简便。希望大家有更好的方法交流交流。