Sometimes, we have situations where we get data as HTML data and we have to convert it to DataTable. From DataTable, we can export it to xlsx or use it according to our need.

The problem I faced with HTML data, was that I had to export a report in xlsx format and I had only HTML data. The HTML data was exporting into xlsx but with a format message, while opening the Excel. That means, it was writing the HTML to the Excel sheet as it appeared. But for the Excel sheet, we need columns to be written properly into each cell. xlsx doesn't support direct HTML conversion to xlsx cells.
To export HTML data to xlsx, I had to convert the HTML data to DataTable from where I can export the data easily and there will be no format message while opening the Excel. Below is the code to convert the HTML to DataTable.
  1. public static DataTable ConvertHTMLTablesToDataTable(string HTML)
  2. {
  5. DataTable dt = null;
  6. DataRow dr = null;
  7. DataColumn dc = null;
  8. string TableExpression = "<table[^>]*>(.*?)</table>";
  9. string HeaderExpression = "<th[^>]*>(.*?)</th>";
  10. string RowExpression = "<tr[^>]*>(.*?)</tr>";
  11. string ColumnExpression = "<td[^>]*>(.*?)</td>";
  12. bool HeadersExist = false;
  13. int iCurrentColumn = 0;
  14. int iCurrentRow = 0;
  16. // Get a match for all the tables in the HTML
  17. MatchCollection Tables = Regex.Matches(HTML, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
  19. // Loop through each table element
  20. foreach (Match Table in Tables)
  21. {
  23. // Reset the current row counter and the header flag
  24. iCurrentRow = 0;
  25. HeadersExist = false;
  27. // Add a new table to the DataSet
  28. dt = new DataTable();
  30. // Create the relevant amount of columns for this table (use the headers if they exist, otherwise use default names)
  31. if (Table.Value.Contains("<th"))
  32. {
  33. // Set the HeadersExist flag
  34. HeadersExist = true;
  36. // Get a match for all the rows in the table
  37. MatchCollection Headers = Regex.Matches(Table.Value, HeaderExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
  39. // Loop through each header element
  40. foreach (Match Header in Headers)
  41. {
  42. //dt.Columns.Add(Header.Groups(1).ToString);
  43. dt.Columns.Add(Header.Groups[1].ToString());
  45. }
  46. }
  47. else
  48. {
  49. for (int iColumns = 1; iColumns <= Regex.Matches(Regex.Matches(Regex.Matches(Table.Value, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase)[0].ToString(), RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase)[0].ToString(), ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase).Count; iColumns++)
  50. {
  51. dt.Columns.Add("Column " + iColumns);
  52. }
  53. }
  55. // Get a match for all the rows in the table
  56. MatchCollection Rows = Regex.Matches(Table.Value, RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
  58. // Loop through each row element
  59. foreach (Match Row in Rows)
  60. {
  62. // Only loop through the row if it isn't a header row
  63. if (!(iCurrentRow == 0 & HeadersExist == true))
  64. {
  66. // Create a new row and reset the current column counter
  67. dr = dt.NewRow();
  68. iCurrentColumn = 0;
  70. // Get a match for all the columns in the row
  71. MatchCollection Columns = Regex.Matches(Row.Value, ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
  73. // Loop through each column element
  74. foreach (Match Column in Columns)
  75. {
  77. DataColumnCollection columns = dt.Columns;
  79. if (!columns.Contains("Column " + iCurrentColumn))
  80. {
  81. //Add Columns
  82. dt.Columns.Add("Column " + iCurrentColumn);
  83. }
  84. // Add the value to the DataRow
  85. dr[iCurrentColumn] = Column.Groups[1].ToString();
  86. // Increase the current column
  87. iCurrentColumn += 1;
  89. }
  91. // Add the DataRow to the DataTable
  92. dt.Rows.Add(dr);
  94. }
  96. // Increase the current row counter
  97. iCurrentRow += 1;
  98. }
  101. }
  103. return (dt);
  105. }
This line gets all the Table matches as defined in TableExpression variable.
  1. MatchCollection Tables = Regex.Matches(HTML, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
This line gets the total number of columns using ColumnExpressions as <td>. It created each <td> as a column.
  1. MatchCollection Columns = Regex.Matches(Row.Value, ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
This line gets the header using HeaderExpression as <th>. It created each <th> as a Header.
  1. MatchCollection Headers = Regex.Matches(Table.Value, HeaderExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
Use this code to convert HTML to DataTable. If you have found any problem, please comment below.