OpenXML_导入Excel到数据库
(1).实现功能：通过前台选择.xlsx文件的Excel，将其文件转化为DataTable和List集合
(2).开发环境：Window7旗舰版+vs2013+Mvc4.0
(2).在使用中需要用到的包和dll
1.用NuGet引入OpenXML包【全名叫DocumentFormat.OpenXml】=》注意：现在导入的Excel只支持.xlsx结尾的Excel，若导入.xls结尾的则会出现【文件包含损坏的数据】的错误！
2.WindowsBase.dll
(3).MVC中通过file选择文件并用submit提交到Controller方法如下：
3.1：前台代码
3.2：Controller代码
////// form提交回的Action /// ///public ActionResult FileUpload() { //1.假设选择一个Excel文件 获取第一个Excel文件 var stream = Request.Files[0].InputStream; //2.将选择的文件转换为DataTable var rst = new StreamToDataTable().ReadExcel(stream); //3.将DataTable转换为List集合 var list = this.TableToLists(rst); return View(); } /// /// 加载Excel数据 /// public ListTableToLists(System.Data.DataTable table) { TBToList tables = new TBToList (); var lists = tables.ToList(table); return lists; }
(4).Excel流组织成Datatable方法实现
public class StreamToDataTable
{
///
/// Excel流组织成Datatable
///
/// Excel文件流
/// DataTable
public DataTable ReadExcel(Stream stream)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false)) //若导入.xls格式的Excel则会出现【文件包含损坏的数据】的错误！
{
//打开Stream
IEnumerable sheets = document.WorkbookPart.Workbook.Descendants();
if (sheets.Count() == 0)
{//找出符合条件的sheet，没有则返回
return null;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
//获取Excel中共享数据
SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
IEnumerable rows = worksheetPart.Worksheet.Descendants();//得到Excel中得数据行
DataTable dt = new DataTable("Excel");
//因为需要将数据导入到DataTable中，所以我们假定Excel的第一行是列名，从第二行开始是行数据
foreach (Row row in rows)
{
if (row.RowIndex == 1)
{
//Excel第一行为列名
GetDataColumn(row, stringTable, ref dt);
}
GetDataRow(row, stringTable, ref dt);//Excel第二行同时为DataTable的第一行数据
}
return dt;
}
}
///
/// 根据给定的Excel流组织成Datatable
///
/// Excel文件流
/// 需要读取的Sheet
/// 组织好的DataTable
public DataTable ReadExcelBySheetName(string sheetName, Stream stream)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
{//打开Stream
IEnumerable sheets = document.WorkbookPart.Workbook.Descendants().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{//找出符合条件的sheet，没有则返回
return null;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
//获取Excel中共享数据
SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
IEnumerable rows = worksheetPart.Worksheet.Descendants();//得到Excel中得数据行
DataTable dt = new DataTable("Excel");
//因为需要将数据导入到DataTable中，所以我们假定Excel的第一行是列名，从第二行开始是行数据
foreach (Row row in rows)
{
if (row.RowIndex == 1)
{
//Excel第一行为列名
GetDataColumn(row, stringTable, ref dt);
}
GetDataRow(row, stringTable, ref dt);//Excel第二行同时为DataTable的第一行数据
}
return dt;
}
}
///
/// 构建DataTable的列
///
/// OpenXML定义的Row对象
///
/// 需要返回的DataTable对象
///
/// 构建DataTable的每一行数据，并返回该Datatable
///
/// OpenXML的行
///
/// DataTable
private void GetDataRow(Row row, SharedStringTable stringTable, ref DataTable dt)
{
// 读取算法：按行逐一读取单元格，如果整行均是空数据
// 则忽略改行（因为本人的工作内容不需要空行）-_-
DataRow dr = dt.NewRow();
int i = 0;
int nullRowCount = i;
foreach (Cell cell in row)
{
string cellVal = GetValue(cell, stringTable);
if (cellVal == string.Empty)
{
nullRowCount++;
}
dr[i] = cellVal;
i++;
}
if (nullRowCount != i)
{
dt.Rows.Add(dr);
}
}
///
/// 获取单元格的值
///
///
///
///
(5).Datatable组织为List方法实现
public class TBToListwhere T : new() { /// /// 获取列名集合 /// private IListGetColumnNames(DataColumnCollection dcc) { IList list = new List (); foreach (DataColumn dc in dcc) { list.Add(dc.ColumnName); } return list; } /// ///属性名称和类型名的键值对集合 /// private Hashtable GetColumnType(DataColumnCollection dcc) { if (dcc == null || dcc.Count == 0) { return null; } IListcolNameList = GetColumnNames(dcc); Type t = typeof(T); PropertyInfo[] properties = t.GetProperties(); Hashtable hashtable = new Hashtable(); int i = 0; if (properties.Length == colNameList.Count) { foreach (PropertyInfo p in properties) { foreach (string col in colNameList) { if (!hashtable.Contains(col)) { hashtable.Add(col, p.PropertyType.ToString() + i++); } } } } return hashtable; } /// /// DataTable转换成IList /// /// ///public List ToList(DataTable dt) { if (dt == null || dt.Rows.Count == 0) { return null; } PropertyInfo[] properties = typeof(T).GetProperties();//获取实体类型的属性集合 Hashtable hh = GetColumnType(dt.Columns);//属性名称和类型名的键值对集合 IList colNames = GetColumnNames(hh);//按照属性顺序的列名集合 List list = new List (); T model = default(T); foreach (DataRow dr in dt.Rows) { model = new T();//创建实体 int i = 0; foreach (PropertyInfo p in properties) { if (p.PropertyType == typeof(string)) { p.SetValue(model, dr[colNames[i++]], null); } else if (p.PropertyType == typeof(int)) { p.SetValue(model, int.Parse(dr[colNames[i++]].ToString()), null); } else if (p.PropertyType == typeof(DateTime)) { p.SetValue(model, DateTime.Parse(dr[colNames[i++]].ToString()), null); } else if (p.PropertyType == typeof(float)) { p.SetValue(model, float.Parse(dr[colNames[i++]].ToString()), null); } else if (p.PropertyType == typeof(double)) { p.SetValue(model, double.Parse(dr[colNames[i++]].ToString()), null); } } list.Add(model); } return list; } /// /// 按照属性顺序的列名集合 /// private IListGetColumnNames(Hashtable hh) { PropertyInfo[] properties = typeof(T).GetProperties();//获取实体类型的属性集合 IList ilist = new List (); int i = 0; foreach (PropertyInfo p in properties) { ilist.Add(GetKey(p.PropertyType.ToString() + i++, hh)); } return ilist; } /// /// 根据Value查找Key /// private string GetKey(string val, Hashtable tb) { foreach (DictionaryEntry de in tb) { if (de.Value.ToString() == val) { return de.Key.ToString(); } } return null; } }
(6).既然List集合都得到了，那么导入数据到数据库中那也不是什么难事了.......