asp.net mvc 中使用NPOI导出excel

版本信息：NPOI1.2.5（2.0以上的版本很多方法不清楚）

明确三点：

path：

         mvc 部署网站的时候，我们肯定要拷贝的一个文件夹就mvc的UI层，有点可以肯定的是，你部署网站的路径不一定都是一样的；假如我们在UI层新建一个Excel文件夹，单独存放到处的excel的文件；这样，我们就绝对不能使用绝对路径，excel 要是相对路；

写在Controller里面：

string path = System.Web.HttpContext.Current.Server.MapPath("~/Excel");  //获取Excel的虚拟路径

DataTable:

            将Data从数据库中以DataTable的类型保存在内存中，datatable以及dataset ，是我们用来保存数据库表某个表或者表的集合结构的，

string connectionString = ConfigurationManager.ConnectionStrings["连接字符串name"].ConnectionString;

            string sql = "select * from UserInfo";
            try
            {
                using (SqlDataAdapter apter = new SqlDataAdapter(sql, connectionString))
                {
                    apter.SelectCommand.CommandType = CommanType.Text;

                    DataTable dt = new DataTable();
                    dt.TableName = "操作日志";
                    apter.Fill(dt);
                }
            }

NPOI:

         注意版本信息，命名空间和方法

引入DLL文件后，在类中添加命名空间

using NPOI.SS.UserModel
using NPOI.HSSF.UserModel

使用方法：

public void WriteExcel(DataTable dt, string filePath)
        {
            if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count >= 0)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);

                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]));
                    }
                }
                // 写入到客户端  
                using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                {
                    book.Write(ms);
                    using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    book = null;
                }
            }

 

 

完整代码：

 

string path = System.Web.HttpContext.Current.Server.MapPath("~/Excel"); //相对路径  （在Controller里面使用，把path作为参数）

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <returns></returns>
        public ReturnModel<bool> GetOutPutExcel(string path, string username)
        {
            ReturnModel<bool> result = new ReturnModel<bool>();
            bool right = false;

            string connectionString = ConfigurationManager.ConnectionStrings["CloudBoxForm"].ConnectionString;

            string sql = "select  OperateTime,OperateUser ,OperateAction,OperateStatus  from OperateLog where DataStatus=1";
            string dateStr = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss");
            string filepath = string.Format("{0}\\{1}_{2}操作日志.xls", path,username, dateStr);
            string fileexcelname = Path.GetFileName(filepath);
            try
            {
                using (SqlDataAdapter apter = new SqlDataAdapter(sql, connectionString))
                {
                    apter.SelectCommand.CommandType = CommanType.Text;

                    DataTable dt = new DataTable();
                    dt.TableName = "操作日志";
                    apter.Fill(dt);

                    //开始创建
                    WriteExcel(dt, filepath);
                    result.Message = fileexcelname;
                    right = true;
                }
            }
            catch
            {
                right = false;
            }
            result.Data = right;
            return result;
        }




public void WriteExcel(DataTable dt, string filePath)
        {
            if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count >= 0)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);

                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]));
                    }
                }
                // 写入到客户端  
                using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                {
                    book.Write(ms);
                    using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    book = null;
                }
            }