前言
本文对应的场景是导入Excel数据,Excel对应的字段都配置在xml文件中。截图如下:
代码实战
工具类
实体类:XMLReadModel.cs
1
2
3
4
5
6
7
8
9
10
11
12
|
public class XMLReadModel
{
/// <summary>
/// 导入所需键值对
/// </summary>
public Hashtable ImportHashtable { set ; get ; } = new Hashtable();
/// <summary>
/// 导出所需键值对
/// </summary>
public Hashtable ExportHashtable { set ; get ; } = new Hashtable();
}
|
工具方法:读取xml文件内容到实体中。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
/// <summary>
/// 读取xml文件到hashtable
/// </summary>
public static XMLReadModel ReadToHashtable( string path)
{
var xr = new XMLReadModel();
var xmldoc = new XmlDocument();
xmldoc.Load(path);
//获取节点列表
var topM = xmldoc.SelectNodes( "//ColumnName" );
foreach (XmlElement element in topM)
{
var enabled = element.Attributes[0].Value;
if (enabled == "true" ) //字段启用
{
var dbProperty = element.GetElementsByTagName( "DbProperty" )[0].InnerText;
var excelProperty = element.GetElementsByTagName( "ExcelProperty" )[0].InnerText;
if (!xr.ImportHashtable.ContainsKey(excelProperty))
{
xr.ImportHashtable.Add(excelProperty, dbProperty);
}
if (!xr.ExportHashtable.ContainsKey(dbProperty))
{
xr.ExportHashtable.Add(dbProperty, excelProperty);
}
}
}
return xr;
}
|
Excel文件内容转成datatable方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
/// <summary>
/// excel文件流转化成datatable
/// </summary>
public static DataTable ExcelToTableForXLSX(Stream fileStream, Hashtable ht = null , bool haveNote = false )
{
var dt = new DataTable();
using (var fs = fileStream)
{
var xssfworkbook = new XSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头 判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
if (haveNote)
{
firstRowNum += 1;
}
var header = sheet.GetRow(firstRowNum);
var columns = new List< int >();
for (var i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string .Empty)
{
dt.Columns.Add( new DataColumn( "Columns" + i.ToString()));
//continue;
}
else
{
if (ht != null )
{
var o = ht[obj.ToString()].ToString(); //这里就是根据xml中读取的字段对应关系进行字段赋值的。
dt.Columns.Add( new DataColumn(o));
}
else
{
dt.Columns.Add( new DataColumn(obj.ToString()));
}
}
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
var hasValue = false ;
if (sheet.GetRow(i) == null )
{
continue ;
}
foreach (var j in columns)
{
var cell = sheet.GetRow(i).GetCell(j);
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
}
if (dr[j] != null && dr[j].ToString() != string .Empty)
{
hasValue = true ;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
|
获取Excel单元格值类型,转成C#对应的值类型。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null )
return null ;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null ;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default :
return "=" + cell.CellFormula;
}
}
|
datatable转成list实体方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
/// <summary>
/// DataTable转成List
/// </summary>
public static List<T> ToDataList<T>( this DataTable dt)
{
var list = new List<T>();
var plist = new List<PropertyInfo>( typeof (T).GetProperties());
foreach (DataRow item in dt.Rows)
{
var s = Activator.CreateInstance<T>();
for (var i = 0; i < dt.Columns.Count; i++)
{
var info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
if (info != null )
{
try
{
if (!Convert.IsDBNull(item[i]))
{
object v = null ;
if (info.PropertyType.ToString().Contains( "System.Nullable" ))
{
v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
}
else
{
if (info.PropertyType.Equals( typeof ( bool )))
{
var value = item[i].ToString();
if (value.Equals( "true" , StringComparison.CurrentCultureIgnoreCase) || value.Equals( "false" , StringComparison.CurrentCultureIgnoreCase))
v = Convert.ChangeType(item[i], info.PropertyType);
else if (value.Equals( "1" , StringComparison.CurrentCultureIgnoreCase) || value.Equals( "0" , StringComparison.CurrentCultureIgnoreCase))
{
if (value.Equals( "1" , StringComparison.CurrentCultureIgnoreCase))
v = true ;
else
v = false ;
}
}
else
{
v = Convert.ChangeType(item[i], info.PropertyType);
}
}
info.SetValue(s, v, null );
}
}
catch (Exception ex)
{
throw new Exception( "字段[" + info.Name + "]转换出错," + ex.Message);
}
}
}
list.Add(s);
}
return list;
}
|
导入Excel方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
[HttpPost, Route( "api/Workstage/ImportFile" )]
public object ImportFile()
{
var filelist = HttpContext.Current.Request.Files;
var models = new List<DModel>();
var path = HttpContext.Current.Server.MapPath( "/ImportConfig/ModelConfig.xml" );
var xr = XMLHelper.ReadToHashtable(path); //读取Excel的字段对应关系,代码的实体字段和Excel中的字段对应,在后面的Excel的值读取还有数据库实体赋值用得到。
try
{
if (filelist.Count > 0)
{
for (var i = 0; i < filelist.Count; i++)
{
var file = filelist[i];
var fileName = file.FileName;
var fn = fileName.Split( '\\' );
if (fn.Length > 1)
{
fileName = fn[fn.Length - 1];
}
DataTable dataTable = null ;
var fs = fileName.Split( '.' );
if (fs.Length > 1)
{
dataTable = ExcelHelp.ExcelToTableForXLSX(file.InputStream, xr.ImportHashtable); //excel转成datatable
}
models = dataTable.ToDataList<DWorkstage>(); //datatable转成list
}
}
var succe = new List<DModel>(); //需要插入的数据列表
var exportList = new List<DModel>(); //需要导出给用户的失败数据列表
// 做一些数据逻辑处理,把处理好的数据加到succe列表中
if (succe.Any())
{
SqlBulkCopyHelper.BulkInsertData(succe, "DModel" );
}
var url = string .Empty;
if (exportList.Any())
{
var extDt = exportList.ToDataTable(xr.ExportHashtable); //把数据库中的字段转成Excel中需要展示的字段,并保存到datatable中。
url = SaveFile(extDt, "失败信息.xlsx" ); //把datatable保存到本地服务器或者文件服务器中,然后把文件下载地址返回给前端。
}
var list = new { failed = faile.Take(100).ToList(), failedCount = faile.Count }; //数据太多的话,浏览器会崩溃
var json = new { list, msg = "添加成功" , url };
return json;
}
catch (Exception ex)
{
var json = new { msg = "添加失败" , ex.Message, ex };
return json;
}
}
|
具体的xml文件
具体的节点可以自己命名。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
<? xml version = "1.0" encoding = "utf-8" ?>
< TableConfig >
<!--商品名称-->
< ColumnName Enabled = "true" >
< DbProperty >ProductName</ DbProperty >
< ExcelProperty >商品名称</ ExcelProperty >
</ ColumnName >
<!--原因,导出失败列表时用到的字段,导入时用不到-->
< ColumnName Enabled = "true" >
< DbProperty >SourceCode</ DbProperty >
< ExcelProperty >原因</ ExcelProperty >
</ ColumnName >
<!--创建时间-->
< ColumnName Enabled = "true" >
< DbProperty >CreateTime</ DbProperty >
< ExcelProperty >创建时间</ ExcelProperty >
</ ColumnName >
<!--更新时间-->
< ColumnName Enabled = "true" >
< DbProperty >UpdateTime</ DbProperty >
< ExcelProperty >更新时间</ ExcelProperty >
</ ColumnName >
</ TableConfig >
|
具体的Excel模板
以上就是ASP.NET 上传文件导入Excel的示例的详细内容,更多关于ASP.NET 上传文件导入Excel的资料请关注服务器之家其它相关文章!
原文链接:https://www.cnblogs.com/dawenyang/archive/2021/04/12/14647810.html