MVC利用Aspose.Cells.dll实现Excel数据导入数据库

	/// <summary>导入Excel数据到数据库
        /// 
        /// </summary>
        /// <param name="file">文件</param>
        public ActionResult ImportExcel(HttpPostedFileBase file)
        {
            if (file != null)
            {
                bool exportColumnName = true;
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                workbook.Open(file.FileName);
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
                Cells cells = worksheet.Cells;DataTable datatable = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, exportColumnName);//这里用到Aspose.Cells的ExportDataTableAsString方法来读取excel数据
                 //对班级进行分组
                var groupByClass = datatable.Rows.Cast<DataRow>().GroupBy(u => u["班级"]);
                //导入一次数据就清空班级表
                string deleteSql = "TRUNCATE TABLE dbo.Classes";
                db.Database.ExecuteSqlCommand(deleteSql);

                foreach (var item in groupByClass)
                {
                    Classes classes = new Classes();
                    classes.ClassName = item.Key.ToString();
                    db.Classes.Add(classes);
                    db.SaveChanges();

                }
                string[] fileds = new string[] { "班级" }; // 分组条件
                List<DataTable> grouped = new List<DataTable>(); // 存储分组结果

                //得到分组数据，并存到grouped中
                GroupDataRows(datatable.Rows.Cast<DataRow>(), grouped, fileds, 0, datatable);

                // 输出分组
                foreach (DataTable dt in grouped)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        string className = row["班级"].ToString();
                        int classID = db.Classes.Where(cl => cl.ClassName == className).FirstOrDefault().ID;
                        row.BeginEdit();
                        row["班级"] = classID;
                        if (row["性别"].ToString() == "男")
                        {
                            row["性别"] = 0;
                        }
                        else
                        {
                            row["性别"] = 1;
                        }
                        User user = new User();
                        user.UserName = row["账号"].ToString();
                        user.Password = row["密码"].ToString();
                        user.NickName = row["呢称"].ToString();
                        user.RoleID = 4;
                        user.Sex = int.Parse(row["性别"].ToString());
                        if (row["年龄"].ToString() == "")
                        {
                            user.Age = null;
                        }
                        else
                        {
                            user.Age = int.Parse(row["年龄"].ToString());
                        }
                        if (row["学校"].ToString() == "")
                        {
                            user.School = null;
                        }
                        else
                        {
                            user.School = row["学校"].ToString();
                        }
                        if (row["积分"].ToString() == "")
                        {
                            user.Coins = null;
                        }
                        else
                        {
                            user.Coins = int.Parse(row["积分"].ToString());
                        }

                        user.ClassID = int.Parse(row["班级"].ToString());

                        db.User.Add(user);
                        db.SaveChanges();
                    }
                }
                return RedirectToAction("StudentIndex");
            }
            else
            {
                return RedirectToAction("StudentIndex");
            }

        }
 	/// <summary>把dataTable数据通过某列分组，再把分组数据存到LIst<Datatable>中
        /// 
        /// </summary>
        /// <param name="source">数据中每行数据</param>
        /// <param name="destination">分组后数据存入的Datatable</param>
        /// <param name="groupByFields">分组字段数组</param>
        /// <param name="fieldIndex"></param>
        /// <param name="schema">数据集</param>
        public static void GroupDataRows(IEnumerable<DataRow> source, List<DataTable> destination, string[] groupByFields, int fieldIndex, DataTable schema)
        {
            if (fieldIndex >= groupByFields.Length || fieldIndex < 0)
            {
                DataTable dt = schema.Clone();
                foreach (DataRow row in source)
                {
                    DataRow dr = dt.NewRow();
                    dr.ItemArray = row.ItemArray;
                    dt.Rows.Add(dr);
                }

                destination.Add(dt);
                return;
            }

            var results = source.GroupBy(o => o[groupByFields[fieldIndex]]);
            foreach (var rows in results)
            {
                GroupDataRows(rows, destination, groupByFields, fieldIndex + 1, schema);
            }

            fieldIndex++;
        }

 
 
public System.Data.DataTable ExportDataTableAsString ( Int32 firstRow, Int32 firstColumn, Int32 totalRows, Int32 totalColumns, Boolean exportColumnName
 )
 
 
 
Name
Description
firstRow
The row number of the first cell
 to export out.
firstColumn
The column number of the first cell to export out.
totalRows
Number of rows to be imported.
totalColumns
Number
 of columns to be imported.
exportColumnName
Indicates whether the data in the first row are exported to the column name of the DataTable