C# 或 JQuery导出Excel

首先要添加NPOI.dll文件

然后添加类：NPOIHelper.cs

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Text;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using NPOI.DDF;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace WeixinService.Bll
{
    public class NPOIHelper
    {
        public NPOIHelper()
        {

        }


        /// </summary>
        /// <param name="dt"> 数据源</param>
        /// <returns>stream</returns>
        public static Stream StreamData(DataTable dt, string sheetname, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = null;

            HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽
            int[] arrColWidth = new int[dt.Columns.Count];
            foreach (DataColumn item in dt.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }


            sheet = (HSSFSheet)workbook.CreateSheet(sheetname);
            #region 表头及样式
            {
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
                headerRow.HeightInPoints = 25;
                headerRow.CreateCell(0).SetCellValue(strHeaderText);

                HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                HSSFFont font = (HSSFFont)workbook.CreateFont();
                font.FontHeightInPoints = 20;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                headerRow.GetCell(0).CellStyle = headStyle;
                sheet.AddMergedRegion(new Region(0, 0, 0, dt.Columns.Count - 1));
                headerRow = null;
                //headerRow.Dispose();
            }
            #endregion


            #region 列头及样式
            {
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
                HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                HSSFFont font = (HSSFFont)workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                foreach (DataColumn column in dt.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); //遍历datatable，将datatable的列名赋予sheet
                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                    //设置列宽
                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                }
                headerRow = null;
            }
            #endregion



            int index = 2; //表头和列头已经占用一行，所以从2开始
            foreach (DataRow row in dt.Rows)
            {
                HSSFRow datarow = (HSSFRow)sheet.CreateRow(index);

                foreach (DataColumn column in dt.Columns)
                {
                    // row.CreateCell(column.Ordinal).SetCellValue(datarow[column].ToString()); //遍历并将值放入sheet

                    HSSFCell newCell = (HSSFCell)datarow.CreateCell(column.Ordinal); //　　实例化cell

                    string drValue = row[column].ToString();
                    if (drValue == null || drValue == "")
                    {
                        newCell.SetCellValue("");
                        continue;
                    }

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                        case "System.DateTime"://日期类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                        case "System.Float":
                        case "System.Single":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;

                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
                index++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            //headerrow = null;
            //workbook = null;
            workbook.Dispose();
            return ms;
        }

        public void SetColWidth()
        {

        }

        /// <summary>
        /// Datatable数据填充如excel
        /// </summary>
        /// <param name="filename">excel文件名</param>
        /// <param name="dt"> 数据源</param>
        /// <param name="Response"> response响应</param>
        ///  <param name="headerStr"> 表头标题</param>
        public static void DataTableToExcel(string filename, DataTable dt, string sheetname, HttpResponse Response, string headerStr)
        {
            MemoryStream ms = StreamData(dt, sheetname, headerStr) as MemoryStream; //as MemoryStream  as用作转换,此处可以省略
            try
            {

                Response.Clear();
                Response.ContentType = "application/vnd.ms-excel";
                Response.ContentEncoding = Encoding.UTF8;
                Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename + ".xls"));
                Response.AddHeader("content-length", ms.Length.ToString());
                Byte[] data = ms.ToArray(); //文件写入采用二进制流的方式。所以此处要转换为字节数组
                Response.BinaryWrite(data);
            }
            catch
            {
                Response.Clear();
                Response.ClearHeaders();
                Response.Write("<script language=javascript>alert( '导出Excel错误'); </script>");
            }
            Response.Flush();
            Response.Close();
            Response.End();
            ms = null;
        }

    }
}


最后就是利用这个类来使用了：

/// <summary>
       /// 导出查询数据
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       protected void ExportQueryExcel_Click(object sender, EventArgs e)
       {
           var kssj = Request.Params["kssj"];
           var jssj = Request.Params["jssj"];
           var hh = Request.Params["hh"];
           try
           {
               var dataTable = _userRegDal.QueryUserReg(kssj, jssj, hh);
               dataTable.Columns[0].ColumnName = "用户号";
               dataTable.Columns[1].ColumnName = "联系电话";
               dataTable.Columns[2].ColumnName = "绑定时间";
               NPOIHelper.DataTableToExcel("用户绑定查询数据", dataTable, "数据表", Response, "用户绑定查询数据");
           }
           catch (Exception ex)
           {
               Log.Debug("方法名:Button1_Click,错误原因:" + ex.Message);
           }
       }

       /// <summary>
       /// 导出全部数据
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       protected void ExportAllExcel_Click(object sender, EventArgs e)
       {
           try
           {
               var dataTable = _userRegDal.QueryUserRegAll();
               dataTable.Columns[0].ColumnName = "用户号";
               dataTable.Columns[1].ColumnName = "联系电话";
               dataTable.Columns[2].ColumnName = "绑定时间";
               NPOIHelper.DataTableToExcel("用户绑定全部数据", dataTable, "数据表", Response, "用户绑定全部数据");
           }
           catch (Exception ex)
           {
               Log.Debug("方法名:Button2_Click,错误原因:" + ex.Message);
           }
       }

以上是针对后台下载文件，现在针对前台下载文件，主要结合JQuery,如下 ：

　

　$('#frmExport').attr('action', '/AjaxSwitchManage/ExportSwitch.cspx?exptInfo=' + arrdata);
　　$('#frmExport').submit();
　　return false;

 

[Action]
        public object ExportSwitch(string exptInfo)
        {

            string[] arrdata = exptInfo.Split(',');

            string id = arrdata[0];
            string switchName = arrdata[1];
            string switchMac = arrdata[2];
            string switchIp = arrdata[3];
            string areaId = arrdata[4];
            if (areaId == "")
            {
                areaId = id;
            }

            string[] titles = { "交换机名称", "型号", "MAC地址", "IP地址", "端口数", "是否控制", "控制方式", "是否已配置", "管理方式", "普通用户", "管理用户", "区域名" };
            string[] columns = { "Name", "Type", "Mac", "Ip", "PortCount", "CtrlEnable", "CtrlMode", "HasConfiged", "UseType", "User", "AdminUser", "AreaName" };
            int[] widths = { 100, 80, 100, 100, 60, 60, 60, 60, 60, 100, 100, 80 };
            var list = SwitchBLL.GetSwitchBySearchList(id, new SwitchInfoSearchInfo { AreaId = int.Parse(areaId), SwitchName = switchName, SwitchMac = switchMac, SwitchIp = switchIp });
            NPOIHelper<SwitchBasicInfo> npoi = new NPOIHelper<SwitchBasicInfo>(titles, columns, widths, list);
            try
            {
                using (MemoryStream ms = npoi.CommonToExcel() as MemoryStream)
                {
                    string filename = HttpUtility.UrlEncode("交换机基本信息" + ".xls");
                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.AddHeader("Content-Disposition", ("attachment;filename=" + filename));
                    HttpContext.Current.Response.Charset = "UTF-8";
                    HttpContext.Current.Response.ContentType = "application/ms-excel";
                    HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                    HttpContext.Current.Response.Flush();
                }

            }
            catch (Exception ex)
            {
                throw new Exception("导出excel失败：" + ex.Message);
            }
            return "";
        }