Free Support Forum - aspose.com

Exporting a rich text sql database field to an excel cell

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);
Please feel free to write back in case you have further inquiries.

I tried your example and only received “This Paragraph”. here is my code.



Dim cellA1 = mtgAgendaSheet.Cells((“c” + (irow).ToString()))
cellA1.HtmlString = “

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.


  • Introduce internal WF team
  • Identify key contacts at the company
  • Discuss project process and timeline
    1. No formatting
    2.    * line 1
         * line 2
      Bolding
      underlining
      italics
      crossout

      1,2,3,
    3. 234234
    4. 234234
    5. 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

    6. tags. Please note, a
      tag is not inserted at the end of an item beginning with a

    7. tag, as a line break automatically occurs at that point.

      Hope this helps.
    8. 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

    9. tag closing inappropriately. Please give a try to the latest build of Aspose.Cells for .NET 7.6.1.4 (attached) to see if it makes any difference. In case your problem persists, please provide us the HTML string by saving it to disk. After reviewing the results on our end, if required, we will log appropriate ticket for investigation and correction purposes.
    10. 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 & "
      1. the numbering
      2. " & vbCrLf & "
      3. is fine as long
      4. " & vbCrLf & "
      5. as you don't want
      6. " & vbCrLf & "
      7. to indent to have points
      8. " & vbCrLf & "
      9. under the number" & vbCrLf & "
          " & vbCrLf & "
        1. because is doesn't
        2. " & vbCrLf & "
        3. look right with only numbers
        4. " & vbCrLf & "
        5. and no letters for common outline style adf
        6. " & vbCrLf & "
        " & vbCrLf & "
      10. " & 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 & "
      " & vbCrLf & "

      "

      Example 3

      "

        " & vbCrLf & "
      • bullet 1
      • " & vbCrLf & "
      • bullet 2" & vbCrLf & "
          " & vbCrLf & "
        1. number 1
        2. " & vbCrLf & "
        3. number 2
        4. " & vbCrLf & "
        " & vbCrLf & "
      • " & 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 = "
      1. the numbering
      2. is fine as long
      3. as you don’t want
      4. "
        + "
      5. to indent to have points
      6. under the number
        1. because is doesn’t
        2. "
          + “
        3. look right with only numbers
        4. 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
        1. number 1
        2. 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.