Set border removes style wrap text

zinsmeik · April 27, 2004, 2:00am

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)

Laurence · April 27, 2004, 7:00pm

Hi Klaus,

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

Aspose.Cells for .NET (Download | NuGet)

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

Thanks for your cooperation.

zinsmeik · April 28, 2004, 12:46am

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

Laurence · April 28, 2004, 4:41am

Hi Klaus,

I found the problem and will fix it ASAP.

zinsmeik · May 7, 2004, 4:45am

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

Laurence · May 7, 2004, 8:59am

Hi Klaus,

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

Aspose.Cells for .NET (Download | NuGet)

zinsmeik · May 9, 2004, 11:42pm

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

trwatkins · August 24, 2004, 8:48am

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 = “$1 Embarrassed 1”;

///Remove the rest of the sheets

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

{

sheet = excel.Worksheets Idea ;

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);

}

Laurence · August 24, 2004, 5:08pm

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 ++;

}

trwatkins · August 26, 2004, 4:55am

Works perfect! Thank you!!

ahsaniqbalsidiqui · November 12, 2020, 3:04am

@zinsmeik,
Aspose.Cells has replaced Aspose.Excel and provides advanced features to format cells including adding borders. You can add border to a single cell and also to a range of cells. Following is an example where border is added to a range of cells.

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the first (default) worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];

// Accessing the "A1" cell from the worksheet
Cell cell = worksheet.Cells["A1"];

// Adding some value to the "A1" cell
cell.PutValue("Hello World From Aspose");

// Creating a range of cells starting from "A1" cell to 3rd column in a row
Range range = worksheet.Cells.CreateRange(0, 0, 1, 3);

// Adding a thick top border with blue line
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Blue);

// Adding a thick bottom border with blue line
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Blue);

// Adding a thick left border with blue line
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Blue);

// Adding a thick right border with blue line
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thick, Color.Blue);

// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls");

Here is a detailed article about formatting the cells:
Cells Formatting

Here is the link to the latest version of this new product for free trials:
Aspose.Cells for .NET (Latest Version)

A comprehensive detailed solution is available here which contains hundreds of ready to run examples for testing different features of this product.