首先要添加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 = http://www.mamicode.com/row[column].ToString();"color: blue;">if (drValue =http://www.mamicode.com/= null || drValue =http://www.mamicode.com/= "")                    {                        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 = http://www.mamicode.com/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);           }       }

C# 导出Excel