NopiHelper Excel导出 导入

先上一个Helper类：

public class NopiHelper    {        public string DataTableToExcel(DataTable tempDataTable, string path, bool is2007 = true)        {            try            {                //创建工作薄                var wk = GetWorkbook(is2007);                SetHssfWorkbook(wk, tempDataTable);                var outPath = string.Format(@"{0}.{1}", path, is2007 ? @"xlsx" : @"xls");                using (var file = new FileStream(outPath, FileMode.Create))                {                    wk.Write(file);                }                return outPath;            }            catch (Exception ex)            {                throw ex;            }        }        public MemoryStream DataTableToExcel(DataTable tempDataTable)        {            try            {                //创建工作薄                var wk = new HSSFWorkbook();                SetHssfWorkbook(wk, tempDataTable);                var file = new MemoryStream();                wk.Write(file);                return file;            }            catch (Exception ex)            {                throw ex;            }        }        public MemoryStream DataTableToExcel(DataSet ds)        {            try            {                var wk = new HSSFWorkbook();                //创建工作薄                for (int i = 0; i < ds.Tables.Count; i++)                {                    SetHssfWorkbook(wk, ds.Tables[i]);                }                var file = new MemoryStream();                wk.Write(file);                return file;            }            catch (Exception ex)            {                throw ex;            }        }        public MemoryStream DataTableToExcel_Xlsx(DataSet ds)        {            try            {                var wk = new XSSFWorkbook();                //创建工作薄                for (int i = 0; i < ds.Tables.Count; i++)                {                    SetHssfWorkbook(wk, ds.Tables[i]);                }                var file = new MemoryStream();                wk.Write(file);                return file;            }            catch (Exception ex)            {                throw ex;            }        }        public DataTable GetFromExcel(string filePath, bool isFirstRowColumn = true)        {            try            {                ISheet sheet = null;                DataTable data = new DataTable();                int startRow = 0;                var workbook = GetWorkbook(filePath);                sheet = workbook.GetSheetAt(0);                if (sheet != null)                {                    IRow firstRow = sheet.GetRow(0);                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数                    if (isFirstRowColumn)                    {                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                        {                            ICell cell = firstRow.GetCell(i);                            if (cell != null)                            {                                string cellValue = cell.StringCellValue;                                if (cellValue != null)                                {                                    DataColumn column = new DataColumn(cellValue);                                    data.Columns.Add(column);                                }                            }                        }                        startRow = sheet.FirstRowNum + 1;                    }                    else                    {                        startRow = sheet.FirstRowNum;                    }                    //最后一列的标号                    int rowCount = sheet.LastRowNum;                    for (int i = startRow; i <= rowCount; ++i)                    {                        IRow row = sheet.GetRow(i);                        if (row == null) continue; //没有数据的行默认是null　　　　　　　                        DataRow dataRow = data.NewRow();                        for (int j = row.FirstCellNum; j < cellCount; ++j)                        {                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null                                dataRow[j] = row.GetCell(j).ToString();                        }                        data.Rows.Add(dataRow);                    }                }                return data;            }            catch (Exception ex)            {                throw ex;            }        }        public DataTable GetFromExcel(Stream stream, bool isFirstRowColumn = true)        {            try            {                ISheet sheet = null;                DataTable data = new DataTable();                int startRow = 0;                var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream); // 2007 格式                sheet = workbook.GetSheetAt(0);                if (sheet != null)                {                    IRow firstRow = sheet.GetRow(0);                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数                    if (isFirstRowColumn)                    {                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                        {                            ICell cell = firstRow.GetCell(i);                            if (cell != null)                            {                                string cellValue = cell.StringCellValue;                                if (cellValue != null)                                {                                    DataColumn column = new DataColumn(cellValue);                                    data.Columns.Add(column);                                }                            }                        }                        startRow = sheet.FirstRowNum + 1;                    }                    else                    {                        startRow = sheet.FirstRowNum;                    }                    //最后一列的标号                    int rowCount = sheet.LastRowNum;                    for (int i = startRow; i <= rowCount; ++i)                    {                        IRow row = sheet.GetRow(i);                        if (row == null) continue; //没有数据的行默认是null　　　　　　　                        DataRow dataRow = data.NewRow();                        for (int j = row.FirstCellNum; j < cellCount; ++j)                        {                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null                                dataRow[j] = row.GetCell(j).ToString();                        }                        data.Rows.Add(dataRow);                    }                }                return data;            }            catch (Exception ex)            {                throw ex;            }        }        #region Private        private IWorkbook GetWorkbook(string path)        {            try            {                IWorkbook userModel;                using (var file = new FileStream(path, FileMode.Open, FileAccess.Read))                {                    try                    {                        userModel = new NPOI.XSSF.UserModel.XSSFWorkbook(file); // 2007 格式                    }                    catch (Exception)                    {                        userModel = new NPOI.HSSF.UserModel.HSSFWorkbook(file); // 2003 格式                    }                    finally                    {                        file.Close();                    }                }                return userModel;            }            catch (Exception ex)            {                throw ex;            }        }        private IWorkbook GetWorkbook(bool is2007 = true)        {            try            {                IWorkbook userModel;                if (is2007)                    userModel = new NPOI.XSSF.UserModel.XSSFWorkbook(); // 2007 格式                else                    userModel = new NPOI.HSSF.UserModel.HSSFWorkbook(); // 2003 格式                return userModel;            }            catch (Exception ex)            {                throw ex;            }        }        private void SetHssfWorkbook(IWorkbook hssfWorkbook, DataTable tempDataTable)        {            try            {                //创建一个名称为mySheet的表                var tb = hssfWorkbook.CreateSheet(tempDataTable.TableName);                //标题行                var rowhead = tb.CreateRow(0);                for (var i = 0; i < tempDataTable.Columns.Count; i++) //写入字段                {                    var cell = rowhead.CreateCell(i); //在第二行中创建单元格                    cell.SetCellValue(tempDataTable.Columns[i].Caption); //循环往第二行的单元格中添加数据                }                for (var y = 0; y < tempDataTable.Rows.Count; y++)                {                    var rowcontent = tb.CreateRow(y + 1);                    for (int i = 0; i < tempDataTable.Columns.Count; i++)                    {                        var cell = rowcontent.CreateCell(i); //在第二行中创建单元格                        cell.SetCellValue(tempDataTable.Rows[y][tempDataTable.Columns[i].ToString()].ToString());                        //循环往第二行的单元格中添加数据                    }                }            }            catch (Exception ex)            {                throw ex;            }        }        #endregion    }

一、Excel导出功能：

页面按钮：

 window.open("/User/UserExportExcel/?SearchPhone=" + SearchPhone);

控制器代码：

/// <summary>        /// 导出用户列表        /// </summary>        /// <param name="collection"></param>        /// <returns></returns>        public ActionResult UserExportExcel(FormCollection collection)        {            try            {                var searchEntity = GetSelect();                var listData = new UserBll().GetList(searchEntity).OrderByDescending(c => c.Id).ToList();                DataSet ds = new DataSet();                var dt = new DataTable();                dt.Columns.Add("序号");                dt.Columns.Add("编号");                dt.Columns.Add("用户名");                var i = 1;                if (listData.Count > 20000)                {                    DataRow dr = dt.NewRow();                    dr["序号"] = i++;                    dr["编号"] = "数据过多请缩小时间范围，分批导出";                    dr["用户名"] = "数据过多请缩小时间范围，分批导出";                    dt.Rows.Add(dr);                }                else                {                    listData.ForEach(entity =>                    {                        DataRow dr = dt.NewRow();                        dr["序号"] = i++;                        dr["编号"] = entity.No;                        dr["用户名"] = entity.Name;                        dt.Rows.Add(dr);                    });                }                dt.TableName = "用户列表_" + ConfigInfo.CurrrentDateTime.ToString("yyyyMMdd") + "_" + (1).ToString().PadLeft(4, '0') + ".xlsx";                ds.Tables.Add(dt);                string title = "用户列表（" + ConfigInfo.CurrrentDateTime.ToString("yyyyMMdd") + "）" + ".xlsx";                var stream = new NopiHelper().DataTableToExcel_Xlsx(ds);                return File(stream.ToArray(), "application/ms-excel", title);            }            catch (Exception ex)            {                throw;            }        }

二、Excel导入功能：

1 、页面代码：

<input type="file" id="ExcelFile" accept=".csv,.xlsx" />            <input type="button" value="上传" id="btn_UploadExcel" />            <table id="table_body" cellpadding="5" cellspacing="0" width="100%" align="center" class="grid" border="0">                <thead>                    <tr>                        <th>序号</th>                        <th>真实姓名</th>                        <th>手机号</th>                        <th>备注信息</th>                    </tr>                </thead>                <tbody></tbody>            </table>            <input type="hidden" name="json" id="json" value="" />

<input type="button" value="发送XX" id="btn_SendAddInterestActivity" />

2、上传按钮JS：

//上传数据            $("#btn_UploadExcel").click(function () {                var obj = document.getElementById("ExcelFile");                var formdata = new FormData();                var fileObj = obj.files;                for (var i = 0; i < fileObj.length; i++)                    formdata.append("file" + i, fileObj[i]);                $.ajax({                    type: 'POST',                    url: '/AddInterestActivity/UploadExcel',                    data: formdata,                    contentType: false,                    processData: false                }).then(function (data) {                    if (!isEmpty(data)) {                        var html = "";                        $.each(data, function (index, value) {                            html += "<tr>";                            html += "<td>" + value["序号"] + "</td>";                            html += "<td>" + value["真实姓名"] + "</td>";                            html += "<td>" + value["手机号"] + "</td>";                            html += "<td>" + value["备注"] + "</td>";                            html += "</tr>";                        });                        $("#table_body tbody").html(html);                    }                    else {                        alert("数据类型错误");                    }                }, function () {                    //failCal                });            });

收集数据保存：

 //发送            $("#btn_SendAddInterestActivity").click(function () {                                    $.messager.confirm("确认", "确定执行此操作", function (r) {                        var tbody = $("#table_body tbody");                        var trList = tbody.children("tr")                        var json = [];                        for (var i = 0; i < trList.length; i++) {                            var tdArr = trList.eq(i).find("td");                            var RealName = tdArr.eq(1).html();//手机号                            var UserPhone = tdArr.eq(2).html();//手机号                            var Remark = tdArr.eq(3).html();//备注                            if (UserPhone.indexOf("不合格") > 0 || RealName.indexOf("不合格") > 0) {                                alert("不合格");                                $("#submitloading").hide();                                return false;                            }                            json.push({ "UserPhone": UserPhone, "RealName": RealName, "Remark": Remark });                        }                        $("#json").val(JSON.stringify(json));                        //alert("发送红包");                        var data = { json: $("#json").val(), activityId: $("#Id").val() };                        $.ajax({                            type: 'POST',                            url: '/AddInterestActivity/SendAddInterestActivity',                            data: data,                            success: function (msg) {                                if (msg.ResultType == 0) {                                    alert(msg.Message);                                    tbody.html("");                                }                                else {                                    alert("发送失败");                                }                            }                        });                    });            });

3、上传控制器：

public ActionResult UploadExcel()        {            HttpFileCollectionBase fileToUpload = Request.Files;            foreach (string file in fileToUpload)            {                var curFile = Request.Files[file];                if (curFile.ContentLength < 1) continue;                string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(curFile.FileName);                if (fileName.Substring((fileName.IndexOf(".") + 1)) == "xls" || fileName.Substring((fileName.IndexOf(".") + 1)) == "xlsx" || fileName.Substring((fileName.IndexOf(".") + 1)) == "csv")                {                    DataTable dt = new NopiHelper().GetFromExcel(curFile.InputStream);                    if (!dt.Columns.Contains("手机号"))                    {                        return null;                    }                    for (int i = 0; i < dt.Rows.Count; i++)                    {                        //验证“手机号”                        var toName = dt.Rows[i]["手机号"].ToString();                        var operationResult_ToUser = new UserBll().GetByPhone(toName);                        if (operationResult_ToUser.ResultType != OperationResultType.Success)                        {                            dt.Rows[i]["手机号"] += " <span style='color:red'>（不合格）</span>";                        }                        ////验证“交易金额”                        //var money = dt.Rows[i]["交易金额"].ToString();                        //if (string.IsNullOrEmpty(money) || money == "0")                        //{                        //    dt.Rows[i]["交易金额"] += " <span style='color:red'>（不合格）</span>";                        //}                    }                    string json = Newtonsoft.Json.JsonConvert.SerializeObject(dt);                    return Content(json, "text/json");                }            }            return null;        }

[HttpPost]        public ActionResult SendAddInterestActivity(string json, int activityId)        {            try            {                //var importEndTime = Convert.ToDateTime(ImportEndTime);                if (json == "[]" || activityId == 0)                {                    return Json(new OperationResult(OperationResultType.Success, @"上传文件错误，或活动不存在"));                }                //执行保存代码                return Json(new OperationResult(OperationResultType.Success, @"保存成功"));            }            catch (Exception ex)            {                throw;            }        }