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：前台代码

< form  action="Home/FileUpload" method="post" enctype="multipart/form-data"> 　   < div  style="width:100%;height:auto;   　　     < input  id="uploadfile" type="file" name="file" /> 　　      < input  type="submit" value="上传Excel" /> 　   </ div > </ form >

    3.2：Controller代码

/// <summary> /// form提交回的Action /// </summary> /// <returns></returns> 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(); } /// <summary> /// 加载Excel数据 /// </summary> public  List<ExcelImport> TableToLists(System.Data.DataTable table) {      TBToList<ExcelImport> tables = new  TBToList<ExcelImport>();      var  lists = tables.ToList(table);      return  lists; }

(4).Excel流组织成Datatable方法实现

public  class  StreamToDataTable     {         /// <summary>         /// Excel流组织成Datatable         /// </summary>         /// <param name="stream">Excel文件流</param>         /// <returns>DataTable</returns>         public  DataTable ReadExcel(Stream stream)         {             using  (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false ))     //若导入.xls格式的Excel则会出现【文件包含损坏的数据】的错误！             {                 //打开Stream                 IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();                 if  (sheets.Count() == 0)                 { //找出符合条件的sheet，没有则返回                     return  null ;                 }                   WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);                 //获取Excel中共享数据                 SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;                 IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>(); //得到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;             }         }             /// <summary>         /// 根据给定的Excel流组织成Datatable         /// </summary>         /// <param name="stream">Excel文件流</param>         /// <param name="sheetName">需要读取的Sheet</param>         /// <returns>组织好的DataTable</returns>         public  DataTable ReadExcelBySheetName( string  sheetName, Stream stream)         {             using  (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false ))             { //打开Stream                 IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().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<Row> rows = worksheetPart.Worksheet.Descendants<Row>(); //得到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;             }         }           /// <summary>         /// 构建DataTable的列         /// </summary>         /// <param name="row">OpenXML定义的Row对象</param>         /// <param name="stringTablePart"></param>         /// <param name="dt">需要返回的DataTable对象</param>         /// <returns></returns>         public  void  GetDataColumn(Row row, SharedStringTable stringTable, ref  DataTable dt)         {             DataColumn col = new  DataColumn();             foreach  (Cell cell in  row)             {                 string  cellVal = GetValue(cell, stringTable);                 col = new  DataColumn(cellVal);                 dt.Columns.Add(col);             }         }           /// <summary>         /// 构建DataTable的每一行数据，并返回该Datatable         /// </summary>         /// <param name="row">OpenXML的行</param>         /// <param name="stringTablePart"></param>         /// <param name="dt">DataTable</param>         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);             }         }             /// <summary>         /// 获取单元格的值         /// </summary>         /// <param name="cell"></param>         /// <param name="stringTablePart"></param>         /// <returns></returns>         private  string  GetValue(Cell cell, SharedStringTable stringTable)         {             //由于Excel的数据存储在SharedStringTable中，需要获取数据在SharedStringTable 中的索引             string  value = string .Empty;             try             {                 if  (cell.ChildElements.Count == 0)                     return  value;                   value = double .Parse(cell.CellValue.InnerText).ToString();                   if  ((cell.DataType != null ) && (cell.DataType == CellValues.SharedString))                 {                     value = stringTable.ChildElements[Int32.Parse(value)].InnerText;                 }             }             catch  (Exception)             {                 value = "N/A" ;             }             return  value;         }       }

(5).Datatable组织为List方法实现

public  class  TBToList<T> where  T : new ()     {         /// <summary>         /// 获取列名集合         /// </summary>         private  IList< string > GetColumnNames(DataColumnCollection dcc)         {             IList< string > list = new  List< string >();             foreach  (DataColumn dc in  dcc)             {                 list.Add(dc.ColumnName);             }             return  list;         }           /// <summary>         ///属性名称和类型名的键值对集合         /// </summary>         private  Hashtable GetColumnType(DataColumnCollection dcc)         {             if  (dcc == null  || dcc.Count == 0)             {                 return  null ;             }             IList< string > colNameList = 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;         }           /// <summary>         /// DataTable转换成IList         /// </summary>         /// <param name="dt"></param>         /// <returns></returns>         public  List<T> ToList(DataTable dt)         {             if  (dt == null  || dt.Rows.Count == 0)             {                 return  null ;             }               PropertyInfo[] properties = typeof (T).GetProperties(); //获取实体类型的属性集合             Hashtable hh = GetColumnType(dt.Columns); //属性名称和类型名的键值对集合             IList< string > colNames = GetColumnNames(hh); //按照属性顺序的列名集合             List<T> list = new  List<T>();             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;         }           /// <summary>         /// 按照属性顺序的列名集合         /// </summary>         private  IList< string > GetColumnNames(Hashtable hh)         {             PropertyInfo[] properties = typeof (T).GetProperties(); //获取实体类型的属性集合             IList< string > ilist = new  List< string >();             int  i = 0;             foreach  (PropertyInfo p in  properties)             {                 ilist.Add(GetKey(p.PropertyType.ToString() + i++, hh));             }             return  ilist;         }           /// <summary>         /// 根据Value查找Key         /// </summary>         private  string  GetKey( string  val, Hashtable tb)         {             foreach  (DictionaryEntry de in  tb)             {                 if  (de.Value.ToString() == val)                 {                     return  de.Key.ToString();                 }             }             return  null ;         }     }

转自：http://www.cnblogs.com/pfwbloghome/p/4969792.html

转载于:https://www.cnblogs.com/nele/p/4970085.html