asp.net 利用NPOI导出Excel通用类的方法

解决中文文件名保存Excel乱码问题，主要是判断火狐或者IE浏览器，然后做对应的判断处理，核心代码如下：

System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
 //设置下载的Excel文件名\
 if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1)
 {
    //火狐浏览器
    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?="));
}
else
{
    //IE等浏览器
    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
}

废话不多说，直接上类库代码，ATNPOIHelper.cs：

using System;
using System.Linq;
using System.Web;
using System.IO;
using NPOI;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.Collections.Generic;
using System.Text;

namespace AT.Utility.DotNetFile
{
  /*
  导出Excel包含的功能：
  1.多表头导出最多支持到三行，表头格式说明
  相邻父列头之间用'#'分隔,父列头与子列头用空格(' ‘)分隔,相邻子列头用逗号分隔(‘,')
  两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名
  三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值
  三行时请注意：列头要重复
  2.添加表头标题功能
  3.添加序号功能
  4.根据数据设置列宽

  缺陷：
  数据内容不能合并列合并行

  改进思路：
  添加一属性：设置要合并的列，为了实现多列合并可以这样设置{“列1,列2”,”列4”}
    */
  /// <summary>
  /// 利用NPOI实现导出Excel
  /// </summary>
  public class ATNPOIHelper
  {

    #region 初始化

    /// <summary>
    /// 声明 HSSFWorkbook 对象
    /// </summary>
    private static HSSFWorkbook _workbook;

    /// <summary>
    /// 声明 HSSFSheet 对象
    /// </summary>
    private static HSSFSheet _sheet;

    #endregion

    #region Excel导出

    /// <summary>
    /// Excel导出
    /// </summary>
    /// <param name="fileName">文件名称 如果为空或NULL，则默认“新建Excel.xls”</param>
    /// <param name="list"></param>
    /// <param name="ColMergeNum">合计：末行合计时，合并的列数</param>
    /// <param name="method">导出方式 1：WEB导出（默认）2：按文件路径导出</param>
    /// <param name="filePath">文件路径 如果WEB导出，则可以为空；如果按文件路径导出，则默认桌面路径</param>
    public static void Export(string fileName, IList<NPOIModel> list, int ColMergeNum, int method = 1, string filePath = null)
    {
      // 文件名称
      if (!string.IsNullOrEmpty(fileName))
      {
        if (fileName.IndexOf('.') == -1)
        {
          fileName += ".xls";
        }
        else
        {
          fileName = fileName.Substring(1, fileName.IndexOf('.')) + ".xls";
        }
      }
      else
      {
        fileName = "新建Excel.xls";
      }
      // 文件路径
      if (2 == method && string.IsNullOrEmpty(filePath))
      {
        filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
      }
      // 调用导出处理程序
      Export(list, ColMergeNum);
      // WEB导出
      if (1 == method)
      {
        System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        //设置下载的Excel文件名\
        if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1)
        {
          //火狐浏览器
          System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?="));
        }
        else
        {
          //IE等浏览器
          System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
        }
        using (MemoryStream ms = new MemoryStream())
        {
          //将工作簿的内容放到内存流中
          _workbook.Write(ms);
          //将内存流转换成字节数组发送到客户端
          System.Web.HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
          System.Web.HttpContext.Current.Response.End();
          _sheet = null;
          _workbook = null;
        }
      }
      else if (2 == method)
      {
        using (FileStream fs = File.Open(filePath, FileMode.Append))
        {
          _workbook.Write(fs);
          _sheet = null;
          _workbook = null;
        }
      }
    }

    /// <summary>
    /// 导出方法实现
    /// </summary>
    /// <param name="list"></param>
    private static void Export(IList<NPOIModel> list, int ColMergeNum)
    {

      #region 变量声明

      // 初始化
      _workbook = new HSSFWorkbook();
      // 声明 Row 对象
      IRow _row;
      // 声明 Cell 对象
      ICell _cell;
      // 总列数
      int cols = 0;
      // 总行数
      int rows = 0;
      // 行数计数器
      int rowIndex = 0;
      // 单元格值
      string drValue = null;

      #endregion

      foreach (NPOIModel model in list)
      {
        // 工作薄命名
        if (model.sheetName != null)
          _sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName);
        else
          _sheet = (HSSFSheet)_workbook.CreateSheet();

        // 获取数据源
        DataTable dt = model.dataSource;
        // 初始化
        rowIndex = 0;
        // 获取总行数
        rows = GetRowCount(model.headerName);
        // 获取总列数
        cols = GetColCount(model.headerName);

        //合计：合并表格末行N列,rows为表头行数，dt.Rows.Count为数据行数
        if (ColMergeNum > 1)
        {
          CellRangeAddress region_Merge = new CellRangeAddress(rows + dt.Rows.Count, rows + dt.Rows.Count, 0, ColMergeNum - 1);
          _sheet.AddMergedRegion(region_Merge);
        }

        ICellStyle myBodyStyle = bodyStyle;
        ICellStyle myTitleStyle = titleStyle;
        ICellStyle myDateStyle = dateStyle;
        ICellStyle myBodyRightStyle = bodyRightStyle;
        // 循环行数
        foreach (DataRow row in dt.Rows)
        {

          #region 新建表，填充表头，填充列头，样式

          if (rowIndex == 65535 || rowIndex == 0)
          {
            if (rowIndex != 0)
              _sheet = (HSSFSheet)_workbook.CreateSheet();

            // 构建行
            for (int i = 0; i < rows + model.isTitle; i++)
            {
              _row = _sheet.GetRow(i);
              // 创建行
              if (_row == null)
                _row = _sheet.CreateRow(i);

              for (int j = 0; j < cols; j++)
                _row.CreateCell(j).CellStyle = myBodyStyle;
            }

            // 如果存在表标题
            if (model.isTitle > 0)
            {
              // 获取行
              _row = _sheet.GetRow(0);
              // 合并单元格
              CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1));
              _sheet.AddMergedRegion(region);
              // 填充值
              _row.CreateCell(0).SetCellValue(model.tableTitle);
              // 设置样式
              _row.GetCell(0).CellStyle = myTitleStyle;
              // 设置行高
              _row.HeightInPoints = 20;
            }

            // 取得上一个实体
            NPOIHeader lastRow = null;
            IList<NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle);
            // 创建表头
            foreach (NPOIHeader m in hList)
            {
              var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1);
              if (data.Count() > 0)
              {
                lastRow = data.First();
                if (m.headerName == lastRow.headerName)
                  m.firstCol = lastRow.firstCol;
              }

              // 获取行
              _row = _sheet.GetRow(m.firstRow);
              // 合并单元格
              CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol);

              _sheet.AddMergedRegion(region);
              // 填充值
              _row.CreateCell(m.firstCol).SetCellValue(m.headerName);
            }
            // 填充表头样式
            for (int i = 0; i < rows + model.isTitle; i++)
            {
              _row = _sheet.GetRow(i);
              for (int j = 0; j < cols; j++)
              {
                _row.GetCell(j).CellStyle = myBodyStyle;
                //设置列宽
                _sheet.SetColumnWidth(j, (model.colWidths[j] + 1) * 450);
              }
            }

            rowIndex = (rows + model.isTitle);
          }

          #endregion

          #region 填充内容

          // 构建列
          _row = _sheet.CreateRow(rowIndex);
          foreach (DataColumn column in dt.Columns)
          {
            // 添加序号列
            if (1 == model.isOrderby && column.Ordinal == 0)
            {
              _cell = _row.CreateCell(0);
              _cell.SetCellValue(rowIndex - rows);
              _cell.CellStyle = myBodyStyle;
            }

            // 创建列
            _cell = _row.CreateCell(column.Ordinal + model.isOrderby);

            // 获取值
            drValue = row[column].ToString();

            switch (column.DataType.ToString())
            {
              case "System.String"://字符串类型
                _cell.SetCellValue(drValue);
                _cell.CellStyle = myBodyStyle;
                break;
              case "System.DateTime"://日期类型
                DateTime dateV;
                DateTime.TryParse(drValue, out dateV);
                _cell.SetCellValue(dateV);

                _cell.CellStyle = myDateStyle;//格式化显示
                break;
              case "System.Boolean"://布尔型
                bool boolV = false;
                bool.TryParse(drValue, out boolV);
                _cell.SetCellValue(boolV);
                _cell.CellStyle = myBodyStyle;
                break;
              case "System.Int16"://整型
              case "System.Int32":
              case "System.Int64":
              case "System.Byte":
                int intV = 0;
                int.TryParse(drValue, out intV);
                _cell.SetCellValue(intV);
                _cell.CellStyle = myBodyRightStyle;
                break;
              case "System.Decimal"://浮点型
              case "System.Double":
                double doubV = 0;
                double.TryParse(drValue, out doubV);
                _cell.SetCellValue(doubV.ToString("f2"));
                _cell.CellStyle = myBodyRightStyle;
                break;
              case "System.DBNull"://空值处理
                _cell.SetCellValue("");
                break;
              default:
                _cell.SetCellValue("");
                break;
            }

          }

          #endregion

          rowIndex++;
        }
      }
    }

    #region 辅助方法

    /// <summary>
    /// 表头解析
    /// </summary>
    /// <remarks>
    /// </remarks>
    /// <param name="header">表头</param>
    /// <param name="rows">总行数</param>
    /// <param name="addRows">外加行</param>
    /// <param name="addCols">外加列</param>
    /// <returns></returns>
    private static IList<NPOIHeader> GetHeaders(string header, int rows, int addRows)
    {
      // 临时表头数组
      string[] tempHeader;
      string[] tempHeader2;
      // 所跨列数
      int colSpan = 0;
      // 所跨行数
      int rowSpan = 0;
      // 单元格对象
      NPOIHeader model = null;
      // 行数计数器
      int rowIndex = 0;
      // 列数计数器
      int colIndex = 0;
      //
      IList<NPOIHeader> list = new List<NPOIHeader>();
      // 初步解析
      string[] headers = header.Split(new string[] { "#" }, StringSplitOptions.RemoveEmptyEntries);
      // 表头遍历
      for (int i = 0; i < headers.Length; i++)
      {
        // 行数计数器清零
        rowIndex = 0;
        // 列数计数器清零
        colIndex = 0;
        // 获取所跨行数
        rowSpan = GetRowSpan(headers[i], rows);
        // 获取所跨列数
        colSpan = GetColSpan(headers[i]);

        // 如果所跨行数与总行数相等，则不考虑是否合并单元格问题
        if (rows == rowSpan)
        {
          colIndex = GetMaxCol(list);
          model = new NPOIHeader(headers[i],
            addRows,
            (rowSpan - 1 + addRows),
            colIndex,
            (colSpan - 1 + colIndex),
            addRows);
          list.Add(model);
          rowIndex += (rowSpan - 1) + addRows;
        }
        else
        {
          // 列索引
          colIndex = GetMaxCol(list);
          // 如果所跨行数不相等，则考虑是否包含多行
          tempHeader = headers[i].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
          for (int j = 0; j < tempHeader.Length; j++)
          {

            // 如果总行数=数组长度
            if (1 == GetColSpan(tempHeader[j]))
            {
              if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
              {
                model = new NPOIHeader(tempHeader[j],
                  (j + addRows),
                  (j + addRows) + (rows - tempHeader.Length),
                  colIndex,
                  (colIndex + colSpan - 1),
                  addRows);
                list.Add(model);
              }
              else
              {
                model = new NPOIHeader(tempHeader[j],
                    (j + addRows),
                    (j + addRows),
                    colIndex,
                    (colIndex + colSpan - 1),
                    addRows);
                list.Add(model);
              }
            }
            else
            {
              // 如果所跨列数不相等，则考虑是否包含多列
              tempHeader2 = tempHeader[j].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
              for (int m = 0; m < tempHeader2.Length; m++)
              {
                // 列索引
                colIndex = GetMaxCol(list) - colSpan + m;
                if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
                {
                  model = new NPOIHeader(tempHeader2[m],
                    (j + addRows),
                    (j + addRows) + (rows - tempHeader.Length),
                    colIndex,
                    colIndex,
                    addRows);
                  list.Add(model);
                }
                else
                {
                  model = new NPOIHeader(tempHeader2[m],
                      (j + addRows),
                      (j + addRows),
                      colIndex,
                      colIndex,
                      addRows);
                  list.Add(model);
                }
              }
            }
            rowIndex += j + addRows;
          }
        }
      }
      return list;
    }

    /// <summary>
    /// 获取最大列
    /// </summary>
    /// <param name="list"></param>
    /// <returns></returns>
    private static int GetMaxCol(IList<NPOIHeader> list)
    {
      int maxCol = 0;
      if (list.Count > 0)
      {
        foreach (NPOIHeader model in list)
        {
          if (maxCol < model.lastCol)
            maxCol = model.lastCol;
        }
        maxCol += 1;
      }

      return maxCol;
    }

    /// <summary>
    /// 获取表头行数
    /// </summary>
    /// <param name="newHeaders">表头文字</param>
    /// <returns></returns>
    private static int GetRowCount(string newHeaders)
    {
      string[] ColumnNames = newHeaders.Split(new char[] { '@' });
      int Count = 0;
      if (ColumnNames.Length <= 1)
        ColumnNames = newHeaders.Split(new char[] { '#' });
      foreach (string name in ColumnNames)
      {
        int TempCount = name.Split(new char[] { ' ' }).Length;
        if (TempCount > Count)
          Count = TempCount;
      }
      return Count;
    }

    /// <summary>
    /// 获取表头列数
    /// </summary>
    /// <param name="newHeaders">表头文字</param>
    /// <returns></returns>
    private static int GetColCount(string newHeaders)
    {
      string[] ColumnNames = newHeaders.Split(new char[] { '@' });
      int Count = 0;
      if (ColumnNames.Length <= 1)
        ColumnNames = newHeaders.Split(new char[] { '#' });
      Count = ColumnNames.Length;
      foreach (string name in ColumnNames)
      {
        int TempCount = name.Split(new char[] { ',' }).Length;
        if (TempCount > 1)
          Count += TempCount - 1;
      }
      return Count;
    }

    /// <summary>
    /// 列头跨列数
    /// </summary>
    /// <remarks>
    /// </remarks>
    /// <param name="newHeaders">表头文字</param>
    /// <returns></returns>
    private static int GetColSpan(string newHeaders)
    {
      return newHeaders.Split(',').Count();
    }

    /// <summary>
    /// 列头跨行数
    /// </summary>
    /// <remarks>
    /// </remarks>
    /// <param name="newHeaders">列头文本</param>
    /// <param name="rows">表头总行数</param>
    /// <returns></returns>
    private static int GetRowSpan(string newHeaders, int rows)
    {
      int Count = newHeaders.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).Length;
      // 如果总行数与当前表头所拥有行数相等
      if (rows == Count)
        Count = 1;
      else if (Count < rows)
        Count = 1 + (rows - Count);
      else
        throw new Exception("表头格式不正确！");
      return Count;
    }

    #endregion

    #region 单元格样式

    /// <summary>
    /// 数据单元格样式
    /// </summary>
    private static ICellStyle bodyStyle
    {
      get
      {
        ICellStyle style = _workbook.CreateCellStyle();
        style.Alignment = HorizontalAlignment.CENTER; //居中
        style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
        style.WrapText = true;//自动换行
        // 边框
        style.BorderBottom = BorderStyle.THIN;
        style.BorderLeft = BorderStyle.THIN;
        style.BorderRight = BorderStyle.THIN;
        style.BorderTop = BorderStyle.THIN;
        // 字体
        //IFont font = _workbook.CreateFont();
        //font.FontHeightInPoints = 10;
        //font.FontName = "宋体";
        //style.SetFont(font);

        return style;
      }
    }

    /// <summary>
    /// 数据单元格样式
    /// </summary>
    private static ICellStyle bodyRightStyle
    {
      get
      {
        ICellStyle style = _workbook.CreateCellStyle();
        style.Alignment = HorizontalAlignment.RIGHT; //居中
        style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
        style.WrapText = true;//自动换行
        // 边框
        style.BorderBottom = BorderStyle.THIN;
        style.BorderLeft = BorderStyle.THIN;
        style.BorderRight = BorderStyle.THIN;
        style.BorderTop = BorderStyle.THIN;
        // 字体
        //IFont font = _workbook.CreateFont();
        //font.FontHeightInPoints = 10;
        //font.FontName = "宋体";
        //style.SetFont(font);

        return style;
      }
    }

    /// <summary>
    /// 标题单元格样式
    /// </summary>
    private static ICellStyle titleStyle
    {
      get
      {
        ICellStyle style = _workbook.CreateCellStyle();
        style.Alignment = HorizontalAlignment.CENTER; //居中
        style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
        style.WrapText = true;//自动换行

        //IFont font = _workbook.CreateFont();
        //font.FontHeightInPoints = 14;
        //font.FontName = "宋体";
        //font.Boldweight = (short)FontBoldWeight.BOLD;
        //style.SetFont(font);

        return style;
      }
    }

    /// <summary>
    /// 日期单元格样式
    /// </summary>
    private static ICellStyle dateStyle
    {
      get
      {
        ICellStyle style = _workbook.CreateCellStyle();
        style.Alignment = HorizontalAlignment.CENTER; //居中
        style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
        style.WrapText = true;//自动换行
        // 边框
        style.BorderBottom = BorderStyle.THIN;
        style.BorderLeft = BorderStyle.THIN;
        style.BorderRight = BorderStyle.THIN;
        style.BorderTop = BorderStyle.THIN;
        // 字体
        //IFont font = _workbook.CreateFont();
        //font.FontHeightInPoints = 10;
        //font.FontName = "宋体";
        //style.SetFont(font);

        IDataFormat format = _workbook.CreateDataFormat();
        style.DataFormat = format.GetFormat("yyyy-MM-dd");
        return style;
      }
    }

    #endregion

    #endregion
  }

  /// <summary>
  /// 实体类
  /// </summary>
  public class NPOIModel
  {
    /// <summary>
    /// 数据源
    /// </summary>
    public DataTable dataSource { get; private set; }
    /// <summary>
    /// 要导出的数据列数组
    /// </summary>
    public string[] fileds { get; private set; }
    /// <summary>
    /// 工作薄名称数组
    /// </summary>
    public string sheetName { get; private set; }
    /// <summary>
    /// 表标题
    /// </summary>
    public string tableTitle { get; private set; }
    /// <summary>
    /// 表标题是否存在 1：存在 0：不存在
    /// </summary>
    public int isTitle { get; private set; }
    /// <summary>
    /// 是否添加序号
    /// </summary>
    public int isOrderby { get; private set; }
    /// <summary>
    /// 表头
    /// </summary>
    public string headerName { get; private set; }
    /// <summary>
    /// 取得列宽
    /// </summary>
    public int[] colWidths { get; private set; }
    /// <summary>
    /// 构造函数
    /// </summary>
    /// <remarks>
    /// </remarks>
    /// <param name="dataSource">数据来源 DataTable</param>
    /// <param name="filed">要导出的字段，如果为空或NULL，则默认全部</param>
    /// <param name="sheetName">工作薄名称</param>
    /// <param name="headerName">表头名称 如果为空或NULL，则默认数据列字段
    /// 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',')
    /// 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名
    /// 三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值
    /// 三行时请注意：列头要重复
    /// </param>
    /// <param name="tableTitle">表标题</param>
    /// <param name="isOrderby">是否添加序号 0：不添加 1：添加</param>
    public NPOIModel(DataTable dataSource, string filed, string sheetName, string headerName, string tableTitle = null, int isOrderby = 0)
    {
      if (!string.IsNullOrEmpty(filed))
      {
        this.fileds = filed.ToUpper().Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);

        // 移除多余数据列
        for (int i = dataSource.Columns.Count - 1; i >= 0; i--)
        {
          DataColumn dc = dataSource.Columns[i];
          if (!this.fileds.Contains(dataSource.Columns[i].Caption.ToUpper()))
          {
            dataSource.Columns.Remove(dataSource.Columns[i]);
          }
        }

        // 列索引
        int colIndex = 0;
        // 循环排序
        for (int i = 0; i < dataSource.Columns.Count; i++)
        {
          // 获取索引
          colIndex = GetColIndex(dataSource.Columns[i].Caption.ToUpper());
          // 设置下标
          dataSource.Columns[i].SetOrdinal(colIndex);
        }
      }
      else
      {
        this.fileds = new string[dataSource.Columns.Count];
        for (int i = 0; i < dataSource.Columns.Count; i++)
        {
          this.fileds[i] = dataSource.Columns[i].ColumnName;
        }
      }
      this.dataSource = dataSource;

      if (!string.IsNullOrEmpty(sheetName))
      {
        this.sheetName = sheetName;
      }
      if (!string.IsNullOrEmpty(headerName))
      {
        this.headerName = headerName;
      }
      else
      {
        this.headerName = string.Join("#", this.fileds);
      }
      if (!string.IsNullOrEmpty(tableTitle))
      {
        this.tableTitle = tableTitle;
        this.isTitle = 1;
      }
      // 取得数据列宽 数据列宽可以和表头列宽比较，采取最长宽度
      colWidths = new int[this.dataSource.Columns.Count];
      foreach (DataColumn item in this.dataSource.Columns)
      {
        colWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
      }
      // 循环比较最大宽度
      for (int i = 0; i < this.dataSource.Rows.Count; i++)
      {
        for (int j = 0; j < this.dataSource.Columns.Count; j++)
        {
          int intTemp = Encoding.GetEncoding(936).GetBytes(this.dataSource.Rows[i][j].ToString()).Length;
          if (intTemp > colWidths[j])
          {
            colWidths[j] = intTemp;
          }
        }
      }
      if (isOrderby > 0)
      {
        this.isOrderby = isOrderby;
        this.headerName = "序号#" + this.headerName;
      }
    }

    /// <summary>
    /// 获取列名下标
    /// </summary>
    /// <param name="colName">列名称</param>
    /// <returns></returns>
    private int GetColIndex(string colName)
    {
      for (int i = 0; i < this.fileds.Length; i++)
      {
        if (colName == this.fileds[i])
          return i;
      }
      return 0;
    }
  }

  /// <summary>
  /// 表头构建类
  /// </summary>
  public class NPOIHeader
  {
    /// <summary>
    /// 表头
    /// </summary>
    public string headerName { get; set; }
    /// <summary>
    /// 起始行
    /// </summary>
    public int firstRow { get; set; }
    /// <summary>
    /// 结束行
    /// </summary>
    public int lastRow { get; set; }
    /// <summary>
    /// 起始列
    /// </summary>
    public int firstCol { get; set; }
    /// <summary>
    /// 结束列
    /// </summary>
    public int lastCol { get; set; }
    /// <summary>
    /// 是否跨行
    /// </summary>
    public int isRowSpan { get; private set; }
    /// <summary>
    /// 是否跨列
    /// </summary>
    public int isColSpan { get; private set; }
    /// <summary>
    /// 外加行
    /// </summary>
    public int rows { get; set; }

    public NPOIHeader() { }
    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="headerName">表头</param>
    /// <param name="firstRow">起始行</param>
    /// <param name="lastRow">结束行</param>
    /// <param name="firstCol">起始列</param>
    /// <param name="lastCol">结束列</param>
    /// <param name="rows">外加行</param>
    /// <param name="cols">外加列</param>
    public NPOIHeader(string headerName, int firstRow, int lastRow, int firstCol, int lastCol, int rows = 0)
    {
      this.headerName = headerName;
      this.firstRow = firstRow;
      this.lastRow = lastRow;
      this.firstCol = firstCol;
      this.lastCol = lastCol;
      // 是否跨行判断
      if (firstRow != lastRow)
        isRowSpan = 1;
      if (firstCol != lastCol)
        isColSpan = 1;

      this.rows = rows;
    }
  }
}

3、导出代码示例如下：

/// <summary>
/// 导出测点列表表格
/// </summary>
[HttpGet]
[AllowAnonymous]
public void ExportMeasurePointData(string TreeID, string TreeType)
{
  DataTable dtResult = new DataTable();
  DataTable dtExcel = new DataTable();
  try
  {
    string sql = string.Format("EXEC P_GET_ZXJG_TagList '{0}','{1}'", TreeID, TreeType);
    dtResult = QuerySQL.GetDataTable(sql);
    dtExcel = dtResult.Copy();
    dtExcel.Columns.Add("xuhao", typeof(string));
    dtExcel.Columns.Add("StrValueTime", typeof(string));
    dtExcel.Columns["xuhao"].SetOrdinal(0);
    dtExcel.Columns["StrValueTime"].SetOrdinal(2);
    for (int i = 0; i < dtResult.Rows.Count; i++)
    {
      dtExcel.Rows[i]["xuhao"] = (i + 1).ToString();
      dtExcel.Rows[i]["StrValueTime"] = Convert.ToDateTime(dtResult.Rows[i]["F_ValueTime"]).ToString("yyyy-MM-dd HH:mm:ss");
    }
    List<NPOIModel> list = new List<NPOIModel>();
    list.Add(new NPOIModel(dtExcel, "xuhao;F_Description;StrValueTime;F_Value;F_Unit;F_AlmLow;F_AlmUp", "sheet", "序号#监测点#采集时间#当前数值#工程单位#报警下限#报警上限"));
    ATNPOIHelper.Export("测点列表", list, 0);
  }
  catch (Exception ex)
  {

  }
}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。

原文链接：http://blog.csdn.net/zdw_wym/article/details/73608820?utm_source=tuicool&utm_medium=referral