IsTextWrapped is not working when used with a Style object and ApplyStyle for a row

Aspose.Cells Product Family
1

I've created a Style object and set the borders and IsTextWrap to true. The borders get applied by the IsTextWrap does not. Any ideas?


Dim Workbook As New Workbook
Workbook.Open(CurrentContext.Server.MapPath("~/Reports/export/export.xls"), FileFormatType.Default)

Dim Worksheet As Worksheet = Workbook.Worksheets(0)

'Add a title
Worksheet.Cells("A1").PutValue("Exported as of " & GetCurrentDate())

'Add a new style to the Workbook
Dim CurrentNameStyle As Style = Workbook.Styles(Workbook.Styles.Add())
CurrentNameStyle.Font.IsBold = True
CurrentNameStyle.Font.Color = Color.White
CurrentNameStyle.Font.Size = 10
CurrentNameStyle.BackgroundColor = Color.Black
CurrentNameStyle.ForegroundColor = Color.Black
CurrentNameStyle.Pattern = BackgroundType.Solid
CurrentNameStyle.VerticalAlignment = TextAlignmentType.Distributed

Dim CurrentNameStyleFlag As StyleFlag = New StyleFlag
CurrentNameStyleFlag.FontBold = True
CurrentNameStyleFlag.FontColor = True
CurrentNameStyleFlag.FontSize = True
CurrentNameStyleFlag.CellShading = True
CurrentNameStyleFlag.VerticalAlignment = True


'Add a another new style to the Workbook
Dim DealStyle As Style = Workbook.Styles(Workbook.Styles.Add())
DealStyle.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Dotted
DealStyle.IsTextWrapped = True

Dim DealStyleFlag As StyleFlag = New StyleFlag
DealStyleFlag.Borders = True
DealStyleFlag.WrapText = True

Dim Export As DataTable = GetExport()
Dim strCurrentName As String = String.Empty
Dim blnNameRow = True
Dim blnStop = False
Dim i As Integer
Dim currentRow As Integer = 2
Dim DealCounter As Integer = 0

While i < Export.Rows.Count
Try
strCurrentName = Export.Rows(i)("name")

While strCurrentName = Export.Rows(i)("name")
If blnNameRow = True Then

DealCounter = 0

Worksheet.Cells(currentRow, 1).PutValue(strCurrentName)
Dim Range As Range = Worksheet.Cells.CreateRange(currentRow, 0, 1, Worksheet.Cells.MaxDataColumn + 1)
Range.ApplyStyle(CurrentNameStyle, CurrentNameStyleFlag)
Worksheet.Cells.SetRowHeight(currentRow, 20)

currentRow += 1

blnNameRow = False
End If



'Apply style for the row, but only if it isn't the first deal
If DealCounter > 1 Then
Dim Range As Range = Worksheet.Cells.CreateRange(currentRow, 0, 1, Worksheet.Cells.MaxDataColumn + 1)
Range.ApplyStyle(DealStyle, DealStyleFlag)
End If


DealCounter += 1

Worksheet.Cells(currentRow, 0).PutValue(DealCounter)

Worksheet.Cells(currentRow, 1).PutValue(RemoveSmartQuotes(Export.Rows(i)("companyname")) & FormatAddOn(RemoveSmartQuotes(Export.Rows(i)("x"))))

Worksheet.Cells(currentRow, 2).PutValue(RemoveSmartQuotes(ConvertEmptyStringToNA(Export.Rows(i)("x"))))

Worksheet.Cells(currentRow, 3).PutValue(RemoveSmartQuotes(ConvertEmptyStringToNA(Export.Rows(i)("x"))))

Worksheet.Cells(currentRow, 4).PutValue(RemoveSmartQuotes(ConvertEmptyStringToNA(Export.Rows(i)("x"))))

Worksheet.Cells(currentRow, 5).PutValue(ConvertZeroToNA(Export.Rows(i)("x"), "N"))
Worksheet.Cells(currentRow, 5).Style.HorizontalAlignment = TextAlignmentType.Right

Worksheet.Cells(currentRow, 6).PutValue(ConvertZeroToNA(Export.Rows(i)("x"), "N"))
Worksheet.Cells(currentRow, 6).Style.HorizontalAlignment = TextAlignmentType.Right

Worksheet.Cells(currentRow, 7).PutValue(ConvertZeroToNA(Export.Rows(i)("x"), "P"))
Worksheet.Cells(currentRow, 7).Style.HorizontalAlignment = TextAlignmentType.Right

Worksheet.Cells(currentRow, 8).PutValue(ConvertZeroToNA(Export.Rows(i)("x"), "P"))
Worksheet.Cells(currentRow, 8).Style.HorizontalAlignment = TextAlignmentType.Right


Worksheet.Cells(currentRow, 9).PutValue(ConvertDateToNA(Export.Rows(i)("x")))
Worksheet.Cells(currentRow, 9).Style.HorizontalAlignment = TextAlignmentType.Right
SetIOIDueDateColor(Worksheet.Cells(currentRow, 9), CDate(GetCurrentDate()), Export.Rows(i)("x"))

Worksheet.Cells(currentRow, 10).PutValue(Export.Rows(i)("x"))
Worksheet.Cells(currentRow, 10).Style.HorizontalAlignment = TextAlignmentType.Center

Worksheet.Cells(currentRow, 11).PutValue("$" & RemoveSmartQuotes(Export.Rows(i)("x")) & " - $" & RemoveSmartQuotes(Export.Rows(i)("x")) & " (" & RemoveSmartQuotes(Export.Rows(i)("x")) & ")")


Worksheet.Cells(currentRow, 12).PutValue(RemoveSmartQuotes(Export.Rows(i)("x")))


Worksheet.AutoFitRow(currentRow)


i += 1
currentRow += 1

End While

blnNameRow = True
strCurrentName = Export.Rows(i)("name")



Catch ex As IndexOutOfRangeException
End Try
End While


Workbook.Save("Export.xls", FileFormatType.Default, SaveType.OpenInExcel, CurrentContext.Response)


2

Hi,

Well, I implemented your scenario a bit with latest version 4.8.2.x and it works fine.

I could not use your exact code fully as you used some pointers and also you are retrieving data from some data table. Well, I think you need to make sure one thing which is when you apply text wrapping to certain range of cells, the rows for those range should be extended enough (using Worksheet.SetRowHeight() method or you may use Cells.AutoFitRow() method) to display the contents properly in multi-lines.

Here is my sample code for your reference and attached is the output file.

Sample code:
Dim Workbook As New Workbook()
Dim worksheet As Aspose.Cells.Worksheet = Workbook.Worksheets(0)

'Add a title
worksheet.Cells(“A1”).PutValue(“Exported as of”)
'Add a new style to the Workbook
Dim CurrentNameStyle As Style = Workbook.Styles(Workbook.Styles.Add())
CurrentNameStyle.Font.IsBold = True
CurrentNameStyle.Font.Color = Color.White
CurrentNameStyle.Font.Size = 10
CurrentNameStyle.BackgroundColor = Color.Black
CurrentNameStyle.ForegroundColor = Color.Black
CurrentNameStyle.Pattern = BackgroundType.Solid
CurrentNameStyle.VerticalAlignment = TextAlignmentType.Distributed

Dim CurrentNameStyleFlag As New StyleFlag()
CurrentNameStyleFlag.FontBold = True
CurrentNameStyleFlag.FontColor = True
CurrentNameStyleFlag.FontSize = True
CurrentNameStyleFlag.CellShading = True
CurrentNameStyleFlag.VerticalAlignment = True


'Add a another new style to the Workbook
Dim DealStyle As Style = Workbook.Styles(Workbook.Styles.Add())
DealStyle.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Dotted
DealStyle.IsTextWrapped = True

Dim DealStyleFlag As New StyleFlag()
DealStyleFlag.Borders = True
DealStyleFlag.WrapText = True

'DataTable Export = GetExport();

Dim ds As New DataSet()

Dim dt As System.Data.DataTable = New DataTable(“Table1”)
dt.Columns.Add(“Customer Identification Number”, GetType(String))
dt.Columns.Add(“Customer Name”, GetType(String))
dt.Columns.Add(“Description Text”, GetType(String))
For i As Integer = 1 To 20
Dim dr As System.Data.DataRow = dt.NewRow()
dr(“Customer Identification Number”) = i.ToString()
dr(“Customer Name”) = "Name " & i
dr(“Description Text”) = "Description " & i
dt.Rows.Add(dr)
Next i
ds.Tables.Add(dt)

worksheet.Cells.ImportDataTable(ds.Tables(0), True, 1, 0, True)

Dim Range As Range = worksheet.Cells.CreateRange(0, 0, 2, worksheet.Cells.MaxDataColumn + 1)
Range.ApplyStyle(DealStyle, DealStyleFlag)




worksheet.AutoFitRow(0)
worksheet.AutoFitRow(1)

'Or extend row heights for first and second rows. Moreover, you may
'Extend the column widths for your need too.
'worksheet.Cells.SetRowHeight(0, 30);
'worksheet.Cells.SetRowHeight(1, 40);

Workbook.Save(“e:\test\outExport_test2.xls”)

If you still find any issue, kindly create a console application, zip it and post it here to reproduce the issue. We will check it soon.


Thank you.

3

The only way I can get it to work is to set the IsTextWrapped property on the cell.

Could it have to do with the template or the data in cell?

Side question: Does Aspose.Cell have a function to remove Smart characters or handle them?

4

Hi,

Well, the wrapping text feature should work equally fine for the following cases:
1) For a Single Cell - using Cell.Style.IsTextWrapped property

2) For a Single Cell - Create a Style object or get the existing style of the cells(using Cell.GetStyle() method) and set the wrapping text attribute e.g style.IsTextWrapped to set to true. Now use Cell.SetStyle() to apply the style giving the object as a parameter to the method.

3) For a Row - Create a Style object and set the wrapping text attribute e.g
style.IsTextWrapped to set to true. Also, create the StyleFlag object to make the corresponding wrapping text attribute on i.e. StyleFlag.WrapText = True. Now use Cells.ApplyRowStyle() to apply
the style giving the row index, style object and styleflag object as parameters for the method.

4) For a Column - Create a Style object and set the wrapping text
attribute e.g
style.IsTextWrapped to set to true. Also, create the StyleFlag object
to make the corresponding wrapping text attribute on i.e.
StyleFlag.WrapText = True. Now use Cells.ApplyColumnStyle() to apply
the style giving the column index, style object and styleflag object as parameters
for the method.

5) For a Range - Create a Style object and set the wrapping text
attribute e.g
style.IsTextWrapped to set to true. Also, create the StyleFlag object
to make the corresponding wrapping text attribute on i.e.
StyleFlag.WrapText = True. Now create a Range of cells object use range.ApplyStyle() to apply
the style giving the style object and styleflag object as parameters
for the method.

6) For Cells collection - Create a Style object and set the wrapping text
attribute e.g
style.IsTextWrapped to set to true. Also, create the StyleFlag object
to make the corresponding wrapping text attribute on i.e.
StyleFlag.WrapText = True. Now use cells.ApplyStyle() to apply
the style giving the style object and styleflag object as parameters
for the method.

If any case (for your scenario) from the above cases fails, we will surely fix it.
I think we need your template project to reproduce the issue as we could not find your mentioned issue. So, we really appreciate if you could create sample application with template file and data to post it here for analysis.

"Side question: Does Aspose.Cell have a function to remove Smart characters or handle them?"
Could you elaborate what do you mean by Smart characters, so that we can understand you.


Thank you and we appreciate your help in this regard.

5

Amjad,

Thank you for helping me work through this issue. The issue lied in the template code logic and I have been able to correct.

Regarding the Smart characters. My client mostly copied and paste content from MS Word or Outlook and paste it into text field. That content might have carriage returns and they show up as square box when displayed in the rendered Aspose Excel document. I was just wondering if there might be some sort of function that I could wrap around .PutValue to remove those types of characters.

Thanks again.

6

Hi,

Thanks for explaining the smart characters.

We will get back to you for your query soon.

Thank you.

7

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Please post a sample template file with smart characters. This will help us figure out your issue soon.

Thank You & Best Regards,