导入excel数据到数据库

来源：互联网发布：巴以冲突知乎编辑：程序博客网时间：2024/05/21 10:09

1）后台接收excel文件并保存到服务器

var file = Request.Files[0];
var fileInfo = new FileInfo(file.FileName);
string filePath = Server.MapPath("~/UploadFiles/");
filePath = filePath + file.FileName;
file.SaveAs(filePath);

2）将excel数据转化成model对象，

public class ExcelHelper {

#region 保存数据列表到Excel（泛型）+void SaveToExcel<T>(IEnumerable<T> data, string FileName, string OpenPassword = "")
/// <summary>
/// 保存数据列表到Excel（泛型）
/// </summary>
/// <typeparam name="T">集合数据类型</typeparam>
/// <param name="data">数据列表</param>
/// <param name="FileName">Excel文件</param>
/// <param name="OpenPassword">Excel打开密码</param>
public static void SaveToExcel<T>(IEnumerable<T> data, string FileName, string OpenPassword = "") {
FileInfo file = new FileInfo(FileName);
try {
using (ExcelPackage ep = new ExcelPackage(file, OpenPassword)) {
ExcelWorksheet ws = ep.Workbook.Worksheets.Add(typeof(T).Name);
ws.Cells["A1"].LoadFromCollection(data, true, TableStyles.Medium10);

ep.Save(OpenPassword);
}
} catch (InvalidOperationException ex) {
Console.WriteLine(ex.Message);
}
}
#endregion
#region 从Excel中加载数据（泛型）+IEnumerable<T> LoadFromExcel<T>(string FileName) where T : new()
/// <summary>
/// 从Excel中加载数据（泛型）
/// </summary>
/// <typeparam name="T">每行数据的类型</typeparam>
/// <param name="FileName">Excel文件名</param>
/// <returns>泛型列表</returns>
public static IEnumerable<T> LoadFromExcel<T>(string FileName) where T : new() {
//FileInfo existingFile = new FileInfo(FileName);
List<T> resultList = new List<T>();
Dictionary<string, int> dictHeader = new Dictionary<string, int>();
FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);
using (ExcelPackage package = new ExcelPackage(fs)) {
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int colStart = worksheet.Dimension.Start.Column; //工作区开始列
int colEnd = worksheet.Dimension.End.Column; //工作区结束列
int rowStart = worksheet.Dimension.Start.Row; //工作区开始行号
int rowEnd = worksheet.Dimension.End.Row; //工作区结束行号
//将每列标题添加到字典中
for (int i = colStart; i < colEnd; i++) {
dictHeader[worksheet.Cells[rowStart, i].Value.ToString()] = i;
}

List<PropertyInfo> propertyInfoList = new List<PropertyInfo>(typeof(T).GetProperties());

for (int row = rowStart + 1; row <rowEnd-1; row++) {
T result = new T();

//为对象T的各属性赋值
foreach (PropertyInfo p in propertyInfoList) {
try {
ExcelRange cell = worksheet.Cells[row, dictHeader[p.Name]]; //与属性名对应的单元格

if (cell.Value == null)
continue;
switch (p.PropertyType.Name.ToLower()) {
case "string":
p.SetValue(result, cell.GetValue<String>());
break;
case "int16":
p.SetValue(result, cell.GetValue<Int16>());
break;
case "int32":
p.SetValue(result, cell.GetValue<Int32>());
break;
case "int64":
p.SetValue(result, cell.GetValue<Int64>());
break;
case "decimal":
p.SetValue(result, cell.GetValue<Decimal>());
break;
case "double":
p.SetValue(result, cell.GetValue<Double>());
break;
case "datetime":
p.SetValue(result, cell.GetValue<DateTime>());
break;
case "boolean":
p.SetValue(result, cell.GetValue<Boolean>());
break;
case "byte":
p.SetValue(result, cell.GetValue<Byte>());
break;
case "char":
p.SetValue(result, cell.GetValue<Char>());
break;
case "single":
p.SetValue(result, cell.GetValue<Single>());
break;
default:
break;
}
} catch (KeyNotFoundException ex) { }
}
resultList.Add(result);
}

}
fs.Close();

return resultList;
}
#endregion

阅读全文

0 0