网页中NPIO对Excel的操作实例

上一节是在wpf中实现对excel的操作方法，这一节看看网页中如何封装实现对excel的上传导入和下载保存的。

看看效果图：

--------------------------

1、同样封装工具类Tools:

   public class Tools
    {


        #region 读excel
        public static DataTable ImportExcelFile(string filepath)
        {
            DataTable dt = new DataTable();

            //打开excel对话框

            if (filepath != null)
            {

                HSSFWorkbook hssfworkbook = null;
                #region//初始化信息
                try
                {
                    using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }
                #endregion

                var sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }
                while (rows.MoveNext())
                {
                    HSSFRow row = (HSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        var cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = "";
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }
        #endregion



        #region list转datatable
        public static DataTable ListToDataTable<T>(IEnumerable<T> c)
        {
            var props = typeof(T).GetProperties();
            var dt = new DataTable();
            dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
            if (c.Count() > 0)
            {
                for (int i = 0; i < c.Count(); i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo item in props)
                    {
                        object obj = item.GetValue(c.ElementAt(i), null);
                        tempList.Add(obj);
                    }
                    dt.LoadDataRow(tempList.ToArray(), true);
                }
            }
            return dt;
        }
        #endregion
        #region 写入excel
        public static MemoryStream WriteExcel<T>(IList<T> list)
        {

            //list转datatable
            var dt = ListToDataTable<T>(list);

            if (null != dt && dt.Rows.Count > 0)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");

                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
                    }
                }
                // 写入到客户端  
                System.IO.MemoryStream ms = new System.IO.MemoryStream(); ;

                book.Write(ms);
                ms.Seek(0, SeekOrigin.Begin);
                book = null;
                return ms;


            }
            return null;
        }
        #endregion
    }

2、网页界面设计：

@model IEnumerable<网页中NPIO对Excel的操作.Models.User>
    
@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>
1、导入数据：
@*enctype= "multipart/form-data"是必需有的，否则action接收不到相应的file*@
@using (Html.BeginForm("Upload", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <br />
    <span>上传文件</span>
    <br />
    <input type="file" name="file" id="file1" />
    <br />
    <br />
    <input id="ButtonUpload" type="submit" value="提交" class="btn btn-primary" />
}<br />
<hr />
2、导出数据：
<a href="/Home/Save">保存成excel</a>


 

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table>
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.pwd)
        </th>
        <th></th>
    </tr>
    @if (Model != null) { 
    foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.pwd)
            </td>
            <td>
                @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
                @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
                @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
            </td>
        </tr>
    }
    }

</table>

3、上传导入

  public ActionResult Index()
        {

            return View(this.TempData["users"]);
        }
        [HttpPost]
        public ActionResult Upload(HttpPostedFileBase file)
        {
            if (file == null)
            {
                return Content("没有文件！", "text/plain");
            }

            var tempname = DateTime.Now.ToLocalTime() + Path.GetExtension(file.FileName);
            var filename = tempname.Replace("/", "").Replace(" ", "").Replace(":", "");
            var fileName = Path.Combine(Request.MapPath("~/Upload"), Path.GetFileName(filename));
            try
            {
                file.SaveAs(fileName);
            }
            catch
            {
                return Content("上传异常 ！", "text/plain");
            }
            var dt = Tools.ImportExcelFile(fileName);
            if (dt == null)
                return Content("取消");
            List<User> list = new List<User>();
            for (int i = 1; i < dt.Rows.Count; i++)
            {
                var row = dt.Rows[i];
                list.Add(new User() {
                    name = row[0].ToString(),
                    pwd = row[1].ToString()
                });
                
            }
            TempData["users"] = list;
            Session["users"] = list;
            return RedirectToAction("index");
        }

4、保存下载

       public ActionResult Save()
        {
            var list = Session["users"] as List<User>;
            var ms = Tools.WriteExcel(list);
            if (ms == null)
                return Content("保存失败,数据无效");
            
            return File(ms, "application/vnd.ms-excel", "用户名单.xls");
        }
//以下这个方法只作参考，实现保存下载就用上面方法
        public ActionResult otherSave()
        {
            var list=Session["users"] as List<User>;
            if (list != null)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");

                // 第一列
                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("姓名");
                row.CreateCell(1).SetCellValue("密码");

                // 第二列后
                for (int i = 0; i < list.Count; i++)
                {
                    User user = list[i] as User;
                    IRow row2 = sheet.CreateRow(i+1);
                    row2.CreateCell(0).SetCellValue(user.name);
                    row2.CreateCell(1).SetCellValue(user.pwd);
                }
               
                // 写入到客户端  
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                book.Write(ms);
                //下面两句也可以实现导出文件功能
                //Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
                //Response.BinaryWrite(ms.ToArray());
                book = null;
              
                ms.Seek(0, SeekOrigin.Begin);
                return File(ms, "application/vnd.ms-excel", "用户名单.xls");
                //ms.Close();
                //ms.Dispose();
            }
            else
            {
                return Content("无数据");
            }
            //return Content("OK");//使用response时打开此备注
        }