将HtmlTable内容导出到Excel，使用NPOI组件代码,C#代码库,德仔网

代码语言
代码分类
【C#】将HtmlTable内容导出到Excel，使用NPOI组件
作者:Alex / 发布于2016/7/27/ 501
/// <summary>  
  /// 将DataTable导出到Excel  
  /// </summary>  
  /// <param name="htmlTable">html表格内容</param>   
  /// <param name="fileName">仅文件名（非路径）</param>   
  /// <returns>返回Excel文件绝对路径</returns>  
  public static string ExportHtmlTableToExcel(string htmlTable, string fileName)  
  {  
      string result;  
      try  
      {  
          #region 第一步：将HtmlTable转换为DataTable  
          htmlTable = htmlTable.Replace("\"", "'");  
          var trReg = new Regex(pattern: @"(?<=(<[t|T][r|R]))[\s\S]*?(?=(</[t|T][r|R]>))");  
          var trMatchCollection = trReg.Matches(htmlTable);  
          DataTable dt = new DataTable("data");  
          for (int i = 0; i < trMatchCollection.Count; i++)  
          {  
              var row = "<tr " + trMatchCollection[i].ToString().Trim() + "</tr>";  
              var tdReg = new Regex(pattern: @"(?<=(<[t|T][d|D|h|H]))[\s\S]*?(?=(</[t|T][d|D|h|H]>))");  
              var tdMatchCollection = tdReg.Matches(row);  
              if (i == 0)  
              {  
                  foreach (var rd in tdMatchCollection)  
                  {  
                      var tdValue = RemoveHtml("<td " + rd.ToString().Trim() + "</td>");  
                      DataColumn dc = new DataColumn(tdValue);  
                      dt.Columns.Add(dc);  
                  }  
              }  
              if (i > 0)  
              {  
                  DataRow dr = dt.NewRow();  
                  for (int j = 0; j < tdMatchCollection.Count; j++)  
                  {  
                      var tdValue = RemoveHtml("<td " + tdMatchCollection[j].ToString().Trim() + "</td>");  
                      dr[j] = tdValue;  
                  }  
                  dt.Rows.Add(dr);  
              }  
          }  
          #endregion  
 
 
          #region 第二步：将DataTable导出到Excel  
          result = "ok_" + ExportDataSetToExcel(dt, fileName);  
          #endregion  
      }  
      catch (Exception ex)  
      {  
          result = "err_" + ex.Message;  
      }  
      return result;  
  }  
  
  
  /// <summary>  
  /// 将DataTable导出到Excel  
  /// </summary>  
  /// <param name="dt">DataTable</param>   
  /// <param name="fileName">仅文件名（非路径）</param>   
  /// <returns>返回Excel文件绝对路径</returns>  
  public static string ExportDataSetToExcel(DataTable dt, string fileName)  
  {  
      #region 表头  
      HSSFWorkbook hssfworkbook = new HSSFWorkbook();  
      NPOI.SS.UserModel.Sheet hssfSheet = hssfworkbook.CreateSheet(fileName);  
      hssfSheet.DefaultColumnWidth = 13;  
      hssfSheet.SetColumnWidth(0, 25 * 256);  
      hssfSheet.SetColumnWidth(3, 20 * 256);  
      // 表头  
      NPOI.SS.UserModel.Row tagRow = hssfSheet.CreateRow(0);  
      tagRow.Height = 22 * 20;  
  
  
      // 标题样式  
      NPOI.SS.UserModel.CellStyle cellStyle = hssfworkbook.CreateCellStyle();  
      cellStyle.Alignment = HorizontalAlignment.CENTER;  
      cellStyle.VerticalAlignment = VerticalAlignment.CENTER;  
      cellStyle.BorderBottom = CellBorderType.THIN;  
      cellStyle.BorderBottom = CellBorderType.THIN;  
      cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;  
      cellStyle.BorderLeft = CellBorderType.THIN;  
      cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;  
      cellStyle.BorderRight = CellBorderType.THIN;  
      cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;  
      cellStyle.BorderTop = CellBorderType.THIN;  
      cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;  
  
  
      int colIndex;  
      for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)  
      {  
          tagRow.CreateCell(colIndex).SetCellValue(dt.Columns[colIndex].ColumnName);  
          tagRow.GetCell(colIndex).CellStyle = cellStyle;  
      }  
      #endregion  
      #region 表数据  
      // 表数据    
      for (int k = 0; k < dt.Rows.Count; k++)  
      {  
          DataRow dr = dt.Rows[k];  
          NPOI.SS.UserModel.Row row = hssfSheet.CreateRow(k + 1);  
          for (int i = 0; i < dt.Columns.Count; i++)  
          {  
              row.CreateCell(i).SetCellValue(dr[i].ToString());  
              row.GetCell(i).CellStyle = cellStyle;  
          }  
      }  
      #endregion  
      FileStream file = new FileStream(HttpContext.Current.Request.PhysicalApplicationPath + "Temp/" + fileName + ".xls", FileMode.Create);  
      hssfworkbook.Write(file);  
      file.Close();  
      var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath);  
      return (basePath + "Temp/" + fileName + ".xls");  
  }  
  
  
  
  
  /// <summary>  
  ///     去除HTML标记  
  /// </summary>  
  /// <param name="htmlstring"></param>  
  /// <returns>已经去除后的文字</returns>  
  public static string RemoveHtml(string htmlstring)  
  {  
      //删除脚本      
      htmlstring =  
          Regex.Replace(htmlstring, @"<script[^>]*?>.*?</script>",  
                        "", RegexOptions.IgnoreCase);  
      //删除HTML      
      htmlstring = Regex.Replace(htmlstring, @"<(.[^>]*)>", "", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"([\r\n])[\s]+", "", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"-->", "", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"<!--.*", "", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"&(quot|#34);", "\"", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"&(amp|#38);", "&", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"&(lt|#60);", "<", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"&(gt|#62);", ">", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"&(nbsp|#160);", "   ", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"&(iexcl|#161);", "\xa1", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"&(cent|#162);", "\xa2", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"&(pound|#163);", "\xa3", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"&(copy|#169);", "\xa9", RegexOptions.IgnoreCase);  
      htmlstring = Regex.Replace(htmlstring, @"&#(\d+);", "", RegexOptions.IgnoreCase);  
  
  
      htmlstring = htmlstring.Replace("<", "");  
      htmlstring = htmlstring.Replace(">", "");  
      htmlstring = htmlstring.Replace("\r\n", "");  
      return htmlstring;  
  }
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
同语言下
可能有用的
Alex贡献的其它代码(27)