前端利用Post方式与后台交互导出Excel-白红宇

前端利用Post方式与后台交互导出Excel

阅读量：6192 次

发布时间：2019-06-21

本文共 6620 字，大约阅读时间需要 22 分钟。

最近做了一个功能，将页面查询的表格数据导出到excel表格中，利用NOPI的方法做导出功能。

1.下载NOPI.dll并引用到项目中。

2.前端页面的导出方法：

//导出    function btnExport() {        var colArr = [];        //这里写获取页面表格字段的显示名称，并存到colArr数组里面，为了方便在后台循环渲染。        if(colArr.length == 0){            alert("请查询要导出的数据！");            return;       }        //这里是要传到后台查询数据的查询条件，根据自己所要传的参数进行修改，ColNames这个传上面获取字段显示名称的数组        var searchdata = {            AreaNO: $('#chooseBox').val(),            StartTime: sDateResult,            EndTime: sDateResult,            ColNames: colArr        };         //QueryType可以自己定义，后台会根据这个进行判断导出对应的页面数据        var postData = { QueryType: 1, FileName: "你想要导出的文件名称", QueryParms: JSON.stringify(searchdata) };        $.post("/Export/ExcelExport", postData, function (data) {            if (data) {                location.href = '/Export/GetExcelFile?download=' + data; //根据后台返回文件名称进行下载            }        });    }

3.后台的对应的方法：

public class ExportController : Controller    {        ///         /// 下载Excel文件        /// 
        ///         /// 
             public FileResult GetExcelFile(string download)        {            FileStream fs = (FileStream)Session[download];            Session.Remove(download);            return File(fs, "application/vnd.ms-excel", download);        }        ///         /// 导出Excel表格        /// 
        ///         ///         ///         /// 
             public string ExcelExport(int queryType, string fileName, string queryParms)        {            QueryParmsObj o = new QueryParmsObj();            QueryParmsObj request = (QueryParmsObj)JsonToObject(queryParms, o);            System.IO.FileStream fs;            fileName = fileName + "_导出数据_" + DateTime.Now.ToString("yyyyMMddHHmmss");            string download = string.Format(@"{0}.xlsx", fileName);            try            {                //创建Excel文件的对象                IWorkbook workbook = new HSSFWorkbook();                //根据不同的类型导出不同的报表                if (queryType == 1)                 {                    //这里通过参数查询获取要导出的数据                    var otherController = DependencyResolver.Current.GetService
     
      ();                    var response = otherController.FinancialAnalysisMConerlistData(request.AreaNO,request.StartTime,request.EndTime,null,null,null,null);                    JObject list = (JObject)JsonConvert.DeserializeObject(response);                    GetRowsOfType1(workbook, list, fileName,request.ColNames);                }                else if (queryType == 2)                {                }                string pathString = Request.ApplicationPath;                var mappath = Server.MapPath(pathString);                var domainPath = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();                if (!Directory.Exists(string.Format("{0}\\Report", domainPath)))                {                    Directory.CreateDirectory(string.Format("{0}\\Report", domainPath));                }                string filePath = String.Format(@"{0}\\Report\\{1}_{2}.xlsx", domainPath, fileName, DateTime.Now.ToString("yyyyMMddHHmmss"));                // 写入到客户端                fs = new FileStream(filePath, FileMode.OpenOrCreate);                workbook.Write(fs);                fs.Seek(0, SeekOrigin.Begin);            }            catch (Exception)            {                throw new Exception();            }            Session[download] = fs;            return download;        }        /// 
              /// 导出格式和数据渲染        /// 
        /// 
              /// 
              /// 
              /// 
              /// 
              private IWorkbook GetRowsOfType1(IWorkbook workbook, JObject list, string name,List
      
        colNames)        {            //添加一个sheet            ISheet sheet1 = workbook.CreateSheet("Sheet1");            int colSpan = colNames.Count-1;            SetSheetTitle(workbook, sheet1, name,colSpan);            IRow row2 = sheet1.CreateRow(2);            for (var i = 0; i < colNames.Count; i++)            {                row2.CreateCell(i).SetCellValue(colNames[i]);            }            //将数据逐步写入sheet1各个行            var listData = list["data"];            for (int i = 0; i < listData.ToList().Count; i++)            {                IRow rowtemp = sheet1.CreateRow(i + 3);                var cellVal = "";                for (int j = 0; j < listData[i].ToList().Count; j++)                {                    cellVal = listData[i].ToList()[j].First.ToString();                    rowtemp.CreateCell(j).SetCellValue(cellVal);                }            }            return workbook;        }        /// 
               /// 设置导出Excel标题        /// 
        /// 
               /// 
               /// 
               private void SetSheetTitle(IWorkbook workbook, ISheet sheet1, string name,int colSpan)        {            //给sheet1添加第一行的头部标题            IRow row0 = sheet1.CreateRow(0);            row0.HeightInPoints = 30;//行高            row0.CreateCell(0).SetCellValue(name);//创建第一列            IFont font = workbook.CreateFont(); //创建一个字体样式对象            font.FontHeightInPoints = 17;//字体大小            font.Boldweight = short.MaxValue;//字体加粗            ICellStyle style = workbook.CreateCellStyle();//创建样式对象            style.SetFont(font); //将字体样式赋给样式对象            style.Alignment = HorizontalAlignment.Center;            row0.Cells[0].CellStyle = style; ; //把样式赋给单元格            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, colSpan);            sheet1.AddMergedRegion(cellRangeAddress);            IRow row1 = sheet1.CreateRow(1);            row1.HeightInPoints = 20;//行高            row1.CreateCell(0).SetCellValue("制表日期：" + DateTime.Now.Date.ToString("yyyy-MM-dd"));            font = workbook.CreateFont(); //创建一个字体样式对象            font.FontHeightInPoints = 13;//字体大小            font.Boldweight = short.MaxValue;//字体加粗            style = workbook.CreateCellStyle();//创建样式对象            style.SetFont(font);            row1.Cells[0].CellStyle = style;            cellRangeAddress = new CellRangeAddress(1, 1, 0, colSpan);            sheet1.AddMergedRegion(cellRangeAddress);        }        /// 把Json文本转为实体        public static object JsonToObject(string jsonString, object obj)        {            return JsonConvert.DeserializeObject(jsonString, obj.GetType());        }        /// 
               /// 查询参数实体        /// 
        private class QueryParmsObj        {            public string AreaNO { get; set; }            public string StartTime { get; set; }            public string EndTime { get; set; }            public List
       
         ColNames { get; set; }        }    }