MVC实现datatable导出到excel

由于使用后台分页，所以datatables的导出excel只能导出当前页，所以只好重新写一个导出方法 

html代码
<div class="btn btn-primary" onclick="download()">下载</div>

 

js 代码 
   function download() {
        var EquipmentNo = $("#txtEquipmentNo").val();
        var OrganizationID = $("#txtOrganizationID").val();

             $.ajax({
                    type: 'POST',
                    url: '@Url.Action("GetMultiGNDFile")',
                            //contentType: "application/json; charset=utf-8",
                    data: {
                        OrganizationID: OrganizationID,
                        EquipmentNo: EquipmentNo,
                        dateFrom: dateFrom,
                        dateTo: dateTo,
                        StartIndex: 1,
                        PageSize: 100000,//最大只能是10万行
                    },
                    async: false,
                    success: function (result) { 
                            console.log(result);

                            window.location = '/DataAnalysis/Download?fileName=' + result;
  

                    },
                    error: function (XMLHttpRequest, textStatus, errorThrown) {
                        //$.dialog.alert("查询失败");
                        //$wrapper.spinModal(false);
                    }

             });
 
    }

 

controller层
   
     public ActionResult Download(string fileName)
        { 
            return File(fileName, "application/vnd.ms-excel", "welcome.xlsx");
        }

        [HttpPost]
        public JsonResult GetMultiGNDFile(QueryModels qm)
        { 

            string ExcelFileName = Server.MapPath("~/WorkFolder/Excel/") + Guid.NewGuid().ToString() + ".xlsx";

            try
            { 
                DataSet ds = com.aaa.DAL.DataAnalysis.MultiGND.GetMultiGND1(qm);

                ExcelHelper.DataTableToExcel(ExcelFileName, ds.Tables[0], "sheet", true);

                return Json(ExcelFileName);
            }
            catch (Exception ex)
            { 
                return Json(ExcelFileName);
            }
        }

数据查询代码
     
       public static DataSet GetMultiGND1(QueryModels qm)
            {
                string sqlstr = @"select  EquipmentNo,Status,ROUND(StandResistance,2) AS StandResistance ,
                                          ROUND(Resistance1,2) AS Resistance1,ROUND(Resistance2,2) AS Resistance2,
                                          ROUND(Resistance3,2) AS Resistance3,ROUND(Resistance4,2) AS Resistance4,
                                          ROUND(Resistance5,2) AS Resistance5,ROUND(Resistance6,2) AS Resistance6,
                                          ROUND(Resistance7,2) AS Resistance7,ROUND(Resistance8,2) AS Resistance8,  CreateDate
                                    from multignd
                                        where EquipmentNo LIKE @EquipmentNo and CreateDate between @dateFrom and @dateTo AND
                                              EquipmentNo IN (SELECT EquipmentNo FROM equipment WHERE  EquipmentType='MultiGND' AND OrganizationID LIKE @OrganizationID )
                                    LIMIT @StartIndex,@PageSize;
                                select  count(1) as Total
                                    from multignd
                                        where EquipmentNo LIKE @EquipmentNo and CreateDate between @dateFrom and @dateTo AND
                                              EquipmentNo IN (SELECT EquipmentNo FROM equipment WHERE  EquipmentType='MultiGND' AND OrganizationID LIKE @OrganizationID );";

                MySqlParameter[] sqlParameter =  { 
                    new MySqlParameter("EquipmentNo",qm.EquipmentNo+"%"),
                    new MySqlParameter("dateFrom",qm.dateFrom),
                    new MySqlParameter("dateTo",qm.dateTo),
                    new MySqlParameter("OrganizationID",qm.OrganizationID+"%"),
                    new MySqlParameter("StartIndex",qm.StartIndex),
                    new MySqlParameter("PageSize",qm.PageSize),
            };

                DataSet ds = MySqlHelper_.ExecuteDataSet(sqlstr, sqlParameter);

                return ds;
            }

 

 

datatable导出excel代码

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace com.aaa.Helper.NPOIExcel
{
    /// <summary>
    /// Excel文件和DataTable之间转换帮助类
    /// </summary>
    public class ExcelHelper : IDisposable
    {

        /// <summary>
        /// 把DataTable的数据写入到指定的excel文件中
        /// </summary>
        /// <param name="TargetFileNamePath">目标文件excel的路径</param>
        /// <param name="sourceData">要写入的数据</param>
        /// <param name="sheetName">excel表中的sheet的名称，可以根据情况自己起</param>
        /// <param name="IsWriteColumnName">是否写入DataTable的列名称</param>
        /// <returns>返回写入的行数</returns>
        public static int DataTableToExcel(string TargetFileNamePath, DataTable sourceData, string sheetName, bool IsWriteColumnName)
        {

            //数据验证
            //if (!File.Exists(TargetFileNamePath))
            //{
            //    //excel文件的路径不存在
            //    throw new ArgumentException("excel文件的路径不存在或者excel文件没有创建好");
            //}
            if (sourceData == null)
            {
                throw new ArgumentException("要写入的DataTable不能为空");
            }

            if (sheetName == null && sheetName.Length == 0)
            {
                throw new ArgumentException("excel中的sheet名称不能为空或者不能为空字符串");
            }



            //根据Excel文件的后缀名创建对应的workbook
            IWorkbook workbook = null;
            if (TargetFileNamePath.IndexOf(".xlsx") > 0)
            {  //2007版本的excel
                workbook = new XSSFWorkbook();
            }
            else if (TargetFileNamePath.IndexOf(".xls") > 0) //2003版本的excel
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                return -1;    //都不匹配或者传入的文件根本就不是excel文件，直接返回
            }



            //excel表的sheet名
            ISheet sheet = workbook.CreateSheet(sheetName);
            if (sheet == null) return -1;   //无法创建sheet，则直接返回


            //写入Excel的行数
            int WriteRowCount = 0;



            //指明需要写入列名，则写入DataTable的列名,第一行写入列名
            if (IsWriteColumnName)
            {
                //sheet表创建新的一行,即第一行
                IRow ColumnNameRow = sheet.CreateRow(0); //0下标代表第一行
                //进行写入DataTable的列名
                for (int colunmNameIndex = 0; colunmNameIndex < sourceData.Columns.Count; colunmNameIndex++)
                {
                    ColumnNameRow.CreateCell(colunmNameIndex).SetCellValue(sourceData.Columns[colunmNameIndex].ColumnName);
                }
                WriteRowCount++;
            }


            //写入数据
            for (int row = 0; row < sourceData.Rows.Count; row++)
            {
                //sheet表创建新的一行
                IRow newRow = sheet.CreateRow(WriteRowCount);
                for (int column = 0; column < sourceData.Columns.Count; column++)
                {

                    newRow.CreateCell(column).SetCellValue(sourceData.Rows[row][column].ToString());

                }

                WriteRowCount++;  //写入下一行
            }


            //写入到excel中
            FileStream fs = new FileStream(TargetFileNamePath, FileMode.OpenOrCreate, FileAccess.Write);
            workbook.Write(fs);

            fs.Flush();
            fs.Close();

            workbook.Close();
            return WriteRowCount;
        }





        /// <summary>
        /// 从Excel中读入数据到DataTable中
        /// </summary>
        /// <param name="sourceFileNamePath">Excel文件的路径</param>
        /// <param name="sheetName">excel文件中工作表名称</param>
        /// <param name="IsHasColumnName">文件是否有列名</param>
        /// <returns>从Excel读取到数据的DataTable结果集</returns>
        public static DataTable ExcelToDataTable(string sourceFileNamePath, string sheetName, bool IsHasColumnName)
        {

            if (!File.Exists(sourceFileNamePath))
            {
                throw new ArgumentException("excel文件的路径不存在或者excel文件没有创建好");
            }

            if (sheetName == null || sheetName.Length == 0)
            {
                throw new ArgumentException("工作表sheet的名称不能为空");
            }

            //根据Excel文件的后缀名创建对应的workbook
            IWorkbook workbook = null;
            //打开文件
            FileStream fs = new FileStream(sourceFileNamePath, FileMode.Open, FileAccess.Read);
            if (sourceFileNamePath.IndexOf(".xlsx") > 0)
            {  //2007版本的excel
                workbook = new XSSFWorkbook(fs);
            }
            else if (sourceFileNamePath.IndexOf(".xls") > 0) //2003版本的excel
            {
                workbook = new HSSFWorkbook(fs);
            }
            else
            {
                return null;    //都不匹配或者传入的文件根本就不是excel文件，直接返回
            }




            //获取工作表sheet
            ISheet sheet = workbook.GetSheet(sheetName);
            //获取不到，直接返回
            if (sheet == null) return null;



            //开始读取的行号
            int StartReadRow = 0;
            DataTable targetTable = new DataTable();



            //表中有列名,则为DataTable添加列名
            if (IsHasColumnName)
            {
                //获取要读取的工作表的第一行
                IRow columnNameRow = sheet.GetRow(0);   //0代表第一行
                                                        //获取该行的列数(即该行的长度)
                int CellLength = columnNameRow.LastCellNum;

                //遍历读取
                for (int columnNameIndex = 0; columnNameIndex < CellLength; columnNameIndex++)
                {
                    //不为空，则读入
                    if (columnNameRow.GetCell(columnNameIndex) != null)
                    {
                        //获取该单元格的值
                        string cellValue = columnNameRow.GetCell(columnNameIndex).StringCellValue;
                        if (cellValue != null)
                        {
                            //为DataTable添加列名
                            targetTable.Columns.Add(new DataColumn(cellValue));
                        }
                    }
                }

                StartReadRow++;
            }



            ///开始读取sheet表中的数据

            //获取sheet文件中的行数
            int RowLength = sheet.LastRowNum;
            //遍历一行一行地读入
            for (int RowIndex = StartReadRow; RowIndex < RowLength; RowIndex++)
            {
                //获取sheet表中对应下标的一行数据
                IRow currentRow = sheet.GetRow(RowIndex);   //RowIndex代表第RowIndex+1行

                if (currentRow == null) continue;  //表示当前行没有数据，则继续
                //获取第Row行中的列数，即Row行中的长度
                int currentColumnLength = currentRow.LastCellNum;

                //创建DataTable的数据行
                DataRow dataRow = targetTable.NewRow();
                //遍历读取数据
                for (int columnIndex = 0; columnIndex < currentColumnLength; columnIndex++)
                {
                    //没有数据的单元格默认为空
                    if (currentRow.GetCell(columnIndex) != null)
                    {
                        dataRow[columnIndex] = currentRow.GetCell(columnIndex);
                    }
                }
                //把DataTable的数据行添加到DataTable中
                targetTable.Rows.Add(dataRow);
            }


            //释放资源
            fs.Close();
            workbook.Close();

            return targetTable;
        }


        #region IDisposable 成员

        public void Dispose()
        {

        }

        #endregion
    }

}