Hi,
I would like to know if there is some approach to importing some HTML into exists template using smart markers (or any other way to attach that
for example
<tr> <td>id</td> <td> name </td> </tr>
<tr> <td>1</td> <td> ziv </td> </tr>
I thought I can insert that via SetDataSource as var , but this is not printing multiple rows (not sure how I can use it ). of course, I’ve set the property name as var for the smart marker ( i.e
&=$htmlContent(HTML)
) but this is not responding as expected. (displaying as empty row instead of inserting 2 rows)
any other ideas on what I can do?
@zivdaniel,
Which version of Aspose.Cells for .NET you are using? I tested your scenario/case with latest version/fix: Aspose.Cells for .NET 22.10.x and it works fine. I used the following sample code and the output file is fine tuned:
e.g.
Sample code:
Workbook workbook = new Workbook();
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = workbook;
workbook.Worksheets[0].Cells["A1"].PutValue("&=$VariableArray(HTML)");
designer.SetDataSource("VariableArray", new String[] { "<table width=\"100%\" border=\"0\" cellpadding=\"2\" "
+ "cellspacing=\"0\"><tr><td>"
+ "<p align=\"left\">id</td><td> name</font></td></tr>"
+ "<tr><td><p align=\"left\"></p>1 </td><td> zv</p></td></tr>"
+ "</table>" });
designer.Process();
workbook.Save("e:\\test2\\out1.xlsx");
Please find attached the output file for your reference.
files.zip (6.0 KB)
we working with the latest version of aspose ( 22.10.X)
I’ll try to change my as you displaying here. thank you
btw- there is any way to split that into multiple rows and column and not show them as single table in the column? (column numbers are known, but rows is N)
@zivdaniel,
This can be achieved via a simple workaround. You may first import your desired HTML (string/stream) into a new Workbook. Now export the data to fill a datatable. Then, either import that datatable directly via Cells.ImportData() method or use Smart Markers feature to extract data into the cells from datasource. See the following sample code on how to workaround it for your reference.
e.g.
Sample code:
string html = @"<table border=""2px"">
<tr><td><table><tbody><tr><th>Header 1</th></tr></tbody></table></td><td><table><tbody><tr><th>Header 2</th></tr></tbody></table></td></tr>
<tr><td>Value 1</td><td>Value 2</td></tr>
<tr><td>Value 1A</td><td>Value 2B</td></tr>
</table>";
byte[] byteArray = Encoding.ASCII.GetBytes(html);
MemoryStream stream = new MemoryStream(byteArray);
Workbook workbook = new Workbook(stream);
Worksheet sheet = workbook.Worksheets[0];
DataTable dataTable = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1, true);
Workbook workbook1 = new Workbook("Book1.xlsx"); //this is your template file.
ImportTableOptions options = new ImportTableOptions();
options.InsertRows = true;
options.IsFieldNameShown = true;
workbook1.Worksheets[0].Cells.ImportData(dataTable, 0, 0, options);
workbook1.Save("e:\\test2\\out1.xlsx");
Hope, this helps a bit.
Yes, this helps a lot thank you!!
I’ve managed to insert the HTML as you suggest (attached )
there is any way to import the table to excel also with the design?
I’m thinking about taking your idea, with the import HTML into excel then copying the generated rows into the template.
this is the code that is responsible for HTML:
private static void RenderHtml(string html, WorkbookDesigner designer)
{
//var dataTable = GetHtmlAsDataTable(html);
//var options = new ImportTableOptions
//{
// InsertRows = true,
// IsFieldNameShown = true,
// CheckMergedCells = true,
// ConvertGridStyle = true
//};
var htmlDataArea = designer.Workbook.Worksheets.Names.Find(match => match.Text == "html_data_area");
if (htmlDataArea != null)
{
var range = htmlDataArea.GetRange();
var sheet = RenderHtmlWorksheet(html);
var activeWorksheet = designer.Workbook.Worksheets[designer.Workbook.Worksheets.ActiveSheetIndex];
activeWorksheet.Cells.CopyRows(sheet.Cells, 0, range.FirstRow, 0);
}
}
private static DataTable GetHtmlAsDataTable(string html)
{
var sheet = RenderHtmlWorksheet(html);
var dataTable = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1, true);
return dataTable;
}
private static Worksheet RenderHtmlWorksheet(string html)
{
byte[] byteArray = Encoding.ASCII.GetBytes(html);
var stream = new MemoryStream(byteArray);
var workbook = new Workbook(stream);
var sheet = workbook.Worksheets[0];
return sheet;
}
usagereport_27_10_2022_14_06_26_ with_copy_html_cells_into_template.pdf (52.5 KB)
usagereport_27_10_2022_13_47_35_with_data_table.pdf (60.4 KB)
btw - you mention that there is also possible to move the data between them with the smart markers. do you have any example / documentation?
@zivdaniel,
What do you mean, please elaborate? If you meant import HTML table into an existing template Excel file then it will insert the table into a single cell and won’t split data to multiple rows and columns.
Yes, you are doing ok.
Please see the sample code for your reference.
e.g.
Sample code:
string html = @"<table border=""2px"">
<tr><td><table><tbody><tr><th>Header 1</th></tr></tbody></table></td><td><table><tbody><tr><th>Header 2</th></tr></tbody></table></td></tr>
<tr><td>Value 1</td><td>Value 2</td></tr>
<tr><td>Value 1A</td><td>Value 2B</td></tr>
</table>";
byte[] byteArray = Encoding.ASCII.GetBytes(html);
MemoryStream stream = new MemoryStream(byteArray);
Workbook workbook = new Workbook(stream);
Worksheet sheet = workbook.Worksheets[0];
DataTable dataTable = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1, true);
dataTable.TableName = "Table1";
Workbook workbook1 = new Workbook();
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = workbook1;
//input smart markers
workbook1.Worksheets[0].Cells["A1"].PutValue("&=Table1.[Header 1]");
workbook1.Worksheets[0].Cells["B1"].PutValue("&=Table1.[Header 2]");
designer.SetDataSource(dataTable);
designer.Process(true);
workbook1.Save("e:\\test2\\out1.xlsx");
Also, see the document on Smart Markers for your reference.
Hi, thank you very much.
I’ve proceed with the option to copy rows and the report downloaded as expected. thank you!
1 thing - the method “copy rows” working only on one row and not on multiple as sounds from the method name.
this is the
var range = htmlDataArea.GetRange();
var sheet = RenderHtmlWorksheet(html);
var activeWorksheet = designer.Workbook.Worksheets[designer.Workbook.Worksheets.ActiveSheetIndex];
//loop over all the rows and copy the rows to template
for (int i = 1; i < sheet.Cells.MaxDataRow + 1; i++)
{
activeWorksheet.Cells.CopyRows(sheet.Cells, sheet.Cells.MinRow, range.FirstRow, i + 1);
}
instead of
activeWorksheet.Cells.CopyRows(sheet.Cells, sheet.Cells.MinRow, range.FirstRow, i + 1);
I think, (not sure if I understand that correctly) its should be
activeWorksheet.Cells.CopyRows(sheet.Cells, sheet.Cells.MinRow, range.FirstRow);
@zivdaniel
1,Please change the loop as the following codes:
activeWorksheet.Cells.CopyRows(sheet.Cells, sheet.Cells.MinRow, range.FirstRow, range.RowCount);
BTW ,please cache sheet.Cells.MaxDataRow before the loop for we have to iterate all data to get max data row.
2,If you want to copy the whole worksheet, please use Worksheet.Copy as the following 
activeWorksheet.Copy(sheet);
3, You can use Range.Copy method to copy range.
Hi @simon.zhao
I’ve tried to change that as you suggested but I got a compilation error for the no-overload method for that.
image.png (49.3 KB)
Thank you for the ideas, basically - I’m importing HTML into a new workbook and then copying the rows into templates that already contain data
if we have range.copy(sheet) this will be awesome I think, but tricky as well.
@zivdaniel,
We asked you to use the line of code instead:
activeWorksheet.Cells.CopyRows(sheet.Cells, sheet.Cells.MinRow, range.FirstRow, range.RowCount);
But you are not doing it rightly. So, please do the needful and update your code segment accordingly.
@Amjad_Sahi,
not sure I understand you correctly.
option 1: loop over the rowcount
code: image.png (48.9 KB)
result: usagereport_31_10_2022_11_03_51.pdf (55.7 KB)
option 2: without loop.
code: image.png (50.0 KB)
result : usagereport_31_10_2022_11_03_14.pdf (55.7 KB)
option 3: loop over max data count.
code: image.png (49.3 KB)
result: usagereport_31_10_2022_11_04_29.pdf (78.1 KB)
@zivdaniel,
Thanks for the files and screenshots.
Your provided options with resource files seem ok. If you find any issue, let us know with details and samples.