Free Support Forum - aspose.com

Set border removes style wrap text

Hello,

using Aspose.Excel 1.9.1.3 I have following problem:

The Template file has the cell style “Word Wrap” enable. I insert my table, and draw a border for each row. Then I size them to wake enough room for the text to wrap.
When I run this code the text does not wrap (and the cell format the checkbox “wrap text” is not checked). When I comment line 10 out the text wraps perfectly.
Do I do something wrong ?

Regards,

Klaus

1 Dim Excel As Excel = New Excel(licenseFile, Page)
2 Dim designerFile As String = mapPath + "\exportTemplate.xls"
3 Dim noOfRows As Integer ’ Number of Rows of the datatable
4 Excel.Open(designerFile)
5 Dim sheet As Worksheet = Excel.Worksheets(0)
6 sheet.Name = "ArchiveBox"
7 noOfRows = mSearchResult.Tables.Item(0).Rows.Count()
8 sheet.Cells.ImportDataTable(mSearchResult.Tables.Item(0), False, 5, 1)
9 For i As Integer = 4 To noOfRows + 4
10 Excel.Worksheets(0).Cells.CreateRange(i, 1, 1, 12).SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black)
11 Next
12 ’ size rows
13 Excel.Worksheets(0).Cells.CreateRange(4, 1, 12, noOfRows + 4).RowHeight = 35
14 Excel.Save(“ArchiveBox.xls”, SaveType.OpenInBrowser, FileFormatType.Default, Page.Response)

Hi Klaus,

I tested your code and it worked fine in my machine.
So please download the latest hotfix and have a try.

If problem still exists, could you email your template file to me?

Thanks for your cooperation.

Hi,

thanks for your fast reply. I have installed the latest hotfix and the problem still exist. I will email you my template and the results with border and without border.

Klaus

Hi Klaus,

I found the problem and will fix it ASAP.

Hi,

I have installed the Hotfix 1.9.3 and tried again. the problem still exists. When I set a border the cell format “Wrap text” will be unchecked. Can you check this again ? Do you need the template again ?

King Regards,

Klaus

Hi Klaus,

It’s fixed in hotfix 1.9.3.1. Please download it and have a try.

Hi,

thanks again …now it works fine.

Big compliment to the fast support, for other software I wait 6 mounth or more to fix a problem !

Klaus

I’m using version 2.2.3.1 and it’s still removing the text wrap. Here’s the code:

private void CreateOpenItemsReport(Excel excel)

{

try

{

string designerFile = System.Configuration.ConfigurationSettings.AppSettings.Get(“AsposeFile”);

excel.Open(designerFile);

GetWorkgroupList();

this.dataTbl.Reset();

this.oleDbComm = this.oleDbConn.CreateCommand();

this.oleDbComm.CommandText = “p_sel_CASH_LOG_RPT_OPEN_ITEMS”;

this.oleDbComm.CommandType = CommandType.StoredProcedure;

this.oleDbComm.Parameters.Add(new OleDbParameter("@BEGIN_DATE",OleDbType.VarChar,30));

this.oleDbComm.Parameters.Add(new OleDbParameter("@END_DATE", OleDbType.VarChar,30));

this.oleDbComm.Parameters.Add(new OleDbParameter("@LOG_TYPE",OleDbType.Integer));

this.oleDbComm.Parameters.Add(new OleDbParameter("@FROM_REF_TO", OleDbType.Numeric));

this.oleDbComm.Parameters.Add(new OleDbParameter("@TO_REF_TO", OleDbType.Numeric));

this.oleDbComm.Parameters.Add(new OleDbParameter("@LOGIN_ID", OleDbType.VarChar, 25));

///If the begin date equals the end date, add the hour and minutes

///so we can get the whole day.

switch (this.txtBeginDt.Text == this.txtEndDt.Text)

{

case true:

this.txtBeginDt.Text = this.txtBeginDt.Text + " 00:00";

this.txtEndDt.Text = this.txtEndDt.Text + " 23:59";

break;

case false:

break;

}

oleDbComm.Parameters["@BEGIN_DATE"].Value = txtBeginDt.Text;

oleDbComm.Parameters["@END_Date"].Value = txtEndDt.Text;

oleDbComm.Parameters["@LOG_TYPE"].Value = GetLogType();

oleDbComm.Parameters["@FROM_REF_TO"].Value = 1;

oleDbComm.Parameters["@TO_REF_TO"].Value = j;

oleDbComm.Parameters["@LOGIN_ID"].Value = Session.SessionID;

//This portion applies to all logs

this.oleDbAdapter = new OleDbDataAdapter(this.oleDbComm);

this.oleDbAdapter.Fill(this.dataTbl);

Worksheet sheet = excel.Worksheets[“Open Items ReportRegBnk”];

sheet = excel.Worksheets[“Open Items ReportRegBnk”];

sheet.Name = “Open Items Report1”;

Cells cells = sheet.Cells;

sheet.Cells.ImportDataTable(this.dataTbl, false, 1,0);

//Create style

int styleIndex;

Aspose.Excel.Style style;

styleIndex = excel.Styles.Add();

style = excel.Styles[styleIndex];

Border borderStyle = style.Borders[BorderType.TopBorder];

borderStyle.LineStyle = CellBorderType.Thin;

borderStyle.Color = Color.Black;

Border borderStyle2 = style.Borders[BorderType.BottomBorder];

borderStyle2.LineStyle = CellBorderType.Thin;

borderStyle2.Color = Color.Black;

Border borderStyle3 = style.Borders[BorderType.LeftBorder];

borderStyle3.LineStyle = CellBorderType.Thin;

borderStyle3.Color = Color.Black;

Border borderStyle4 = style.Borders[BorderType.RightBorder];

borderStyle4.LineStyle = CellBorderType.Thin;

borderStyle4.Color = Color.Black;

int f = 2;

for(int i = 1; i <= this.dataTbl.Rows.Count; i ++)

{

cells[i,15].Formula = “=N” + f + " & O" + f;

cells[i,15].Style = style;

f ++;

}

sheet.PageSetup.SetHeader(1, "&“Tahoma,Bold” &12 " + "Open Items Report\rOpen Items from " + txtBeginDt.Text + " to " + txtEndDt.Text + “\r” + slogTypeName);

//Set column width for workgroup

sheet.AutoFitColumn(8);

sheet.Cells.SetColumnWidth(13,40); //Comments column

//Auto fit rows

for(int i = 0; i < dataTbl.Rows.Count+1; i++)

{

sheet.AutoFitRow(i);

}

sheet.PageSetup.PrintTitleRows = “$1Embarrassed1”;

///Remove the rest of the sheets

for(int i = 0; i < excel.Worksheets.Count ; i ++)

{

sheet = excel.WorksheetsIdea;

if(sheet.Name != “Open Items Report1”)

{

excel.Worksheets.RemoveAt(i);

i --;

}

}

//After we populate the data table, we need to delete the

//records that were added to REF_CORP_CAGE_LOG_RPT

DeleteFromRefTable(Session.SessionID, 1, j, Label1.Text);

excel.Save(“OpenItems1.xls”, SaveType.OpenInExcel, FileFormatType.Default, Response);

}

catch(Exception ex)

{

throw new Exception(“Create Open Items Report”,ex);

}

}

You can use two approaches to format a cell:

1: Use Cell.Style property to format a cell.

cell1.Style.Borders[BorderType.LeftBorder].Color = Color.Red;

cell1.Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;

In this case, other formatting set in the designer file is preserved, including the wrap text setting.

2: Create a Style object and assign it to a cell.

You used this method in your code. This method is used to set same formatting to different cells. It can greatly speed up your program and save memory. However, preset formatting is not preserved for all the result cells will have same formatting. You have to set the wrap text property in your code.

//Create style

int styleIndex;

Aspose.Excel.Style style;

styleIndex = excel.Styles.Add();

style = excel.Styles[styleIndex];

Border borderStyle = style.Borders[BorderType.TopBorder];

borderStyle.LineStyle = CellBorderType.Thin;

borderStyle.Color = Color.Black;

Border borderStyle2 = style.Borders[BorderType.BottomBorder];

borderStyle2.LineStyle = CellBorderType.Thin;

borderStyle2.Color = Color.Black;

Border borderStyle3 = style.Borders[BorderType.LeftBorder];

borderStyle3.LineStyle = CellBorderType.Thin;

borderStyle3.Color = Color.Black;

Border borderStyle4 = style.Borders[BorderType.RightBorder];

borderStyle4.LineStyle = CellBorderType.Thin;

borderStyle4.Color = Color.Black;

style.IsTextWrapped = true;

int f = 2;

for(int i = 1; i <= this.dataTbl.Rows.Count; i ++)

{

cells[i,15].Formula = "=N" + f + " & O" + f;

cells[i,15].Style = style;

f ++;

}


Works perfect! Thank you!!