Adding line breaks in Excel output file in .NET

[mcausi]

I’m taking database records and trying to output them to an excel file using the following code:

int rowsImported = sheet.Cells.ImportDataReader(sqlReader, true, 1, 0, false, “”, false);

Some of my data should have line breaks within the cell at pre-determined locations.

For example, this could be a cell value:

Row 1 \n Row 2\n Row 3

However, I’m trying to get the output to break on the \n.

Is there a special character etc that will cause it to break?

[amjad.sahi]

Hi,

Thanks for your query.

Well, once you have imported data to the worksheet using Cells.ImportDataReader() method, you got to specify the text wrapping on for the cell(s)/ range of cells. You may simply create a Style object and set/ specify the wrapping text option on (you may also create the StyleFlag object and make the relevant option on), now apply the style to the cell/ range of cells/column/row etc. Please see the documents for your reference:

Line Breaks and Text Wrapping
Configuring Alignment Settings

Thank you.

[mcausi]

There seems to be a different in the way putValue put data in the cell vs importdatareader.

I can get the putvalue to break on the \n character.

When the importdatareader style is set to wrap, it sets the column to wrap, but does not replace the \n with a break. See attached image of the putvalue vs importdatareader cells.

Here is my code:

Aspose.Cells.Worksheet sheet = excel.Worksheets[0];

int rowsImported = sheet.Cells.ImportDataReader(sqlReader, false, 1, 0, false, “”, false);

Aspose.Cells.Cells cell = sheet.Cells;

cell.SetColumnWidth(1, 35);

cell[0, 1].PutValue(“I am using\nthe latest version of \nAspose.Cells to \ntest this functionality”);

Aspose.Cells.Style style = cell[0, 1].GetStyle();

style.IsTextWrapped = true;

cell[0, 1].SetStyle(style);

//Make Cell’s Text wrap

Aspose.Cells.Style style1 = cell[1, 1].GetStyle();

style1.IsTextWrapped = true;

cell[1, 1].SetStyle(style1);

[amjad.sahi]

Hi,

Thanks for providing us further details and screen shot.

I have evaluated your scenario/ case a bit. You are right, the line break character ‘\n’ works fine while using Cell.PutValue() method but if we have a database table field that has some text with “\n” in it, when we import data from the data source to paste into Excel worksheet, it does not work even though we set the wrapping text option on for the Style object. We need to evaluate your issue in details. We will investigate and look into it and get back to you soon.

Thank you.

[amjad.sahi]

Hi,

I have evaluated your issue further. I observed the issue as you mentioned. I found Aspose.Cells does not replace the ‘\n’ character with line break in the cell when setting wrapping text while applying style/ formatting to the imported data from the database/ data source. We got to investigate if this is an issue with the product or something is changed while importing data from data source to Excel. To test the scenario/ case I used a simple table Employees from the attached “Northwind1.mdb” MS Access database. The second record in the table has “Vice President,\n Sales” (which I manually place “\n” in it) as its Title data. I used the following sample code with the sample database:
e.g
Sample code:

OleDbConnection con = new OleDbConnection(“provider=microsoft.jet.oledb.4.0;data source=e:\test2\Northwind1.mdb”);
con.Open();
OleDbCommand cmd = new OleDbCommand(“Select EmployeeID,Title from Employees”, con);
OleDbDataReader dr = cmd.ExecuteReader();
dr.Read();

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets.Add(“DataSheet”);
sheet.Cells.ImportDataReader(dr, true, 0, 0, true);

Worksheet _worksheet = workbook.Worksheets[“DataSheet”];
//Create a style object
Aspose.Cells.Style style = _worksheet.Cells[1, 1].GetStyle();
//Set wrapping text on
style.IsTextWrapped = true;
//Apply the style to the cell
_worksheet.Cells[1, 1].SetStyle(style); //it does not apply style to B2 cell, it still shows ‘\n’ char in the cell.

_worksheet.AutoFitRow(0);

Cell cell = sheet.Cells[“B14”];
cell.PutValue(“Vice President,\nSales”);
style = _worksheet.Cells[“B14”].GetStyle();
//Set wrapping text on
style.IsTextWrapped = true;
//Apply the style to the cell
_worksheet.Cells[“B14”].SetStyle(style); //It works fine if I used Cell.PutValue method.

_worksheet.AutoFitRow(13);

workbook.Save(“e:\test2\out1importfromdatareader1.xlsx”);
dr.Close();

I have logged a ticket with an id “CELLSNET-43710” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.

[mcausi]

I found that if I put in \r\n instead of just \n, the importdatareader function will break (so long as the istextwrapped is set.

However, it only works if I set the style at a row/column level.

ie.:

Aspose.Cells.Style style1 = cell[1,1].GetStyle();

style1.IsTextWrapped = true;

cell[1,1].SetStyle(style1);

If I try to apply the wrap at a column level it doesn’t seem to work.

ie.

Aspose.Cells.Style style1 = cell[1].GetStyle();

style1.IsTextWrapped = true;

cell[1].SetStyle(style1);

Not sure if this helps you figure out the problem… Also, please at the same time, let me know how I could make it work, so I could apply the “wrap” to the whole column at once.

Thanks

[amjad.sahi]

Hi,

It does not work either even I use “Vice President,\r\n Sales” instead of “Vice President,\n Sales” as the Title field value of the table (in Northwind1.mdb database) in my example in my previous post. Could you spare us little time, so we could evaluate your issue on our end.

Once we have any update on it, we will let you know here.

Thank you.

[mcausi]

Here is my exact code. Notice I have hardcoded a \r\n as the fieldname for now for the B column:

string sql = “SELECT CONVERT(VARCHAR, EventDate, 100) as ‘EventDate’ , ‘first\r\nsecond\r\nthird’ , LogonName FROM EventLog WHERE EventDate BETWEEN @FromDate AND @ToDate”;

SqlDatabase sqlDatabase = new SqlDatabase(DB.ConnectionString);

using (DbCommand sqlCmd = sqlDatabase.GetSqlStringCommand(sql))

{

sqlDatabase.AddInParameter(sqlCmd, “@FromDate”, DbType.DateTime, Convert.ToDateTime(rcFromEventDate.DateInput.SelectedDate));

sqlDatabase.AddInParameter(sqlCmd, “@ToDate”, DbType.DateTime, Convert.ToDateTime(rcToEventDate.DateInput.SelectedDate));

using (IDataReader sqlReader = sqlDatabase.ExecuteReader(sqlCmd))

{

Aspose.Cells.Worksheet sheet = excel.Worksheets[0];

sheet.Cells[“A1”].PutValue("Event Logs between " + Convert.ToDateTime(rcFromEventDate.DateInput.SelectedDate).ToShortDateString() + " and " + Convert.ToDateTime(rcToEventDate.DateInput.SelectedDate).ToShortDateString());

sheet.Cells.Merge(0, 0, 1, 10);

int rowsImported = sheet.Cells.ImportDataReader(sqlReader, false, 1, 0, false, “”, false);

Aspose.Cells.Cells cell = sheet.Cells;

//TODO: update column without going row by row.

for (int i = 1; i <= rowsImported; i++)

{

Aspose.Cells.Style style2 = cell[i, 1].GetStyle();

style2.IsTextWrapped = true;

cell[i, 1].SetStyle(style2);

}

sheet.AutoFitColumns();

string fileName = “EventLog_” + DateTime.Now.ToFileTimeUtc() + “.xlsx”;

string filePath = ConfigurationManager.AppSettings[“TempFileDirectory”].ToString() + fileName;

excel.Save(filePath);

}

}

See my image of the output.

[amjad.sahi]

Hi,

Could you replace the lines of code:

i.e.,

//TODO: update column without going row by row.

for (int i = 1; i <= rowsImported; i++)

{

Aspose.Cells.Style style2 = cell[i, 1].GetStyle();

style2.IsTextWrapped = true;

cell[i, 1].SetStyle(style2);

}

…

with:

Aspose.Cells.Style style2 = excel.CreateStyle();

style2.IsTextWrapped = true;

StyleFlag flag2 = new StyleFlag();

flag2.WrapText = true;

cell.ApplyColumnStyle(1,style2, flag2);

…

if it works fine.

Thank you.

[amjad.sahi]

Hi,

I have evaluate your issue further.

The reason you are getting ‘\n’ characters shown in the cells with their respective value is that when you manually insert ‘\n’ into some filed’s value on the back end, it is actually inserted ‘\n’ instead of “\n”. So, my manually inserted value was "Vice President,\n Sales instead of “Vice President,\n Sales”. To confirm this value has “\n” , you may simply use:

Console.WriteLine(cell[“B2”].StringValue); --> gives the value having ‘\n’ and you won’t see ‘\n’ as first back slash actually inserts ‘’ (i.e., ‘\’ --> ‘’) on the console. Mind you, if the value has single line break character (i.e., ‘\n’), you will not see ‘\n’ on the console. Now the question is how to insert line break on the back end, you should simply use Ctrl + Enter whenever you need to insert line break in your value (for the table’s field) in the database. I have done this for the underlying Title field value in the updated MS Access database (attached) table.

I have re-tested your scenario/ case using my updated database with the following sample code and it works as expected now (I have also attached the output file for your reference here):

e.g

Sample code:

OleDbConnection con = new OleDbConnection(“provider=microsoft.jet.oledb.4.0;data source=e:\test2\Northwind1.mdb”);

con.Open();

OleDbCommand cmd = new OleDbCommand(“Select EmployeeID,Title from Employees”, con);

OleDbDataReader dr = cmd.ExecuteReader();

dr.Read();

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets.Add(“DataSheet”);

sheet.Cells.ImportDataReader(dr, true, 0, 0, true);

Aspose.Cells.Cells cell = sheet.Cells;

Console.WriteLine(cell[“B2”].StringValue);

Worksheet _worksheet = workbook.Worksheets[“DataSheet”];

//Create a style object

Aspose.Cells.Style style = workbook.CreateStyle();

//Set wrapping text on

style.IsTextWrapped = true;

StyleFlag flag = new StyleFlag();

flag.WrapText = true;

//Apply the style to the cell

_worksheet.Cells.ApplyColumnStyle(1,style, flag);

_worksheet.AutoFitRows();

_worksheet.AutoFitColumn(1);

workbook.Save(“e:\test2\outimportfromdatareader1.xlsx”);

dr.Close();

Please check the B2 cell value on the console and in the output Excel file’s sheet, it is fine tuned on both locations.

Hope, this helps now.

Thank you.

[mcausi]

I’m using SQL server as my database, and can’t figure out what ASCII string to insert that would represent the CTRL+ENTER.

I have tried:

‘first’ + CHAR(13) + CHAR(10) + 'second’

but have had no luck with the excel file output. It just literally outputs CHAR… to the cell.

[amjad.sahi]

Hi,

Well, we did test it as:

‘first’ + CHAR(13) + CHAR(10) + 'second’

it works fine. So, we think it should work fine using SQL Server database table on your end, kindly re-test your scenario.

If you still find the issue, kindly provide your generated Excel file (via Aspose.Cells APIs), we will check it soon.

Thank you.

[mcausi]

If I hard code ‘first’ + CHAR(13) + CHAR(10) + ‘second’ in my sql statement select I get it to wrap. However, the autofit columns doesn’t seem to be allowing the space. I’ll attach my excel.

If however, I try to read a database field (without hardcoding CHAR), and the sql table has the literal text “first + CHAR(13) + CHAR(10) + second”, it comes in as the literal text and shows CHAR… in the excel output.

I have only had real success in storing \r\n in the database via a SQL insert (which seems to put in a hidden character in the database) and then having ASPOSE output actually break on that. Do you think that when I store \r\n in the database it is actually storing a "CHAR(13) CHAR(10) in the database as the proper format as expected by ASPOSE excel?

[amjad.sahi]

Hi,

Well, yes, I think you may use ‘\r\n’ escape sequence which actually refers to carriage return (CHAR(13) and line feed (CHAR(10) chars as it is working for you.

Thank you.

[mcausi]

Ok, I shall continue with the method of inserting the\r\n in the database.

Thanks.