C#datatable导出Excel的Demo

2019独角兽企业重金招聘Python工程师标准>>>

#region 导出申请列表到Excel
        public ActionResult ExportApplyList()
        {
            //处理经销商id和名称模糊筛选
            var dealerid = 0;
            var dealerName = "";
            var dealer =HttpContext.Current.Request.Form[dealer];
            if (!string.IsNullOrEmpty(dealer))
            {
                if (!int.TryParse(dealer, out dealerid))
                    dealerName = dealer;
            }
            var count = 0;
            var dt = WithdrawalApplyBll.GetInstance().GetDealerCushApplyByPage(page, pagesize, provinceId, cityId, accountType, createBeginTime,
                createEndTime, payStartTime, payEndTime, dealerid, dealerName, isChange, status, out count);

            //options是前台拼接过来的字符串，用处是用户选择导出的列（row）
            //options格式为"表格列名-DataRow|"
            //demo: "经销商ID-DealerID|经销商名称-DealerFullName|"
            string options = HttpContext.Current.Request.Form("options");
            
            if (string.IsNullOrEmpty(options))
                return null;
            var oArray = options.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
            var map = new Dictionary<string, string>() { };
            foreach (var str in oArray)
            {
                var temp = str.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
                if (temp.Length >= 2)
                    //map{"列名","SQL查出的字段名"}
                    map.Add(temp[0], temp[1]);
            }

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet  
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("经销商提现申请");
            //给sheet1添加第一行的头部标题  
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            int nIndex = 0;
            foreach (string key in map.Keys)
            {
                NPOI.SS.UserModel.ICell cell = row1.CreateCell(nIndex);
                cell.SetCellValue(key);
                nIndex++;
            }
            //将数据逐步写入sheet1各个行  
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                for (int j = 0; j < map.Keys.Count; j++)
                {
                    var _name = map[row1.GetCell(j).StringCellValue];
                    //把状态字段替换成文字，也可以自己定义枚举等
                    if ( _name == "Status")
                        rowtemp.CreateCell(j).SetCellValue(GetApplyReplaceTxt(dt.Rows[i], _name));
                    else
                        rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][map[row1.GetCell(j).StringCellValue]].ToString().Trim());
                }
            }
            string strdate = DateTime.Now.ToString("yyyyMMddhhmmss");//获取当前时间  
            //写入到客户端
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", "经销商奖励信息：" + strdate + ".xls");
        }




        //只是一个把数字替换成文字的方法，也可以写枚举 取枚举值
        private string GetApplyReplaceTxt(DataRow dr, string name)
        {
            var resultTxt = string.Empty;
            switch (name)
            {
                case "AccountType":
                    switch (EConvert.ToInt(dr["AccountType"], 0))
                    {
                        case 0:
                            resultTxt = "个人账户";
                            break;
                        case 1:
                            resultTxt = "对公账户";
                            break;
                        default:
                            resultTxt = "";
                            break;
                    }

                    break;
                case "Status":
                    switch (EConvert.ToInt(dr["Status"], 0))
                    {

                        case -1:
                            resultTxt = "已驳回";
                            break;
                        case 0:
                            resultTxt = "未审核";
                            break;
                        case 1:
                            resultTxt = "已审核";
                            break;
                        case 2:
                            resultTxt = "支付完成";
                            break;
                        case 9:
                            resultTxt = "支付失败";
                            break;
                        default:
                            resultTxt = "";
                            break;
                    }
                    break;
            }
            return resultTxt;
        }
        #endregion

 

转载于:https://my.oschina.net/CrazyBoy1024/blog/1570629