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

网络编程 2024/11/16 佚名

3 2 1

无为清净楼资源网 Design By www.qnjia.com

解决中文文件名保存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}", "=" + 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}", "=" + 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) 
  { 
 
  } 
}

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

asp.net,导出Excel,asp.net,excel,导出,NPOI导出Excel

标签：

asp.net,导出Excel,asp.net,excel,导出,NPOI导出Excel

无为清净楼资源网 Design By www.qnjia.com

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

无为清净楼资源网 Design By www.qnjia.com

评论“asp.net 利用NPOI导出Excel通用类的方法”

暂无评论...

www.qnjia.com 无为清净楼资源网

139,976互联网资源

144,792高清电影

21,817无损音乐

631,128技术资源

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

asp.net,导出Excel,asp.net,excel,导出,NPOI导出Excel

VS2015自带LocalDB数据库用法详解

Asp.net MVC 中利用jquery datatables 实现数据分页显示功能

评论“asp.net 利用NPOI导出Excel通用类的方法”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

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

asp.net,导出Excel,asp.net,excel,导出,NPOI导出Excel

VS2015自带LocalDB数据库用法详解

Asp.net MVC 中利用jquery datatables 实现数据分页显示功能

评论“asp.net 利用NPOI导出Excel通用类的方法”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存