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) 
  { 
 
  } 
} 

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