Excel导入

一、上传excel表到服务器;通过读取excel表获得数据;记录到数据库。(包含模板下载)

1、视图代码

 @using (Html.BeginForm("Index", "Excel", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <h2>
        基站信息导入</h2>
    <div>
        <fieldset id="myfieldset">
            <legend>excel模版格式 </legend><font color="red">导入基站的模板格式如下,若模板格式不正确,则相应的基站不能导入!</font> 
            <p style="color: Red; text-align: center;">@Html.ActionLink("下载模版", "GetFile")</p>
        </fieldset>
    </div>
    <div style="margin-top: 20px;">
        <fieldset id="myfieldset1">
            <legend>基站批量信息导入</legend>
            <p>
                选择文件:<input id="FileUpload" type="file" name="files" style="width: 250px; height: 24px;
                    background: White" class="easyui-validatebox" /></p>
            <p>
                <input id="btnImport" type="submit" value="导入" style="width: 60px; height: 28px;" /></p>
            <p style="color: Red; text-align: center;">@ViewBag.error</p>
        </fieldset>
    </div>
}
View Code

2、数据模型

public class ListModel
    {
        public int Id { get; set; }
        public string Head { get; set; }
        public string Center { get; set; }
    }
View Code

3、控制器代码

using System; 
using System.Data;
using System.Data.OleDb;
using System.IO; 
using System.Transactions;
using System.Web;
using System.Web.Mvc;  
using UploadExcel.Models; 

namespace UploadExcel.Controllers
{
    public class ExcelController : Controller
    { 
        public ActionResult Index()
        {
            return View();
        } 

        [HttpPost]
        public ActionResult Index(HttpPostedFileBase filebase)
        { 
            HttpPostedFileBase file = Request.Files["files"];
            string FileName;
            string savePath;
            if (file == null || file.ContentLength <= 0)
            {
                ViewBag.error = "文件不能为空";
                return View();
            }
            else
            {
                string filename = Path.GetFileName(file.FileName);
                int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
                string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
                string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
                int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
                string FileType = ".xls,.xlsx";//定义上传文件的类型字符串

                FileName = NoFileName  + fileEx;
                if (!FileType.Contains(fileEx))
                {
                    ViewBag.error = "文件类型不对,只能导入xls和xlsx格式的文件";
                    return View();
                }
                if (filesize >= Maxsize)
                {
                    ViewBag.error = "上传文件超过4M,不能上传";
                    return View();
                }
                string path = AppDomain.CurrentDomain.BaseDirectory + "/App_Data/"; 
                savePath = Path.Combine(path, FileName);
                file.SaveAs(savePath);
            }  
            string strConn;
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";" + "Extended Properties=Excel 12.0";
            //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";" + "Extended Properties=Excel 8.0";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
            DataSet myDataSet = new DataSet();
            try
            {
                myCommand.Fill(myDataSet, "ExcelInfo");
            }
            catch (Exception ex)
            {
                ViewBag.error = ex.Message;
                return View();
            }
            DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();

            //引用事务机制,出错时,事物回滚
            using (TransactionScope transaction = new TransactionScope())
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    int id = int.Parse(table.Rows[i][0].ToString());
                    string  head = table.Rows[i][1].ToString();
                    string center = table.Rows[i][2].ToString();
                    var model = new ListModel()
                                          {
                                              Id = id,
                                              Head = head,
                                              Center = center
                                          };
                    //此处写录入数据库代码;
                }
                transaction.Complete();
            }   
            ViewBag.error = "导入成功";
            System.Threading.Thread.Sleep(2000);
            return RedirectToAction("Index");
        }

        public FileResult GetFile()//获得模板下载地址
        {
            string path = AppDomain.CurrentDomain.BaseDirectory + "/App_Data/";
            string fileName = "配置信息.xls";
            return File(path + fileName, "text/plain", fileName);
        }
View Code

4、注意事项

      数据模型、模板、数据库表字段 要一致;模板工作簿名(如Sheet1)要和代码的一致;

     原文:http://www.cnblogs.com/bianlan/archive/2012/05/14/2500705.html

 

二、上传excel表到服务器;通过NPOI组件获得数据;记录到数据库。

1、视图代码、数据模型代码都和上面的一样;添加NPOI组件(到网上下载);添加ICSharpCode.SharpZipLib.dll

2、控制器代码

 

using System; 
using System.Data;
using System.IO; 
using System.Transactions;
using System.Web;
using System.Web.Mvc;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using UploadExcel.Models;

namespace UploadExcel.Controllers
{
    public class NpoiController : Controller
    {
        //
        // GET: /Npoi/

        public ActionResult Index()
        {
            return View();
        }
        [HttpPost]
        public ActionResult Index(HttpPostedFileBase filebase)
        {
            HttpPostedFileBase file = Request.Files["files"];
            string FileName;
            string savePath;
            if (file == null || file.ContentLength <= 0)
            {
                ViewBag.error = "文件不能为空";
                return View();
            }
            else
            {
                string filename = Path.GetFileName(file.FileName);
                int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
                string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
                string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
                int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
                string FileType = ".xls,.xlsx";//定义上传文件的类型字符串

                FileName = NoFileName + fileEx;
                if (!FileType.Contains(fileEx))
                {
                    ViewBag.error = "文件类型不对,只能导入xls和xlsx格式的文件";
                    return View();
                }
                if (filesize >= Maxsize)
                {
                    ViewBag.error = "上传文件超过4M,不能上传";
                    return View();
                }
                string path = AppDomain.CurrentDomain.BaseDirectory + "/App_Data/";
                savePath = Path.Combine(path, FileName);
                file.SaveAs(savePath);
            }

            DataTable table = ExcelToDataTable(savePath);


            //引用事务机制,出错时,事物回滚
            using (TransactionScope transaction = new TransactionScope())
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    int id = int.Parse(table.Rows[i][0].ToString());
                    string head = table.Rows[i][1].ToString();
                    string center = table.Rows[i][2].ToString();
                    var model = new ListModel()
                    {
                        Id = id,
                        Head = head,
                        Center = center
                    };
                    //此处写录入数据库代码;
                }
                transaction.Complete();
            }
            ViewBag.error = "导入成功";
            System.Threading.Thread.Sleep(2000);
            return RedirectToAction("Index");
        }

        /// <summary>读取excel
        /// 根据索引读取Sheet表数据,默认读取第一个sheet
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <param name="sheetIndex">sheet表的索引,从0开始</param>
        /// <returns>数据集</returns>
        public static DataTable ExcelToDataTable(string strFileName, int sheetIndex = 0)
        {
            DataTable dt = new DataTable();
            HSSFWorkbook hssfworkbook = null;
            XSSFWorkbook xssfworkbook = null;
            string fileExt = Path.GetExtension(strFileName);//获取文件的后缀名
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xls")
                    hssfworkbook = new HSSFWorkbook(file);
                else if (fileExt == ".xlsx")
                    xssfworkbook = new XSSFWorkbook(file);//初始化太慢了,不知道这是什么bug
            }
            if (hssfworkbook != null)
            {
                HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
                if (sheet != null)
                {
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
                    for (int j = 0; j < cellCount; j++)
                    {
                        HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                        dt.Columns.Add(cell.ToString());
                    }
                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                    {
                        HSSFRow row = (HSSFRow)sheet.GetRow(i);
                        DataRow dataRow = dt.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        dt.Rows.Add(dataRow);
                    }
                }
            }
            else if (xssfworkbook != null)
            {
                XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
                if (xSheet != null)
                {
                    System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
                    XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
                    for (int j = 0; j < cellCount; j++)
                    {
                        XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
                        dt.Columns.Add(cell.ToString());
                    }
                    for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
                    {
                        XSSFRow row = (XSSFRow)xSheet.GetRow(i);
                        DataRow dataRow = dt.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        dt.Rows.Add(dataRow);
                    }
                }
            }
            return dt;
        }
    }
}
View Code

 

3、参考:http://www.cnblogs.com/wohexiaocai/p/3529641.html

 

 

 

posted @ 2014-03-12 17:23  泷泷  阅读(565)  评论(0编辑  收藏  举报