Syncfusion Excel (XlsIO) Library is a .NET Excel library that allows users to export data from various data sources to Excel worksheets in C# and VB.NET. This library allows you to export even HTML tables to Excel worksheets. This feature will be useful when there is a need to extract data from an HTML document and manage it.
If you are looking for easy steps to do this, then you are in the right place.
Consider a scenario where you have webpages with HTML tables. To retrieve data from each webpage, you might need to copy the tables in the page manually. To automate this process, XlsIO provides the support to export any number of HTML tables in a webpage to an Excel worksheet. This makes the work simple and saves time, too. The export includes the table formatting, as well.
Let’s see how to export HTML tables from a webpage to Excel worksheets in C# using XlsIO.
Export HTML table
The ImportHtmlTable method loads an HTML file and imports all the tables in the file to the worksheet. This import operation includes the table formatting that is defined within the HTML file.
For demonstration, I am going to create an ASP.NET Core application to export HTML tables to an Excel worksheet using the Syncfusion XlsIO library.
Step 1: Create an ASP.NET Core project.
Step 2: Add a view; I am naming it ExportHtmlTables.cshtml. Add the following sample code. This code contains an HTML table in the HTML DIV, a hidden field, and an Export button. This button has been assigned a jQuery click event handler to copy the contents of the HTML DIV to the hidden field, so that the HTML string can be sent to the server.
@{Html.BeginForm("ImportHtmlTable", "ImportHtmlTable", FormMethod.Post);
{
<div class="Common">
<div class="tablediv">
<div class="rowdiv">
<p>
Essential XlsIO supports exporting HTML tables into Excel worksheets. The <b>ImportHtmlTable</b> method loads an HTML file and exports all the tables in the file to the worksheet.
</p>
<b>Features:</b>
<br />
<ul>
<li>Imports HTML table</li>
<li>Imports with table formatting </li>
</ul>
<br />
<div id="cssStyle">
<style type="text/css">
th {
color:rgb(0,0,0);
font-family:Tahoma, sans-serif;
font-size:10pt;
white-space:nowrap;
background-color:rgb(255,174,33);
border-top:solid;
border-top-width: thin;
border-bottom:solid;
border-bottom-width:thin;
border-left:solid;
border-left-width:thin;
border-right:solid;
border-right-width:thin;
border-top-color:rgb(0,0,0);
border-bottom-color:rgb(0,0,0);
border-left-color:rgb(0,0,0);
border-right-color:rgb(0,0,0);
font-weight:bold;
vertical-align:bottom;
}
td {
color:rgb(0,0,0);
font-family:Tahoma, sans-serif;
font-size:10pt;
white-space:nowrap;
background-color:rgb(239,243,247);
border-left:solid;
border-left-width:thin;
border-right:solid;
border-right-width:thin;
border-top-color:rgb(0,0,0);
border-bottom-color:rgb(0,0,0);
border-bottom:solid;
border-bottom-width:thin;
border-left-color:rgb(0,0,0);
border-right-color:rgb(0,0,0);
vertical-align:bottom;
}
</style>
</div>
<div id="Grid">
<table cellspacing="0" width="500" style="table-layout:fixed;border-collapse:collapse;width:500pt">
<tr>
<th class="X64" style="text-align:left;">
<span>CustomerID</span>
</th>
<th class="X64" style="text-align:left;">
<span>CompanyName</span>
</th>
<th class="X64" style="text-align:left;">
<span>ContactName</span>
</th>
<th class="X64" style="text-align:left;">
<span>Phone</span>
</th>
</tr>
<tr height="25">
<td class="X65" style="text-align:left;">
<span>ALFKI</span>
</td>
<td class="X65" style="text-align:left;">
<span>Alfreds Futterkiste</span>
</td>
<td class="X65" style="text-align:left;">
<span>Maria Anders</span>
</td>
<td class="X65" style="text-align:left;">
<span>030-0074321</span>
</td>
</tr>
<tr height="25">
<td class="X65" style="text-align:left;">
<span>ANATR</span>
</td>
<td class="X65" style="text-align:left;">
<span>Ana Trujillo Emparedados</span>
</td>
<td class="X65" style="text-align:left;">
<span>Ana Trujillo</span>
</td>
<td class="X65" style="text-align:left;">
<span>(5) 555-4729</span>
</td>
</tr>
<tr height="25">
<td class="X65" style="text-align:left;">
<span>ANTON</span>
</td>
<td class="X65" style="text-align:left;">
<span>Antonio Moreno Taquería</span>
</td>
<td class="X65" style="text-align:left;">
<span>Antonio Moreno</span>
</td>
<td class="X65" style="text-align:left;">
<span>(5) 555-3932</span>
</td>
</tr>
<tr height="25">
<td class="X65" style="text-align:left;">
<span>AROUT</span>
</td>
<td class="X65" style="text-align:left;">
<span>Around the Horn</span>
</td>
<td class="X65" style="text-align:left;">
<span>Thomas Hardy</span>
</td>
<td class="X65" style="text-align:left;">
<span>(171) 555-7788</span>
</td>
</tr>
<tr height="25">
<td class="X65" style="text-align:left;">
<span>BERGS</span>
</td>
<td class="X65" style="text-align:left;">
<span>Berglunds snabbköp</span>
</td>
<td class="X65" style="text-align:left;">
<span>Christina Berglund</span>
</td>
<td class="X65" style="text-align:left;">
<span>0921-12 34 65</span>
</td>
</tr>
<tr height="25">
<td class="X65" style="text-align:left;">
<span>BLAUS</span>
</td>
<td class="X65" style="text-align:left;">
<span>Blauer See Delikatessen</span>
</td>
<td class="X65" style="text-align:left;">
<span>Hanna Moos</span>
</td>
<td class="X65" style="text-align:left;">
<span>0621-08460</span>
</td>
</tr>
</table>
</div>
</div>
<br />
<input name="tableHTML" id="tbl" hidden="hidden"/>
<div class="rowdiv">
<div class="celldiv">
<input id="ExportTbl" class="buttonStyle" type="submit" name="button" value="Export" onclick="exportHTML()" style="width:125px;" />
</div>
</div>
</div>
</div>
Html.EndForm();
}
}
<script type="text/javascript">
function exportHTML() {
let value = "<html>" + document.getElementById("cssStyle").innerHTML + "<body>" + document.getElementById("Grid").innerHTML + "</body></html>";
value = value.replace("<tbody>", "");
value = value.replace("</tbody>", "");
document.getElementById("tbl").setAttribute("value", value);
}
</script>This code will create the webpage shown in the following screenshot.
Step 3: Create an Excel document in the controller class ExportHtmlTableController.cs.
Step 4: The HTML string parsed into the hidden field is now passed to the click event. Then, convert the HTML string into a stream and load it using the ImportHtmlTable method, then export it to an Excel worksheet. The start row and column must be specified while exporting them. After exporting to a worksheet, auto fit the rows and columns and save the output Excel file.
The following code example shows how to export a webpage with an HTML table to an Excel worksheet in C# using XlsIO.
public ActionResult ImportHtmlTable(string button, string tableHTML)
{
if (button == null)
return View();
MemoryStream ms = new MemoryStream();
// The instantiation process consists of two steps.
// Step 1: Instantiate the spreadsheet creation engine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
// Step 2 : Instantiate the Excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
// A workbook is created.
IWorkbook workbook = application.Workbooks.Create(1);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
byte[] byteArray = Encoding.UTF8.GetBytes(tableHTML);
MemoryStream file = new MemoryStream(byteArray);
// Imports HTML table into the worksheet from first row and first column.
worksheet.ImportHtmlTable(file, 1, 1);
worksheet.UsedRange.AutofitColumns();
worksheet.UsedRange.AutofitRows();
workbook.SaveAs(ms);
ms.Position = 0;
}
return File(ms, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Export-HTML-Table.xlsx");
}Note: You can also use this ImportHtmlTable method to load an HTML file from a disk to export the HTML tables to an Excel worksheet. For that, you need to specify the starting row and column values while importing the HTML file. Refer to the following code example.
//Exports HTML tables to the worksheet from first row and first column.
worksheet.ImportHtmlTable("Import-HTML-Table.html", 1, 1);The following screenshot is the output of the Excel file exported from the webpage with the HTML table.
GitHub samples
You can download the example of exporting HTML tables to Excel worksheets in C# on this GitHub demo.
Wrapping up
As you can see, Syncfusion Excel (XlsIO) Library provides support to easily export HTML tables to Excel in C#. Take a moment to peruse the documentation, where you’ll find other importing options and features like data tables, collection objects, grid view, data columns, and CSV, all with accompanying code samples.
Using the XlsIO library, you can also export Excel data to PDFs, images, data tables, CSV, TSV, HTML, collections of objects, ODS, JSON, and more file formats.
If you are new to our Excel Library, it is highly recommended to follow our Getting Started guide.
Are you already a Syncfusion user? You can download the product setup ® for File Formats products” href=”https://www.syncfusion.com/downloads/fileformats/confirm” target=”_blank” rel=”noopener”>here.
If you have any questions about these features, please let us know in the comments section below. You can also contact us through our support forum, support portal, or feedback portal. We are always happy to assist you!
Comments (11)
where do we import File???
Hi Gagan,
Your query is not clear. Are you asking how to import an HTML file to an Excel worksheet? If that is your query then it is not supported. Only the tables in an HTML file can be imported into an Excel worksheet.
If you can explain your query clearly, we can assist you.
Regards,
Johnson
hello , i want some selected columns export from table ….
Hi Kranti,
There is no option to export specific columns from HTML table to Excel. But you can achieve your requirement by deleting the unwanted columns after exporting to Excel worksheet.
You can refer the following links to delete columns from Excel worksheet using XlsIO.
1. https://www.syncfusion.com/kb/1918/delete-excel-rows-and-columns-in-c-vb-net
2. https://www.syncfusion.com/kb/1918/delete-excel-rows-and-columns-in-c-vb-net
Regards,
Mohan.
Hi Kranti,
There is another option is there. u can directly use MVC action methods and directly get data from the database. u can add additional or remove unwanted columns. then export to .xlsx
using FileStreamResult action method in MVC
The Best way u can store data in the session. then when clicking the download excel button. That time u can restore data from the session object and export it to excel.
Best Regards,
Avinash
Hello Johnson and Avinash,
I am trying to export table in html to excel but when i generate i get empty excel. unable to convert html table to excel.
I am using asp.net mvc
C# code i am using in same as above.
I have used nuget package for installing dll.
I have registered license key.
I haven’t used essestial file format studio. hence didnt used unlock keys.
I have connected with ben spears to reach you out faster.
Expecting your prompt reply.
Hi Harsh,
We have fixed a similar issue in our 2022 Volume 2 release v20.2.0.36. We suggest you to update to the latest version and check whether the issue fixed at your end. Please update us whether if you are still facing any issue.
Regards,
Mohan.
Hi,
You have used style classes like these – <td class="X65"…. Is it possible to share some samples on what styles are set using these?
Thank you!
Hi Sunil,
There is a mistake in the code snippet. X64 and X65 refer to the CSS class style which is used in the HTML table. Instead of this, we have used the names with “th” and “tr”. It will be modified and republished.
Regards,
Mohan.
hi
is there any way of importing into excel an HTML table, with additional data carrying over to cells comments ? (you know, the ones with the small red triangle in the upper left corner)
thank you
Johnny
Hi Johnny
Currently, there are no options to import comments from the HTML table to Excel. But you can add comments to Excel cells after importing the table to the worksheet. Please refer to the below documentation to know how to add comments in Excel.
https://help.syncfusion.com/file-formats/xlsio/working-with-drawing-objects#comments
Regards,
Mohan.
Comments are closed.