This project has moved. For the latest updates, please go here.

Unreadable content when creating a table with header columns that contain newline

Feb 17, 2016 at 2:40 PM

I have created a custom table within a worksheet where multiple header columns contain a newline character with wraptext turned on the for the column. When I generate a spreadsheet based on the data excel finds unreadable content.

Through a quick look at the source and analyzing the sheet content it appears that the newline character is getting encoded with a _x005F_x005F within the contents for the cell and this is causing the bad data. I believe this is due to the fact that the ExcelEncodeString function in ExcelWorkBook.cs is expecting to read a \n for the newline character but the newline has already been converted to unicode _x000A and that value meets the regular expression which encodes it with _x005F_x005F.

I have tried specifying newline in different formats in c# (unicode etc) to no avail. If anyone else has encountered this problem or can offer any insights then it would be greatly appreciated



eg. Header column data for table: Cost\n2016-2\nTest

I am generating my worksheet data cell by cell dynamically by setting the .Value property on each cell. After the cells with data have been filled then I am creating a new table based on the entire range of cells within my spreadsheet as below:

var range = m_workSheet.Cells[1, 1, rowIndex - 1, columnsInfo.Count];
        var table = m_workSheet.Tables.Add(range, "table1");
        table.ShowTotal = false;
        table.TableStyle = TableStyles.Light11;