.NET项目笔记——使用NPOI读取Excel导入数据和导出Excel的功能

前提：由于有差不多两年时间没有进行B/S项目开发了，换了新工作，项目中要求有Excel导入数据库的功能，故保存下来供以后查看。

一、使用jQuery的uploadify插件完成选择文件并上传的功能：

（1）先引入相关文件：

<script src="../Scripts/uploadify/swfobject.js" type="text/javascript"></script>
<link href="../Scripts/uploadify/uploadify.css" rel="stylesheet" type="text/css" />
<script src="../Scripts/uploadify/jquery.uploadify.min.js" type="text/javascript"></script>

（2）然后界面中使用file表单控件：

<input type="file" name="file_upload" id="file_upload" style="background-color: White;" />
<input type="hidden" id="filename" />
<a href="javascript:void(0)" onclick="ImportExcel()">导入</a>

说明：hidden控件此处是为了暂存上传后的文件路径，供导入操作时使用。

（3）在加载函数中添加如下代码，使file控件采用uploadify样式：

$(function () {
　　$("#file_upload").uploadify({
　　　　//指定swf文件
       'swf': '/Scripts/uploadify/uploadify.swf',
       //后台处理的页面（稍后添加说明）
       'uploader': 'ImportHandler.ashx',
       //按钮显示的文字
       'buttonText': '选择导入文件',
       //显示的高度和宽度，默认 height 30；width 120
       //'height': 15,
       //'width': 80,
       //上传文件的类型  默认为所有文件    'All Files'  ;  '*.*'
       //在浏览窗口底部的文件类型下拉菜单中显示的文本
       'fileTypeDesc': 'All Files',
       //允许上传的文件后缀
       'fileTypeExts': '*.xls; *.xlsx',
       //发送给后台的其他参数通过formData指定
       'formData': { 'someVal': "123", 'otherVal': "123"},
       //上传文件页面中，你想要用来作为文件队列的元素的id, 默认为false  自动生成,  不带#
       //'queueID': 'fileQueue',
       //选择文件后自动上传
       'auto': true,
       //设置为true将允许多文件上传
       'multi': false,
       onUploadSuccess: function (file, data, response) {
       　　if (data) {
          　　alert("上传成功");
　　　　　　　　$("#filename").val(data);
          }
          else {
             alert("上传失败");
          }
　　　　}
　　});
});

file控件在页面中显示如右图所示：。

说明：

• 使用uploadify的原因是因为file控件的原样式（如图：）不美观，且不方便前端工程师设计样式；
• 之前使用过uploadify实现上传图片并预览的功能，只需修改加载函数中的相关参数，并将返回图片的url地址赋值给<img>标签的src属性即可。

（4）在项目中添加“一般用户处理程序”：ImportHandler.ashx（名字自定义，加载函数中自行修改'uploader'属性即可）

　　/// <summary>
    /// ImportHandler 的摘要说明
    /// </summary>
    public class ImportHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            //接收上传后的文件
            HttpPostedFile file = context.Request.Files["Filedata"];
            //其他参数
            string someVal = context.Request["someVal"];
            string otherVal = context.Request["otherVal"];
            //获取文件的保存路径
            string uploadPath =
                HttpContext.Current.Server.MapPath("/ImportUserFiles" + "\\");
            //判断上传的文件是否为空
            if (file != null)
            {
                if (!Directory.Exists(uploadPath))
                {
                    Directory.CreateDirectory(uploadPath);
                }
                //保存文件
                string tmpStr = DateTime.Now.ToString("yyMMddHHmmss") + file.FileName;
                file.SaveAs(uploadPath + tmpStr);
                string fullFileName = uploadPath + tmpStr;
                context.Response.Write(fullFileName);
            }
            else
            {
                context.Response.Write("");
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }

这样就完成了文件的选择上传功能了，接下来就可以使用返回的文件保存路径的url值进行Excel的读取导入操作了。

 

二、使用NPOI实现读取Excel数据到DataTable

（1）在第一部分的第（2）小点添加了导入按钮，先完成js函数ImportExcel（）的代码：

　　　　  //导入
        function ImportExcel() {var fileName = $("#filename").val();
            if (fileName == "") {
                alert("请选择Excel文件！");
                return;
            }
            else {
                var extension = fileName.substring(fileName.lastIndexOf('.') + 1);
                if (extension != "xlsx" && extension != "xls") {
                    alert("上传的文件不是Excel文件，请重试！");
                    return;
                }
            }
            $.post("?Action=ImportExcel", { FileName: fileName}, function (data) {
                if (data) {
                    var result = eval("(" + data + ")");
                    $("#showFileName").html(result.Message);
                }
            });
        }

（2）因为使用的是ajax post方式，在后台获取FileName等参数后，使用NPOI读取Excel数据：

　　　　private void ActionInit()
        {
            string action = "";
            if (!string.IsNullOrEmpty(Request.QueryString["Action"]))//获取form的Action中的参数 
            {
                action = Request.QueryString["Action"].Trim().ToLower();//去掉空格并变小写 
            }
            else
            {
                return;
            }
            string message = "";
            switch (action)
            {case "importexcel":
                    string fileName = Request.Form["FileName"];if (!string.IsNullOrEmpty(fileName))
                    {
                        message = ImportExcel(fileName);
                    }
                    else
                    {
                        message = "未获取到文件信息，已停止导入！";
                    }
                    Response.Write(JsonHelper.EncodeJson(new { Message = message }));
                    Response.End();
                    break;
                default:
                    Response.Write("");
                    Response.End();
                    break;
            }
        }

注：上面代码中的ImportExcel()方法，就是具体的导入Excel的操作，下面只写出利用该方法中调用的利用NPOI获取Excel数据到DataTable的方法。
（3）利用NPOI获取Excel数据到DataTable：

　　　　　/// <summary>
        /// 利用NPOI获取Excel数据到DataTable中
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        private DataTable GetDataByNPOI(string filepath, out string ErrorMsg)
        {
            ErrorMsg = "";
            try
            {
                IWorkbook workbook = null;
                string fileExt = Path.GetExtension(filepath);
                using (var file = new FileStream(filepath, FileMode.Open, FileAccess.Read))
                {
                    if (fileExt == ".xls")
                        workbook = new HSSFWorkbook(file);
                    else if (fileExt == ".xlsx")
                        workbook = new XSSFWorkbook(file);
                }
                //获取sheet页
                var sheet = workbook.GetSheetAt(0);
                //获取总条数
                int RowCount = sheet.LastRowNum;
                //获取sheet页的第一条数据
                IRow firstRow = sheet.GetRow(0);
                //获取总列数
                int CellCount = firstRow.LastCellNum;

                DataTable dt = new DataTable();
                if (CellCount < 4)
                {
                    ErrorMsg = "导入Excel格式与模板不符，请核查！";
                    return null;
                }
                CellCount = 4;
                string[] columnStr = new string[] { "序号", "姓名", "手机", "邮箱" };
                for (int j = 0; j < CellCount; j++)
                {
                    string value = firstRow.GetCell(j).StringCellValue;
                    if (!value.Equals(columnStr[j]))
                    {
                        ErrorMsg = "导入Excel格式与模板不符，请核查！";
                        return null;
                    }
                    DataColumn dc = new DataColumn(value, typeof(String));
                    dt.Columns.Add(dc);
                }
                bool hasValue = true;//记录该行的姓名列是否有值，没有则不读取
                IRow row;
                DataRow dr;
                object obj;
                for (int i = 1; i <= RowCount; i++)
                {
                    hasValue = true;
                    row = sheet.GetRow(i);
                    dr = dt.NewRow();
                    for (int j = 0; j < CellCount; j++)
                    {
                        obj = row.GetCell(j);
                        if (obj != null)
                        {
                            //校验姓名列是否为空
                            if (j == 1 && string.IsNullOrEmpty(obj.ToString()))
                            {
                                hasValue = false;
                            }
                            dr[j] = obj.ToString();
                        }
                        else
                        {
                            dr[j] = "";
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                    if (dt.Rows.Count > 100)
                    {
                        ErrorMsg = "一次最多导入100条用户信息，请核查！";
                        return null;
                    }
                }
                return dt;
            }
            catch (Exception ex)
            {
                ErrorMsg = "读取Excel数据失败，请核查！\n描述：" + ex.Message;
                return null;
            }
        }

说明：使用NPOI中的IWorkbook需要添加这些引用：

 

三、使用NPOI导出数据到Excel中（两种方式）

（1）第一种方式：先将生成的Excel文件保存到服务器，然后再返回其完成路径下载文件。

　　　　 /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="listUser"></param>
        /// <param name="fileName"></param>
        private string ExportExcel(IList<User> listUser, string fileName)
        {
            try
            {
                HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();//创建工作簿

                string title= Common.Common.ConvertToLegalText(fileName);
                ISheet sheet = book.CreateSheet(title);//创建一个名为 title的表
                IRow headerrow = sheet.CreateRow(0);//创建一行，此行为第一行           
                ICellStyle style = book.CreateCellStyle();//创建表格样式
                style.Alignment = HorizontalAlignment.Center;//水平对齐方式
                style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐方式
                //给 sheet 添加第一行的头部标题          
                headerrow.CreateCell(0).SetCellValue("序号");
                headerrow.CreateCell(1).SetCellValue("姓名");
                headerrow.CreateCell(2).SetCellValue("用户名");
                headerrow.CreateCell(3).SetCellValue("手机");
                headerrow.CreateCell(4).SetCellValue("邮箱");
                for (int i = 0; i < listUser.Count; i++)
                {
                    IRow row = sheet.CreateRow(i + 1);               //新创建一行
                    ICell cell = row.CreateCell(i);         //在新创建的一行中创建单元格
                    cell.CellStyle = style;        //设置单元格格式
                    row.CreateCell(0).SetCellValue(i + 1);        //给单元格赋值
                    row.CreateCell(1).SetCellValue(listUser[i].TrueName);
                    row.CreateCell(2).SetCellValue(listUser[i].UserName);
                    row.CreateCell(3).SetCellValue(listUser[i].Telephone);
                    row.CreateCell(4).SetCellValue(listUser[i].Email);
                }
                string uploadPath = Server.MapPath("/此处填写存储的文件夹" + "\\");
                if (File.Exists(uploadPath + fileName))
                {
                    File.Delete(uploadPath + fileName);
                }
                FileStream fs = new FileStream(uploadPath + fileName, FileMode.Create, FileAccess.ReadWrite);
                book.Write(fs);
                book = null;
                fs.Close();
                fs.Dispose();
                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

（2）第二种方式：Ajax方式输出Excel文件

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="listUser"></param>
        /// <param name="fileName"></param>
        private string ExportExcel(IList<User> listUser, string fileName)
        {
            try
            {
                HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();//创建工作簿
                string title= Common.Common.ConvertToLegalText(fileName);
                ISheet sheet = book.CreateSheet(title);//创建一个名为 title的表
                IRow headerrow = sheet.CreateRow(0);//创建一行，此行为第一行           
                ICellStyle style = book.CreateCellStyle();//创建表格样式
                style.Alignment = HorizontalAlignment.Center;//水平对齐方式
                style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐方式
                //给 sheet 添加第一行的头部标题          
                headerrow.CreateCell(0).SetCellValue("序号");
                headerrow.CreateCell(1).SetCellValue("姓名");
                headerrow.CreateCell(2).SetCellValue("用户名");
                headerrow.CreateCell(3).SetCellValue("手机");
                headerrow.CreateCell(4).SetCellValue("邮箱");
                for (int i = 0; i < listUser.Count; i++)
                {
                    IRow row = sheet.CreateRow(i + 1);               //新创建一行
                    ICell cell = row.CreateCell(i);         //在新创建的一行中创建单元格
                    cell.CellStyle = style;        //设置单元格格式
                    row.CreateCell(0).SetCellValue(i + 1);        //给单元格赋值
                    row.CreateCell(1).SetCellValue(listUser[i].TrueName);
                    row.CreateCell(2).SetCellValue(listUser[i].UserName);
                    row.CreateCell(3).SetCellValue(listUser[i].Telephone);
                    row.CreateCell(4).SetCellValue(listUser[i].Email);
                }
                MemoryStream ms = new MemoryStream();
            　　book.Write(ms);
            　　/* 
             　　* （1）转换成UTF8支持中文。
             　　* （2）HttpUtility.UrlEncode 
             　　* 在 Encode 的时候, 将空格转换成加号('+'), 
             　　* 在 Decode 的时候将加号转为空格, 
             　　* 但是浏览器是不能理解加号为空格的, 所以如果文件名包含了空格, 在浏览器下载得到的文件, 空格就变成了加号
             　　* 这里将+转换为“%20”
            　　*/
            　　Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls",
                　　HttpUtility.UrlEncode(title, System.Text.Encoding.UTF8).Replace("+", "%20").Replace("%27", "'")));
            　　Response.BinaryWrite(ms.ToArray());
                book = null;
                fs.Close();
                fs.Dispose();
　　　　　　　　　Response.End();
                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }