IsTextWrapped Issue


#1

Greetings,

I’m using version 3.30 and I’m having trouble getting text to wrap. In my code I’ve tried using:

<br /><font size="2">cells.SetColumnWidth(cells.ColumnNameToIndex("F"), 30)<br />cells.Columns(cells.ColumnNameToIndex("F")).Style.IsTextWrapped = </font><font color="#0000ff" size="2">True</font><br />

I make this call before importing the data from a datatable. Should I try doing this after importing the data instead?

I have also tried to use a style like so without success:

<br /><font size="2">Index = Excel.Styles.Add()<br />Style = Excel.Styles(Index)<br />Style.Name = "Wrap"<br />Style.IsTextWrapped = </font><font color="#0000ff" size="2">True<br /></font><font size="2">Range = cells.CreateRange("F3", "F4")<br />Range.Style = Excel.Styles("Wrap")</font><br />

Any assistance you can provide will be appreciated. Thanks!


#2

I tried your code and all work fine. Please right-click column F and choose “Format Cells”. In “Alignment” tab, you will see “Wrap Text” option is checked.

Please note this property only take effects if text is wider than column width or you explicitly put new line character in text.

And which version of Aspose.Excel are you using? Is it the latest version?


#3

Thanks for the reply Laurence. I checked the cell on my spreadsheet and wrap text was not checked. I am attaching the spreadsheet that is generated by the component. If you would like I can post all of the code I use to create it.

I have been using version 3.3.0 but will download and install 3.3.3.


#4

Please post all of your code. Thank you.
I will check this issue right now.


#5

Here's the code I use to create the spreadsheet. If you can see ways I can improve it or create it with less lines, please feel free to point those out Smile

Also, is there a way to specify that the spreadsheet open in a new browser window?

Dim Excel As New Excel
Dim License As New Aspose.Excel.License
License.SetLicense(ConfigurationSettings.AppSettings("Path") & "Aspose.Excel.lic")
Dim dt2 As DataTable
Dim Cells As Cells
Dim intTemp As Integer
Dim Index As Integer

For Each dt2 In DS.Tables
intTemp = Excel.Worksheets.Add(SheetType.Worksheet)
Dim Sheet As Worksheet
Dim Range As Range
Dim Style As Aspose.Excel.Style

Sheet = Excel.Worksheets(intTemp)
Sheet.PageSetup.Zoom = 40
Sheet.PageSetup.LeftMargin = 0
Sheet.PageSetup.RightMargin = 0
Sheet.PageSetup.Orientation = PageOrientationType.Landscape
Sheet.PageSetup.CenterHorizontally = True
Sheet.PageSetup.PaperSize = PaperSizeType.PaperLegal
Sheet.PageSetup.PrintTitleRows = "$1:$2"
Sheet.Zoom = 75

Cells = Excel.Worksheets(intTemp).Cells

'Bold Style
Index = Excel.Styles.Add()
Style = Excel.Styles(Index)
Style.Name = "BoldStyle"
Style.Font.IsBold = True
Style.HorizontalAlignment = TextAlignmentType.Center
Style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin

'Wrap Style
Index = Excel.Styles.Add()
Style = Excel.Styles(Index)
Style.Name = "Wrap"
Style.IsTextWrapped = True
Style.HorizontalAlignment = TextAlignmentType.Center

'Border Stuff
Index = Excel.Styles.Add
Style = Excel.Styles(Index)
Style.Name = "Borders"
Style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin

'Left Aligned Border Stuff
Index = Excel.Styles.Add
Style = Excel.Styles(Index)
Style.Name = "LeftBorder"
Style.HorizontalAlignment = TextAlignmentType.Left
Style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin

'Date Stuff
Index = Excel.Styles.Add
Style = Excel.Styles(Index)
Style.Number = 14
Style.Name = "DateStyle"
Style.Custom = "dd-MMM-yyyy"
Style.HorizontalAlignment = TextAlignmentType.Center
Style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
Style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin
Style.HorizontalAlignment = TextAlignmentType.Center

'Set Values
Cells("B1").PutValue("B#")
Cells("C1").PutValue("P/N")
Cells("D1").PutValue("Nomenclature")
Cells("E1").PutValue("Calibration TO")
Cells("F1").PutValue("Issues")
Cells("G1").PutValue("Review / Analysis Started")
Cells("G2").PutValue("Baseline")
Cells("H2").PutValue("Scheduled")
Cells("I2").PutValue("Actual")
Cells("J1").PutValue("Review / Analysis Completed")
Cells("J2").PutValue("Baseline")
Cells("K2").PutValue("Scheduled")
Cells("L2").PutValue("Actual")
Cells("M1").PutValue("AFTO 45")
Cells("M2").PutValue("Yes/No")
Cells("N1").PutValue("Shop Review/PMEL Prepare AFTO 45")
Cells("N2").PutValue("Baseline")
Cells("O2").PutValue("Scheduled")
Cells("P2").PutValue("Actual")
Cells("Q1").PutValue("AFMETCAL Review & Authorize")
Cells("Q2").PutValue("Baseline")
Cells("R2").PutValue("Scheduled")
Cells("S2").PutValue("Actual")
Cells("T1").PutValue("PMEL Return to Shop")
Cells("T2").PutValue("Baseline")
Cells("U2").PutValue("Scheduled")
Cells("V2").PutValue("Actual")
Cells("W1").PutValue("AFMC 134")
Cells("W2").PutValue("Yes/No")
Cells("X1").PutValue("Shop Fill out AFMC Form 134")
Cells("X2").PutValue("Baseline")
Cells("Y2").PutValue("Scheduled")
Cells("Z2").PutValue("Actual")
Cells("AA1").PutValue("Calibration Started")
Cells("AA2").PutValue("Baseline")
Cells("AB2").PutValue("Scheduled")
Cells("AC2").PutValue("Baseline")
Cells("AD1").PutValue("Calibration Completed")
Cells("AD2").PutValue("Baseline")
Cells("AE2").PutValue("Scheduled")
Cells("AF2").PutValue("Actual")

'Set Column Width
Cells.SetColumnWidth(Cells.ColumnNameToIndex("D"), 25)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("E"), 25)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("F"), 30)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("G"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("H"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("I"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("J"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("K"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("L"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("M"), 15)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("N"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("O"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("P"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("Q"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("R"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("S"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("T"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("U"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("V"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("W"), 15)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("X"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("Y"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("Z"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("AA"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("AB"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("AC"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("AD"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("AE"), 12)
Cells.SetColumnWidth(Cells.ColumnNameToIndex("AF"), 12)

'Set Wrapped columns
Cells.Columns(Cells.ColumnNameToIndex("D")).Style.IsTextWrapped = True
Cells.Columns(Cells.ColumnNameToIndex("E")).Style.IsTextWrapped = True
Cells.Columns(Cells.ColumnNameToIndex("F")).Style.IsTextWrapped = True

Range = Cells.CreateRange("A1", "AF2")
Range.Style = Excel.Styles("BoldStyle")

'Merge Ranges
Range = Cells.CreateRange("B1", "B2")
Range.Merge()
Range = Cells.CreateRange("C1", "C2")
Range.Merge()
Range = Cells.CreateRange("D1", "D2")
Range.Merge()
Range = Cells.CreateRange("E1", "E2")
Range.Merge()
Range = Cells.CreateRange("F1", "F2")
Range.Merge()
Range = Cells.CreateRange("G1", "I1")
Range.Merge()
Range = Cells.CreateRange("J1", "L1")
Range.Merge()
Range = Cells.CreateRange("N1", "P1")
Range.Merge()
Range = Cells.CreateRange("Q1", "S1")
Range.Merge()
Range = Cells.CreateRange("T1", "V1")
Range.Merge()
Range = Cells.CreateRange("X1", "Z1")
Range.Merge()
Range = Cells.CreateRange("AA1", "AC1")
Range.Merge()
Range = Cells.CreateRange("AD1", "AF1")
Range.Merge()
Range = Cells.CreateRange("A1", "A2")
Range.Merge()

'Begin Date Formatting
Range = Cells.CreateRange("G3", "L3")
Range.Style = Excel.Styles("DateStyle")
Range = Cells.CreateRange("N3", "V3")
Range.Style = Excel.Styles("DateStyle")
Range = Cells.CreateRange("X3", "AF3")
Range.Style = Excel.Styles("DateStyle")
Excel.Worksheets(intTemp).Name = dt2.TableName.ToString

'LeftBorder
Range = Cells.CreateRange("A3", "F3")
Range.Style = Excel.Styles("LeftBorder")
'LeftBorder
Range = Cells.CreateRange("M3", "M3")
Range.Style = Excel.Styles("LeftBorder")
'LeftBorder
Range = Cells.CreateRange("W3", "W3")
Range.Style = Excel.Styles("LeftBorder")

Cells.ImportDataTable(dt2, False, 2, 0)
Cells.Columns(Cells.ColumnNameToIndex("F")).Style.IsTextWrapped = True
Next
Excel.Worksheets.RemoveAt("Sheet1")
Excel.Save("CalibrationReport.xls", SaveType.OpenInBrowser, FileFormatType.Default, Me.Response)


#6

There are several ways to set cells’ style: set a column, set a row, set a range and set a cell. Currently Aspose.Excel doesn’t combine them all as in MS Excel.

MS Excel can combine them all because users manually set those style. The performance issue is not critical. However, Aspose.Excel is running in a program so performance is very important.

The priority of setting styles are list as following, from low to high:
set a column, set a row, set a range and set a cell

If you set a cell’s style, all previous set style will be kept. However, if you set a range’s style, other style set by column or row are overrided.

So please add the following extra code in your program:

Cells.ImportDataTable(dt2, False, 2, 0)
Cells.Columns(Cells.ColumnNameToIndex(“F”)).Style.IsTextWrapped = True

’Extra code added here
Dim i As Integer
For i = 1 To dt2.Rows.Count
Cells(i, Cells.ColumnNameToIndex(“F”)).Style.IsTextWrapped = True
Next


#7

Laurence,

I’ve added the code you recommened and when I check the spreadsheet the properyt for wrapping text is checked but in the spreadsheet the cell does not show all of the text. How can I have the text wrap and have the cell expand to show it all?

I’m attaching the latest spreadsheet that I was able to generate.


#8

There’s another issue I would like to know how to fix. Please take a look at column “D” in the attached spreadsheet, how can I make this column automatically resize so that text does not run over into column “E”?

Also, is it possible to force the spreadsheet to open in a new browser window?


#9

Please use Worksheet.AutoFitRow and Worksheet.AutoFitColumn method. Then your data will show as your wish.

To force the spreadsheet to open in a new browser window, you can use Response.Redirect method to redirect to a new aspx page. In that page’s Page_Load event, call Excel.Save method to send file to client browser.