I have a sql database table with a field that has rich text html code in it. I need to export this field to a cell in export. Is this even possible? if so please let me know the syntax to use.
thanks
Pimentinho
Hi Pimentinho,
Thank you for considering Aspose products.
If I am correct in my understanding, you wish to insert HTML code in a spreadsheet cell. This is possible using the Cell.HtmlString property. Please check the below provided sample code and attached resultant Excel file for your reference.
C#
//Initialize Workbook
var workbook = new Aspose.Cells.Workbook();
//Initialize an instance of Worksheet and get first worksheet from workbook
var worksheet = workbook.Worksheets[0];
//Initialize an instance of Cells and get all cells from Worksheet
var cells = worksheet.Cells;
//Insert HTML code in first cell using HtmlString Property
cells[0, 0].HtmlString = “
This Paragraph
This Bold Text”;
//Save the Workbook in XLSX format
workbook.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);
I tried your example and only received “This Paragraph”. here is my code.
Dim cellA1 = mtgAgendaSheet.Cells((“c” + (irow).ToString()))
cellA1.HtmlString = “
This Bold Text”
This Paragraph
This Bold Text”
mtgAgendaSheet.Cells(“c” + (irow).ToString()).SetStyle(LeftStyle)
I also tried this code to get data from database:
Dim cellA1 As Aspose.Cells.Cell
cellA1 = mtgAgendaSheet.Cells((“c” + (irow).ToString()))
cellA1.HtmlString = System.Web.HttpUtility.HtmlDecode(CStr((topicDV.Table.Rows(icount).Item(0))))
mtgAgendaSheet.Cells(“c” + (irow).ToString()).SetStyle(LeftStyle)
It displayed some of the text but not all … and no formatting.
I am writing in
VB.net so if you could give examples in it rather than C# I would appreciate it.thanks
Hi Pimentinho,
Thank you for your response.
I am not sure why can’t you see the results on your end. I have performed the same procedure again on my machine, and the resultant Excel file seems just fine in MS Excel 2010 and 2007. Attached to this post is my resultant Excel spreadsheet as well as the snapshot of it for elaboration purposes. Please give a try again with the latest version of Aspose.Cells for .NET (Latest Version) , if you are previously using any older version of the component.
Below provided is the sample code in VB.NET
'Initialize Workbook
Dim workbook = New Aspose.Cells.Workbook()
'Initialize an instance of Worksheet and get first worksheet from workbook
Dim worksheet = workbook.Worksheets(0)
'Initialize an instance of Cells and get all cells from Worksheet
Dim cells = worksheet.Cells
'Insert HTML code in different cell using HtmlString Property
cells(0, 0).HtmlString = “
This Paragraph
This Bold Text”
cells(1, 0).HtmlString = “<Font Style=”“FONT-WEIGHT: bold;FONT-FAMILY: Calibri;FONT-SIZE: 20pt;”“>Aspose.Cells for .Net”
cells(2, 0).HtmlString = “<font size=”“14"”>Aspose.Cells for .Net"
worksheet.AutoFitRows()
'Save the Workbook in XLSX format
workbook.Save(myDir & “output.xlsx”, SaveFormat.Xlsx)
I am retrieving the html code for a field in a sql table ... in your example, you are hard coding the html tags in it ... I am just using the value that was stored ... I am able to get it to insert into the cells BUT .. it is adding an additional crlf on every line ... and it is not displaying correctly all the tags ... can you give me a list of all the current html tags that are allowed ... and tell me how to get rid of the extra lines? thanks
Hi David,
Most commonly used text formatting HTML tags such as bold, italic, paragraph, break etc; are supported at the moment, therefore such tags should render properly when injected in a worksheet cell. A complete list of all supported tags isn’t readily available but we can compile a list for you. In meanwhile, please share the HTML string that isn’t rendering properly. We can look into it to provide further guidance in this regard.
here is an example: An extra crlf is added between lines … and there are no dot points for the indented items.
* line 1
* line 2
Bolding
underlining
italics
crossout
1,2,3,
Hi David,
Thank you for providing the HTML string in question. By looking at the shared HTML code, it seems that the said problems could be due to the illegal usage of
tags within
tag is not inserted at the end of an item beginning with a
Hope this helps.
I am still having problems with the extra lines showing up. My management really wants me to get rid of them. I have attached screen shots of the results … as well as what it looks like when I stop the code and look at the variable X (in yellow below) and look at the text and the html views.
My management wants to be able to have the bullet points show up to.
Here is the code I am using.
cellA1.HtmlString = System.Web.HttpUtility.HtmlDecode(CStr((topicDV.Table.Rows(_icount).Item(3)))) + " entered: " + CStr(topicDV.Table.Rows(_icount).Item(4)) + " updated: " + CStr(topicDV.Table.Rows(_icount).Item(5))
mtgAgendaSheet.Cells(“c” + (_irow).ToString()).SetStyle(leftstylebold)
mtgAgendaSheet.Cells(“d” + (_irow).ToString()).SetStyle(leftstylebold)
mtgAgendaSheet.Cells(“e” + (_irow).ToString()).SetStyle(leftstylebold)
mtgAgendaSheet.Cells(“f” + (_irow).ToString()).SetStyle(leftstylebold)
mtgAgendaSheet.Cells(“g” + (_irow).ToString()).SetStyle(leftstylebold)
mtgAgendaSheet.Cells(“h” + (_irow).ToString()).SetStyle(leftstylebold)
_irow = _irow + 1
mtgAgendaSheet.Cells.Merge(_irow - 1, 2, 1, 6)
cellA1 = mtgAgendaSheet.Cells((“c” + (_irow).ToString()))
Dim x As String
x = System.Web.HttpUtility.HtmlDecode(CStr((topicDV.Table.Rows(_icount).Item(0))))
cellA1.HtmlString = x.ToString
mtgAgendaSheet.Cells.Merge(_irow, 2, 1, 6)
'create an object for AutoFitterOptions
Dim options As New AutoFitterOptions()
'set auto-fit for merged cells
options.AutoFitMergedCells = True
'Autofit rows in the sheet(including the merged cells)
mtgAgendaSheet.AutoFitRows(options)
_irow = _irow + 1
Hi David,
Thank you for writing back.
The said behaviour could be a bug in your current version of Aspose.Cells for .NET component because your HTML string seems to be OK with an exception of one
I installed the version of Aspose and am still having the same problems.
Here are the four html strings that I am using in my testing.
Example 1
"
- " & vbCrLf & "
- the numbering " & vbCrLf & "
- is fine as long " & vbCrLf & "
- as you don't want " & vbCrLf & "
- to indent to have points " & vbCrLf & "
- under the number" & vbCrLf & "
- " & vbCrLf & "
- because is doesn't " & vbCrLf & "
- look right with only numbers " & vbCrLf & "
- and no letters for common outline style adf " & vbCrLf & "
" & vbCrLf & "
Example 2
"
- " & vbCrLf & "
- why don't bullets translate? " & vbCrLf & "
-
this is strike through" & vbCrLf & " - THIS is underline?????? " & vbCrLf & "
- This is italics " & vbCrLf & "
- This is bold " & vbCrLf & "
- asdf " & vbCrLf & "
"
Example 3
"
- " & vbCrLf & "
- bullet 1 " & vbCrLf & "
- bullet 2" & vbCrLf & "
- " & vbCrLf & "
- number 1 " & vbCrLf & "
- number 2 " & vbCrLf & "
" & vbCrLf & "
* asterix 1
" & vbCrLf & "* asterix 2
" & vbCrLf & "
" & vbCrLf & "*asterix 3
" & vbCrLf & "*asterix 4
" & vbCrLf & "x
" & vbCrLf & "(1) number 1
" & vbCrLf & "(2) NUMBER 2
" & vbCrLf & "
" & vbCrLf & ". period 1
" & vbCrLf & ". period 2
" & vbCrLf & "
" & vbCrLf & "
" & vbCrLf & "
Hi David,
Thank you for providing your sample HTML string for our review.
I believe, “&vbCrLf &” is an unwanted string and it could be causing the carriage return. Anyhow, I have removed it manually and saved the results to a spreadsheet, that does not show the unwanted line breaks anymore. Please have a look at the below provided code snippet and resultant attached output.
C#
Workbook book = new Workbook();
Cell A1 = book.Worksheets[0].Cells[“A1”];
string html = string.Empty;
html = "
- the numbering
- is fine as long
- as you don’t want "
- to indent to have points
- under the number
- because is doesn’t "
- look right with only numbers
- and no letters for common outline style adf
+ “
+ "
A1.HtmlString = html;
html = “
- why don’t bullets translate?
this is strike through”- <span style=“text-decoration: underline;”>THIS is underline???
- ”
+ "This is italics - This is bold "
- asdf
+ “
+ “
<span style=“text-decoration: line-through;”>
”;Cell B1 = book.Worksheets[0].Cells[“B1”];
B1.HtmlString = html;
html = "
- bullet 1
- bullet 2
- number 1
- number 2
+ “
* asterix 1
* asterix 2
*asterix 3
*asterix 4
x
(1) number 1
”
+ “(2) NUMBER 2
. period 1
. period 2
Cell C1 = book.Worksheets[0].Cells[“C1”];
C1.HtmlString = html;
book.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);
Although, there are few tags that have not rendered properly. I am looking into it further to confirm if such tags are supported as HTML based cell value. I will keep you posted with updates in this regard.
I have ran the above example against the most recent version of Aspose.Cells. And bullet points are still not rendered for unordered lists among other tags not being honored. Is this on the queue for fixes? Also could a list of supported tags being provided?
Hi Zach,
Thank you for trying the latest version of Aspose.Cells.
Please note, it is the limitation of Excel application that it does not recognize the HTML tags for the bullet (un-ordered) list. However, the bullets can be simulated using the symbols such as asterisks or dots at the start of each item. Please check the cell C1 from the resultant spreadsheet using the above code snippet.
In spite of the fact that Aspose.Cells APIs allow to add HTML string to the cell but they will not render if the displaying application (Excel) does not support them therefore the presented behavior is not a bug on the part of Aspose.Cells APIs.
Regarding the list of supported HTML tags, unfortunately, we currently do not have a list. However, we will check if we can create such list and add it to the documentation for future reference.