MVC execl 导入导出

<input id="xls" name="xls" type="file" style="width:210px;" />
&nbsp;&nbsp;<input type="submit" value="导入" />
&nbsp;&nbsp;<a href="~/Demo/Excel_ExcelExport">导出</a>


#region Excel

        public ActionResult Excel_Index()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Excel_Index(FormCollection formdata)
        {
            ViewBag.Data = null;
            ViewBag.Msg = string.Empty;

            if (this.HttpContext.Request.Files.Count <= 0 || this.HttpContext.Request.Files[0].ContentLength <= 0)
            {
                ViewBag.Msg = "请选择文件！";
                return View();
            }

            string fileExt = System.IO.Path.GetExtension(this.HttpContext.Request.Files[0].FileName).ToLower();
            if (fileExt != ".xls" && fileExt != ".xlsx")
            {
                ViewBag.Msg = "选择的文件格式不对！";
                return View();
            }

            ViewBag.Data = Demo.ImportFromStream(this.HttpContext.Request.Files[0].InputStream);

            return View();
        }

        public ActionResult Excel_ExcelExport()
        {
            DataTable dt = new DataTable();
            DataColumn dc1 = new DataColumn("column1", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("column2", Type.GetType("System.String"));
            DataColumn dc3 = new DataColumn("column3", Type.GetType("System.String"));
            DataColumn dc4 = new DataColumn("column4", Type.GetType("System.String"));
            DataColumn dc5 = new DataColumn("column5", Type.GetType("System.String"));
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            dt.Columns.Add(dc5);

            for (int i = 0; i < 10; i++)
            {
                DataRow dr = dt.NewRow();
                dr["column1"] = "test1";
                dr["column2"] = "test2";
                dr["column3"] = "test3";
                dr["column4"] = "test4";
                dr["column5"] = "test5";
                dt.Rows.Add(dr);
            }

            byte[] bytes = Demo.ExportToBytes(dt);

            return File(bytes, "application/x-excel", "ExcelExport.xls");
        }

        #endregion

using NPOI.SS.UserModel;

/// <summary>
    /// Excel导入导出工具类
    /// </summary>
    public static class Demo    {
        /// <summary>
        /// 导入Excel
        /// </summary>
        public static System.Data.DataTable ImportFromStream(System.IO.Stream fileStream)
        {
            NPOI.SS.UserModel.IWorkbook workbook;
            NPOI.SS.UserModel.ISheet sheet;
            System.Data.DataTable data = new System.Data.DataTable();

            try
            {
                try
                {
                    workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fileStream); //07
                }
                catch
                {
                    workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream); //03
                }

                //第一个sheet工作表
                sheet = workbook.GetSheetAt(0);

                if (sheet != null)
                {
                    //第一行
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        System.Data.DataColumn column = new System.Data.DataColumn(firstRow.GetCell(i).StringCellValue);
                        data.Columns.Add(column);
                    }

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = sheet.FirstRowNum; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        System.Data.DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            //if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            //    dataRow[j] = row.GetCell(j).ToString();
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            {
                                switch (row.GetCell(j).CellType)
                                {
                                    case CellType.String: //文本
                                        dataRow[j] = row.GetCell(j).StringCellValue;
                                        break;
                                    case CellType.Numeric: //数值
                                        if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
                                        {
                                            dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                                        }
                                        else
                                        {
                                            dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                                        }
                                        break;
                                    case CellType.Boolean: //bool
                                        dataRow[j] = row.GetCell(j).BooleanCellValue;
                                        break;
                                    case CellType.Blank: //空白
                                        dataRow[j] = "";
                                        break;
                                    default: dataRow[j] = "ERROR";
                                        break;
                                }
                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 导入Excel
        /// </summary>
        public static System.Data.DataTable ImportFromBytes(byte[] fileBytes)
        {
            System.Data.DataTable table = null;

            if (fileBytes == null || fileBytes.Length <= 0)
            {
                return table;
            }

            using (var fileStream = new System.IO.MemoryStream(fileBytes))
            {
                table = ImportFromStream(fileStream);
            }

            return table;
        }

        /// <summary>
        /// 导入Excel
        /// </summary>
        public static System.Data.DataTable ImportFromFilename(string fileName)
        {
            System.Data.DataTable table = null;

            if (!System.IO.File.Exists(fileName))
            {
                return table;
            }

            using (var fileStream = new System.IO.FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read))
            {
                table = ImportFromStream(fileStream);
            }

            return table;
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        public static System.IO.Stream ExportToStream(System.Data.DataTable table)
        {
            System.IO.MemoryStream ms = new System.IO.MemoryStream();

            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
            NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);

            IRow headRow = sheet.CreateRow(0);
            foreach (System.Data.DataColumn column in table.Columns)
            {
                headRow.CreateCell(column.Ordinal).SetCellValue(string.IsNullOrWhiteSpace(column.Caption) ? column.ColumnName : column.Caption);
            }

            int rowIndex = 1;

            foreach (System.Data.DataRow row in table.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (System.Data.DataColumn column in table.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }
            workbook.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);

            sheet = null;
            headerRow = null;
            workbook = null;

            return ms;
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        public static byte[] ExportToBytes(System.Data.DataTable table)
        {
            byte[] bytes = null;

            using (var stream = ExportToStream(table))
            {
                bytes = new byte[stream.Length];
                stream.Read(bytes, 0, bytes.Length);
                stream.Seek(0, System.IO.SeekOrigin.Begin);
            }

            return bytes;
        }
    }