MVC架构下的导出为excel的代码

  public void OutPutdata(out string filename, out string filetype, out string filePath, out long filesize, MODEL.ComplexQuery SaveQuery)
        {
            var querydal = new DAL.ComplexQuery();
            querydal.ComQueryData(ref SaveQuery);
            var dt = SaveQuery.QueryResult.Tables[0];//返回datatable表
            if (dt.Rows.Count < 1)//判断是否存在数据
                throw new Exception(@"数据不存在,请与系统管理员联系!");
            var savepath = @"D:\PIMS\PROD\ComplexQuery";
            if (!Directory.Exists(savepath))//如果不存在则创建该文件夹
                Directory.CreateDirectory(savepath);

            var xlApp = new Application();
            xlApp.DisplayAlerts = true;//是否弹出选择框
            xlApp.AlertBeforeOverwriting = true;
            var workbooks = xlApp.Workbooks;//定义excel
            var workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            var worksheet = (Worksheet)workbook.Worksheets[1];//取得sheet1
            Range range = null;//定义range
            //写入每列的列名
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                range = (Range)worksheet.Cells[1, i + 1];
                range.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中对齐
            }
            //动态定义列数
           range = xlApp.Range[worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 1, dt.Columns.Count]];

            range.HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中对齐
            var data = new object[dt.Rows.Count, dt.Columns.Count];
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                for (var n = 0; n < dt.Columns.Count; n++)
                {
                    data[i, n] = dt.Rows[i][n];
                }
            }
            range.Value2 = data;//将数据赋值到range
            workbook.Saved = true;
            var newid = "";
            var datetime = DateTime.Now.ToString("yyyyMMddhhmmss");
            //保存路径
            savepath = PubMethod.GetNewPathForDupes(savepath + "\\" + datetime + ".xls", out newid);
            workbook.SaveAs(savepath, XlFileFormat.xlExcel8, null, null, false, false,
                                         XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            xlApp.Quit();
            workbook = null;
            workbooks = null;
            worksheet = null;
            var fi = new FileInfo(savepath);
            filename = savepath;
            filetype = "xls";
            filesize = fi.Length;
            filePath = savepath;
            DAL.PROD.PIMSCommon.DataExportLogAdd(datetime, newid, "ComplexQuery", Convert.ToInt32(fi.Length), savepath,
                                                 SaveQuery.userID);
            GC.Collect();//垃圾回收
        }

 

Controller中代码:

 using (var bll = new QueryBLL.QueryClient())
                {
                    var filename = "";
                    var filetype = "";
                    var filePath = "";
                    long filesize;
                    bll.OutPutdata(out filetype, out filePath, out filesize, CDQuery);
                    jr.Data = new
                                  {
                                      iserror = false,
                                      filename = filePath.Substring(filePath.LastIndexOf(@"\") + 1),//文件名
                                      filetype = filetype,//文件类型
                                      filePath = filePath,//文件路径
                                      filesize = (filesize / long.Parse("1024")).ToString("0.00"),//文件大小
                                  };
                }